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/

posted @ 2011-05-30 14:04  邀月  阅读(4262)  评论(12编辑  收藏