将页面中查询出来的数据导入到Excel中:
string strExcelFileName = Path.GetTempPath() + fileName + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
//设置导出所有数据
//打开Microsoft.Jet.OleDb.4.0连接
string oldSTR = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + strExcelFileName + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";
using (OleDbConnection oleDbConn = new OleDbConnection(oldSTR))
{
OleDbCommand oleDbCmd = new OleDbCommand();
oleDbConn.Open();
OleDbTransaction dbtran = oleDbConn.BeginTransaction();
oleDbCmd.CommandType = CommandType.Text;
oleDbCmd.Connection = oleDbConn;
oleDbCmd.Transaction = dbtran;
string sSql = "";
try
{
//dbtran.Begin();
#region 创建EXCEL
//写列名
sSql = "CREATE TABLE sheet1(";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text(200),";
}
else
{
sSql += "[" + dt.Columns[i].ColumnName + "] Text(200))";
}
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
//写行数据
for (int j = 0; j < dt.Rows.Count; j++)
{
sSql = " INSERT INTO sheet1 VALUES('";
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < dt.Columns.Count - 1)
sSql += dt.Rows[j][i].ToString() + "','";
else
try
{
sSql += dt.Rows[j][i].ToString() + "')";
}
catch { sSql += "" + "')"; }
}
oleDbCmd.CommandText = sSql;
oleDbCmd.ExecuteNonQuery();
}
#endregion
dbtran.Commit();
}
catch (System.Exception ex)
{
dbtran.Rollback();
}
finally
{
//断开连接
oleDbCmd.Dispose();
oleDbConn.Close();
oleDbConn.Dispose();
}
try
{
using (FileStream fs = new FileStream(strExcelFileName, FileMode.OpenOrCreate))
{
System.IO.FileInfo file = new System.IO.FileInfo(strExcelFileName);
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
//让用户输入下载的本地地址
curContext.Response.Clear();
curContext.Response.Buffer = true;
curContext.Response.Charset = "gb2312";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpContext.Current.Server.UrlEncode(fileName) + ".xls");
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.ContentType = "application/ms-excel";
// 读取excel数据到内存
byte[] buffer = new byte[fs.Length - 1];
fs.Read(buffer, 0, (int)fs.Length - 1);
// 写到aspx页面
curContext.Response.BinaryWrite(buffer);
curContext.Response.Flush();
curContext.ApplicationInstance.CompleteRequest();
fs.Close();
fs.Dispose();
//删除临时文件
File.Delete(strExcelFileName);
}
}
catch (Exception ex)
{
}
}