.NET开发不可不知、不可不用的辅助类(三)(报表导出---终结版)

简介:
.NET导出报表一般是采用导出Excel报表的方式输出内容。而这又分为两种方式:使用Excel模板方式和使用网页输出Excel格式两种。
首先介绍简单的一种,网页输出Excel内容,这种不需要引用Excel的程序集。
ExpandedBlockStart.gif     /// <summary>
InBlock.gif    
/// 报表导出辅助类
ExpandedBlockEnd.gif    
/// </summary>

None.gif     public  class ExportToExcel
ExpandedBlockStart.gif     {
ContractedSubBlock.gif        字段信息        
InBlock.gif
InBlock.gif        public ExportToExcel()
ExpandedSubBlockStart.gif        {
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 带参数的构造函数
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="fileName">导出的Excel文件名</param>
InBlock.gif        
/// <param name="sourceTable">源数据DataTable</param>
ExpandedSubBlockEnd.gif        
/// <param name="title">报表的抬头</param>

InBlock.gif        public ExportToExcel(string fileName, DataTable sourceTable, string title)
ExpandedSubBlockStart.gif        {
InBlock.gif            this.fileName = fileName;
InBlock.gif            this.sourceTable = sourceTable;
InBlock.gif            this.title = title;
ExpandedSubBlockEnd.gif        }

InBlock.gif
InBlock.gif        public void ExportReport()
ExpandedSubBlockStart.gif        {
InBlock.gif            if (SourceTable == null || SourceTable.Rows.Count == 0)
ExpandedSubBlockStart.gif            {
InBlock.gif                return;
ExpandedSubBlockEnd.gif            }

InBlock.gif
InBlock.gif            DataGrid dataGrid = new DataGrid();
InBlock.gif            dataGrid.DataSource = sourceTable;
InBlock.gif            dataGrid.DataBind();
InBlock.gif
InBlock.gif            HttpResponse Response = HttpContext.Current.Response;
InBlock.gif            Response.Clear();
InBlock.gif            Response.Buffer = true;
InBlock.gif            Response.AddHeader(C_HTTP_HEADER_CONTENT, C_HTTP_ATTACHMENT + HttpUtility.UrlEncode(fileName + ".xls"));
InBlock.gif            Response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL;
InBlock.gif            Response.ContentEncoding = Encoding.GetEncoding("gb2312");
InBlock.gif            Response.Charset = charSet;
InBlock.gif
InBlock.gif            StringWriter oStringWriter = new StringWriter();
InBlock.gif            HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
InBlock.gif            dataGrid.RenderControl(oHtmlTextWriter);
InBlock.gif
InBlock.gif            string str = oStringWriter.ToString();
InBlock.gif            int trPosition = str.IndexOf("<tr>", 0);
InBlock.gif            string str1 = str.Substring(0, trPosition - 1);
InBlock.gif            string str2 = str.Substring(trPosition, str.Length - trPosition);
InBlock.gif
InBlock.gif            string str3 = "\r\n\t<tr>";
InBlock.gif            str3 += "\r\n\t\t<td align=\"center\" colspan=\"" + sourceTable.Rows.Count +
InBlock.gif                    "\" style=\"font-size:14pt;    font-weight:bolder;height:30px;\">" + title + "</td>";
InBlock.gif            str3 += "\r\n\t</tr>";
InBlock.gif
InBlock.gif            Response.Write(str1 + str3 + str2);
InBlock.gif            Response.End();
ExpandedSubBlockEnd.gif        }

ExpandedBlockEnd.gif    }
使用时候代码如下:
None.gif         private  void btnExport2_Click( object sender, EventArgs e)
ExpandedBlockStart.gif         {
InBlock.gif            DataTable table = SelectAll().Tables[0];
InBlock.gif            ExportToExcel export = new ExportToExcel("TestExport", table, "TestExport");
InBlock.gif            export.ExportReport();
ExpandedBlockEnd.gif        }

None.gif
None.gif         public  static DataSet SelectAll()
ExpandedBlockStart.gif         {
InBlock.gif            string sqlCommand = " Select ID, Name, Age, Man, CONVERT(CHAR(10), Birthday ,120) as Birthday from Test";
InBlock.gif
InBlock.gif            DataSet ds = new DataSet();
InBlock.gif            string connectionString = "Server=localhost;Database=Test;uid=sa;pwd=123456";
InBlock.gif
InBlock.gif            SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand, connectionString);
InBlock.gif            adapter.Fill(ds);
InBlock.gif
InBlock.gif            return ds;
ExpandedBlockEnd.gif        }
另外一种就是先定义好Excel模板,然后输出指定格式的内容,这些内容通过开始单元格名称定位,然后写入内容,但是这种功能比较强大,输出的Excel内容也比较整齐。
1. 首先在Web.Config中配置下
 <system.web>
   <identity impersonate="true"></identity>   
 </system.web>
