.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工作区操作
行列操作
}
首先介绍简单的一种，网页输出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; }
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开发不可不知、不可不用的辅助类（三）（报表导出---终结版），如需转载请自行联系原博主。
