给你一个直接在Excel内上传数据到SqlServer的代码:
可适当修改,
FormName为数据库内的表格,
DBString为数据库连接字段,
Sub UploadData()
If FormName = "" Then Exit Sub
Dim a As New ADODB.Connection
Dim b As New ADODB.Recordset
a.Open DBString
RowCount = Application.CountA(Rows("1:1"))
For i = 2 To application.counta(range("a:a"))
sql = ""
Set b = Nothing
For j = 1 To RowCount - 1
sql = sql & "'" & Cells(i, j) & "', "
Next
sql = "insert into " & FormName & " values(" & sql & "'" & Cells(i, RowCount) & "')"
b.Open Source:=sql, ActiveConnection:=a
Next
MsgBox "OK!"
End Sub
方法是多样的,找到一个适合的即可,
要想完成你的任务,你需要了解数据库的连接属性,如下为连接字段,可根据你的实际情况来修改,基本参数的设置要靠自己,
Provider=SQLOLEDB.1;Persist Security Info=False;User id=md-inspection;PWD=mdOL20!31)25;Initial Catalog=Online_Inspection;Data Source=Cduvmdb01,1433
另外需要在VBE页面添加相关引用:
环境:c#.2005+Access+ Sql
一、把DataTable插入数据库
public static void DataTableToDB()
{
string _strExcelFileName = @"D:example.xls";
DataTable dtExcel = ExcelToDataTable(_strExcelFileName,"Sheet1");
for (int i = 0; i
{
InsertDataToAccess(dtExcel.Rows[i][0].ToString(), float.Parse(dtExcel.Rows[i][1].ToString()));
}
}
二、把Excel数据读入DataTable
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" +"Extended Properties=Excel 5.0;";
string strExcel = string.Format("select * from [{0}$]", strSheetName);
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
conn.Close();
}
return ds.Tables[strSheetName];
}
三、向Access数据库表插入数据
public static void InsertDataToAccess(string _strPara,float _fPara)
{
OleDbConnection oleDbConn = new OleDbConnection();
oleDbConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:ExcelData.mdb;User Id=admin;Password=;";
oleDbConn.Open();
string strInsertString = "INSERT INTO tb_excelData (strCollumn1,fCollumn2) VALUES (@strCollumn1,@fCollumn2)";
OleDbCommand oComm = new OleDbCommand(strInsertString, oleDbConn);
oComm.Parameters.Add("@strCollumn1", OleDbType.Char , 50);
oComm.Parameters["@strCollumn1"].Value = _strPara;
oComm.Parameters.Add("@fCollumn2", OleDbType.Double);
oComm.Parameters["@fCollumn2"].Value = _fPara;
ocomm.ExecuteNonQuery();
oleDbConn.Close();
}
代码如下:
Sub 文件合并()
Dim wb As Workbook, sh As Worksheet, pT As String, wb2 As Workbook, t
t = Timer
'检测是否有无关工作簿被打开
If Workbooks.Count > 1 Then
MsgBox "关闭打开的其他工作簿"
Exit Sub
End If
'指定文件所在文件夹
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then Exit Sub '如果按了取消或X关闭了对话框,则直接退出
pT = .SelectedItems(1)
End With
Application.ScreenUpdating = False '关闭屏幕刷新
Application.DisplayAlerts = False '关闭对话框
'创建一个新工作表,名为New.xls
Set wb = Workbooks.Add
shJS = wb.Worksheets.Count
fn = Dir(pT & "*.xls")
While fn ""
If fn = wb.Name Then GoTo gg
i = i + 1
If i > shJS Then
Set sh = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
End If
Set wb2 = Workbooks.Open(fn, ReadOnly = True)
wb2.Worksheets(1).Cells.Copy wb.Worksheets(i).Cells
wb.Worksheets(i).Name = Left(fn, Len(fn) - 4)
wb2.Close
gg:
fn = Dir
Wend
wb.SaveAs pT & "new.xls"
wb.Close
Application.ScreenUpdating = True '打开屏幕刷新
Application.DisplayAlerts = True '打开对话框
MsgBox "共用时" & Timer - t & "秒。生成新文件new.xls"
End Sub
数据库中表A,字段为A1,A2,A3,a4,a5
电子表格 d:shuju.xls
包含项目b1,b2,b3,b4,b5
insert into dbo.A
(a1,a2,a3,a4,a5)
SELECT b1,b2,b3,b4,b5
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source=d:shuju.xls;Extended Properties=EXCEL 8.0' )...[Sheet1$] Rowset_1
这样就可以导入,当然也可以使用控件TXLSFile,如需要向我可向我索要
mchestnut@163.com
以上是Excel导入到SQLServer的源代码实现的详细内容。更多信息请关注PHP中文网其他相关文章!