商务网站建设实训报告1600字,wordpress同步新浪微博,建企业网站用什么源码,网站制作的重要流程图文章目录 1、加入NPOI 程序集#xff0c;使用nuget添加程序集2、引用NPOI程序集3、设置表格样式4、excel加载图片5、导出excel 1、加入NPOI 程序集#xff0c;使用nuget添加程序集 2、引用NPOI程序集
private IWorkbook ExportExcel(PrintQuotationOrderViewModel model){//… 文章目录 1、加入NPOI 程序集使用nuget添加程序集2、引用NPOI程序集3、设置表格样式4、excel加载图片5、导出excel 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添加边框/// /summaryprivate 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);
