.NET导出报表一般是采用导出Excel报表的方式输出内容。而这又分为两种方式：使用Excel模板方式和使用网页输出Excel格式两种。
首先介绍简单的一种，网页输出Excel内容，这种不需要引用Excel的程序集。
/// <summary> /// 报表导出辅助类 /// </summary> public class ExportToExcel { 字段信息 public ExportToExcel() { } /// <summary> /// 带参数的构造函数 /// </summary> /// <param name="fileName">导出的Excel文件名</param> /// <param name="sourceTable">源数据DataTable</param> /// <param name="title">报表的抬头</param> public ExportToExcel(string fileName, DataTable sourceTable, string title) { this.fileName = fileName; this.sourceTable = sourceTable; this.title = title; } public void ExportReport() { if (SourceTable == null || SourceTable.Rows.Count == 0) { return; } DataGrid dataGrid = new DataGrid(); dataGrid.DataSource = sourceTable; dataGrid.DataBind(); HttpResponse Response = HttpContext.Current.Response; Response.Clear(); Response.Buffer = true; Response.AddHeader(C_HTTP_HEADER_CONTENT, C_HTTP_ATTACHMENT + HttpUtility.UrlEncode(fileName + ".xls")); Response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL; Response.ContentEncoding = Encoding.GetEncoding("gb2312"); Response.Charset = charSet; StringWriter oStringWriter = new StringWriter(); HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter); dataGrid.RenderControl(oHtmlTextWriter); string str = oStringWriter.ToString(); int trPosition = str.IndexOf("<tr>", 0); string str1 = str.Substring(0, trPosition - 1); string str2 = str.Substring(trPosition, str.Length - trPosition); string str3 = "\r\n\t<tr>"; str3 += "\r\n\t\t<td align=\"center\" colspan=\"" + sourceTable.Rows.Count + "\" style=\"font-size:14pt; font-weight:bolder;height:30px;\">" + title + "</td>"; str3 += "\r\n\t</tr>"; Response.Write(str1 + str3 + str2); Response.End(); } }
使用时候代码如下：
private void btnExport2_Click(object sender, EventArgs e) { DataTable table = SelectAll().Tables[0]; ExportToExcel export = new ExportToExcel("TestExport", table, "TestExport"); export.ExportReport(); } public static DataSet SelectAll() { string sqlCommand = " Select ID, Name, Age, Man, CONVERT(CHAR(10), Birthday ,120) as Birthday from Test"; DataSet ds = new DataSet(); string connectionString = "Server=localhost;Database=Test;uid=sa;pwd=123456"; SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand, connectionString); adapter.Fill(ds); return ds; }
另外一种就是先定义好Excel模板，然后输出指定格式的内容，这些内容通过开始单元格名称定位，然后写入内容，但是这种功能比较强大，输出的Excel内容也比较整齐。
1. 首先在Web.Config中配置下
<system.web>
<identity impersonate="true"></identity>
</system.web>
2. 创建一个Excel模板文件，如下图所示，当然这个是简单的Excel模板，你可以定义很复杂
3. 在网站的根目录中创建一个Temp目录，给EveryOne读写权限，当然你也可以给AuthenticatedUsers
4. 辅助类代码
/// <summary> /// 报表导出基类 /// </summary> public abstract class BaseReport { 变量及属性 public BaseReport() { excelHelper = new ExcelHelper(false); } /// <summary> /// 打开Excel文件和关闭Excel /// </summary> /// <returns>返回OK表示成功</returns> protected virtual bool OpenFile() { return excelHelper.OpenFile(fileName); } /// <summary> /// 关闭工作薄和excel文件 /// </summary> protected virtual void CloseFile() { excelHelper.stopExcel(); } /// <summary> /// 导出EXCEL文件 /// </summary> protected virtual void ExportFile() { string tempFileName = HttpContext.Current.Request.PhysicalApplicationPath + @"Temp\" + sheetName.Replace(".xls", ""); string SaveFileName = tempFileName + DateTime.Now.ToLongDateString() + DateTime.Now.ToLongTimeString().Replace(":", "-") + ".xls"; excelHelper.SaveAsFile(SaveFileName); CloseFile(); HttpResponse Response = HttpContext.Current.Response; Response.Clear(); Response.Buffer = true; Response.AddHeader(C_HTTP_HEADER_CONTENT, C_HTTP_ATTACHMENT + HttpUtility.UrlEncode(DateTime.Now.ToLongDateString() + sheetName)); Response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL; Response.ContentEncoding = Encoding.GetEncoding("gb2312"); Response.Charset = CharSet; Response.WriteFile(SaveFileName); Response.Flush(); Response.Clear(); File.Delete(SaveFileName); } /// <summary> /// 填充表单数据到excel中 /// </summary> /// <param name="GotoCell">定义的首个Cell名称</param> /// <param name="dt">数据表Datatable</param> protected virtual void FillCell(string GotoCell, DataTable dt) { int BeginRow = 2; int RowCount = dt.Rows.Count; Range rgFill = excelHelper.GotoCell(GotoCell); if (RowCount > BeginRow) { excelHelper.InsertRows(rgFill.Row + 1, RowCount - BeginRow); //从定位处的下一行的上面插入新行 } //Fill if (RowCount > 0) { excelHelper.DataTableToExcelofObj(dt, excelHelper.IntToLetter(rgFill.Column) + rgFill.Row.ToString(), false); } } private void AppendTitle(string titleAppendix) { if (titleAppendix != null && titleAppendix != string.Empty) { try { excelHelper.AppendToExcel(titleAppendix, "Title"); } catch (Exception ex) { throw new Exception("您没有指定一个Title的单元格", ex); } } } /// <summary> /// 写入内容 /// </summary> public virtual void ExportExcelFile() { ExportExcelFile(string.Empty); } /// <summary> /// 写入内容并追加标题内容 /// </summary> /// <param name="titleAppendix">追加在Title后面的内容（一般如年月份）</param> public virtual void ExportExcelFile(string titleAppendix) { try { OpenFile(); AppendTitle(titleAppendix); FillFile(); ExportFile(); } catch //(Exception ex) { CloseFile(); throw; } } protected virtual void FillFile() { } }
/// <summary> ///通用的报表导出类 /// </summary> /// <example> /// <code> /// DataTable dt = InitTableData(); //InitTableData为自定义获取数据表的函数 /// CommonExport report = new CommonExport(dt, "架空线.xls", "Start"); //Start是Excel一个单元格名称 /// report.ExportExcelFile(); /// </code> /// </example> public class CommonExport : BaseReport { private DataTable sourceTable; private string startCellName; /// <summary> /// 构造函数 /// </summary> /// <param name="sourceTable">要导出的DataTable对象</param> /// <param name="excelFileName">相对于根目录的文件路径，如Model/Test.xls</param> /// <param name="startCellName">开始的单元格名称</param> public CommonExport(DataTable sourceTable, string excelFileName, string startCellName) { fileName = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, excelFileName); sheetName = Path.GetFileName(fileName); this.sourceTable = sourceTable; this.startCellName = startCellName; } /// <summary> /// 填写文件 /// </summary> protected override void FillFile() { FillCell(startCellName, sourceTable); }
/// <summary> /// Excel帮助类 /// </summary> internal class ExcelHelper : IDisposable { 一般的属性变量 打开工作薄变量 关闭工作薄变量 /// <summary> /// 当前工作薄 /// </summary> public Workbook CurrentExcelWorkBook { get { return excelWorkbook; } set { excelWorkbook = value; } } /// <summary> /// 释放对象内存，推出进程 /// </summary> /// <param name="obj"></param> private void NAR(object obj) { try { Marshal.ReleaseComObject(obj); } catch { } finally { obj = null; } } public ExcelHelper() { StartExcel(); } /// <summary> /// 确定Excel打开是否可见 /// </summary> /// <param name="visible">true为可见</param> public ExcelHelper(bool visible) { m_app_visible = visible; StartExcel(); } /// <summary> /// 开始Excel应用程序 /// </summary> private void StartExcel() { if (excelApp == null) { excelApp = new ApplicationClass(); } // Excel是否可见 excelApp.Visible = m_app_visible; } public void Dispose() { stopExcel(); GC.SuppressFinalize(this); } 打开、保存、关闭Excel文件 windows窗口，workbook工作薄，worksheet工作区操作 行列操作 }
本文转自博客园伍华聪的博客，原文链接：.NET开发不可不知、不可不用的辅助类（三）（报表导出---终结版），如需转载请自行联系原博主。