ASP.NET 导出

 

using NPOI.SS.UserModel;
using NPOI.SS.Util;

public FileResult _OutUserExcel(string id, string className)
{

System.IO.MemoryStream ms = new System.IO.MemoryStream();
DateTime dt = DateTime.Now;
string title = string.Format("{0}-报名学员", className);
string dateTime = dt.ToString("yyyyMMddHHmmss");
string fileName = title + dateTime + ".xls";
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

//设置标题
IRow rowBT = sheet1.CreateRow(0);
rowBT.HeightInPoints = 25;//行高
SetCellRangeAddress(sheet1, 0, 0, 0, 5);

rowBT.CreateCell(0).SetCellValue(title);
//标题样式设置
ICellStyle cellStyleBT = book.CreateCellStyle();
cellStyleBT.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐(默认应该为center，如果center无效则用justify)
cellStyleBT.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
rowBT.Cells[0].CellStyle = cellStyleBT;
IFont font = book.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontHeightInPoints = 18;
cellStyleBT.SetFont(font);

//表头样式设置
ICellStyle styleHeader = book.CreateCellStyle();
styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐(默认应该为center，如果center无效则用justify)
styleHeader.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
styleHeader.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleHeader.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
IFont fontHeader = book.CreateFont();
fontHeader.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
fontHeader.FontHeightInPoints = 12;
cellStyleBT.SetFont(fontHeader);
//设置表头
IRow rowHead = sheet1.CreateRow(1);
rowHead.HeightInPoints = 20;
rowHead.CreateCell(0).SetCellValue("序号");
rowHead.Cells[0].CellStyle = styleHeader;
rowHead.CreateCell(1).SetCellValue("学员姓名");
rowHead.Cells[1].CellStyle = styleHeader;
rowHead.CreateCell(2).SetCellValue("岗位");
rowHead.Cells[2].CellStyle = styleHeader;
rowHead.CreateCell(3).SetCellValue("身份证号");
rowHead.Cells[3].CellStyle = styleHeader;
rowHead.CreateCell(4).SetCellValue("工作单位");
rowHead.Cells[4].CellStyle = styleHeader;
rowHead.CreateCell(5).SetCellValue("受令单位");
rowHead.Cells[5].CellStyle = styleHeader;

//内容样式设置
sheet1.SetColumnWidth(0, 180 * 20);
sheet1.SetColumnWidth(1, 180 * 30);
sheet1.SetColumnWidth(3, 180 * 30);
sheet1.SetColumnWidth(4, 180 * 60);
sheet1.SetColumnWidth(5, 180 * 60);
sheet1.SetColumnWidth(6, 180 * 60);

ICellStyle style = book.CreateCellStyle();
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐(默认应该为center，如果center无效则用justify)
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

var list = service.GetEnrollUser(id);
//将数据逐步写入sheet1各个行
int i = 0;
foreach (var item in list)
{
++i;
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(i);
rowtemp.Cells[0].CellStyle = style;
rowtemp.CreateCell(1).SetCellValue(item.UserName);
rowtemp.Cells[1].CellStyle = style;
rowtemp.CreateCell(2).SetCellValue(item.UserPostName);
rowtemp.Cells[2].CellStyle = style;
rowtemp.CreateCell(3).SetCellValue(item.UserIDCard);
rowtemp.Cells[3].CellStyle = style;
rowtemp.CreateCell(4).SetCellValue(item.Workplace);
rowtemp.Cells[4].CellStyle = style;
rowtemp.CreateCell(5).SetCellValue(item.AcceptorName);
rowtemp.Cells[5].CellStyle = style;
}
// 写入到客户端
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", fileName);
}

/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}