Excel匯入到SQLServer的原始碼實現-辦公室軟體-PHP中文網

Excel匯入到SQLServer的原始碼實現

WBOY

發布： 2024-01-23 21:45:10

轉載

1044 人瀏覽過

一個能將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) & ＂', ＂

sql = ＂insert into ＂ & FormName & ＂ values(＂ & sql & ＂'＂ & Cells(i, RowCount) & ＂')＂

b.Open Source:=sql, ActiveConnection:=a

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

Excel匯入到SQLServer的原始碼實現

另外需要在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