//用于浏览器导出Excel，即return File(path, "application/vnd.ms-excel", titleName + ".xlsx");
using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; namespace BaiCellsMall.Common { public class XSSFWorkbookCreateHelper { //创建Excel文件的对象 public XSSFWorkbook book; //添加一个sheet public ISheet sheet1; //默认单元格宽度 int CellWidth; //默认单元格高度 short CellHeight; Dictionary<int, int> dicWidth; Dictionary<int, short> dicHeight; /// <summary> /// /// </summary> /// <param name="cellWidth">单元格宽</param> /// <param name="cellHeight">单元格高</param> public XSSFWorkbookCreateHelper(int cellWidth, short cellHeight, VerticalAlignment? center = null) { CellWidth = cellWidth; CellHeight = cellHeight; dicWidth = new Dictionary<int, int>(); dicHeight = new Dictionary<int, short>(); //创建Excel文件的对象 book = new XSSFWorkbook(); //NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet sheet1 = book.CreateSheet("Sheet"); //样式 ICellStyle style = book.CreateCellStyle();//设置垂直居中格式 style.VerticalAlignment = center == null ? VerticalAlignment.Center : center.Value;// VerticalAlignment.Center;垂直居中 } /// <summary> /// 添加单元格 /// </summary> public void AddCell(CellModel model) { try { int cellWidth = CellWidth; short cellHeight = CellHeight; if (model.Width != null) { cellWidth = model.Width.Value; //第一个宽不被覆盖 if (!dicWidth.ContainsKey(model.C)) { dicWidth.Add(model.C, model.Width.Value); } } else if (dicWidth.ContainsKey(model.C)) { cellWidth = dicWidth[model.C]; } if (model.Height != null) { cellHeight = model.Height.Value; //第一个不被覆盖 if (!dicHeight.ContainsKey(model.R)) { dicHeight.Add(model.R, model.Height.Value); } } else if (dicHeight.ContainsKey(model.R)) { cellHeight = dicHeight[model.R]; } IRow row = sheet1.GetRow(model.R); if (row == null) { row = sheet1.CreateRow(model.R); row.Height = cellHeight; } var cell = row.GetCell(model.C); if (cell == null) { sheet1.SetColumnWidth(model.C, cellWidth); cell = row.CreateCell(model.C); } cell.SetCellValue(model.Value); //合并 if (model.REnd > model.R || model.CEnd > model.C) { for (int r = model.R; r <= model.REnd; r++) { var rowr = sheet1.GetRow(r); if (rowr == null) { rowr = sheet1.CreateRow(r); rowr.Height = cellHeight; } for (int c = model.C + 1; c <= model.CEnd; c++) { var cellc = rowr.GetCell(c); if (cellc == null) { sheet1.SetColumnWidth(c, cellWidth); cellc = rowr.CreateCell(c); } } } // 合并单元格 CellRangeAddress cra = new CellRangeAddress(model.R, model.REnd, model.C, model.CEnd); // 起始行, 终止行, 起始列, 终止列 sheet1.AddMergedRegion(cra); } } catch (Exception ex) { throw ex; } } } /// <summary> /// 单元格对象 /// </summary> public class CellModel { #region 重载构造 private CellModel() { } public CellModel(int r, int c, bool value, int? rend = null, int? cend = null, int? width = null, short? height = null) { init(r,c,value,rend,cend,width,height); } public CellModel(int r, int c, string value, int? rend = null, int? cend = null, int? width = null, short? height = null) { init(r, c, value, rend, cend, width, height); } public CellModel(int r, int c, IRichTextString value, int? rend = null, int? cend = null, int? width = null, short? height = null) { init(r, c, value, rend, cend, width, height); } public CellModel(int r, int c, DateTime value, int? rend = null, int? cend = null, int? width = null, short? height = null) { init(r, c, value, rend, cend, width, height); } public CellModel(int r, int c, double value, int? rend = null, int? cend = null, int? width = null, short? height = null) { init(r, c, value, rend, cend, width, height); } #endregion private void init(int r, int c, dynamic value, int? rend = null, int? cend = null, int? width = null, short? height = null) { R = r; C = c; if (value == null) { value = ""; } Value = value; if (rend == null) { rend = r; } if (cend == null) { cend = c; } REnd = rend.Value; CEnd = cend.Value; Width = width; Height = height; } internal int R;//行坐标 internal int C;//列坐标 internal int REnd;//单元格宽结束坐标，用于占多行(合并单元格) internal int CEnd;//单元格列结束坐标，用于占多列(合并单元格) internal dynamic Value;//单元格值，类型可选：bool、string、IRichTextString、DateTime、double internal int? Width; internal short? Height; } }
调用例
[HttpGet] public ActionResult ExportExcel(string searchJsonStr, int pageSize, int pageIndex) { try { //数据 Search_ViewOrderMain search = JsonConvert.DeserializeObject<Search_ViewOrderMain>(searchJsonStr); var dataList = OperateContext.Current.BLLSession.IOrder_MainBLL.GetOrderList(search, pageSize, pageIndex).Data; var states = EnumberHelper.EnumToList<EnumOrderState>(); //生成工作簿对象 int w = 4500; XSSFWorkbookCreateHelper bookHelper = new XSSFWorkbookCreateHelper(w, 400, NPOI.SS.UserModel.VerticalAlignment.Center); int r = 0, c = 0; //表头 bookHelper.AddCell(new CellModel(r, c++, "Customer Name", 1));//用户名 bookHelper.AddCell(new CellModel(r, c++, "Customer Email", 1)); bookHelper.AddCell(new CellModel(r, c++, "Customer Phone Number", 1)); bookHelper.AddCell(new CellModel(r, c, "Customer Address", cend: c+4));//地址 c += 5; bookHelper.AddCell(new CellModel(r, c++, "Invoice Number", 1)); bookHelper.AddCell(new CellModel(r, c++, "Invoice Date", 1)); bookHelper.AddCell(new CellModel(r, c++, "Invoice Due Date", 1)); bookHelper.AddCell(new CellModel(r, c++, "Product Name", 1)); bookHelper.AddCell(new CellModel(r, c++, "Product Part Number", 1));//商品零件号 bookHelper.AddCell(new CellModel(r, c++, "Quantity", 1));//商品数量 bookHelper.AddCell(new CellModel(r, c++, "Unit Price", 1));//单价 bookHelper.AddCell(new CellModel(r, c++, "Sales Total", 1));//总销售数量 bookHelper.AddCell(new CellModel(r, c++, "Shipping Fee", 1));//运费 bookHelper.AddCell(new CellModel(r, c++, "Sales Tax Rate", 1));//税率 bookHelper.AddCell(new CellModel(r, c++, "Sales Tax Total", 1));//税费 bookHelper.AddCell(new CellModel(r, c++, "Invoice Total", 1));//发票总额 bookHelper.AddCell(new CellModel(r, c++, "Payment Status", 1)); bookHelper.AddCell(new CellModel(r, c++, "Payment Method", 1)); bookHelper.AddCell(new CellModel(r, c++, "Payment Service FEE", 1));//支付服务费 r++; c = 3; bookHelper.AddCell(new CellModel(r, c++, "street Address"));//街道地址 bookHelper.AddCell(new CellModel(r, c++, "city")); bookHelper.AddCell(new CellModel(r, c++, "State")); bookHelper.AddCell(new CellModel(r, c++, "zipcode"));//邮编 bookHelper.AddCell(new CellModel(r, c++, "country"));//国家地区 //表内容 var rAdd = 0;//一行订单因为多种商品要多换的行数 for (int i = 0; i < dataList.Count; i++) { View_OrderMain item = dataList[i]; r++; c = 0; rAdd = item.OrderProduct.Count-1;//多换的行数 bookHelper.AddCell(new CellModel(r, c++, item.MemberName, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, item.Email, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, item.Mobile, r + rAdd)); string state = states.Exists(x => x.EnumValue == item.OrderState) ? states.FirstOrDefault(x => x.EnumValue == item.OrderState).Desction : ""; bookHelper.AddCell(new CellModel(r, c++, string.IsNullOrWhiteSpace(item.ShippingAddress)? item.ShippingAddress2: item.ShippingAddress, r + rAdd, w * 3)); string city = ""; if (item.Region.Split(',').Length > 1) { city = item.Region.Split(',')[item.Region.Split(',').Length-1]; } bookHelper.AddCell(new CellModel(r, c++, city, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, state, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, item.ZipCode, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, item.Region, r + rAdd, w * 3)); bookHelper.AddCell(new CellModel(r, c++, item.InvoiceNumber, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, item.InvoiceDate.HasValue? item.InvoiceDate.Value.ToString("yyyy--MM-dd HH:mm"):"", r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, item.InvoiceDueDate.HasValue ? item.InvoiceDueDate.Value.ToString("yyyy--MM-dd HH:mm") : "", r + rAdd)); //商品 int salesTotal = 0;//商品总销售 int startC = c; int startR = r; foreach (var product in item.OrderProduct) { c = startC;//恢复列 bookHelper.AddCell(new CellModel(r, c++, product.ProductName)); bookHelper.AddCell(new CellModel(r, c++, product.PartNumber)); bookHelper.AddCell(new CellModel(r, c++, product.SaleNumber));//销售数量 bookHelper.AddCell(new CellModel(r, c++, product.Price.ToString())); salesTotal += product.SaleNumber; r++;//商品换行 } r = startR;//恢复行 bookHelper.AddCell(new CellModel(r, c++, salesTotal, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, item.Postage.ToString(), r + rAdd));//运费 bookHelper.AddCell(new CellModel(r, c++, item.CostRatio.ToString(), r + rAdd));//税率 decimal tax = item.CostRatio<=0?0: Math.Round(((item.TotalFee + item.FreeFee) / item.CostRatio),2,MidpointRounding.AwayFromZero); bookHelper.AddCell(new CellModel(r, c++, tax.ToString(), r + rAdd));//税费 bookHelper.AddCell(new CellModel(r, c++, item.InvoiceTotal.ToString(), r + rAdd, w * 3)); //支付信息 bookHelper.AddCell(new CellModel(r, c++, item.PayTime.HasValue? "Payment completed" : "Awaiting payment", r + rAdd)); string payMethod = ""; if (item.CreateType == 0) { switch (item.PayType) { case 1: payMethod = "Card"; break; case 2: payMethod = "Online"; break; case 3: payMethod = "Account Balance"; break; case 4: payMethod = "Insufficient account balance"; payMethod += " Account Balance：" + item.PayBalance; payMethod += " Online：" + item.PayCash; break; default: break; } } bookHelper.AddCell(new CellModel(r, c++, payMethod, r + rAdd)); bookHelper.AddCell(new CellModel(r, c++, "" , r + rAdd));//Payment Service FEE r += rAdd;//因为商品多换的行数 } string path = AppDomain.CurrentDomain.BaseDirectory + "/template/excel"; if (!System.IO.Directory.Exists(path)) { System.IO.Directory.CreateDirectory(path); } path += "/order_data.xlsx"; using (FileStream ms = new FileStream(path, FileMode.Create)) { bookHelper.book.Write(ms); string titleName = "OrderData"; return File(path, "application/vnd.ms-excel", titleName + ".xlsx"); } } catch (Exception ex) { Common.LogHelper.Instance.Error("导出统计数据出错", ex); } return Content("Export failed"); }
//导出excel详细 scope.ExportExcel = function () { if (scope.rowCount > 1000) { alert("本次导出数据较多，成功导出后将自动下载，请勿频繁操作！") } if (scope.rowCount > 0) { window.open("ExportExcel?searchJsonStr=" + JSON.stringify(scope.searchModel)); } }