ASP.NET中将检索出的数据写入Exel形成Report的一种solution

关键词：DataSet,DataTable, DataBase, Excel, Report
正文：
    在基于web的MIS中，经常需要将从DataBase检索出的数据形成报表。而Exel报表在公司尤为常见。在.net Framework下，检索出的数据保存至DataSet或者DataTable是最常见的方法了。那么，如何将DataSet或DataTable中保存的内存数据写入Excel呢？
        可以采用一个比较愚蠢stupid的方法：将数据按照自定义的style,方便写入Excel。将Excel文件保存为spreadsheet XML格式即可。
    一般来说，数据写入到Excel文件分为3部分，Excel head file , Excel Data file, Excel footer file.将其3者合并在一起就形成了一个完整的Excel文件
代码如下：

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.IO;

//using System.

/// <summary>

/// Summary description for FileOperation

/// </summary>

///

namespace Intern.File

{

/// <summary>

/// this class is desgned to manipulate file, such as excel, text etc.

/// designed by:He Ren Gang

/// designed Date:2008/4/30

/// </summary>

///Base Interface, for other concrete class to implement it,

///such as excelClass,textClass

public interface IFileOperation

{

string FileName { get; }

string FullPathName { get; }

void CreateFileHeader(string byWho);

void SaveData(DataTable dt);

void CreateFooter();

}

public enum ExelSaveType

{

TYPE1,

TYPE2,

TYPE3

}

/// <summary>

/// ExcelFileOperation Class, mainly Operates for Excel file.

/// </summary>

public class ExcelFileOperation:IFileOperation

{

private StreamWriter sw;

private string _fileName;

private string _fullPathName;

public string FileName

{

get

{

_fileName = DateTime.Now.Year.ToString() +

DateTime.Now.Month.ToString() +

DateTime.Now.Day.ToString() +

DateTime.Now.Minute.ToString() +

DateTime.Now.Second.ToString();

_fileName += ".xls";

return _fileName;

}

public string FullPathName

{

get

{

if (!Directory.Exists(HttpContext.Current.Server.MapPath("BugReports")))

Directory.CreateDirectory(HttpContext.Current.Server.MapPath("BugReports"));

_fullPathName = HttpContext.Current.Server.MapPath(@"BugReports\" + _fileName);

return _fullPathName;

}

public ExcelFileOperation()

{

_fileName = FileName;

_fullPathName = FullPathName;

}

/// <summary>

/// functionality:

/// write the XML spread sheet head information to specified filename , mainly includes the representation format,

/// font, size, color, and column name etc.

///author:He ren gang

///completed time:2008/4/30

/// </summary>

/// <param name="filename"></param>

/// <param name="byWho"></param>

public void CreateFileHeader(string byWho)

{

FileInfo file = new FileInfo(_fullPathName);

sw = new StreamWriter(_fullPathName);

sw.WriteLine(@"<?xml version='1.0'?>

<?mso-application progid='Excel.Sheet'?>

<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'

xmlns:o='urn:schemas-microsoft-com:office:office'

xmlns:x='urn:schemas-microsoft-com:office:excel'

xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'

xmlns:html='http://www.w3.org/TR/REC-html40'>

<Author>Renggang He</Author>

<LastAuthor>Renggang He</LastAuthor>

<Company>Microsoft</Company>

</DocumentProperties>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

</Style>

</Style>

</Style>

</Style>

</Style>

</Style>

</Style>

</Style>

</Styles>

<Table ss:ExpandedColumnCount='11' x:FullColumns='1'

x:FullRows='1' ss:DefaultRowHeight='15'>

<Cell ss:Index='4' ss:MergeAcross='1' ss:StyleID='s63'><Data ss:Type='String'>BUGS REPORTS</Data></Cell>

</Row>

<Cell ss:Index='5' ss:MergeAcross='1' ss:StyleID='s65'><Data ss:Type='String'>Generated Time:" + DateTime.Now.ToShortDateString()+@"</Data></Cell>

<Cell ss:StyleID='s65'><Data ss:Type='String'>Reporter:" + byWho + @"</Data></Cell>

</Row>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Description</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Project</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Category</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Reported By</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Reported On</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Priority</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Assigned To</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Status</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Last Updated By</Data></Cell>

<Cell ss:StyleID='s66'><Data ss:Type='String'>Last Updated On</Data></Cell>

</Row>");

}

/// <summary>

/// functinality: save the data from DataTable to the spreedsheet XML file,

/// which will be saved as EXCEl File suffixed with .xls;

///comments: the data format is defined by the spreedsheet head file that has written into relevant file prio.

///author:He RenGang

///completed time:2008/4/30

/// </summary>

/// <param name="dt"></param>

public void SaveData(DataTable dt)

{

for (int i = dt.Rows.Count-1; i >=0; i--)

{

sw.WriteLine(@"<Row ss:AutoFitHeight='0'>");

for (int j = 0; j < dt.Columns.Count; j++)

{

string stype = GetStyleString(dt.Rows[i][8].ToString());

sw.WriteLine(@"<Cell ss:StyleID='" + stype + "'><Data ss:Type='String'>" + dt.Rows[i][j].ToString() + "</Data></Cell>");

}

sw.WriteLine(@"</Row>");

}

/// <summary>

/// currrently: I define such Style .

/// if type is New --> Red color :s79

/// if type is ReOpen-->Blue color:s70

/// others -->default color

/// </summary>

/// <param name="BugType"></param>

/// <returns></returns>

private string GetStyleString(string BugType)

{

if (BugType.ToLower()== "new")

return "s69";

else if (BugType == "re-opened")

return "s70";

else

return "s67";

}

/// <summary>

/// functionality:continue to write the spreedsheet footer into specified file that will be saved as Excel File.

/// completed time:2008/4/30;

/// author:He RenGang

/// </summary>

public void CreateFooter()

{

sw.WriteLine(@"</Table>

</PageSetup>

<Panes>

<Pane>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Table ss:ExpandedColumnCount='1' ss:ExpandedRowCount='1' x:FullColumns='1'

x:FullRows='1' ss:DefaultRowHeight='15'>

</Table>

</PageSetup>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Table ss:ExpandedColumnCount='1' ss:ExpandedRowCount='1' x:FullColumns='1'

x:FullRows='1' ss:DefaultRowHeight='15'>

</Table>

</PageSetup>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>");

}

/// <summary>

/// Functionality:

/// Save Bugs from Datatable Object into SpreedSheet Excel File.

/// the process includes:

/// 1 CreateExcelFileHeader();

/// 2 SaveExcelData(dt);

/// 3 CreateExcelFooter();

///

///Author:He RenGang

///completed Time: 2008/4/30;

/// </summary>

/// <param name="dt"></param>

public void SaveFile(DataTable dt)

{

try

{

string byWho = HttpContext.Current.Session["loginuser"].ToString();

                CreateFileHeader(byWho);
                SaveData(dt);
                CreateFooter();

sw.Close();

HttpContext.Current.Session["bugreportfilename"] = _fileName;

HttpContext.Current.Response.Write("<script language=jscript>window.open('DownLoadBugReports.aspx?relativefilename=" + _fileName + "')</script>");

//HttpContext.Current.Response.Write("<script language=jscript>alert('The Bug report has been saved as " + relativefileName + "')</script>");

}

catch (Exception ex)

{

}

return;

}

示例效果如下：

如果有好方法，欢迎交流。