using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
/// <summary>
/// Excel_Export_Class 的摘要说明
/// </summary>
public class Excel_Export_Class
{
public Excel_Export_Class()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 类版本
/// </summary>
public string version
{
get { return "0.1"; }
}
readonly int EXCEL03_MaxRow = 65535;
/// <summary>
/// 将DataTable转换为excel2003格式。
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public byte[] DataTable2Excel(DataTable dt, string sheetName)
{
IWorkbook book = new HSSFWorkbook();
if (dt.Rows.Count < EXCEL03_MaxRow)
DataWrite2Sheet(dt, 0, dt.Rows.Count - 1, book, sheetName);
else
{
int page = dt.Rows.Count / EXCEL03_MaxRow;
for (int i = 0; i < page; i++)
{
int start = i * EXCEL03_MaxRow;
int end = (i * EXCEL03_MaxRow) + EXCEL03_MaxRow - 1;
DataWrite2Sheet(dt, start, end, book, sheetName + i.ToString());
}
int lastPageItemCount = dt.Rows.Count % EXCEL03_MaxRow;
DataWrite2Sheet(dt, dt.Rows.Count - lastPageItemCount, lastPageItemCount, book, sheetName + page.ToString());
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
return ms.ToArray();
}
private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, IWorkbook book, string sheetName)
{
ISheet sheet = book.CreateSheet(sheetName);
IRow header = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = header.CreateCell(i);
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
}
int rowIndex = 1;
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
IRow excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
}
}
//调用导出excel,并直接下载
dt = GetPageList.ExecuteSqlReTable(sql);
if (dt.Rows.Count > 0)
{
Excel_Export_Class excel = new Excel_Export_Class();
string fileName = "优酷订购关系";
byte[] data = excel.DataTable2Excel(dt, fileName);
fileName += ".xls";
if (Request.Browser.Browser == "IE")
fileName = HttpUtility.UrlEncode(fileName);
Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
Response.BinaryWrite(data);
}