使用NPOI或POI 导出Excel大数据(百万级以上)，导致内存溢出的解决方案（NPOI，POI）
使用工具：POI(JAVA)，NPOI(.Net)
致谢博主 Crazy_Jeff 提供的思路
一、问题描述：
导出任务数据量近100W甚至更多，导出的项目就会内存溢出，挂掉。
二、原因分析：
1、每个进程在写Excel文件时，都是先将数据加载到内存，然后再将内存里面的数据生成文件；因此单个进程任务的数据量过大，将无法及时回收系统内存，最终导致系统内存耗尽而宕机。
2、导出中查询结果是一次性全部查询出来，占用大量系统内存资源。
三、优化方案思路：
1、将所有导出查询全部改成分页的方式查询；
2、将写Excel文件使用IO流来实现，采用POI，或NPOI拼接xml字符串完成，迭代一批数据就flush进硬盘，同时把list，大对象赋值为空，显式调用垃圾回收器，及时回收内存。
首先提供Java版代码POI实现，来自：https://blog.csdn.net/SirLZF/article/details/47438899
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.OutputStreamWriter; import java.io.Writer; import java.lang.reflect.Method; import java.util.Calendar; import java.util.Enumeration; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.UUID; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFDataFormat; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.chengfeng.ne.global.service.ITaskService; import com.thinkjf.core.config.GlobalConfig; /** * 功能描述：生成Excel文件类 * @author Jeff * @version 1.0 * @date 2015-08-03 */ @Service("xlsxOutPutService") public class XlsxOutPutService { @Autowired private ITaskService taskService; /** * 导出每个sheet行数 */ public int pageSize = Integer.parseInt(GlobalConfig .getPropertyValue("common.exoprt.Worksheet.max.rownum")); /** * 根据传入的不同serviceName来执行不同的查询语句 * @param serviceName * @param execMethod * @param params * @param pageIndex * @return */ public List<?> queryBySerivceName(String serviceName,String execMethod, Map<String, Object> params,int pageIndex)throws Exception{ List<?> resultList = null; if("taskService".equals(serviceName)){ resultList = taskService.queryExportResultPage(execMethod,params, pageIndex, pageSize); } return resultList; } /** * 生成Excel文件外部调用方法 * @param headList 标题列表 * @param fieldName 字段列表 * @param sheetName 工作薄sheet名称 * @param tempFilePath 临时文件目录 * @param filePath 目标文件 * @param execMethod 执行sql * @param params 查询参数 * @param serviceName 执行service方法对象名称 * @throws Exception */ public void generateExcel(List<String> headList,List<String> fieldName,String sheetName, String tempFilePath,String filePath,String execMethod, Map<String, Object> params,String serviceName) throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> styles = createStyles(wb); XSSFSheet sheet = wb.createSheet(sheetName); String sheetRef = sheet.getPackagePart().getPartName().getName(); String sheetRefList = sheetRef.substring(1); File tempFiledir = new File(tempFilePath); if(!tempFiledir.exists()){ tempFiledir.mkdirs(); } String uuid = UUID.randomUUID().toString(); uuid = uuid.replace("-", ""); File sheetFileList = new File(tempFilePath + "/sheet_" + uuid + ".xml"); File tmpFile = new File(tempFilePath + "/"+uuid+".xlsx"); FileOutputStream os = new FileOutputStream(tmpFile); wb.write(os); os.close(); Writer fw = new OutputStreamWriter(new FileOutputStream( sheetFileList), "UTF-8"); //生成sheet generateExcelSheet(headList,fieldName, fw, styles,execMethod,params,serviceName); fw.close(); //将临时文件压缩替换 FileOutputStream out = new FileOutputStream(filePath); substituteAll(tmpFile, sheetFileList, sheetRefList, out); out.close(); // 删除临时文件 tmpFile.delete(); sheetFileList.delete(); tmpFile = null; sheetFileList = null; os = null; fw = null; out = null; Runtime.getRuntime().gc(); } /** * 生成sheet * @param headList * @param fields * @param out * @param styles * @param execMethod * @param params * @throws Exception */ private void generateExcelSheet(List<String> headList,List<String> fields,Writer out, Map<String, XSSFCellStyle> styles,String execMethod, Map<String, Object> params,String serviceName) throws Exception { XSSFCellStyle stringStyle = styles.get("cell_string"); XSSFCellStyle longStyle = styles.get("cell_long"); XSSFCellStyle doubleStyle = styles.get("cell_double"); XSSFCellStyle dateStyle = styles.get("cell_date"); Calendar calendar = Calendar.getInstance(); SpreadsheetWriter sw = new SpreadsheetWriter(out); sw.beginWorkSheet(); sw.beginSetColWidth(); for (int i = 10, len = headList.size() - 2; i < len; i++) { sw.setColWidthBeforeSheet(i, 13); } sw.setColWidthBeforeSheet(headList.size() - 1, 16); sw.endSetColWidth(); sw.beginSheet(); // 表头 sw.insertRowWithheight(0, headList.size(), 25); int styleIndex = ((XSSFCellStyle) styles.get("sheet_title")).getIndex(); for (int i = 0, len = headList.size(); i < len; i++) { sw.createCell(i, headList.get(i), styleIndex); } sw.endWithheight(); // int pageIndex = 1;// 查询起始页 Boolean isEnd = false;// 是否是最后一页，循环条件 do {// 开始分页查询 // 导出查询改为分页查询方式，替代原有queryExportResult()方法 long startTimne = System.currentTimeMillis(); List<?> dataList = this.queryBySerivceName(serviceName, execMethod, params, pageIndex); long endTime = System.currentTimeMillis(); System.out.println("查询"+pageIndex+"完成用时="+((endTime-startTimne))+"毫秒"); if (dataList != null && dataList.size() > 0) { //写方法------- int cellIndex = 0; for (int rownum = 1, len = dataList.size() + 1; rownum < len; rownum++) { cellIndex = 0; sw.insertRow((pageIndex-1)*pageSize+rownum); Object data = dataList.get(rownum-1); Object val = null; Method fieldMethod = null; for (int k = 0, len2 = fields.size(); k < len2; k++) { fieldMethod = (Method) data.getClass().getMethod("get"+ fields.get(k)); fieldMethod.setAccessible(true);// 不进行安全检测 val = fieldMethod.invoke(data); if(val == null){ sw.createCell(cellIndex,"",stringStyle.getIndex()); }else{ String typeName = fieldMethod.getGenericReturnType().toString(); if (typeName.endsWith("int") || typeName.endsWith("nteger")) { sw.createCell(cellIndex, (Integer) val, longStyle.getIndex()); } else if (typeName.endsWith("ong")) { sw.createCell(cellIndex, (Long) val, longStyle.getIndex()); } else if (typeName.endsWith("ouble")) { sw.createCell(cellIndex, (Double) val, doubleStyle.getIndex()); } else if (typeName.endsWith("util.Date")) { calendar.setTime((java.util.Date) val); sw.createCell(cellIndex, calendar, dateStyle.getIndex()); } else if (typeName.endsWith("sql.Date")) { calendar.setTime((java.sql.Date) val); sw.createCell(cellIndex, calendar, dateStyle.getIndex()); } else { sw.createCell(cellIndex, val==null?"":val.toString().replace("<", "<").replace(">", ">"), stringStyle.getIndex()); } } cellIndex++; } sw.endRow(); if (rownum % 2000 == 0) { out.flush(); } } //------------ isEnd = true; pageIndex++; } else { isEnd = false; } dataList = null; Runtime.getRuntime().gc(); } while (isEnd); sw.endSheet(); // 合并单元格 // sw.beginMergerCell(); // for (int i = 0, len = dataList.size() + 1; i < len; i++) { // sw.setMergeCell(i, 8, i, 9); // } // sw.endMergerCell(); sw.endWorkSheet(); } /** * 创建Excel样式 * @param wb * @return */ private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> stylesMap = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style = wb.createCellStyle(); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_string", style); XSSFCellStyle style2 = wb.createCellStyle(); style2.setDataFormat(fmt.getFormat("0")); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_long", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setDataFormat(fmt.getFormat("0.00")); style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_double", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setDataFormat(fmt.getFormat("yyyy-MM-dd HH:mm:ss")); style4.setAlignment(XSSFCellStyle.ALIGN_CENTER); style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("cell_date", style4); XSSFCellStyle style5 = wb.createCellStyle(); style5.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setAlignment(XSSFCellStyle.ALIGN_CENTER); style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); stylesMap.put("sheet_title", style5); return stylesMap; } /** * 打包压缩 * @param zipfile * @param tmpfileList * @param entryList * @param out * @throws IOException */ private void substituteAll(File zipfile,File tmpfileList, String entryList, OutputStream out) throws IOException { ZipFile zip = new ZipFile(zipfile); ZipOutputStream zos = new ZipOutputStream(out); @SuppressWarnings("unchecked") Enumeration<ZipEntry> en = (Enumeration<ZipEntry>)zip.entries(); while (en.hasMoreElements()) { ZipEntry ze = en.nextElement(); if (!entryList.contains(ze.getName())) { zos.putNextEntry(new ZipEntry(ze.getName())); InputStream is = zip.getInputStream(ze); copyStream(is, zos); is.close(); is = null; System.gc(); } } InputStream is = null; zos.putNextEntry(new ZipEntry(entryList)); is = new FileInputStream(tmpfileList); copyStream(is, zos); is.close(); zos.close(); zip.close(); is = null; zos = null; zip = null; System.gc(); } private static void copyStream(InputStream in, OutputStream out) throws IOException { byte[] chunk = new byte[1024*10]; int count; while ((count = in.read(chunk)) >= 0) out.write(chunk, 0, count); } public int getTrueColumnNum(String address) { address = address.replaceAll("[^a-zA-Z]", "").toLowerCase(); char[] adds = address.toCharArray(); int base = 1; int total = 0; for (int i = adds.length - 1; i >= 0; i--) { total += (adds[i] - 'a' + 1) * base; base = 26 * base; } return total; } public static class SpreadsheetWriter { private final Writer _out; private int _rownum; public SpreadsheetWriter(Writer out) { this._out = out; } public void beginWorkSheet() throws IOException { this._out .write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"); } public void beginSheet() throws IOException { this._out.write("<sheetData>\n"); } public void endSheet() throws IOException { this._out.write("</sheetData>"); // 合并单元格 } public void endWorkSheet() throws IOException { this._out.write("</worksheet>"); } //插入行 不带高度 public void insertRow(int rownum) throws IOException { this._out.write("<row r=\"" + (rownum + 1) + "\">\n"); this._rownum = rownum; } public void endRow() throws IOException { this._out.write("</row>\n"); } //插入行且设置高度 public void insertRowWithheight(int rownum, int columnNum, double height) throws IOException { this._out.write("<row r=\"" + (rownum + 1) + "\" spans=\"1:" + columnNum + "\" ht=\"" + height + "\" customHeight=\"1\">\n"); this._rownum = rownum; } public void endWithheight() throws IOException { this._out.write("</row>\n"); } public void beginSetColWidth() throws IOException { this._out.write("<cols>\n"); } // 设置列宽 下标从0开始 public void setColWidthBeforeSheet(int columnIndex, double columnWidth) throws IOException { this._out.write("<col min=\"" + (columnIndex + 1) + "\" max=\"" + (columnIndex + 1) + "\" width=\"" + columnWidth + "\" customWidth=\"1\"/>\n"); } public void endSetColWidth() throws IOException { this._out.write("</cols>\n"); } public void beginMergerCell() throws IOException { this._out.write("<mergeCells>\n"); } public void endMergerCell() throws IOException { this._out.write("</mergeCells>\n"); } // 合并单元格 下标从0开始 public void setMergeCell(int beginColumn, int beginCell, int endColumn, int endCell) throws IOException { this._out.write("<mergeCell ref=\"" + getExcelName(beginCell + 1) + (beginColumn + 1) + ":" + getExcelName(endCell + 1) + (endColumn + 1) + "\"/>\n");// 列行:列行 } public void createCell(int columnIndex, String value, int styleIndex) throws IOException { String ref = new CellReference(this._rownum, columnIndex) .formatAsString(); this._out.write("<c r=\"" + ref + "\" t=\"inlineStr\""); if (styleIndex != -1) this._out.write(" s=\"" + styleIndex + "\""); this._out.write(">"); this._out.write("<is><t>" + value + "</t></is>"); this._out.write("</c>"); } public void createCell(int columnIndex, String value) throws IOException { createCell(columnIndex, value, -1); } public void createCell(int columnIndex, double value, int styleIndex) throws IOException { String ref = new CellReference(this._rownum, columnIndex) .formatAsString(); this._out.write("<c r=\"" + ref + "\" t=\"n\""); if (styleIndex != -1) this._out.write(" s=\"" + styleIndex + "\""); this._out.write(">"); this._out.write("<v>" + value + "</v>"); this._out.write("</c>"); } public void createCell(int columnIndex, double value) throws IOException { createCell(columnIndex, value, -1); } public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException { createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex); } //10 进制转26进制 private String getExcelName(int i) { char[] allChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray(); StringBuilder sb = new StringBuilder(); while (i > 0) { sb.append(allChar[i % 26 - 1]); i /= 26; } return sb.reverse().toString(); } } }
调用方法如下
String tempFilePath = GlobalConfig.getPropertyValue("common.attach.upload_dir") + "/task/tmp/"; //调用新的生成方法
xlsxOutPutService.generateExcel(Arrays.asList(cellName), fieldName,MessageUtils.getMessage(exportDateType.toString()),tempFilePath, expFilePath, execMethod, params, "taskService");
.net NPOI实现，这里没有使用list对象，而是将list转成了datatable后再来生成execl,支持多sheet操作
using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.IO; using System.Text; using ICSharpCode.SharpZipLib.Zip; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel;
/// <summary> /// Xlsx输出 /// editor:571115139@qq.com /// </summary> public class XlsxOutputHelper { private const int FlushCnt = 2000; private static readonly string _tempFilePath = LocalStorge.TempDirectory; public int TotalCnt = 0; public Action<int> ProgressShow = null; private readonly string _batchId; private List<EntryPackage> _sheetFileList = new List<EntryPackage>(); private readonly string _filePath; private readonly string _tempFile; private Dictionary<string, XSSFCellStyle> _styles; public XlsxOutputHelper(string filePath) { var ext = Path.GetExtension(filePath); if (ext != ".xlsx") { _filePath = Path.GetFileNameWithoutExtension(filePath) + ".xlsx"; } else { _filePath = filePath; } File.Create(_filePath).Close(); _batchId = Guid.NewGuid().ToString("N"); _tempFile = _tempFilePath + "/" + _batchId + ".xlsx"; } public void BeginGenerate(List<string> sheetNames) { XSSFWorkbook wb = new XSSFWorkbook(); _styles = CreateStyles(wb); foreach (var sheetName in sheetNames) { wb.CreateSheet(sheetName); } using (var os = new FileStream(_tempFile, FileMode.Create, FileAccess.ReadWrite)) { wb.Write(os); } } /// <summary> /// 生成Excel,多个sheet文件外部调用方法 /// </summary> /// <param name="headList">标题列表</param> /// <param name="sheetName">工作薄sheet名称</param> /// <param name="querySerivce">查询服务</param> public bool GenerateSheet(List<string> headList, string sheetName, Func<int/*页码*/,int/*数据标识*/, DataPackage/*返回数据*/> querySerivce) { if (!File.Exists(_tempFile)) throw new Exception("请先执行BeginGenerate方法"); XSSFWorkbook wb = new XSSFWorkbook(_tempFile); XSSFSheet sheet = (XSSFSheet)wb.GetSheet(sheetName); string sheetRef = sheet.GetPackagePart().PartName.Name; string sheetRefList = sheetRef.Substring(1); wb.Close(); if (!Directory.Exists(_tempFilePath)) { Directory.CreateDirectory(_tempFilePath); } string guid = Guid.NewGuid().ToString("N"); string sheetFileListFile = _tempFilePath + "/sheet_" + guid + ".xml"; bool isOk = true; using (var s = File.OpenWrite(sheetFileListFile)) { using (StreamWriter fw = new StreamWriter(s, Encoding.UTF8)) { //生成sheet if (!GenerateExcelSheet(headList, fw, _styles,querySerivce)) { isOk = false; } } } if (!isOk) { FileHelper.DeleteFile(sheetFileListFile); return false; } _sheetFileList.Add(new EntryPackage() { EntryPath = sheetRefList, XmlFile = sheetFileListFile }); return true; } /// <summary> /// 结束生成Excel写入文件到本地 /// </summary> /// <param name="writefileConsole"></param> /// <returns></returns> public bool EndGenerate(Action<string> writefileConsole) { if (!File.Exists(_tempFile)) throw new Exception("请先执行BeginGenerate方法"); if (_sheetFileList == null || _sheetFileList.Count == 0) return false; writefileConsole("正在写入文件，请耐心等待...."); //将临时文件压缩替换 using (var output = File.OpenWrite(_filePath)) { SubstituteAll(_tempFile, _sheetFileList, output); } // 删除临时文件 FileHelper.DeleteFile(_tempFile); foreach (var entryPackage in _sheetFileList) { FileHelper.DeleteFile(entryPackage.XmlFile); } return true; } ///// <summary> ///// 生成Excel文件外部调用方法 ///// </summary> ///// <param name="headList">标题列表</param> ///// <param name="sheetName">工作薄sheet名称</param> ///// <param name="filePath">目标文件</param> ///// <param name="writefileConsole">进度输出</param> //public bool GenerateExcel(List<string> headList, string sheetName, string filePath, Action<string> writefileConsole) //{ // XSSFWorkbook wb = new XSSFWorkbook(); // Dictionary<string, XSSFCellStyle> styles = CreateStyles(wb); // XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(sheetName); // string sheetRef = sheet.GetPackagePart().PartName.Name; // string sheetRefList = sheetRef.Substring(1); // if (!Directory.Exists(_tempFilePath)) // { // Directory.CreateDirectory(_tempFilePath); // } // string guid = Guid.NewGuid().ToString("N"); // string sheetFileListFile = _tempFilePath + "/sheet_" + guid + ".xml"; // string tmpFile = _tempFilePath + "/" + guid + ".xlsx"; // using (var os = new FileStream(tmpFile, FileMode.Create, FileAccess.ReadWrite)) // { // wb.Write(os); // } // using (var s = File.OpenWrite(sheetFileListFile)) // { // using (StreamWriter fw = new StreamWriter(s, Encoding.UTF8)) // { // //生成sheet // if (!GenerateExcelSheet(headList, fw, styles)) // { // return false; // } // } // } // writefileConsole("正在写入文件，请耐心等待...."); // //将临时文件压缩替换 // using (var output = File.OpenWrite(filePath)) // { // SubstituteAll(tmpFile, sheetFileListFile, sheetRefList, output); // } // // 删除临时文件 // File.Delete(tmpFile); // File.Delete(sheetFileListFile); // return true; //} /// <summary> /// 生成sheet /// </summary> /// <param name="headList"></param> /// <param name="output"></param> /// <param name="styles"></param> /// <param name="querySerivce"></param> private bool GenerateExcelSheet(List<string> headList, StreamWriter output, Dictionary<string, XSSFCellStyle> styles, Func<int/*页码*/, int/*数据标识*/, DataPackage/*返回数据*/> querySerivce) { XSSFCellStyle stringStyle = styles["cell_string"]; XSSFCellStyle longStyle = styles["cell_long"]; XSSFCellStyle doubleStyle = styles["cell_double"]; XSSFCellStyle dateStyle = styles["cell_date"]; SpreadsheetWriter sw = new SpreadsheetWriter(output); int[] arrColWidth = new int[headList.Count]; for (int i = 0; i < headList.Count; i++) { arrColWidth[i] = Math.Max(Encoding.GetEncoding(936).GetBytes(headList[i]).Length, 10); } sw.BeginWorkSheet(); sw.BeginSetColWidth(); for (int i = 0; i < headList.Count; i++) { sw.SetColWidthBeforeSheet(i, arrColWidth[i]+1); } sw.EndSetColWidth(); sw.BeginSheet(); // 表头 sw.InsertRowWithheight(0, headList.Count, 15); int styleIndex = styles["sheet_title"].Index; for (int i = 0, len = headList.Count; i < len; i++) { sw.CreateCell(i, headList[i], styleIndex); } sw.EndWithheight(); // int pageIndex = 1;// 查询起始页 bool hasNextRow;// 是否还有数据，循环条件 int flag = 0;//用于多批数据的处理 int rownum = 1;//总行数 do {// 开始分页查询 // 导出查询改为分页查询方式，替代原有queryExportResult()方法 DataPackage data = querySerivce(pageIndex, flag); if (!data.IsSucess) return false; if(flag==0 || data.Flag==0) flag = data.Flag; if (flag != 0 && flag != data.Flag) { flag = data.Flag; pageIndex = 1; hasNextRow = true; continue; } var dt = data.Table; if (dt != null && dt.Rows.Count > 0) { int cellIndex; foreach (DataRow row in dt.Rows) { cellIndex = 0; sw.InsertRow(rownum); #region 填充内容 foreach (DataColumn column in dt.Columns) { string drValue = row[column].ToString(); if (drValue.IsNullOrWiteSpace()) { sw.CreateCell(cellIndex, "", stringStyle.Index); } else { switch (column.DataType.ToString()) { case "System.DateTime"://日期类型 DateTime.TryParse(drValue, out DateTime dateV); sw.CreateCell(cellIndex, dateV, dateStyle.Index); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int.TryParse(drValue, out int intV); sw.CreateCell(cellIndex, intV, longStyle.Index); break; case "System.Decimal"://浮点型 case "System.Double": double.TryParse(drValue, out double doubV); sw.CreateCell(cellIndex, doubV, doubleStyle.Index); break; case "System.DBNull"://空值处理 sw.CreateCell(cellIndex, "", stringStyle.Index); break; default: sw.CreateCell(cellIndex, drValue.Replace("<", "<").Replace(">", ">"), stringStyle.Index); break; } } cellIndex++; } #endregion sw.EndRow(); if (rownum % FlushCnt == 0) { output.Flush(); } rownum++; } ProgressShow?.Invoke(TotalCnt += rownum - 1); hasNextRow = true; pageIndex++; } else { hasNextRow = false; } GC.Collect(); } while (hasNextRow); sw.EndSheet(); sw.EndWorkSheet(); return true; } /// <summary> /// 创建Excel样式 /// </summary> /// <param name="wb"></param> /// <returns></returns> private static Dictionary<string, XSSFCellStyle> CreateStyles(XSSFWorkbook wb) { Dictionary<string, XSSFCellStyle> stylesMap = new Dictionary<string, XSSFCellStyle>(); IDataFormat fmt = wb.CreateDataFormat(); ICellStyle style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Left; style.VerticalAlignment = VerticalAlignment.Center; stylesMap.Add("cell_string", (XSSFCellStyle)style); ICellStyle style2 = wb.CreateCellStyle(); style2.DataFormat = fmt.GetFormat("0"); style2.Alignment = HorizontalAlignment.Center; style2.VerticalAlignment = VerticalAlignment.Center; stylesMap.Add("cell_long", (XSSFCellStyle)style2); ICellStyle style3 = wb.CreateCellStyle(); style3.DataFormat = fmt.GetFormat("0"); style3.Alignment = HorizontalAlignment.Center; style3.VerticalAlignment = VerticalAlignment.Center; stylesMap.Add("cell_double", (XSSFCellStyle)style3); ICellStyle style4 = wb.CreateCellStyle(); style4.DataFormat = fmt.GetFormat("yyyy-MM-dd HH:mm"); style4.Alignment = HorizontalAlignment.Center; style4.VerticalAlignment = VerticalAlignment.Center; stylesMap.Add("cell_date", (XSSFCellStyle)style4); ICellStyle style5 = wb.CreateCellStyle(); style5.FillForegroundColor = IndexedColors.Grey25Percent.Index; style5.FillPattern = FillPattern.SolidForeground; style5.Alignment = HorizontalAlignment.Center; style5.VerticalAlignment = VerticalAlignment.Center; IFont font = wb.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; style5.SetFont(font); stylesMap.Add("sheet_title", (XSSFCellStyle)style5); return stylesMap; } /// <summary> /// 打包压缩 /// </summary> /// <param name="zipfile"></param> /// <param name="sheetList"></param> /// <param name="output"></param> private void SubstituteAll(string zipfile, List<EntryPackage> sheetList, Stream output) { using (ZipOutputStream zos = new ZipOutputStream(output)) { using (ZipFile zip = new ZipFile(zipfile)) { IEnumerator en = zip.GetEnumerator(); while (en.MoveNext()) { if (en.Current == null) continue; ZipEntry ze = (ZipEntry)en.Current; if (!sheetList.Exists(e => e.EntryPath.Contains(ze.Name))) { zos.PutNextEntry(new ZipEntry(ze.Name)); Stream tis = zip.GetInputStream(ze); var length = ze.Size; StreamUtils.Copy(tis, zos, null, (position) => { ProgressShow?.Invoke(Convert.ToInt32((position / (length + 0M)) * 100)); }); } } foreach (var sheetEntry in sheetList) { zos.PutNextEntry(new ZipEntry(sheetEntry.EntryPath)); using (Stream lis = new FileStream(sheetEntry.XmlFile, FileMode.Open, FileAccess.ReadWrite)) { var length = lis.Length; StreamUtils.Copy(lis, zos, null, (position) => { ProgressShow?.Invoke(Convert.ToInt32((position / (length + 0M)) * 100)); }); } } } } } /// <summary> /// 打包压缩 /// </summary> /// <param name="zipfile"></param> /// <param name="xmlfile"></param> /// <param name="entryList"></param> /// <param name="output"></param> private void SubstituteAll(string zipfile, string xmlfile, string entryList, Stream output) { using (ZipOutputStream zos = new ZipOutputStream(output)) { using (ZipFile zip = new ZipFile(zipfile)) { IEnumerator en = zip.GetEnumerator(); while (en.MoveNext()) { if (en.Current == null) continue; ZipEntry ze = (ZipEntry)en.Current; if (!entryList.Contains(ze.Name)) { zos.PutNextEntry(new ZipEntry(ze.Name)); Stream tis = zip.GetInputStream(ze); var length = ze.Size; StreamUtils.Copy(tis, zos, null, (position) => { ProgressShow?.Invoke(Convert.ToInt32((position / (length + 0M)) * 100)); }); } } zos.PutNextEntry(new ZipEntry(entryList)); using (Stream lis = new FileStream(xmlfile, FileMode.Open, FileAccess.ReadWrite)) { var length = lis.Length; StreamUtils.Copy(lis, zos, null, (position) => { ProgressShow?.Invoke(Convert.ToInt32((position / (length + 0M)) * 100)); }); } } } } public class SpreadsheetWriter { private StreamWriter _out; private int _rownum; public SpreadsheetWriter(StreamWriter output) { this._out = output; }
public void BeginWorkSheet()
{
this._out
.Write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"> <dimension ref=\"A1\"/>"+
"<sheetViews><sheetView showRuler=\"1\" showOutlineSymbols =\"1\" defaultGridColor =\"1\" colorId =\"64\" zoomScale =\"100\" workbookViewId =\"0\" ></sheetView></sheetViews><sheetFormatPr baseColWidth=\"8\" defaultRowHeight =\"15\" />");
}
public void BeginSheet() { this._out.Write("<sheetData>\n"); } public void EndSheet() { this._out.Write("</sheetData>"); // 合并单元格 } public void EndWorkSheet() { this._out.Write("</worksheet>"); } //插入行 不带高度 public void InsertRow(int rownum) { this._out.Write("<row r=\"" + (rownum + 1) + "\">\n"); this._rownum = rownum; } public void EndRow() { this._out.Write("</row>\n"); } //插入行且设置高度 public void InsertRowWithheight(int rownum, int columnNum, double height) { this._out.Write("<row r=\"" + (rownum + 1) + "\" spans=\"1:" + columnNum + "\" ht=\"" + height + "\" customHeight=\"1\">\n"); this._rownum = rownum; } public void EndWithheight() { this._out.Write("</row>\n"); } public void BeginSetColWidth() { this._out.Write("<cols>\n"); } // 设置列宽 下标从0开始 public void SetColWidthBeforeSheet(int columnIndex, double columnWidth) { this._out.Write("<col min=\"" + (columnIndex + 1) + "\" max=\"" + (columnIndex + 1) + "\" width=\"" + columnWidth + "\" customWidth=\"1\"/>\n"); } public void EndSetColWidth() { this._out.Write("</cols>\n"); } public void BeginMergerCell() { this._out.Write("<mergeCells>\n"); } public void EndMergerCell() { this._out.Write("</mergeCells>\n"); } // 合并单元格 下标从0开始 public void SetMergeCell(int beginColumn, int beginCell, int endColumn, int endCell) { this._out.Write("<mergeCell ref=\"" + GetExcelName(beginCell + 1) + (beginColumn + 1) + ":" + GetExcelName(endCell + 1) + (endColumn + 1) + "\"/>\n");// 列行:列行 } public void CreateCell(int columnIndex, string value, int styleIndex) { string cellref = new CellReference(this._rownum, columnIndex) .FormatAsString(); this._out.Write("<c r=\"" + cellref + "\" t=\"inlineStr\""); if (styleIndex != -1) this._out.Write(" s=\"" + styleIndex + "\""); this._out.Write(">"); this._out.Write("<is><t>" + value + "</t></is>"); this._out.Write("</c>"); } public void CreateCell(int columnIndex, string value) { CreateCell(columnIndex, value, -1); } public void CreateCell(int columnIndex, double value, int styleIndex) { string cellref = new CellReference(this._rownum, columnIndex) .FormatAsString(); this._out.Write("<c r=\"" + cellref + "\" t=\"n\""); if (styleIndex != -1) this._out.Write(" s=\"" + styleIndex + "\""); this._out.Write(">"); this._out.Write("<v>" + value + "</v>"); this._out.Write("</c>"); } public void CreateCell(int columnIndex, double value) { CreateCell(columnIndex, value, -1); } public void CreateCell(int columnIndex, DateTime value, int styleIndex) { CreateCell(columnIndex, DateUtil.GetExcelDate(value, false), styleIndex); } //10 进制转26进制 private string GetExcelName(int i) { char[] allChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray(); List<char> sb = new List<char>(); while (i > 0) { sb.Add(allChar[i % 26 - 1]); i /= 26; } sb.Reverse(); return string.Join("", sb); } } } public class DataPackage { public bool IsSucess { get; set; } /// <summary> /// 数据标识 /// </summary> public int Flag { get; set; } public DataTable Table { get; set; } public DataPackage(bool isSucess) : this(isSucess, null, 0) { } public DataPackage(bool isSucess,DataTable table):this(isSucess,table,0) { } public DataPackage(bool isSucess, DataTable table,int flag) { IsSucess = isSucess; Table = table; Flag = flag; } } public class EntryPackage { public string EntryPath { get; set; } public string XmlFile { get; set; } }
单个sheet使用如下
ProgressBar getDataProgress = new ProgressBar(); XlsxOutputHelper xlsxOutput = new XlsxOutputHelper(_options.OutpuFile); //更新进度条 xlsxOutput.ProgressShow = (cnt) => { getDataProgress.Dispaly(Convert.ToInt32((cnt / (total + 0M)) * 100)); }; xlsxOutput.BeginGenerate(new List<string> { "sheet1"}); AnnoQureyServ annoQurey = new AnnoQureyServ(...param);//打开查询链接 DataPackage QureyService(int pIndex,int flag) { if (!annoQurey.GetEntityAnnosDt(pIndex, out DataTable result, ref msg[0])) { return new DataPackage(false, null); } return new DataPackage(true, result); } xlsxOutput.TotalCnt = 0; if (!xlsxOutput.GenerateSheet(colNames, "sheet1",QureyService)) { //导出失败 } var isOk = xlsxOutput.EndGenerate((endmsg) => { console(""); console(endmsg); });
多sheet操作，并且一个sheet中需要查询不同数据源的情况下，注意通过pageindex和flag来判断是否在同一个数据源中或者需要切换数据
DataPackage QureyService(int pIndex/*当前数据源查询页码*/, int flag/*当前数据源ID*/) { //当前数据源 DckeyValue src; //下一个数据源 DckeyValue nextSrc; if (flag == 0) { src= srcList.Skip(curIndex - 1).Take(1).FirstOrDefault(); } else if (pIndex == 1) { ++curIndex; src= srcList.Find(f => f.Dkey == flag); } else { src= srcList.Find(f => f.Dkey == flag); } nextSrc= srcList.Skip(curIndex).Take(1).FirstOrDefault(); if (src == null) { if (nextSrc == null) return new DataPackage(true); return new DataPackage(true, null, nextSrc.Dkey);//读取下一个数据源 } ....... ..... }
或者使用poi，SXSSFWorkbook自带的方法，只是创建对象导致效率稍微低点，具体使用方法查看官网
public static void main(String[] args) throws Throwable { SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk Sheet sh = wb.createSheet(); for(int rownum = 0; rownum < 1000; rownum++){ Row row = sh.createRow(rownum); for(int cellnum = 0; cellnum < 10; cellnum++){ Cell cell = row.createCell(cellnum); String address = new CellReference(cell).formatAsString(); cell.setCellValue(address); } } // Rows with rownum < 900 are flushed and not accessible for(int rownum = 0; rownum < 900; rownum++){ Assert.assertNull(sh.getRow(rownum)); } // ther last 100 rows are still in memory for(int rownum = 900; rownum < 1000; rownum++){ Assert.assertNotNull(sh.getRow(rownum)); } FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx"); wb.write(out); out.close(); // dispose of temporary files backing this workbook on disk wb.dispose(); }
The next example turns off auto-flushing (windowSize=-1) and the code manually controls how portions of data are written to disk
关于分页的问题，建议在一次连接中完成
代码测试可用，内存占用很稳定，如果每次分页查询数据量较大的话建议在之后显式调用GC
