using NPOI;
using NPOI.POIFS;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
//导出为excel
private void OutputExcel(List<SalaryDataClass> datas, string headerTxt, string fileName)
{
HttpContext curContent = HttpContext.Current;
curContent.Response.ContentType = "application/vnd.ms-excel";
curContent.Response.ContentEncoding = Encoding.UTF8;
curContent.Response.Charset = "";
curContent.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
byte[] buff = Export(datas, headerTxt).GetBuffer();
curContent.Response.BinaryWrite(buff);
curContent.Response.End();
}
private MemoryStream Export(List<SalaryDataClass> datas, string headerTxt)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle dateStyle =(HSSFCellStyle) workbook.CreateCellStyle();
dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;
HSSFFont font =(HSSFFont) workbook.CreateFont();
font.FontHeightInPoints=11;
font.FontName = "宋体";
dateStyle.SetFont(font);
HSSFDataFormat format =(HSSFDataFormat) workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
//列宽
int[] arrCountWidth = new int[6];
foreach (var item in datas)//此处填充表格实体内容,根据需要写
{
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(item.EmpName+Year+Month+"单");
//表头
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
HSSFCell cell=(HSSFCell) headerRow.CreateCell(0);
cell.SetCellValue("测试测试");
cell.CellStyle = dateStyle;
Merge(sheet, 0, 0, 1, 3);
HSSFCell dateCell = (HSSFCell)headerRow.CreateCell(4);
dateCell.SetCellValue("日期:" + Year + "-" + Month ); ;
dateCell.CellStyle = dateStyle;
Merge(sheet, 0, 4, 1, 5);
dateCell = (HSSFCell)secRow.CreateCell(4);
dateCell.CellStyle = dateStyle;
dateCell.SetCellValue(item.Depart);
Merge(sheet, 4, 4, 4, 5);
HSSFRow basicRow = (HSSFRow)sheet.CreateRow(7);
basicRow.CreateCell(0).SetCellValue("列名");
basicRow.GetCell(0).CellStyle = dateStyle;
basicRow.CreateCell(1).SetCellValue(item.BasicSa);
basicRow.GetCell(1).CellStyle = dateStyle;
SetWidth(sheet);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="r1">左上角单元格行标(从0开始,下同)</param>
/// <param name="c1">左上角单元格列标</param>
/// <param name="r2">右下角单元格行标</param>
/// <param name="c2">右下角单元格列标</param>
private void Merge(HSSFSheet sheet, int r1, int c1, int r2, int c2)
{
CellRangeAddress cellRange=new CellRangeAddress(r1, r2, c1, c2);
sheet.AddMergedRegion(cellRange);
}
private void SetWidth(HSSFSheet sheet)
{
for (int col = 0; col <= 6; col++)
{
sheet.AutoSizeColumn(col);
int colWidth = sheet.GetColumnWidth(col) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
HSSFRow currentRow;
if (sheet.GetRow(rowNum) == null)
currentRow = (HSSFRow)sheet.CreateRow(rowNum);
else
currentRow = (HSSFRow)sheet.GetRow(rowNum);
if (currentRow.GetCell(col) != null)
{
HSSFCell currentCell = (HSSFCell)currentRow.GetCell(col);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (colWidth < length)
colWidth = length;
}
}
sheet.SetColumnWidth(col, colWidth*256);
}
}
如有帮助请点个赞吧,谢谢。