引用
using System.Data.SqlClient;
using System.Data.OleDb;
获取Excel一个工作表数据
public static DataTable GetDataTableFromExcel(string SourceFilePath)
{
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + SourceFilePath + ";" +
"Extended Properties=Excel 8.0;";
using (OleDbConnection cn = new OleDbConnection(ConnectionString))
{
cn.Open();
DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the first worksheet.");
}
//修改此处可遍历Excel表格中各个工作表
string WorkSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheetName + "]", cn);
DataTable dt = new DataTable(WorkSheetName);
da.Fill(dt);
return dt;
}
}
导入DataTable到SQL Server
private void simpleButton2_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
dt=GetDataTableFromExcel(openFileDialog1.FileName);
}
DataTable dtTarget = new DataTable();
dtTarget.Columns.Add("ID", typeof(string));
dtTarget.Columns.Add("Number", typeof(string));
dtTarget.Columns.Add("Name", typeof(string));
dtTarget.Columns.Add("SpecialJob_ID", typeof(string));
dtTarget.Columns.Add("Price", typeof(float));
dtTarget.Columns.Add("SessionTime", typeof(string));
dtTarget.Columns.Add("LabourCount", typeof(float));
dtTarget.Columns.Add("Department_ID", typeof(string));
//构建与目标表列数量和数据类型相同的DataTable
foreach (DataRow dr in dt.Rows)
{
if (Convert.ToString(dr[0]) == "") break;
DataRow drnew = dtTarget.NewRow();
drnew[0] = System.Guid.NewGuid().ToString();
drnew[1] = Convert.ToString(dr[4]).Trim().Replace(" ", "");
drnew[2] = dr[0];
drnew[4] = Convert.ToDouble(dr["单价"]);
drnew[5] = dr[1];
drnew[6] = Convert.ToDouble(dr["用工"]);
drnew[7] = "201208111111356afb5fc8-9cb5-405c-926f-07cc94b39d49";
dtTarget.Rows.Add(drnew);
}
using (SqlConnection destinationConnection = new SqlConnection(SQLServerHelper.ConnectionString))
{
destinationConnection.Open();
// Set up the bulk copy object.
// Note that the column positions in the source
// data reader match the column positions in
// the destination table so there is no need to
// map columns.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = "dbo.Process";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dtTarget);
}
catch (Exception ex)
{
}
finally
{
// The SqlBulkCopy object is automatically closed
// at the end of the using block.
}
}
}
}