首页 >软件教程 >办公软件 >Excel导入到SQLServer的源代码实现

Excel导入到SQLServer的源代码实现

WBOY
WBOY转载
2024-01-23 21:45:101037浏览

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页面添加相关引用:

Excel导入到SQLServer的源代码实现

一个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中文网其他相关文章!

声明:
本文转载于:docexcel.net。如有侵权,请联系admin@php.cn删除