public void ExportToExcel()
{
string path = Request.PhysicalApplicationPath + "txtfile//" + getFileName() + ".xls";
string sql = Session["queryStr"].ToString();
DataSet ds = Maticsoft.DBUtility.DbHelperSQL.Query(sql);
if (ds.Tables.Count != 0)
{
//生成.xls文件完整路径名
// string tempFileName = GetTempFileName();
object filename = path;
object Nothing = System.Reflection.Missing.Value;
//创建excel文件,文件名用系统时间生成精确到毫秒
Excel.Application myExcel = new Excel.ApplicationClass();
myExcel.Application.Workbooks.Add(Nothing);
try
{
//把Dataset中的数据插入excel文件中
int totalCount = 0;
for (int k = 0; k < ds.Tables.Count; k++)
{
int row = ds.Tables[k].Rows.Count;
int column = ds.Tables[k].Columns.Count;
for (int i = 0; i < column; i++)
{
myExcel.Cells[totalCount + 1, 1 + i] = convertColumnName(ds.Tables[k].Columns[i].ColumnName);
}
for (int i = 0; i < row; i++)
{
for (int j = 0; j < column; j++)
{
myExcel.Cells[totalCount + 2 + i, 1 + j] = "'" + getRowValue(ds.Tables[k].Rows[i], ds.Tables[k].Columns[j].ColumnName);
}
}
totalCount = totalCount + row + 4;
}
try
{
myExcel.ActiveWorkbook._SaveAs(filename, Nothing, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing);
}
catch
{
return;
}
//让生成的excel文件可见
//myExcel.Visible = true;
}
catch (Exception e)
{
}
foreach(Excel.WorkbookClass w in myExcel.Workbooks)
{
w.Close(false, Nothing, Nothing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(w);
}
myExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myExcel = null;
System.GC.Collect();
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=todayDownList.xls");
System.IO.FileInfo fileInfo = new FileInfo(path);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.WriteFile(path);
Response.End();
}
else
{
//System.Windows.Forms.MessageBox.Show("No Data");
}
}
注意:Excel命名空间在COM里,添加此命名空间时,在机器上应该装有EXCEL才可以