Excel Helper(转)

.NET开发不可不知、不可不用的辅助类（三）（报表导出---终结版） - wuhuacong(伍华聪)的专栏 - 博客园

2009-07-16 08:27:20| 分类：默认分类|字号订阅

.NET开发不可不知、不可不用的辅助类（三）（报表导出---终结版） Posted on 2007-12-03 20:47 伍华聪阅读(1894) 评论(1) 编辑收藏网摘

.NET导出报表一般是采用导出Excel报表的方式输出内容。而这又分为两种方式：使用Excel模板方式和使用网页输出Excel格式两种。
首先介绍简单的一种，网页输出Excel内容，这种不需要引用Excel的程序集。

///<summary>//////</summary>
publicclass#region 字段信息privateconststring C_HTTP_HEADER_CONTENT ="Content-Disposition"privateconststring C_HTTP_ATTACHMENT ="attachment;filename="privateconststring C_HTTP_CONTENT_TYPE_EXCEL ="application/ms-excel"privatestring charSet ="utf-8"privatestring fileName ="Report"privatestring title =""private///<summary>//////</summary>
publicstringgetset///<summary>//////</summary>
publicstringgetset///<summary>//////</summary>
publicstringgetset///<summary>//////</summary>
publicgetset#endregionpublic///<summary>//////</summary>///<param name="fileName">导出的Excel文件名</param>///<param name="sourceTable">源数据DataTable</param>///<param name="title">报表的抬头</param>
public ExportToExcel(string fileName, DataTable sourceTable, stringthis.fileName =this.sourceTable =this.title =publicvoidif (SourceTable ==null|| SourceTable.Rows.Count ==0=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"= charSet;

            StringWriter oStringWriter =new StringWriter();
            HtmlTextWriter oHtmlTextWriter =new HtmlTextWriter(oStringWriter);
            dataGrid.RenderControl(oHtmlTextWriter);

string str =int trPosition = str.IndexOf("<tr>", 0string str1 = str.Substring(0, trPosition -1string str2 = str.Substring(trPosition, str.Length -string str3 ="\r\n\t<tr>"+="\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>"+ str3 + str2);
            Response.End();
        }

使用时候代码如下：

privatevoid btnExport2_Click(object= SelectAll().Tables[0];
            ExportToExcel export =new ExportToExcel("TestExport", table, "TestExport");
            export.ExportReport();
        }publicstaticstring sqlCommand =" Select ID, Name, Age, Man, CONVERT(CHAR(10), Birthday ,120) as Birthday from Test"=newstring connectionString ="Server=localhost;Database=Test;uid=sa;pwd=123456"=new SqlDataAdapter(sqlCommand, connectionString);
            adapter.Fill(ds);

return

另外一种就是先定义好Excel模板，然后输出指定格式的内容，这些内容通过开始单元格名称定位，然后写入内容，但是这种功能比较强大，输出的Excel内容也比较整齐。
1. 首先在Web.Config中配置下
<system.web>
<identity impersonate="true"></identity>
</system.web>
2. 创建一个Excel模板文件，如下图所示，当然这个是简单的Excel模板，你可以定义很复杂

3. 在网站的根目录中创建一个Temp目录，给EveryOne读写权限，当然你也可以给AuthenticatedUsers
4. 辅助类代码

///<summary>//////</summary>
publicabstractclass变量及属性public=new ExcelHelper(false///<summary>//////</summary>///<returns>返回OK表示成功</returns>
protectedvirtualboolreturn excelHelper.OpenFile(fileName);
        }///<summary>//////</summary>
protectedvirtualvoid{
            excelHelper.stopExcel();
        }///<summary>//////</summary>
protectedvirtualvoidstring 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"= CharSet;
            Response.WriteFile(SaveFileName);
            Response.Flush();
            Response.Clear();

            File.Delete(SaveFileName);
        }///<summary>//////</summary>///<param name="GotoCell">定义的首个Cell名称</param>///<param name="dt">数据表Datatable</param>
protectedvirtualvoid FillCell(stringint BeginRow =2int RowCount = dt.Rows.Count;
            Range rgFill =if (RowCount >{
                excelHelper.InsertRows(rgFill.Row +1, RowCount - BeginRow); //从定位处的下一行的上面插入新行
            }//Fill
if (RowCount >0{
                excelHelper.DataTableToExcelofObj(dt, excelHelper.IntToLetter(rgFill.Column) + rgFill.Row.ToString(), falseprivatevoid AppendTitle(stringif (titleAppendix !=null&& titleAppendix !=stringtry{
                    excelHelper.AppendToExcel(titleAppendix, "Title"catchthrownew Exception("您没有指定一个Title的单元格"///<summary>//////</summary>
publicvirtualvoidstring///<summary>//////</summary>///<param name="titleAppendix">追加在Title后面的内容（一般如年月份）</param>
publicvirtualvoid ExportExcelFile(stringtry{
                OpenFile();
                AppendTitle(titleAppendix);
                FillFile();
                ExportFile();
            }catch//(Exception ex)protectedvirtualvoid

///<summary>//////</summary>///<example>///<code>//////    CommonExport report = new CommonExport(dt, "架空线.xls", "Start"); //Start是Excel一个单元格名称
//////</code>///</example>
publicclassprivateprivatestring///<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= Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, excelFileName);
            sheetName =this.sourceTable =this.startCellName =///<summary>//////</summary>
protectedoverridevoid{
            FillCell(startCellName, sourceTable);
        }

///<summary>//////</summary>
internalclass一般的属性变量打开工作薄变量关闭工作薄变量///<summary>//////</summary>
publicgetset///<summary>//////</summary>///<param name="obj"></param>
privatevoid NAR(objecttry{
                Marshal.ReleaseComObject(obj);
            }catchfinallypublic///<summary>//////</summary>///<param name="visible">true为可见</param>
public ExcelHelper(bool= visible;
            StartExcel();
        }///<summary>//////</summary>
privatevoidif (excelApp ==null=new// Excel是否可见
            excelApp.Visible =publicvoid{
            stopExcel();
            GC.SuppressFinalize(this打开、保存、关闭Excel文件windows窗口，workbook工作薄，worksheet工作区操作行列操作

-------------------------------------------------------------------------------------------------------------------------------------------