普通方式导出Excel,比较可行的方法是多Sheet页分批次写,如10000数据分2个Sheet,每个Sheet页5000,分5次每次取1000写,下面代码简单的实现了这个功能,可以指定Sheet页大小和每次取数大小。写的比较随意,可能会有bug,欢迎提出更好的写法。
import java.io.FileOutputStream; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Random; import java.util.Set; import java.util.TreeSet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; public class Excel_导出_S01_Test { public static void main(String[] args) { Excel_导出_S01_Test t = new Excel_导出_S01_Test(); t.exportExcel("2007", true, 1000, 5000, "f:/saveFile/temp/"+ System.currentTimeMillis() + ".xlsx"); t.exportExcel("2007", false, 1000, 5000, "f:/saveFile/temp/"+System.currentTimeMillis() + ".xlsx"); t.exportExcel("2007", false, 5000, 1000, "f:/saveFile/temp/"+System.currentTimeMillis() + ".xlsx"); } /** * @Description: 普通的导出excel方法 * @param expType * excel类型2003 2007 * @param isDelZero * 是否删除动态列中全部为0的值 * @param savePath */ public void exportExcel(String expType, boolean isDelZero, int pageSize, int sheetSize, String savePath) { List<String> columnList = new ArrayList<String>(); // 表头_行数_列数$ columnList.add("编号#2#1$"); columnList.add("品牌名#2#1$"); columnList.add("型号#2#1$"); StringBuffer cellSb = new StringBuffer(); cellSb.append("000"); List<String> dyColumnList = new ArrayList<String>();// 动态列 int dyNum = 4; int subLen = 3;// 子列长度 for (int i = 1; i < dyNum + 1; i++) { // 表头_行数_列数$子列_行数_列数$ 行数固定为1 dyColumnList.add("下游合作商_" + i + "#1#3$数量#1#1$"); dyColumnList.add("下游合作商_" + i + "#1#3$金额#1#1$"); dyColumnList.add("下游合作商_" + i + "#1#3$小计#1#1$"); } dyColumnList.add("合计#1#3$数量合计#1#1$"); dyColumnList.add("合计#1#3$金额合计#1#1$"); dyColumnList.add("合计#1#3$小计合计#1#1$"); String sql = "select 1 from dual"; Workbook wb = null; long resultSize = getSqlResultSize(sql); if (isDelZero) { if (resultSize * dyColumnList.size() > 65535 * 10L) { System.err.println("数据量太大,请使用异步导出"); return; } wb = new SXSSFWorkbook(1000); } else { if ("2003".equals(expType) && columnList.size() + dyColumnList.size() < 256) { wb = new HSSFWorkbook(); } else { wb = new SXSSFWorkbook(1000); } } if (resultSize * (columnList.size() + dyColumnList.size()) > 65535 * 15L) { System.err.println("数据量太大,请使用异步导出"); return; } List<List<Object>> dataList = null; Map<Integer, Integer> zeroCols = null; //删除0值直接取全部数据,不分sheet页 if (isDelZero) { dataList = getSqlResult(sql, 1, (int) resultSize, dyNum); zeroCols = getAllZeroDyColumn(columnList, dyColumnList, dataList,subLen); } columnList.addAll(dyColumnList); if (columnList.size() >= 16383) { System.err.println("暂未实现列分割"); return; }else if(columnList.size()>256&&"2003".equals(expType)){ System.err.println("超出Excel2003限制,选择Excel2007导出"); wb = new SXSSFWorkbook(1000); } int startR = 1; //动态列求和1合并 0不合并 for (int p = 0, plen = dyColumnList.size() * 3; p < plen; p++) { cellSb.append("1"); } String cellInfo = cellSb.toString(); boolean isSplitRow = isSplitExcelHead(columnList);// 是否存在动态列 boolean isCellSum = cellInfo.indexOf("1") > -1;// 是否对列求和 BigDecimal[] cellSum = new BigDecimal[cellInfo.length()]; // 初始化 for (int i = 0; i < cellSum.length; i++) { cellSum[i] = new BigDecimal(0); } final int cellWidth =8; final short rowHeight = (short)380; // 设置表头数据样式 CellStyle headStyle = wb.createCellStyle(); Font headFont = wb.createFont(); headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headStyle.setFillForegroundColor(IndexedColors.LIME.getIndex()); headStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headStyle.setAlignment(CellStyle.ALIGN_CENTER); headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headStyle.setBorderBottom(CellStyle.BORDER_THIN); headStyle.setBorderLeft(CellStyle.BORDER_THIN); headStyle.setBorderRight(CellStyle.BORDER_THIN); headStyle.setBorderTop(CellStyle.BORDER_THIN); headStyle.setTopBorderColor(IndexedColors.LIME.getIndex()); headStyle.setRightBorderColor(IndexedColors.LIME.getIndex()); headStyle.setBottomBorderColor(IndexedColors.LIME.getIndex()); headStyle.setLeftBorderColor(IndexedColors.LIME.getIndex()); headStyle.setFont(headFont); // 普通单元格样式 CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 整型样式 CellStyle longStyle = wb.createCellStyle(); longStyle.setAlignment(CellStyle.ALIGN_CENTER); longStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); DataFormat format = wb.createDataFormat(); longStyle.setDataFormat(format.getFormat("####0")); // double类型样式 CellStyle doubleStyle = wb.createCellStyle(); doubleStyle.setAlignment(CellStyle.ALIGN_CENTER); doubleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); format = wb.createDataFormat(); doubleStyle.setDataFormat(format.getFormat("####0.###")); // final int pageSize = 502;// 每次取1000条数据 // final int sheetSize = 101;// 每个sheet大小 int totalSheet = (int) ((resultSize - 1) / sheetSize + 1); boolean isNeedMutil = (sheetSize > pageSize) && (resultSize > pageSize);// 是否需要多次取数据 if (isDelZero) { totalSheet = 1; } int remainSize = 0, currentRow = 0, lastRow = 0; int blen = 0; // 分次写满一个sheet页 for (int st = 1; st <= totalSheet; st++) { Sheet sheet = wb.createSheet("报表_" + st); sheet.setDefaultColumnWidth(cellWidth); sheet.setDefaultRowHeight(rowHeight); currentRow = 0; blen = 0; //写满一个sheet页是否需要多次取值 isNeedMutil = (sheetSize > pageSize) && (resultSize > pageSize); if (isSplitRow) { createSheetTitle(sheet, 0, columnList, headStyle);//存在动态列 startR = 2; } else { createSheetTitleNormal(sheet, 0, columnList, headStyle); } System.out.println("-------------------------报表头部写完成--------"); if (isDelZero) { createExcelBody(sheet, startR, dataList, zeroCols, isCellSum, true, cellInfo, cellSum, cellStyle, longStyle,doubleStyle); dataList.clear(); dataList=null; } else if (!isNeedMutil) { //先写上次剩余的数据 if (remainSize > 0) { if (st == totalSheet&& remainSize == resultSize % sheetSize) { createExcelSheetBody(sheet,startR,0,dataList,dataList.size() - remainSize, (int) Math.min(st * sheetSize, dataList.size()),isCellSum, true, cellInfo, cellSum, cellStyle,longStyle, doubleStyle); dataList.clear(); dataList=null; break; } else { createExcelSheetBody(sheet,startR,0,dataList,dataList.size() - remainSize, (int) Math.min(st * sheetSize, dataList.size()),isCellSum, false, cellInfo, cellSum, cellStyle,longStyle, doubleStyle); } currentRow = remainSize; remainSize = Math.max(dataList.size() - st * sheetSize, 0); //一次数据填充多个sheet页 if (remainSize > 0) { continue; } dataList.clear(); dataList = null; } int startIndex = remainSize + (st - 1) * sheetSize + 1;// 分页开始字段 int endIndex = remainSize + st * sheetSize; if (endIndex >= resultSize) { endIndex = (int) resultSize; } //System.out.println(startIndex + "----=" + endIndex); String pageSql = "select rptrt.* from (select rptrow.*, rownum rn from ( " + sql + " ) rptrow where rownum <=" + endIndex + ") rptrt where rn >=" + startIndex; dataList = getSqlResult(pageSql, startIndex, endIndex, dyNum); if (st == totalSheet) { createExcelSheetBody(sheet,startR,currentRow,dataList,0,(int) Math.min(sheetSize - currentRow, dataList.size()), isCellSum, true,cellInfo, cellSum, cellStyle, longStyle,doubleStyle); dataList.clear(); dataList=null; break; } else { createExcelSheetBody(sheet,startR,currentRow,dataList,0,(int) Math.min(sheetSize - currentRow,dataList.size()), isCellSum, false,cellInfo, cellSum, cellStyle, longStyle,doubleStyle); } remainSize = currentRow + dataList.size() - sheetSize; } else { // 先把剩余的部分写完 if (remainSize > 0) { // 剩余数量最多写一次 if (st == totalSheet&& remainSize == resultSize % sheetSize) { createExcelSheetBody(sheet, startR, 0, dataList,dataList.size() - remainSize, Math.min(dataList.size(), sheetSize), isCellSum, true, cellInfo, cellSum, cellStyle, longStyle, doubleStyle); dataList.clear(); dataList=null; break; } else { createExcelSheetBody(sheet, startR, 0, dataList, dataList.size() - remainSize, Math.min(dataList.size(), sheetSize), isCellSum, false, cellInfo, cellSum, cellStyle, longStyle, doubleStyle); } dataList.clear(); dataList = null; currentRow = remainSize; } if (resultSize - (st - 1) * sheetSize - 1 - currentRow < 0) { continue; } else { blen = (int) Math.min((sheetSize - 1) / pageSize + 1, (resultSize- (st - 1) * sheetSize - 1 - currentRow) / pageSize + 1); } //分次取数据 for (int b = 0; b < blen; b++) { if (!isNeedMutil) { break; } lastRow = currentRow; int startIndex = remainSize + (st - 1) * sheetSize + 1 + b * pageSize;// 分页开始字段 int endIndex = remainSize + (st - 1) * sheetSize + (b + 1) * pageSize; if (endIndex >= resultSize) { endIndex = (int) resultSize; isNeedMutil = false; remainSize = 0; } if (endIndex > st * sheetSize) { remainSize = endIndex - st * sheetSize; currentRow = st * sheetSize + 1; isNeedMutil = false; } //System.out.println(startIndex + "---=" + endIndex + "---="+ lastRow); String pageSql = "select rptrt.* from (select rptrow.*, rownum rn from ( " + sql + " ) rptrow where rownum <=" + endIndex + ") rptrt where rn >=" + startIndex; dataList = getSqlResult(pageSql, startIndex, endIndex, dyNum); if (st == totalSheet&&endIndex==resultSize) { createExcelSheetBody(sheet, startR, lastRow, dataList,0, dataList.size() - remainSize, isCellSum,true, cellInfo, cellSum, cellStyle, longStyle,doubleStyle); dataList.clear(); dataList=null; break; } else { createExcelSheetBody(sheet, startR, lastRow, dataList,0, dataList.size() - remainSize, isCellSum,false, cellInfo, cellSum, cellStyle, longStyle,doubleStyle); } currentRow += dataList.size() - remainSize; } } } System.out.println("------------报表保存----"); writeExcel(wb, savePath); } public void createExcelSheetBody(Sheet sheet, int headSize, int startR, List bodyList, int startIndex, int endIndex, boolean isCellSum, boolean isLast, String cellInfo, BigDecimal[] cellSum, CellStyle cellStyle, CellStyle longStyle, CellStyle doubleStyle) { Row rowBody = null; Cell cell = null; if (bodyList.size() > 0) { List rowList = null; int colEnd = ((List) bodyList.get(0)).size(); int rowEnd = bodyList.size();// TODO for (int i = startIndex, idx = 0; i < endIndex; i++, idx++) { rowBody = sheet.createRow(idx + headSize + startR); rowList = (List) bodyList.get(i); if (!isCellSum) { for (int j = 0; j < colEnd; j++) { cell = rowBody.createCell(j); Object obj = rowList.get(j); // double long string判断 try { double var = Double.parseDouble(obj + ""); cell.setCellValue(var); if (var != (long) var) { cell.setCellStyle(doubleStyle); } else { cell.setCellStyle(longStyle); } } catch (Exception e) { cell.setCellValue(obj + ""); cell.setCellStyle(cellStyle); } } } else { for (int j = 0; j < colEnd; j++) { cell = rowBody.createCell(j); Object obj = rowList.get(j); try { double var = Double.parseDouble(obj + ""); cell.setCellValue(var); if (var != (long) var) { cell.setCellStyle(doubleStyle); } else { cell.setCellStyle(longStyle); } if (cellInfo.charAt(j) == '1') { cellSum[j] = cellSum[j] .add(new BigDecimal(var)); } } catch (Exception e) { cell.setCellValue(obj + ""); cell.setCellStyle(cellStyle); } } } rowList = null; } if (isCellSum) { if (isLast) { rowBody = sheet.createRow(headSize + startR + endIndex - startIndex); cell = rowBody.createCell(0); cell.setCellStyle(doubleStyle); cell.setCellValue("合计"); for (int j = 1; j < colEnd; j++) { if (cellInfo.charAt(j) == '1') { cell = rowBody.createCell(j); cell.setCellStyle(doubleStyle); cell.setCellValue(cellSum[j] .setScale(3, BigDecimal.ROUND_HALF_UP) .stripTrailingZeros().toPlainString()); } } } } } rowBody = null; cell = null; } /** * @Description: 得到List中0值位置 */ public Map<Integer, Integer> getAllZeroDyColumn(List<String> columnList, List<String> dyList, List<List<Object>> dataList, int subLen) { Map<Integer, Integer> nullIndexMap = new HashMap<Integer, Integer>(); int startIndex = columnList.size(); List<Object> list = null; boolean isNull = true; list = dataList.get(0); // 得到第一行数据所有为0的索引位置 for (int i = startIndex, len = list.size(); i < len; i += subLen) { isNull = true; for (int k = i, klen = i + subLen; k < klen && k < len; k++) { if (!isZero(list.get(k).toString())) { isNull = false; break; } } if (isNull) { nullIndexMap.put((i - startIndex) / subLen, 1); } } for (int j = 1, len2 = dataList.size(); j < len2; j++) { list = dataList.get(j); for (int i = startIndex, len = list.size(); i < len; i += subLen) { isNull = true; if (nullIndexMap.get((i - startIndex) / subLen) != null) { for (int k = i, klen = i + subLen; k < klen && k < len; k++) { if (!isZero(list.get(k).toString())) { isNull = false; break; } } if (!isNull) { // 逐步缩小为0的索引值 nullIndexMap.remove((i - startIndex) / subLen); } } } list = null; } List<String> dyList2 = new ArrayList<String>(); Map<Integer, Integer> resultMap = new HashMap<Integer, Integer>(); for (Entry<Integer, Integer> entry : nullIndexMap.entrySet()) { for (int k = 0; k < subLen; k++) { resultMap.put(entry.getKey() * subLen + startIndex + k, subLen); dyList2.add(dyList.get(entry.getKey() * subLen + k)); } } dyList.removeAll(dyList2); dyList = new ArrayList(dyList); dyList2.clear(); dyList2 = null; list = null; return resultMap; } /** * @Description: 是否为0 */ public boolean isZero(String str) { return str == null || str.replaceAll("(0|,|\\.)", "").length() == 0; } private List<List<Object>> getSqlResult(String pageSql, int startIndex, int endIndex, int dyNum) { return generateTestData(startIndex, endIndex, dyNum); } // 产生随机数据 public List<List<Object>> generateTestData(int startIndex, int totalNum, int dyNum) { Random random = new Random(System.nanoTime()); List<List<Object>> tmpDataList = new ArrayList<List<Object>>(); List<Object> dataList = null; // 产生随机为0的列 List<Integer> tmpList = new ArrayList<Integer>(); for (int i = 0, len = dyNum * 3 / 4; i < len; i++) { tmpList.add(random.nextInt(dyNum) % (dyNum - 1)); } random = new Random(System.nanoTime()); // 去重 Set<Integer> nullValue = new TreeSet(tmpList); System.out.println("----重复数据-----"); for (Integer it : nullValue) { System.out.print((3 + it * 3) + ","); } System.out.println(); for (int i = startIndex; i <= totalNum; i++) { dataList = new ArrayList<Object>(); dataList.add(i); dataList.add("测试"); dataList.add("数据"); for (int k = 0; k < dyNum + 1; k++) { if (nullValue.contains(k)) { dataList.add(String.valueOf(0)); dataList.add(String.valueOf(0)); dataList.add(String.valueOf(0)); } else { dataList.add(String.valueOf(Math.abs(random.nextLong()) % 4000 * 0.098)); dataList.add(String.valueOf(Math.abs(random.nextInt() % 100))); dataList.add(String.valueOf(Math.abs(random.nextInt() % 10000))); } } tmpDataList.add(dataList); dataList = null; } return tmpDataList; } public long getSqlResultSize(String sql) { return 10000; } /** * @Description: 是否存在动态列 */ public boolean isSplitExcelHead(List<String> headList) { for (String str : headList) { if (str.indexOf("#") > -1 && str.indexOf("$") > -1) { return true; } } return false; } public void writeExcel(Workbook wb, String fileName) { if (wb != null) { try { FileOutputStream fileOutStream = new FileOutputStream(fileName); wb.write(fileOutStream); if (fileOutStream != null) { fileOutStream.close(); } } catch (Exception e) { e.printStackTrace(); } } } /** * @Description:删除0值 * @param startR * 开始行 * @param bodyList * 数据 * @param isCellSum * 是否求列和 * @param isLast * 是否是最后一个Sheet页 * @param cellInfo * 列求和信息1求和0不求和 * @param cellSum * 列求和数组 */ public void createExcelBody(Sheet sheet, int startR, List bodyList, Map<Integer, Integer> zeroCols, boolean isCellSum, boolean isLast, String cellInfo, BigDecimal[] cellSum, CellStyle cellStyle, CellStyle longStyle, CellStyle doubleStyle) { Row rowBody = null; Cell cell = null; if (bodyList.size() > 0) { List rowList = null; int rowEnd = bodyList.size() + startR; int colEnd = ((List) bodyList.get(0)).size(); for (int i = startR, idx = 0; i < rowEnd; i++, idx++) { rowBody = sheet.createRow(i); rowList = (List) bodyList.get(idx); if (!isCellSum) { for (int j = 0, col = 0; j < colEnd; j++) { if (zeroCols.containsKey(j)) { j += zeroCols.get(j) - 1; continue; } cell = rowBody.createCell(col); Object obj = rowList.get(j); // double long string判断 try { double var = Double.parseDouble(obj + ""); cell.setCellValue(var); if (var != (long) var) { cell.setCellStyle(doubleStyle); } else { cell.setCellStyle(longStyle); } } catch (Exception e) { cell.setCellValue(obj + ""); cell.setCellStyle(cellStyle); } col++; } } else { for (int j = 0, col = 0; j < colEnd; j++) { if (zeroCols.containsKey(j)) { j += zeroCols.get(j) - 1; continue; } cell = rowBody.createCell(col); Object obj = rowList.get(j); try { double var = Double.parseDouble(obj + ""); cell.setCellValue(var); if (var != (long) var) { cell.setCellStyle(doubleStyle); } else { cell.setCellStyle(longStyle); } if (cellInfo.charAt(col) == '1') { cellSum[col] = cellSum[col].add(new BigDecimal( var)); } } catch (Exception e) { cell.setCellValue(obj + ""); cell.setCellStyle(cellStyle); } col++; } } rowList = null; } if (isCellSum) { if (isLast) { rowBody = sheet.createRow(rowEnd); cell = rowBody.createCell(0); cell.setCellStyle(doubleStyle); cell.setCellValue("合计"); for (int j = 1, col = 1; j < colEnd; j++) { if (zeroCols.containsKey(j)) { j += zeroCols.get(j) - 1; continue; } if (cellInfo.charAt(col) == '1') { cell = rowBody.createCell(col); cell.setCellStyle(doubleStyle); cell.setCellValue(cellSum[col] .setScale(3, BigDecimal.ROUND_HALF_UP) .stripTrailingZeros().toPlainString()); } col++; } } } } rowBody = null; cell = null; } public void createSheetTitleNormal(Sheet sheet, int startR, List<String> headList, CellStyle headStyle) { Row row_head = sheet.createRow(startR); Cell cell = null; int start_col = 0;// 起始列 String headStr = null; for (int i = 0; i < headList.size(); i++) { headStr = String.valueOf(headList.get(i)); cell = row_head.createCell(start_col + i); cell.setCellValue(headStr); cell.setCellStyle(headStyle); } } /** * @Description: Excel 表头为固定2行 */ public void createSheetTitle(Sheet sheet, int startR, List<String> headList, CellStyle headStyle) { Row row_head_0 = null; Row row_head_1 = null; Cell cell = null; // 保存未拆分时的值 String attr = null; // 保存第一次拆分时的值 String[] tmp = null; // 拆分后第一行的值 String[] tmp2 = null; // 拆分后第2行的值 String[] tmp3 = null; // 第一次匹配时的值 String preAttr = null; // 下一次匹配时的值 String lastAttr = null; int preIdx = 0; int start_row = startR;// 起始行 int start_col = 0;// 起始列 row_head_0 = sheet.createRow(start_row + 0); row_head_1 = sheet.createRow(start_row + 1); CellRangeAddress range = new CellRangeAddress(0, 0, 0, 0); for (int i = 0; i < headList.size(); i++) { attr = String.valueOf(headList.get(i)); // 以$分割数据 tmp = attr.split("\\$"); if (tmp.length == 1) { cell = row_head_0.createCell(start_col + i); // #分割的是行 列信息 cell.setCellValue(tmp[0].split("\\#")[0]); cell.setCellStyle(headStyle); cell = row_head_1.createCell(start_col + i); cell.setCellStyle(headStyle); // 创建cell主要是指定样式,防止跨列时少样式 range.setFirstRow(start_row + 0); range.setLastRow(start_row + 1); range.setFirstColumn(start_col + i); range.setLastColumn(start_col + i); sheet.addMergedRegion(range); preIdx = i + 1; } else if (tmp.length == 2) { preAttr = tmp[0]; tmp2 = tmp[0].split("\\#"); tmp3 = tmp[1].split("\\#"); int tmpI = Integer.parseInt(tmp2[2]);// 跨列 int tmpI2 = Integer.parseInt(tmp3[2]);// 跨列信息 if (!preAttr.equals(lastAttr)) { lastAttr = tmp[0]; cell = row_head_0.createCell(start_col + i); cell.setCellValue(tmp2[0]); cell.setCellStyle(headStyle); range.setFirstRow(start_row + 0); range.setLastRow(start_row + 0); range.setFirstColumn(start_col + preIdx); range.setLastColumn(start_col + preIdx + tmpI - 1); sheet.addMergedRegion(range); } cell = row_head_1.createCell(start_col + i); cell.setCellValue(tmp3[0]); cell.setCellStyle(headStyle); // 第二行跨列 if (tmpI2 != 1) { range.setFirstRow(start_row + 1); range.setLastRow(start_row + 1); range.setFirstColumn(start_col + preIdx); range.setLastColumn(start_col + preIdx + tmpI2 - 1); sheet.getRow(start_row + 1) .createCell(start_col + preIdx + tmpI2 - 1) .setCellStyle(headStyle); sheet.addMergedRegion(range); start_col = start_col + tmpI2 - 1; } preIdx++; } } } }
效果如下:
5000一个sheet页:
1000一个sheet页:
去除0值:
转载请注明原处,原文链接: http://53873039oycg.iteye.com/blog/2142939,谢谢。
全文完。
相关推荐
2. 分批次写入:如果数据量过大,可分批次读取和写入,减少一次性内存占用。 六、异常处理 在实际应用中,需要添加适当的异常处理机制,如FileNotFoundException、IOException等,确保程序的健壮性。 总结,Apache...
它可能采用了分批次写入或者优化的内存管理策略,以减少内存占用并提高处理速度。同时,提及的“使用方法见com.teacherInfo.export.test.Test”,意味着在提供的代码示例中,你可以找到一个名为Test的测试类,这个类...
可以通过分批次导出或者使用流式写入的方式来减少内存占用。 2. **异常处理**:在实际开发中,需要充分考虑到各种可能发生的异常情况,并进行妥善处理,比如文件路径不存在、文件已存在等。 3. **安全性**:在...
在Java开发中,数据导出是一项常见的任务,特别是在大数据处理中。阿里巴巴的开源库`easyExcel`为此提供了高效且易用的解决方案。`easyExcel`是专门为处理大量数据设计的一个轻量级工具,它能够有效地解决内存溢出...
此外,为了提高性能,可以考虑使用内存映射或分批次处理大量数据。 总结起来,SpringBoot结合Apache POI提供了一种强大而灵活的方式来导出指定格式的Excel模板。通过创建工作簿、工作表、行和单元格,我们可以轻松...
- 分批次处理大文件以避免内存溢出。 - 使用`SXSSFWorkbook`代替`XSSFWorkbook`,对于大量数据写入,可以提高性能并降低内存需求。 通过理解和熟练应用Apache POI 4.1.2,开发者可以高效地在Java应用程序中处理...
可以采用流式处理或分批次写入来优化内存使用。 - **SXSSFWorkbook**:Apache POI提供SXSSFWorkbook类,支持在硬盘上临时存储部分数据,减少内存占用。 5. **项目集成**: - **Maven或Gradle**:通常会将Apache ...
关键在于合理分批次处理数据,避免内存溢出。 3. **批量导出**:导出数据到Excel文件通常用于报表生成。Java程序可以遍历数据库查询结果或其他数据源,然后使用POI将这些数据写入新创建或已存在的Excel文件。 二、...
- 并行处理:可以利用多线程并行导出不同批次的数据,进一步提高效率。 - 延迟写入:在数据量非常大的情况下,可以采用延迟写入策略,先将数据缓存起来,达到一定阈值或者指定条件时才批量写入文件。 总结来说,...
- **性能优化**:对于大量数据的处理,可以考虑分批次上传或者采用异步处理机制,避免长时间阻塞用户界面。 - **异常处理**:添加异常处理逻辑,确保程序在遇到问题时能够妥善处理并给出反馈。 综上所述,本方案...
- **合理使用监听器**:在处理大量数据时,利用监听器进行分批次处理,避免一次性加载过多数据。 - **优化性能**:根据实际业务场景选择合适的读写模式,如流式、缓存等。 - **错误处理**:在读取过程中,注意...
在大数据量汇出时,考虑到性能和内存消耗,我们需要采用分批次写入的方式。这可以通过创建一个缓冲区,将数据分批写入到Excel工作表中,而不是一次性加载所有数据。例如,我们可以设定每次写入1000行数据,然后清空...