---

1、加入NPOI 程序集，使用nuget添加程序集

2、引用NPOI程序集

private IWorkbook ExportExcel(PrintQuotationOrderViewModel model){//if (model == null) return string.Empty;string tempDirPath = Server.MapPath("/Templates/Excel/");if (!Directory.Exists(tempDirPath)){Directory.CreateDirectory(tempDirPath);}IWorkbook workbook;string excelTempPath = tempDirPath + "quotaExcelTemp-new.xls";//加载excel模板using (FileStream fs = new FileStream(excelTempPath, FileMode.Open, FileAccess.Read)){//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式workbook = new HSSFWorkbook(fs);}ISheet sheet = workbook.GetSheetAt(0);sheet.GetRow(7).GetCell(1).SetCellValue(model.QuotationOrder.QuotedOn.ToString("yyyy-MM-dd"));sheet.GetRow(7).GetCell(6).SetCellValue(model.QuotationOrder.Number);sheet.GetRow(7).GetCell(9).SetCellValue(model.QuotationOrder.CustomerPurchaseNumber);//甲方sheet.GetRow(8).GetCell(1).SetCellValue(model.QuotationOrder.Buyer.Company.Name);sheet.GetRow(9).GetCell(1).SetCellValue(model.QuotationOrder.Buyer.Name);sheet.GetRow(10).GetCell(1).SetCellValue(model.QuotationOrder.Buyer.Email);sheet.GetRow(11).GetCell(1).SetCellValue(model.QuotationOrder.Receiver.Mobile);sheet.GetRow(12).GetCell(1).SetCellValue(model.QuotationOrder.Receiver.Address);//乙方sheet.GetRow(8).GetCell(8).SetCellValue("XXXXX有限公司");ICellStyle cstyle = workbook.CreateCellStyle();cstyle.Alignment = HorizontalAlignment.Left;sheet.GetRow(8).GetCell(8).CellStyle = cstyle;sheet.GetRow(9).GetCell(8).SetCellValue(model.QuotationOrder.SalesmanName);sheet.GetRow(9).GetCell(8).CellStyle = cstyle;sheet.GetRow(10).GetCell(8).SetCellValue(model.QuotationOrder.Salesman.Mobile);sheet.GetRow(10).GetCell(8).CellStyle = cstyle;sheet.GetRow(11).GetCell(8).SetCellValue(model.QuotationOrder.Salesman.Email);sheet.GetRow(11).GetCell(8).CellStyle = cstyle;int count = model.QuotationItems.Count;for (int i = 0; i < count; i++){//设置列头的单元格样式HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;IRow row = sheet.CopyRow(1, 15 + i);ICell cell = row.CreateCell(0);cell.SetCellValue((i + 1));ICellStyle style1 = SetCellStyle((HSSFWorkbook)workbook, HorizontalAlignment.Left);cell.CellStyle = style1;cell = row.CreateCell(1);cell.SetCellValue(model.QuotationItems[i].Product.Name);cell.CellStyle = style1;cell = row.CreateCell(2);cell.CellStyle = style1;//合并单元格CellRangeAddress region = new CellRangeAddress(15 + i, 15 + i, 1, 2);sheet.AddMergedRegion(region);cell = row.CreateCell(3);cell.CellStyle = style1;cell.SetCellValue(model.QuotationItems[i].CustomCode);cell = row.CreateCell(4);cell.CellStyle = style1;cell.SetCellValue(model.QuotationItems[i].Product.Code);cell = row.CreateCell(5);cell.CellStyle = style1;cell.SetCellValue("PCS");cell = row.CreateCell(6);cell.CellStyle = style1;cell.SetCellValue(model.QuotationItems[i].Quantity);cell = row.CreateCell(7);cell.CellStyle = style1;cell.SetCellValue(model.QuotationItems[i].Quotation.DispatchDays >= 0 ? ((int)model.QuotationItems[i].Quotation.DispatchDays).ToString() : "");cell = row.CreateCell(8);cell.CellStyle = style1;cell.SetCellValue(model.QuotationItems[i].Quotation.UnitPriceWithTax >= 0 ? ((decimal)model.QuotationItems[i].Quotation.UnitPriceWithTax).ToString("f2") : "");cell = row.CreateCell(9);cell.CellStyle = style1;cell.SetCellValue(model.QuotationItems[i].Quotation.SubtotalWithTax.ToString("f2"));cell = row.CreateCell(10);cell.CellStyle = style1;cell.SetCellValue(model.QuotationItems[i].Remark);}sheet.GetRow(15 + count).GetCell(1).SetCellValue(model.QuotationOrder.Shipping.Amount.ToString("f2"));sheet.GetRow(15 + count).GetCell(4).SetCellValue(model.QuotationOrder.TotalWithTax.ToString("f2"));sheet.GetRow(15 + count).GetCell(7).SetCellValue(model.QuotationOrder.TotalWithTaxInChinese);sheet.GetRow(20 + count).GetCell(2).SetCellValue(model.Payment);return workbook;}

3、设置表格样式

/// <summary>/// 给Excel添加边框/// </summary>private  ICellStyle SetCellStyle(HSSFWorkbook hssfworkbook, HorizontalAlignment ha){ICellStyle cellstyle = hssfworkbook.CreateCellStyle();cellstyle.Alignment = ha;//有边框cellstyle.BorderBottom = BorderStyle.Thin;cellstyle.BorderLeft = BorderStyle.Thin;cellstyle.BorderRight = BorderStyle.Thin;cellstyle.BorderTop = BorderStyle.Thin;return cellstyle;}

4、excel加载图片

  HSSFPatriarch patriarch = (HSSFPatriarch)sheet.DrawingPatriarch;HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 0, 60, 7, 26 + count, 11, 38 + count);HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(tempDirPath + "1.png", (HSSFWorkbook)workbook));

LoadImage 方法

private int LoadImage(string path, HSSFWorkbook wb){FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);byte[] buffer = new byte[file.Length];file.Read(buffer, 0, (int)file.Length);return wb.AddPicture(buffer, PictureType.PNG);}

5、导出excel

var stream = new MemoryStream();
var work = ExportExcel(printQuotationOrderViewModel);work.Write(stream);//mvc代码return File(stream.GetBuffer(), "application/vnd.ms-excel", quotedOrderModel.Number + ".xls");