MyXls导出Excel时在Web页即时生成原生Excel文件

关于MyXls生成Excel文件，本文不赘述。本文主要介绍在Web条件下利用MyXls即时生成原生Excel的方法。

至于NPOI方法即时生成Excel方法，请参考http://www.cnblogs.com/downmoon/archive/2011/04/16/2017603.html

需要引用dll,org.in2bits.MyXls，下载地址：http://sourceforge.net/projects/myxls/files/MyXls/

并在头部添加using org.in2bits.MyXls;

假如一个DataTable已经获取，那么方法如下：

一、生成本地文件：

  
  

   
    
   
   ///
   
    
   
   <summary>
   
   
        
   
   ///
   
    导出Excel
        
   
   ///
   
    
   
   </summary>
   
   

   
           
   
   public
   
    
   
   static
   
    
   
   void
   
    ExportExcelForPercent(
   
   string
   
    sheetName, 
   
   string
   
    xlsname, 
   
   int
   
    areaid, DateTime dt)
        {

            DataTable table 
   
   =
   
    GetDataTableForPercent(areaid, dt);

            
   
   if
   
    (table 
   
   ==
   
    
   
   null
   
    
   
   ||
   
    table.Rows.Count 
   
   ==
   
    
   
   0
   
   ) { 
   
   return
   
   ; }
            XlsDocument xls 
   
   =
   
    
   
   new
   
    XlsDocument();
            Worksheet sheet 
   
   =
   
    xls.Workbook.Worksheets.Add(sheetName);

            
   
   //
   
   填充表头  
   
   

   
               
   
   foreach
   
    (DataColumn col 
   
   in
   
    table.Columns)
            {
                sheet.Cells.Add(
   
   1
   
   , col.Ordinal 
   
   +
   
    
   
   1
   
   , col.ColumnName);
            }

            
   
   //
   
   填充内容  
   
   

   
               
   
   for
   
    (
   
   int
   
    i 
   
   =
   
    
   
   0
   
   ; i 
   
   <
   
    table.Rows.Count; i
   
   ++
   
   )
            {
                
   
   for
   
    (
   
   int
   
    j 
   
   =
   
    
   
   0
   
   ; j 
   
   <
   
    table.Columns.Count; j
   
   ++
   
   )
                {
                    sheet.Cells.Add(i 
   
   +
   
    
   
   2
   
   , j 
   
   +
   
    
   
   1
   
   , table.Rows[i][j].ToString());
                }
            }

            
   
   //
   
   保存  
   
   

   
               xls.FileName 
   
   =
   
    xlsname;
            xls.Save();
            xls 
   
   =
   
    
   
   null
   
   ;
        }
  
  

