Npoi导出xlsx
xlsx文件可以超出，excel2003行数限制。必要的dll文件。
public void Export() { string filename = Request["searchString"]; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename+ ".xlsx")); NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("BOM详情"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("物料编码"); row1.CreateCell(1).SetCellValue("物料名称"); row1.CreateCell(2).SetCellValue("规格型号"); row1.CreateCell(3).SetCellValue("物料用量"); row1.CreateCell(4).SetCellValue("用量单位"); row1.CreateCell(5).SetCellValue("备注"); //将数据逐步写入sheet1各个行 List<AkBom> pageResult = _akBomRepository.GetPageList(0, 10000, Request["searchString"], ""); for (int i = 0; i < pageResult.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(pageResult[i].ChildNumber); rowtemp.CreateCell(1).SetCellValue(pageResult[i].ChildName); rowtemp.CreateCell(2).SetCellValue(pageResult[i].Spec); rowtemp.CreateCell(3).SetCellValue(double.Parse(pageResult[i].MaterialSum.ToString())); rowtemp.CreateCell(4).SetCellValue(pageResult[i].Unit); rowtemp.CreateCell(5).SetCellValue(pageResult[i].Remark); } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); Response.BinaryWrite(ms.ToArray()); Response.Flush(); Response.End(); }
public void Export() { string searchString = Request["searchString"]; string line = Request["line"]; string station = Request["station"]; string begin = Request["begin"]; string end = Request["end"]; Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "FQC.xlsx")); NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet("FQC"); //excel格式化 NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss"); NPOI.SS.UserModel.ICellStyle numberStyle = workbook.CreateCellStyle(); numberStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00000"); NPOI.SS.UserModel.ICellStyle textStyle = workbook.CreateCellStyle(); textStyle.DataFormat = workbook.CreateDataFormat().GetFormat("@"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("订单号"); row1.CreateCell(1).SetCellValue("条码"); row1.CreateCell(2).SetCellValue("档位名称"); row1.CreateCell(3).SetCellValue("Pmax"); row1.CreateCell(4).SetCellValue("功率档"); row1.CreateCell(5).SetCellValue("功率档范围"); row1.CreateCell(6).SetCellValue("Ipm"); row1.CreateCell(7).SetCellValue("电流档"); row1.CreateCell(8).SetCellValue("电流档范围"); row1.CreateCell(9).SetCellValue("规格"); row1.CreateCell(10).SetCellValue("产品等级"); row1.CreateCell(11).SetCellValue("电池片等级"); row1.CreateCell(12).SetCellValue("FQC不良"); row1.CreateCell(13).SetCellValue("判定结果"); row1.CreateCell(14).SetCellValue("人员"); row1.CreateCell(15).SetCellValue("线别"); row1.CreateCell(16).SetCellValue("工位"); row1.CreateCell(17).SetCellValue("备注"); row1.CreateCell(18).SetCellValue("扫描时间"); row1.CreateCell(19).SetCellValue("输入时间"); row1.CreateCell(20).SetCellValue("Eff"); row1.CreateCell(21).SetCellValue("Isc"); row1.CreateCell(22).SetCellValue("Voc"); row1.CreateCell(23).SetCellValue("Rs"); row1.CreateCell(24).SetCellValue("Rsh"); row1.CreateCell(25).SetCellValue("Vpm"); row1.CreateCell(26).SetCellValue("FF"); row1.CreateCell(27).SetCellValue("Sun"); row1.CreateCell(28).SetCellValue("Temp"); row1.CreateCell(29).SetCellValue("Class"); //将数据逐步写入sheet1各个行 string strSql = "where AkFqc.BarCode like '%@param%' and (AkFqc.DateTime between '@begin' and '@end') and AkFqc.LineTitle like '%@line%' and AkFqc.StationTitle like '%@station%'"; strSql = strSql.Replace("@param", searchString); strSql = strSql.Replace("@begin", begin); strSql = strSql.Replace("@end", end); strSql = strSql.Replace("@line", line); strSql = strSql.Replace("@station", station); List<AkFqc> pageResult = _akFqcRepository.GetPageList(0, 100000, strSql, "order by AkFqc.Id desc"); for (int i = 0; i < pageResult.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(pageResult[i].OrderNumber); rowtemp.CreateCell(1).SetCellValue(pageResult[i].BarCode); rowtemp.CreateCell(2).SetCellValue(pageResult[i].Title); rowtemp.CreateCell(3).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Pmax))); rowtemp.CreateCell(4).SetCellValue(pageResult[i].PTitle); rowtemp.CreateCell(5).SetCellValue(pageResult[i].PScope); rowtemp.CreateCell(6).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Ipm))); rowtemp.CreateCell(7).SetCellValue(pageResult[i].ITitle); rowtemp.CreateCell(8).SetCellValue(pageResult[i].IScope); rowtemp.CreateCell(9).SetCellValue(pageResult[i].Spec); rowtemp.CreateCell(10).SetCellValue(pageResult[i].ProductLevel); rowtemp.CreateCell(11).SetCellValue(pageResult[i].BatteryLevel); rowtemp.CreateCell(12).SetCellValue(pageResult[i].BadReason); rowtemp.CreateCell(13).SetCellValue(pageResult[i].JudgeResult); rowtemp.CreateCell(14).SetCellValue(pageResult[i].Employee); rowtemp.CreateCell(15).SetCellValue(pageResult[i].LineTitle); rowtemp.CreateCell(16).SetCellValue(pageResult[i].StationTitle); rowtemp.CreateCell(17).SetCellValue(pageResult[i].Remark); rowtemp.CreateCell(18).SetCellValue(pageResult[i].DateTime); rowtemp.CreateCell(19).SetCellValue(pageResult[i].ScanDate); rowtemp.CreateCell(20).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Eff))); rowtemp.CreateCell(21).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Isc))); rowtemp.CreateCell(22).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Voc))); rowtemp.CreateCell(23).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Rs))); rowtemp.CreateCell(24).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Rsh))); rowtemp.CreateCell(25).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Vpm))); rowtemp.CreateCell(26).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].FF))); rowtemp.CreateCell(27).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Sun))); rowtemp.CreateCell(28).SetCellValue(Convert.ToDouble(string.Format("{0:0.00000}", pageResult[i].Temp))); rowtemp.CreateCell(29).SetCellValue(pageResult[i].Class); rowtemp.GetCell(0).CellStyle = textStyle; rowtemp.GetCell(1).CellStyle = textStyle; rowtemp.GetCell(2).CellStyle = textStyle; rowtemp.GetCell(3).CellStyle = numberStyle; rowtemp.GetCell(4).CellStyle = textStyle; rowtemp.GetCell(5).CellStyle = textStyle; rowtemp.GetCell(6).CellStyle = numberStyle; rowtemp.GetCell(7).CellStyle = textStyle; rowtemp.GetCell(8).CellStyle = textStyle; rowtemp.GetCell(9).CellStyle = textStyle; rowtemp.GetCell(10).CellStyle = textStyle; rowtemp.GetCell(11).CellStyle = textStyle; rowtemp.GetCell(12).CellStyle = textStyle; rowtemp.GetCell(13).CellStyle = textStyle; rowtemp.GetCell(14).CellStyle = textStyle; rowtemp.GetCell(15).CellStyle = textStyle; rowtemp.GetCell(16).CellStyle = textStyle; rowtemp.GetCell(17).CellStyle = textStyle; rowtemp.GetCell(18).CellStyle = dateStyle; rowtemp.GetCell(19).CellStyle = dateStyle; rowtemp.GetCell(20).CellStyle = numberStyle; rowtemp.GetCell(21).CellStyle = numberStyle; rowtemp.GetCell(22).CellStyle = numberStyle; rowtemp.GetCell(23).CellStyle = numberStyle; rowtemp.GetCell(24).CellStyle = numberStyle; rowtemp.GetCell(25).CellStyle = numberStyle; rowtemp.GetCell(26).CellStyle = numberStyle; rowtemp.GetCell(27).CellStyle = numberStyle; rowtemp.GetCell(28).CellStyle = numberStyle; rowtemp.GetCell(29).CellStyle = textStyle; } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); Response.BinaryWrite(ms.ToArray()); Response.Flush(); Response.End(); }
