Excel导入到SQLServer的源代码实现

Excel导入到SQLServer的源代码实现

给你一个直接在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 Excel导入数据库的源码

环境：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();

}

如何多个excel数据文件批量导入到另一个excel文件表中源代码

代码如下：

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

从excel往delphi里导入数据完整的代码

数据库中表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中文网其他相关文章！