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'>
    <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
    <Author>Renggang He</Author>
    <LastAuthor>Renggang He</LastAuthor>
    <Created>2008-04-30T04:46:02Z</Created>
    <Company>Microsoft</Company>
    <Version>12.00</Version>
    </DocumentProperties>
    <ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>
    <WindowHeight>4695</WindowHeight>
    <WindowWidth>11280</WindowWidth>
    <WindowTopX>120</WindowTopX>
    <WindowTopY>75</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
    <Style ss:ID='Default' ss:Name='Normal'>
    <Alignment ss:Vertical='Bottom'/>
    <Borders/>
    <Font ss:FontName='Calibri' x:Family='Swiss' ss:Size='11' ss:Color='#000000'/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
    </Style>
    <Style ss:ID='s63'>
   <Alignment ss:Vertical='Bottom'/>
   <Font ss:FontName='Arial Black' x:Family='Swiss' ss:Size='11' ss:Color='#000000'/>
    </Style>
    <Style ss:ID='s65'>
    <Alignment ss:Vertical='Bottom'/>
   <Font ss:FontName='Arial Black' x:Family='Swiss' ss:Size='10' ss:Color='#006699'/>
    </Style>
    <Style ss:ID='s66'>
   <Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>
   <Font ss:FontName='Arial' x:Family='Swiss' ss:Size='11' ss:Color='#000000'/>
   <NumberFormat/>
   </Style>
  <Style ss:ID='s67'>
   <Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>
  </Style>
  <Style ss:ID='s68'>
   <Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>
   <NumberFormat ss:Format='Short Date'/>
  </Style>
 <Style ss:ID='s69'>
   <Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>
   <Font ss:FontName='Calibri' x:Family='Swiss' ss:Size='11' ss:Color='#FF0000'/>
  </Style>
<Style ss:ID='s70'>
   <Alignment ss:Horizontal='Center' ss:Vertical='Bottom'/>
   <Font ss:FontName='Calibri' x:Family='Swiss' ss:Size='11' ss:Color='#538ED5'/>
  </Style>
  </Styles>
 <Worksheet ss:Name='Sheet1'>
  <Table ss:ExpandedColumnCount='11'  x:FullColumns='1'
   x:FullRows='1' ss:DefaultRowHeight='15'>
   <Column ss:AutoFitWidth='0' ss:Width='29.25'/>
   <Column ss:AutoFitWidth='0' ss:Width='60'/>
   <Column ss:Index='4' ss:AutoFitWidth='0' ss:Width='57.75'/>
   <Column ss:AutoFitWidth='0' ss:Width='95.25'/>
   <Column ss:AutoFitWidth='0' ss:Width='78.75'/>
   <Column ss:Index='8' ss:AutoFitWidth='0' ss:Width='83.25'/>
   <Column ss:AutoFitWidth='0' ss:Width='57.75'/>
   <Column ss:AutoFitWidth='0' ss:Width='95.25'/>
   <Column ss:AutoFitWidth='0' ss:Width='91.5'/>
   <Row ss:AutoFitHeight='0' ss:Height='18.75'>
    <Cell ss:Index='4' ss:MergeAcross='1' ss:StyleID='s63'><Data ss:Type='String'>BUGS REPORTS</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight='0'>
    <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>
   <Row ss:AutoFitHeight='0'>
    <Cell ss:StyleID='s66'><Data ss:Type='String'>ID</Data></Cell>
    <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>
  <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
   <PageSetup>
    <Header x:Margin='0.3'/>
    <Footer x:Margin='0.3'/>
    <PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>
   </PageSetup>
   <Unsynced/>
   <Selected/>
   <LeftColumnVisible>1</LeftColumnVisible>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>6</ActiveRow>
     <ActiveCol>5</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name='Sheet2'>
  <Table ss:ExpandedColumnCount='1' ss:ExpandedRowCount='1' x:FullColumns='1'
   x:FullRows='1' ss:DefaultRowHeight='15'>
   <Row ss:AutoFitHeight='0'/>
  </Table>
  <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
   <PageSetup>
    <Header x:Margin='0.3'/>
    <Footer x:Margin='0.3'/>
    <PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>
   </PageSetup>
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name='Sheet3'>
  <Table ss:ExpandedColumnCount='1' ss:ExpandedRowCount='1' x:FullColumns='1'
   x:FullRows='1' ss:DefaultRowHeight='15'>
   <Row ss:AutoFitHeight='0'/>
  </Table>
  <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>
   <PageSetup>
    <Header x:Margin='0.3'/>
    <Footer x:Margin='0.3'/>
    <PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>
   </PageSetup>
   <Unsynced/>
   <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;
        }

    }


   
}


示例效果如下:

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

posted on 2008-05-26 16:40  飞天舞者  阅读(592)  评论(1编辑  收藏  举报

导航

For more information about me, feel free email to me winston.he@hotmail.com