//测试,将excel中的sheet1导入到sqlserver中 private void run_Click(object sender, EventArgs e) { System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog(); if (fd.ShowDialog() == DialogResult.OK) { DataSet ds= TransferData(fd.FileName, "sheet1"); AddBatch(ds.Tables[0]); } } public DataSet TransferData(string excelFile, string sheetName) { DataSet ds = new DataSet(); try { //获取全部数据 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;"; if (excelFile.ToLower().IndexOf(".xlsx") > 0 && excelFile.ToLower().EndsWith("xlsx")) { strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelFile + "';Extended Properties='Excel 12.0;HDR=YES'"; } else if (excelFile.ToLower().IndexOf(".xls") > 0 && excelFile.ToLower().EndsWith("xls")) { strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelFile + "';Extended Properties='Excel 8.0;HDR=YES;'"; } else { return null; } OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", sheetName); myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, sheetName); return ds; } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } return null; } /// <summary> /// 批量添加 /// </summary> /// <param name="dt"></param> public void AddBatch(DataTable dt) { SqlBulkCopyColumnMapping[] sc = new SqlBulkCopyColumnMapping[] { new SqlBulkCopyColumnMapping(0, "site"), new SqlBulkCopyColumnMapping(1, "goodslocationid"), new SqlBulkCopyColumnMapping(2, "area")}; DbHelperSQL.TransferData(dt, "wms_goodsLocation", sc); } public static void TransferData(DataTable dtb, string tableName, SqlBulkCopyColumnMapping[] sbccm) { using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) { bcp.BatchSize = 100;//每次传输的行数 bcp.DestinationTableName = tableName;// 目标表 foreach(SqlBulkCopyColumnMapping s in sbccm) bcp.ColumnMappings.Add(s); bcp.WriteToServer(dtb); } }