/// <summary>
/// 导出商品列表
/// </summary>
public FileResult ExportProduct()
{
//创建一个新的xls文件
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个Sheet
ISheet sheet = workbook.CreateSheet("Sheet1");
sheet.DefaultRowHeight = 300;
//创建标题
IRow rowTitle = sheet.CreateRow(0);
rowTitle.Height = 500;
ICellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = HorizontalAlignment.Center;
styleTitle.VerticalAlignment = VerticalAlignment.Center;
IFont fontTitle = workbook.CreateFont();
fontTitle.FontName = "宋体";
fontTitle.FontHeightInPoints = 18;
styleTitle.SetFont(fontTitle);
ICell cellTitle = rowTitle.CreateCell(0);
cellTitle.SetCellValue("商品列表");
cellTitle.CellStyle = styleTitle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //合并单元格
//创建表格样式
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
ICellStyle style = workbook.CreateCellStyle(); ;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.LeftBorderColor = HSSFColor.Black.Index;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.RightBorderColor = HSSFColor.Black.Index;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font);
//创建表头
IRow rowHead = sheet.CreateRow(1);
rowHead.CreateCell(0).SetCellValue("序号");
rowHead.GetCell(0).CellStyle = style;
sheet.SetColumnWidth(0, 256 * 5);
rowHead.CreateCell(1).SetCellValue("商品名称");
rowHead.GetCell(1).CellStyle = style;
sheet.SetColumnWidth(1, 256 * 25);
rowHead.CreateCell(2).SetCellValue("商品品牌");
rowHead.GetCell(2).CellStyle = style;
sheet.SetColumnWidth(2, 256 * 20);
rowHead.CreateCell(3).SetCellValue("商品价格");
rowHead.GetCell(3).CellStyle = style;
sheet.SetColumnWidth(3, 256 * 15);
rowHead.CreateCell(4).SetCellValue("数量");
rowHead.GetCell(4).CellStyle = style;
sheet.SetColumnWidth(3, 256 * 10);
rowHead.CreateCell(5).SetCellValue("总金额");
rowHead.GetCell(5).CellStyle = style;
sheet.SetColumnWidth(3, 256 * 15);
//获取商品列表数据
List<ProductModel> dataList = GetProductList();
//绑定表内容
int rowindex = 2;
int xh = 1;
foreach (var item in dataList)
{
IRow rowContent = sheet.CreateRow(rowindex);
rowContent.CreateCell(0).SetCellValue(xh);
rowContent.GetCell(0).CellStyle = style;
rowContent.CreateCell(1).SetCellValue(item.ProductName);
rowContent.GetCell(1).CellStyle = style;
rowContent.CreateCell(2).SetCellValue(item.ProductBrand);
rowContent.GetCell(2).CellStyle = style;
rowContent.CreateCell(3).SetCellValue(item.ProductPrice.ToString());
rowContent.GetCell(3).CellStyle = style;
rowContent.CreateCell(4).SetCellValue(item.Quantity.ToString());
rowContent.GetCell(4).CellStyle = style;
//设置函数,计算总金额
rowContent.CreateCell(5).SetCellFormula(String.Format("$D{0}*$E{0}", rowindex+1));
rowContent.GetCell(5).CellStyle = style;
rowindex++;
xh++;
}
//输出
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", "商品列表.xls");
}
c#导出Excel NPOI方式
最新推荐文章于 2023-07-21 09:57:21 发布