二、web条件下利用MemoryStream即时生成：

  
  

   
    
   
   ///
   
    
   
   <summary>
   
   
        
   
   ///
   
    Myxls导出Excel
        
   
   ///
   
    
   
   </summary>
   
   

   
           
   
   public
   
    
   
   static
   
    
   
   void
   
    ExportExcelForPercentForWeb(
   
   string
   
    sheetName, 
   
   string
   
    xlsname, 
   
   int
   
    areaid, DateTime curdate)
        {

            XlsDocument xls 
   
   =
   
    
   
   new
   
    XlsDocument();
            Worksheet sheet 
   
   =
   
    xls.Workbook.Worksheets.Add(sheetName);
            
   
   try
   
   
            {
                DataTable table 
   
   =
   
    GetDataTableForPercent(areaid, curdate);

                
   
   if
   
    (table 
   
   ==
   
    
   
   null
   
    
   
   ||
   
    table.Rows.Count 
   
   ==
   
    
   
   0
   
   ) { 
   
   return
   
   ; }
                
   
   //
   
   XlsDocument xls = new XlsDocument();
                
   
   //
   
   Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);

                
   
   //
   
   填充表头  
   
   

   
                   
   
   foreach
   
    (DataColumn col 
   
   in
   
    table.Columns)
                {
                    sheet.Cells.Add(
   
   1
   
   , col.Ordinal 
   
   +
   
    
   
   1
   
   , col.ColumnName);
                }

                
   
   //
   
   填充内容  
   
   

   
                   
   
   for
   
    (
   
   int
   
    i 
   
   =
   
    
   
   0
   
   ; i 
   
   <
   
    table.Rows.Count; i
   
   ++
   
   )
                {
                    
   
   for
   
    (
   
   int
   
    j 
   
   =
   
    
   
   0
   
   ; j 
   
   <
   
    table.Columns.Count; j
   
   ++
   
   )
                    {
                        sheet.Cells.Add(i 
   
   +
   
    
   
   2
   
   , j 
   
   +
   
    
   
   1
   
   , table.Rows[i][j].ToString());
                    }
                }

                
   
   //
   
   保存  
                
   
   //
   
   xls.FileName = xlsname;
                
   
   //
   
   xls.Save();
   
   

   
   
                
   
   #region
   
    客户端保存
   
   
                
   
   using
   
    (MemoryStream ms 
   
   =
   
    
   
   new
   
    MemoryStream())
                {
                    xls.Save(ms);
                    ms.Flush();
                    ms.Position 
   
   =
   
    
   
   0
   
   ;
                    sheet 
   
   =
   
    
   
   null
   
   ;
                    xls 
   
   =
   
    
   
   null
   
   ;
                    HttpResponse response 
   
   =
   
    System.Web.HttpContext.Current.Response;
                    response.Clear();

                    response.Charset 
   
   =
   
    
   
   "
   
   UTF-8
   
   "
   
   ;
                    response.ContentType 
   
   =
   
    
   
   "
   
   application/vnd-excel
   
   "
   
   ;
   
   //
   
   "application/vnd.ms-excel";
   
   

   
                       System.Web.HttpContext.Current.Response.AddHeader(
   
   "
   
   Content-Disposition
   
   "
   
   , 
   
   string
   
   .Format(
   
   "
   
   attachment; filename=
   
   "
   
    
   
   +
   
    xlsname));
                    
   
   //
   
   System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
   
   

   
                       
   
   byte
   
   [] data 
   
   =
   
    ms.ToArray();
                    System.Web.HttpContext.Current.Response.BinaryWrite(data);

                }

                
   
   #endregion
   
   
                
   
   //
   
   xls = null;
   
   

   
               }
            
   
   catch
   
    (Exception ex)
            {
            }
            
   
   finally
   
   
            {
                sheet 
   
   =
   
    
   
   null
   
   ;
                xls 
   
   =
   
    
   
   null
   
   ;
            }

        }
  
  

三、调用方法：

  
  

   
    
   
   private
   
    
   
   void
   
    SaveFile(
   
   int
   
    year, 
   
   int
   
    month, 
   
   int
   
    Areaid)
        {

            
   
   try
   
   
            {
                
   
   string
   
    fileName 
   
   =
   
    
   
   string
   
   .Empty;
                fileName 
   
   =
   
    SQLParser.RandomKey(
   
   10001
   
   , 
   
   99999
   
   ) 
   
   +
   
    
   
   "
   
   .xls
   
   "
   
   ;

           
   
   string
   
    sheetname 
   
   =
   
    
   
   string
   
   .Format(
   
   "
   
   瞬时达网络有限公司{0}[{1}年{2}月]销售表
   
   "
   
   , (Areaid 
   
   ==
   
    
   
   1
   
   ) 
   
   ?
   
    
   
   "
   
   北方区
   
   "
   
    : 
   
   "
   
   南方区
   
   "
   
   , year, month);
                DateTime dt 
   
   =
   
    
   
   new
   
    DateTime(year, month, Areaid);
                ExcelHelper.ExportExcelForPercentForWeb(sheetname, fileName, Areaid, dt);
                
   
   //
   
   Page.Response.Write("<script>window.close();</script>");
                
   
   //
   
   return;
   
   

   
               }
            
   
   catch
   
   
            {
            }
        }
  
  

生成效果：

myXls下载地址：http://sourceforge.net/projects/myxls/files/MyXls/