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

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

至于NPOI方法即时生成Excel方法，请参考http://blog.csdn.net/downmoon/archive/2011/04/17/6329814.aspx

需要引用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("<mce:script type="text/javascript"><!--
window.close();
// --></mce:script>");
                //return;
            }
            catch
            {
            }
        }

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

 

转载地址：http://blog.csdn.net/downmoon/article/details/6454596