2. 创建一个Excel模板文件,如下图所示,当然这个是简单的Excel模板,你可以定义很复杂
  Report_Excel.jpg
3. 在网站的根目录中创建一个Temp目录,给EveryOne读写权限,当然你也可以给AuthenticatedUsers
4. 辅助类代码
ExpandedBlockStart.gif     /// <summary>
InBlock.gif    
/// 报表导出基类
ExpandedBlockEnd.gif    
/// </summary>

None.gif     public  abstract  class BaseReport
ExpandedBlockStart.gif     {
ContractedSubBlock.gif        变量及属性
InBlock.gif
InBlock.gif        public BaseReport()
ExpandedSubBlockStart.gif        {
InBlock.gif            excelHelper = new ExcelHelper(false);
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 打开Excel文件和关闭Excel
InBlock.gif        
/// </summary>        
ExpandedSubBlockEnd.gif        
/// <returns>返回OK表示成功</returns>

InBlock.gif        protected virtual bool OpenFile()
ExpandedSubBlockStart.gif        {
InBlock.gif            return excelHelper.OpenFile(fileName);
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 关闭工作薄和excel文件
ExpandedSubBlockEnd.gif        
/// </summary>

InBlock.gif        protected virtual void CloseFile()
ExpandedSubBlockStart.gif        {
InBlock.gif            excelHelper.stopExcel();
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 导出EXCEL文件
ExpandedSubBlockEnd.gif        
/// </summary>

InBlock.gif        protected virtual void ExportFile()
ExpandedSubBlockStart.gif        {
InBlock.gif            string tempFileName = HttpContext.Current.Request.PhysicalApplicationPath + @"Temp\" + sheetName.Replace(".xls", "");
InBlock.gif            string SaveFileName = tempFileName + DateTime.Now.ToLongDateString() +
InBlock.gif                                  DateTime.Now.ToLongTimeString().Replace(":", "-") + ".xls";
InBlock.gif            excelHelper.SaveAsFile(SaveFileName);
InBlock.gif            CloseFile();
InBlock.gif
InBlock.gif            HttpResponse Response = HttpContext.Current.Response;
InBlock.gif            Response.Clear();
InBlock.gif            Response.Buffer = true;
InBlock.gif            Response.AddHeader(C_HTTP_HEADER_CONTENT,
InBlock.gif                               C_HTTP_ATTACHMENT + HttpUtility.UrlEncode(DateTime.Now.ToLongDateString() + sheetName));
InBlock.gif            Response.ContentType = C_HTTP_CONTENT_TYPE_EXCEL;
InBlock.gif            Response.ContentEncoding = Encoding.GetEncoding("gb2312");
InBlock.gif            Response.Charset = CharSet;
InBlock.gif            Response.WriteFile(SaveFileName);
InBlock.gif            Response.Flush();
InBlock.gif            Response.Clear();
InBlock.gif
InBlock.gif            File.Delete(SaveFileName);
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 填充表单数据到excel中
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="GotoCell">定义的首个Cell名称</param>
ExpandedSubBlockEnd.gif        
/// <param name="dt">数据表Datatable</param>

InBlock.gif        protected virtual void FillCell(string GotoCell, DataTable dt)
ExpandedSubBlockStart.gif        {
InBlock.gif            int BeginRow = 2;
InBlock.gif            int RowCount = dt.Rows.Count;
InBlock.gif            Range rgFill = excelHelper.GotoCell(GotoCell);
InBlock.gif            if (RowCount > BeginRow)
ExpandedSubBlockStart.gif            {
InBlock.gif                excelHelper.InsertRows(rgFill.Row + 1, RowCount - BeginRow); //从定位处的下一行的上面插入新行
ExpandedSubBlockEnd.gif
            }

InBlock.gif            //Fill
InBlock.gif
            if (RowCount > 0)
ExpandedSubBlockStart.gif            {
InBlock.gif                excelHelper.DataTableToExcelofObj(dt, excelHelper.IntToLetter(rgFill.Column) + rgFill.Row.ToString(), false);
ExpandedSubBlockEnd.gif            }

ExpandedSubBlockEnd.gif        }

InBlock.gif
InBlock.gif        private void AppendTitle(string titleAppendix)
ExpandedSubBlockStart.gif        {
InBlock.gif            if (titleAppendix != null && titleAppendix != string.Empty)
ExpandedSubBlockStart.gif            {
InBlock.gif                try
ExpandedSubBlockStart.gif                {
InBlock.gif                    excelHelper.AppendToExcel(titleAppendix, "Title");
ExpandedSubBlockEnd.gif                }

InBlock.gif                catch (Exception ex)
ExpandedSubBlockStart.gif                {
InBlock.gif                    throw new Exception("您没有指定一个Title的单元格", ex);
ExpandedSubBlockEnd.gif                }

ExpandedSubBlockEnd.gif            }

ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 写入内容
ExpandedSubBlockEnd.gif        
/// </summary>

InBlock.gif        public virtual void ExportExcelFile()
ExpandedSubBlockStart.gif        {
InBlock.gif            ExportExcelFile(string.Empty);
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 写入内容并追加标题内容
InBlock.gif        
/// </summary>
ExpandedSubBlockEnd.gif        
/// <param name="titleAppendix">追加在Title后面的内容(一般如年月份)</param>

InBlock.gif        public virtual void ExportExcelFile(string titleAppendix)
ExpandedSubBlockStart.gif        {
InBlock.gif            try
ExpandedSubBlockStart.gif            {
InBlock.gif                OpenFile();
InBlock.gif                AppendTitle(titleAppendix);
InBlock.gif                FillFile();
InBlock.gif                ExportFile();
ExpandedSubBlockEnd.gif            }

InBlock.gif            catch //(Exception ex)
ExpandedSubBlockStart.gif
            {
InBlock.gif                CloseFile();
InBlock.gif                throw;
ExpandedSubBlockEnd.gif            }

ExpandedSubBlockEnd.gif        }

InBlock.gif
InBlock.gif
InBlock.gif        protected virtual void FillFile()
ExpandedSubBlockStart.gif        {
ExpandedSubBlockEnd.gif        }

ExpandedBlockEnd.gif    }
ExpandedBlockStart.gif     /// <summary>
InBlock.gif    
///通用的报表导出类
InBlock.gif    
/// </summary>
InBlock.gif    
/// <example>
InBlock.gif    
/// <code>
InBlock.gif    
/// DataTable dt = InitTableData(); //InitTableData为自定义获取数据表的函数
InBlock.gif    
///    CommonExport report = new CommonExport(dt, "架空线.xls", "Start"); //Start是Excel一个单元格名称
InBlock.gif    
/// report.ExportExcelFile();
InBlock.gif    
/// </code>
ExpandedBlockEnd.gif    
/// </example>

None.gif     public  class CommonExport : BaseReport
ExpandedBlockStart.gif     {
InBlock.gif        private DataTable sourceTable;
InBlock.gif        private string startCellName;
InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 构造函数
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="sourceTable">要导出的DataTable对象</param>
InBlock.gif        
/// <param name="excelFileName">相对于根目录的文件路径,如Model/Test.xls</param>
ExpandedSubBlockEnd.gif        
/// <param name="startCellName">开始的单元格名称</param>

InBlock.gif        public CommonExport(DataTable sourceTable, string excelFileName, string startCellName)
ExpandedSubBlockStart.gif        {
InBlock.gif            fileName = Path.Combine(HttpContext.Current.Request.PhysicalApplicationPath, excelFileName);
InBlock.gif            sheetName = Path.GetFileName(fileName);
InBlock.gif
InBlock.gif            this.sourceTable = sourceTable;
InBlock.gif            this.startCellName = startCellName;
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 填写文件
ExpandedSubBlockEnd.gif        
/// </summary>

InBlock.gif        protected override void FillFile()
ExpandedSubBlockStart.gif        {
InBlock.gif            FillCell(startCellName, sourceTable);
ExpandedSubBlockEnd.gif        }
ExpandedBlockStart.gif     /// <summary>
InBlock.gif    
/// Excel帮助类
ExpandedBlockEnd.gif    
/// </summary>

None.gif     internal  class ExcelHelper : IDisposable
ExpandedBlockStart.gif     {
ContractedSubBlock.gif        一般的属性变量
InBlock.gif
ContractedSubBlock.gif        打开工作薄变量
InBlock.gif
ContractedSubBlock.gif        关闭工作薄变量
InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 当前工作薄
ExpandedSubBlockEnd.gif        
/// </summary>

InBlock.gif        public Workbook CurrentExcelWorkBook
ExpandedSubBlockStart.gif        {
ExpandedSubBlockStart.gif            get return excelWorkbook; }
ExpandedSubBlockStart.gif            set { excelWorkbook = value; }
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 释放对象内存,推出进程
InBlock.gif        
/// </summary>
ExpandedSubBlockEnd.gif        
/// <param name="obj"></param>

InBlock.gif        private void NAR(object obj)
ExpandedSubBlockStart.gif        {
InBlock.gif            try
ExpandedSubBlockStart.gif            {
InBlock.gif                Marshal.ReleaseComObject(obj);
ExpandedSubBlockEnd.gif            }

InBlock.gif            catch
ExpandedSubBlockStart.gif            {
ExpandedSubBlockEnd.gif            }

InBlock.gif            finally
ExpandedSubBlockStart.gif            {
InBlock.gif                obj = null;
ExpandedSubBlockEnd.gif            }

ExpandedSubBlockEnd.gif        }

InBlock.gif
InBlock.gif
InBlock.gif        public ExcelHelper()
ExpandedSubBlockStart.gif        {
InBlock.gif            StartExcel();
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 确定Excel打开是否可见
InBlock.gif        
/// </summary>
ExpandedSubBlockEnd.gif        
/// <param name="visible">true为可见</param>

InBlock.gif        public ExcelHelper(bool visible)
ExpandedSubBlockStart.gif        {
InBlock.gif            m_app_visible = visible;
InBlock.gif            StartExcel();
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        /// <summary>
InBlock.gif        
/// 开始Excel应用程序
ExpandedSubBlockEnd.gif        
/// </summary>

InBlock.gif        private void StartExcel()
ExpandedSubBlockStart.gif        {
InBlock.gif            if (excelApp == null)
ExpandedSubBlockStart.gif            {
InBlock.gif                excelApp = new ApplicationClass();
ExpandedSubBlockEnd.gif            }

InBlock.gif
InBlock.gif            // Excel是否可见
InBlock.gif
            excelApp.Visible = m_app_visible;
ExpandedSubBlockEnd.gif        }

InBlock.gif
InBlock.gif        public void Dispose()
ExpandedSubBlockStart.gif        {
InBlock.gif            stopExcel();
InBlock.gif            GC.SuppressFinalize(this);
ExpandedSubBlockEnd.gif        }

InBlock.gif
InBlock.gif        
ContractedSubBlock.gif        打开、保存、关闭Excel文件        
InBlock.gif
ContractedSubBlock.gif        windows窗口,workbook工作薄,worksheet工作区操作
InBlock.gif
ContractedSubBlock.gif        行列操作        
ExpandedBlockEnd.gif    }

None.gif

本文转自博客园伍华聪的博客,原文链接:.NET开发不可不知、不可不用的辅助类(三)(报表导出---终结版),如需转载请自行联系原博主。


目录
相关文章
|
2天前
|
开发框架 JavaScript 前端开发
5个.NET开源且强大的快速开发框架(帮助你提高生产效率)
5个.NET开源且强大的快速开发框架(帮助你提高生产效率)
|
2月前
|
SQL 开发框架 数据可视化
企业应用开发中.NET EF常用哪种模式?
企业应用开发中.NET EF常用哪种模式?
|
3月前
|
Linux Android开发 iOS开发
基于.Net开发的ChatGPT客户端,兼容Windows、IOS、安卓、MacOS、Linux
基于.Net开发的ChatGPT客户端,兼容Windows、IOS、安卓、MacOS、Linux
67 0
|
11小时前
|
开发框架 前端开发 .NET
福利来袭,.NET Core开发5大案例,30w字PDF文档大放送!!!
为了便于大家查找,特将之前开发的.Net Core相关的五大案例整理成文,共计440页,32w字,免费提供给大家,文章底部有PDF下载链接。
6 1
福利来袭,.NET Core开发5大案例,30w字PDF文档大放送!!!
|
1月前
|
C#
.NET开发中合理使用对象映射库,简化和提高工作效率
.NET开发中合理使用对象映射库,简化和提高工作效率
|
1月前
|
开发框架 前端开发 JavaScript
一款基于.NET Core的快速开发框架、支持多种前端UI、内置代码生成器
一款基于.NET Core的快速开发框架、支持多种前端UI、内置代码生成器
|
2月前
|
JavaScript C#
【傻瓜级JS-DLL-WINCC-PLC交互】2.wincc使用C#开发的.net控件
【傻瓜级JS-DLL-WINCC-PLC交互】2.wincc使用C#开发的.net控件
24 0
|
2月前
|
开发者
.NET微信网页开发之网页授权获取用户基本信息
.NET微信网页开发之网页授权获取用户基本信息
|
2月前
|
存储 SQL 开发框架
国产化之路 Linux Mono下的asp.net 开发笔记(三)
国产化之路 Linux Mono下的asp.net 开发笔记(三)
|
2月前
|
存储 SQL 开发框架
国产化之路 Linux Mono下的asp.net 开发笔记(二)
国产化之路 Linux Mono下的asp.net 开发笔记(二)

相关产品

  • 云迁移中心