关于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/