npoi 导出
public void Output(DataTable table, string SheetName, string reportName)
{
string result = string.Empty;
try
{
HSSFWorkbook workBook = new HSSFWorkbook();
ISheet sheet = workBook.CreateSheet(SheetName); //sheet页名称
NPOI.SS.UserModel.IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeight = 175;
ICellStyle style = workBook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(font);
style.WrapText = true;
IRow rows = sheet.CreateRow(0);
rows.Height = 400;
rows.CreateCell(0).SetCellValue(reportName); //报表名称
rows.GetCell(0).CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
rows = sheet.CreateRow(1);
for (int i = 0; i < table.Columns.Count; i++)
{
rows.CreateCell(i).SetCellValue(table.Columns[i].ColumnName.ToString());
rows.Sheet.SetColumnWidth(i, 4000);
rows.GetCell(i).CellStyle = style;
}
ICellStyle style2 = workBook.CreateCellStyle();
style2.Alignment = HorizontalAlignment.Left;
style2.VerticalAlignment = VerticalAlignment.Center;
style2.SetFont(font);
style2.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
//style2.WrapText = true;
int tem = 0;
int sheetCount = 1;
for (int j = 1; j <= table.Rows.Count; j++)
{
tem++;
if (tem == 60000)//每页最多导出60000
{
tem =0;
sheetCount++;
sheet = workBook.CreateSheet(SheetName + sheetCount);
rows = sheet.CreateRow(0);
for (int i = 0; i < table.Columns.Count; i++)
{
rows.CreateCell(i).SetCellValue(table.Columns[i].ColumnName.ToString());
rows.Sheet.SetColumnWidth(i, 4000);
rows.GetCell(i).CellStyle = style;
}
}
IRow row = sheet.CreateRow(tem + 1);
for (int k = 0; k < table.Columns.Count; k++)
{
row.CreateCell(k).SetCellValue(table.Rows[j - 1][k].ToString());
row.Sheet.SetColumnWidth(k, 4000);
row.GetCell(k).CellStyle = style2;
}
#region
//Row row = sheet.CreateRow(j + 1);
//for (int k = 0; k < table.Columns.Count; k++)
//{
// row.CreateCell(k).SetCellValue(table.Rows[j - 1][k].ToString());
// row.GetCell(k).CellStyle = style2;
//}
#endregion
}
int maxColumn = table.Columns.Count;
//列宽自适应,只对英文和数字有效
//for (int i = 0; i <= maxColumn; i++)
//{
// sheet.AutoSizeColumn(i);
//}
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
ms.Flush();
ms.Position = 0;
workBook = null;
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";//HttpContext.Current.Response
string browser = HttpContext.Current.Request.Browser.Browser.ToString();
string header = string.Empty;
if (browser == "Firefox")
{
header = string.Format("attachment; filename={0}", string.Format("{0}-{1}.xls", reportName, DateTime.Now.ToString("yyyy-MM-dd")), Encoding.UTF8).ToString();
}
else
{
header = string.Format("attachment; filename={0}", HttpUtility.UrlEncode(string.Format("{0}-{1}.xls", reportName, DateTime.Now.ToString("yyyy-MM-dd")), Encoding.UTF8)).ToString();
}
HttpContext.Current.Response.AddHeader("Content-Disposition", header);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
HttpContext.Current.Response.End();
}
}
catch
{
throw new Exception("导出异常");
}
}