这是我做的导出,代码可否优化 DataTable table; private OleDbDataAdapter oadp; int pagesize = 100000; private void LoadData() { Stopwatch sw = new Stopwatch(); sw.Start(); string strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Files/201003乘用车.xlsx") + ";Extended Properties=\"Excel 12.0;HDR=YES\""; OleDbConnection ocn = new OleDbConnection(strconn); ocn.Open(); OleDbDataAdapter oadp = new OleDbDataAdapter("select * from [乘用车$]", ocn); table = new DataTable(); oadp.Fill(table);
int len = table.Rows.Count; int page = len / pagesize + 1; if (len % pagesize == 0) page--; for (int i = 0; i < page; i++) { PageControl(table, i); } sw.Stop(); Response.Write(sw.ElapsedMilliseconds); } private void PageControl(DataTable table, int page) { int start = (page) * pagesize; int end = (page + 1) * pagesize; if (end > table.Rows.Count) end = table.Rows.Count; SqlDataAdapter da = new SqlDataAdapter("select top 0 * from pcar", "server=.\\sqlexpress;database=car;uid=sa;pwd=lyk"); SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);//这句很重要 DataSet ds_xls = new DataSet(); da.Fill(ds_xls); DataTable dt = ds_xls.Tables[0]; for (int i = start; i < end; i++) { dt.Rows.Add(table.Rows[i].ItemArray); } da.Update(dt);//在此处更新数据 }//测试结果是:927488条,443s 和35640条,13s 差不多,大概是1s3000条不到
DataTable table; private OleDbDataAdapter oadp;
int pagesize = 100000;
private void LoadData()
{
Stopwatch sw = new Stopwatch();
sw.Start();
string strconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("Files/201003乘用车.xlsx") + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
OleDbConnection ocn = new OleDbConnection(strconn); ocn.Open();
OleDbDataAdapter oadp = new OleDbDataAdapter("select * from [乘用车$]", ocn);
table = new DataTable();
oadp.Fill(table);
int len = table.Rows.Count;
int page = len / pagesize + 1;
if (len % pagesize == 0)
page--;
for (int i = 0; i < page; i++)
{
PageControl(table, i);
}
sw.Stop();
Response.Write(sw.ElapsedMilliseconds);
} private void PageControl(DataTable table, int page)
{
int start = (page) * pagesize;
int end = (page + 1) * pagesize;
if (end > table.Rows.Count)
end = table.Rows.Count;
SqlDataAdapter da = new SqlDataAdapter("select top 0 * from pcar", "server=.\\sqlexpress;database=car;uid=sa;pwd=lyk");
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);//这句很重要
DataSet ds_xls = new DataSet();
da.Fill(ds_xls); DataTable dt = ds_xls.Tables[0];
for (int i = start; i < end; i++)
{
dt.Rows.Add(table.Rows[i].ItemArray);
}
da.Update(dt);//在此处更新数据
}//测试结果是:927488条,443s 和35640条,13s 差不多,大概是1s3000条不到