应用oledb导出标准格式EXCEL,可以直接返回去导入的那种
/// <summary>
/// 导出excel
/// </summary>
/// <param name="dt">DataTable格式的数据</param>
/// <param name="FileName">要导出的文件名</param>
private void ExportExcel(DataTable dt,string FileName)
{
if (dt == null || dt.Rows.Count < 0) //dt.Rows.Count=0导出列名。
{
return;
}
//临时文件
string tempFile = Server.MapPath("~/download/" + Guid.NewGuid() + ".xls");
//File.Copy(Server.MapPath("download/demo.xls"), tempFile);
//使用OleDb连接
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tempFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'");
StringBuilder head = new StringBuilder();
StringBuilder values = new StringBuilder();
using (con)
{
con.Open();
//创建Sheet,创建列
for (int i = 0; i < dt.Columns.Count; i++)
{
head.Append(dt.Columns[i].ColumnName + " VarChar,");
values.Append("@" + dt.Columns[i].ColumnName + ",");
}
//string head = "[ID] VarChar,[名称] VarChar,[时间] VarChar";
OleDbCommand cmdCreate = new OleDbCommand("CREATE TABLE Sheet1 (" + head.ToString().TrimEnd(',') + ")", con);
cmdCreate.ExecuteNonQuery();
//插入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
OleDbCommand cmd = new OleDbCommand(@"INSERT INTO [Sheet1$] VALUES(" + values.ToString().TrimEnd(',') + ")", con);
for (int j = 0; j < dt.Columns.Count; j++)
{
cmd.Parameters.AddWithValue("@" + dt.Columns[j].ColumnName, dt.Rows[i][j].ToString());
}
cmd.ExecuteNonQuery();
}
}
Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(FileName) + ".xls");
Response.Charset = "GB2312";
GC.GetTotalMemory(false);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.GetTotalMemory(true);
Response.BinaryWrite(File.ReadAllBytes(tempFile));
File.Delete(tempFile);
}