.net(C#)生成excel

注意的是要添加对excel的com组件的引用。具体可以查看网上资料。
本例通过excel2003的com组件生成了一个Interop.Excel.dll（生成后放在bin目录下备引用）
主要实现 （包括一种注释那段代码用IO写文件生成excel的假实现）
protected void Button1_Click(object sender, EventArgs e)
{
//StringWriter sw = new StringWriter();
//HtmlTextWriter htw = new HtmlTextWriter(sw);
//GridView1.RenderControl(htw);
//string strHtml = sw.ToString().Trim();
//string ExcelFileName = "BOReport.xls";
//string FilePhysicialPathName = Request.PhysicalApplicationPath;

////生成的Excel文件名
//string objectExcelFileName = Path.Combine(FilePhysicialPathName, ExcelFileName);

//if( File.Exists( objectExcelFileName ))
//{
// File.Delete(objectExcelFileName);
//}
//FileStream fs = new FileStream(objectExcelFileName, FileMode.Create);
//BinaryWriter bw = new BinaryWriter(fs, Encoding.GetEncoding("GB18030"));
//bw.Write(strHtml);
//bw.Close();
//fs.Close();
//excel模板文件路径
string ExcelFileName = "Templete of BO.xls";
string FilePhysicialPathName = Request.PhysicalApplicationPath;
string objectExcelFileName = Path.Combine(FilePhysicialPathName, ExcelFileName);
//excel生成文件路径
string SaveExcelFileName = "Download/" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string SaveExcel = Path.Combine(FilePhysicialPathName, SaveExcelFileName);
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["Str_connectSql"]);
con.Open();
SqlCommand sc = new SqlCommand("select row_number() over(order by poreceivedt desc) as [index],* from Report.BOReport", con);
SqlDataReader sdr = sc.ExecuteReader();
//创建excel应用程序
Excel.Application excelApp = new Excel.ApplicationClass();
//创建工作薄对象
Excel.Workbook excelWb = excelApp.Workbooks.Open(objectExcelFileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); ;
//创建工作表对象
Excel.Worksheet excelSt = (Excel.Worksheet)excelWb.Sheets.get_Item("open");
int i = 3;//定义插入数据起始行
//迭代结果集数据插入到工作表中
while (sdr.Read())
{
excelSt.Cells[i, 1] = sdr.GetValue(0);
excelSt.Cells[i, 4] = sdr.GetValue(1);
excelSt.Cells[i, 5] = sdr.GetValue(2);
excelSt.Cells[i, 8] = sdr.GetValue(3);
excelSt.Cells[i, 9] = sdr.GetValue(4);
excelSt.Cells[i, 10] = sdr.GetValue(5);
excelSt.Cells[i, 13] = sdr.GetValue(6);
excelSt.Cells[i, 15] = sdr.GetValue(7);
excelSt.Cells[i, 21] = sdr.GetValue(8);
i++;//下一行
}
sdr.Close();//关闭SqlDataReader对象
con.Close();//关闭连接
System.Reflection.Missing oMissing = System.Reflection.Missing.Value;//定义一个使用缺省参数的对象
//保存文件
excelWb.SaveAs(SaveExcel, oMissing, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlShared, oMissing, oMissing, oMissing, oMissing, oMissing);
}
catch (Exception ex)
{
System.Console.Write(ex);
}
finally
{
//停止excel应用程序
excelApp.Quit();
//释放资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
System.GC.Collect();//强制垃圾回收
}
}