using System;
using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Reflection;using Microsoft.Office;//using Excel = Microsoft.Office.Interop.Excel;using Excel;/// <summary>
///InOutInfoToExcel 的摘要说明/// </summary>public class InOutInfoToExcel{ public InOutInfoToExcel() { // //TODO: 在此处添加构造函数逻辑 // }/// <summary>
/// 导出excel单个sheet(导出的excel文件的名字只能为英文,中文的话名字是乱码 且只能导出一个sheet) /// </summary> /// <param name="ds"></param> /// <param name="FileName"></param> public void ToExcel(DataSet ds, string FileName) { //HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.Charset = "GB2312"; //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;//设置输出流为简体中文 System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件 HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName); //设置数字为文本格式 string strStyle = "<style>td{mso-number-format:\"\\@\";}</style>"; System.IO.StringWriter tw = new System.IO.StringWriter();//定义StringWriter输出对象 HtmlTextWriter hw = new HtmlTextWriter(tw);//定义HtmlTextWriter对象 //ctl.RenderControl(hw);//用RenderControl方法输出excel //DataGrid dg = new DataGrid(); //dg.DataSource = ds.Tables[0]; //dg.DataBind(); //dg.RenderControl(hw); hw.WriteLine(strStyle); GridView gv = new GridView(); gv.DataSource = ds.Tables[0]; gv.DataBind(); gv.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString());//输出 HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); } /// <summary> ///导出Excel文件 (多个sheet) /// </summary> /// <param name="dv">用于导出的DataSET[数组]</param> /// <param name="tmpExpDir">导出的文件夹的虚拟路径(在程序里建一个文件夹导出到此文件夹中)/</param> /// <param name="refFileName">文件名,例如test.xls</param> /// <param name="sheetName">Sheet的名称,如果导出多个Sheet[数租]</param> /// <param name="sheetSize">每个Sheet包含的数据行数,此数值不包括标题行。所以,对于65536行数据,请将此值设置为65535</param> /// <param name="setBorderLine">导出完成后,是否给数据加上边框线</param> public bool WebExportToExcel_1(DataSet[] dv, string tmpExpDir, string refFileName, string[] sheetName, int sheetSize, bool setBorderLine) { int RowsToDivideSheet = sheetSize;//计算Sheet行数 int sheetCount = dv.Length; GC.Collect();// 回收其他的垃圾 Application excel; _Workbook xBk; _Worksheet xSt = null;excel = new Excel.ApplicationClass(); xBk = excel.Workbooks.Add(true);
//申明循环中要使用的变量 int dvRowStart = 0; int dvRowEnd; int rowIndex = 0; int colIndex = 0; //对全部Sheet进行操作 for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { rowIndex = 1; colIndex = 1; //设置初始化的行和列 //计算起始行 dvRowStart = 1; //sheetIndex * RowsToDivideSheet; //计算结束行 dvRowEnd = RowsToDivideSheet; //dvRowStart + RowsToDivideSheet - 1; if (dvRowEnd > dv[sheetIndex].Tables[0].Rows.Count) { dvRowEnd = dv[sheetIndex].Tables[0].Rows.Count + 1; } //创建一个Sheet if (null == xSt) { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); } else { xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing); } //设置SheetName // xSt.Name = null; xSt.Name = sheetName[sheetIndex].ToString(); //if (sheetCount > 1) //{ xSt.Name += "1"; } //取得标题 foreach (DataColumn col in dv[sheetIndex].Tables[0].Columns) { //设置标题格式 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐 xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true; //填值,并进行下一列 excel.Cells[rowIndex, colIndex++] = col.ColumnName; } //取得DATASET表格中数据 //int drvIndex; //for (drvIndex = dvRowStart; drvIndex <= dvRowEnd-1; drvIndex++) //{ // //新起一行,当前单元格移至行首 // rowIndex++; // colIndex = 1; // for (int i = 1; i <= dv[sheetIndex].Tables[0].Columns.Count; i++) // { // string aa = dv[sheetIndex].Tables[0].Rows[0][i-1].ToString(); // excel.Cells[rowIndex, colIndex] = "'" + aa + ""; // colIndex++; // } //} //以下代码就是经过修正后的。上面注释的代码有问题。 foreach (DataRow dr in dv[sheetIndex].Tables[0].Rows) { //新起一行,当前单元格移至行首 rowIndex++; colIndex = 1; for (int i = 1; i <= dv[sheetIndex].Tables[0].Columns.Count; i++) { string aa = dr[i - 1].ToString(); excel.Cells[rowIndex, colIndex] = "'" + aa + ""; colIndex++; } } Excel.Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex]); allDataWithTitleRange.Select(); allDataWithTitleRange.Columns.AutoFit(); if (setBorderLine) { allDataWithTitleRange.Borders.LineStyle = 1; }}//Sheet循环结束
string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(tmpExpDir, refFileName)); xBk.SaveCopyAs(absFileName); xBk.Close(false, null, null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); return true; }}