POIUtil(动态复杂表头、动态数据、多线程、合并数据列的POI导出成Excel工具附带生成csv文件)
package com.sckj.base.util; import java.io.IOException; import java.lang.reflect.Constructor; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.concurrent.Callable; import java.util.concurrent.ExecutorCompletionService; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import java.util.concurrent.Future; import javax.servlet.http.HttpServletResponse; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.collections4.ListUtils; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.HorizontalAlignment; 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.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; public class POIUtil { /** * POI导出Excel表格(多行表头、合并单元格) * * @param rowTotalNum 表头总行数 * @param colTotalNum 表头列数 * @param sheetName 表单名称 * @param fileName 导出文件名称(带文件后缀) * @param headerNameList 表头字段名称集合,按顺序传入每行的字段名称,特别注意:每行字段名称为一个数组,合并单元格时字段跨几列就将该字段重复几次 * @param headerNumList 表头字段 行列位置集合,按顺序传入以上对应字段的行列占用位置,特别注意:每行字段行列位置为一个数组,"0,2,0,0" * ===> “起始行,截止行,起始列,截止列” * @param dataList 数据 * @param perPageNum 每个sheet页自定义显示条数 * @param isMerge 是否需要合并相同数据列(如果设置为false,那么columns里设置什么都不会起作用) * @param startRow 除开表头的开始行 * @param columns 合并的列的序号(0开始) * @param clazz 业务逻辑类(写业务方法的类) * @return HSSFWorkbook * @author 47Gamer * @see POI导出每页sheet最多容纳100万左右条数据,设置了perPageNum,每个sheet页显示自定义条数 * @see 所有参数必传 */ public static void exportExcel(Integer rowTotalNum, Integer colTotalNum, String fileName, String sheetName, List<String[]> headerNameList, List<String[]> headerNumList, List<Object> dataList, Integer perPageNum, boolean isMerge, Integer startRow, int[] columns, Class clazz, HttpServletResponse response) { try { /** 执行总条数 */ int total = 0; /** 本机线程数,根据该值来决定开启多少个线程 */ int nThreads = Runtime.getRuntime().availableProcessors(); /** 通过nthreads来创建一个线程池 */ ExecutorService executorService = Executors.newFixedThreadPool(nThreads); /** 通过线程池来创建一个完成服务,泛型为完成服务返回的类型 */ ExecutorCompletionService<Integer> completionService = new ExecutorCompletionService<Integer>( executorService); /** 表头名称 */ String[] headerNames = {}; /** 表头行列坐标位置 */ String[] headerNums = {}; /** 工作空间 */ Workbook wb = new SXSSFWorkbook(); /** 表单行 */ Row row = null; /** 表单页数 */ Sheet sheet = null; /** 表头样式 */ CellStyle style = null; /** 表头字体 */ Font font = null; /** 自定义分组 */ List<List<Object>> groupList = ListUtils.partition(dataList, perPageNum); if (CollectionUtils.isNotEmpty(groupList)) { for (int n = 0; n < groupList.size(); n++) { sheet = wb.createSheet(sheetName + n); ((SXSSFSheet) sheet).trackAllColumnsForAutoSizing(); for (int i = 0; i < rowTotalNum; i++) { headerNames = headerNameList.get(i); headerNums = headerNumList.get(i); style = wb.createCellStyle(); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); font = wb.createFont(); font.setFontName("微软雅黑"); font.setFontHeightInPoints((short) 10); font.setBold(true); style.setFont(font); /** 每行表头 */ row = sheet.createRow(i); for (int j = 0; j < headerNames.length; j++) { ((SXSSFSheet) sheet).autoSizeColumn((short) j); Cell cell = row.createCell((short) j); cell.setCellValue(headerNames[j]); cell.setCellStyle(style); } /** 动态合并单元格 */ for (int l = 0; l < headerNums.length; l++) { ((SXSSFSheet) sheet).autoSizeColumn((short) l, true); String[] temp = headerNums[l].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); if (!(startrow.equals(overrow) && startcol.equals(overcol))) { sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } } } List<Object> subList = new ArrayList<>(); dataList = groupList.get(n); /** 数据总量 */ int listSize = dataList.size(); /** 每条线程处理的初始数据条数 */ int toIndex = 0; if (listSize < nThreads) nThreads = listSize; toIndex = listSize / nThreads; int startRowIndex = rowTotalNum; for (int i = 0; i < nThreads + 1; i++) { if (toIndex * i + toIndex > listSize) { subList = dataList.subList(toIndex * i, listSize); } else { subList = dataList.subList(toIndex * i, toIndex * (i + 1)); } Constructor<?> constructor = clazz.getConstructor(List.class, Sheet.class, int.class, int.class, Workbook.class); @SuppressWarnings("unchecked") Future<Integer> future = completionService .submit((Callable<Integer>) constructor.newInstance(subList, sheet, startRowIndex, colTotalNum, wb)); startRowIndex = future.get(); total = startRowIndex; } System.out.println("第" + (n + 1) + "次执行总条数 : " + (total - rowTotalNum)); /** 是否合并单元格 */ if (isMerge) { for (int i = 0; i < columns.length; i++) { int cellNum = columns[i]; int lastRowNum = sheet.getLastRowNum(); int currentRow = startRow; for (int j = startRow; j <= lastRowNum; j++) { Cell cell = sheet.getRow(j).getCell(cellNum); String cellValue = cell.getStringCellValue(); String nextValue = getNextCellValueString(cellNum, sheet.getRow(j + 1)); /** 将当前单元格值 置空直到最后一个 */ cell.setCellValue(""); if (cellValue.equals(nextValue)) { continue; } else { /** 获取左上角的单元格,合并后只保存左上角单元格的值 */ Cell leftTopCell = sheet.getRow(currentRow).getCell(cellNum); leftTopCell.setCellValue(cellValue); /** 多个cell相同合并 */ if (currentRow != j) { sheet.addMergedRegion(new CellRangeAddress(currentRow, j, cellNum, cellNum)); currentRow = j + 1; } else { currentRow += 1; } } } } } } } executorService.shutdown(); response.setContentType("application/vnd.ms-excel; charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO-8859-1")); response.setCharacterEncoding("utf-8"); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); wb.write(response.getOutputStream()); response.getOutputStream().close(); } catch (Exception e) { e.printStackTrace(); } } /** * 获取下一个单元格的值 * * @param cellNum 单元格列数 * @param nextRow 下一行数 */ private static String getNextCellValueString(int cellNum, Row nextRow) { String nextValue; if (nextRow != null) { Cell nextCell = nextRow.getCell(cellNum); if (nextCell != null) { nextValue = nextCell.getStringCellValue(); } else { nextValue = ""; } } else { nextValue = ""; } return nextValue; } }
Excel转为CSV文件:
public static void excel2Csv(String excelPath, String csvPath) { File outputFile = new File(csvPath); File inputFile = new File(excelPath); StringBuffer data = new StringBuffer(); try { FileOutputStream fos = new FileOutputStream(outputFile); Row row = null; Cell cell = null; Workbook wBook = new XSSFWorkbook(new FileInputStream(inputFile)); int sheetNum = wBook.getNumberOfSheets(); for (int i=0; i <sheetNum; i++) { Sheet sheet = wBook.getSheetAt(i); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case BOOLEAN: data.append(cell.getBooleanCellValue() + "|"); break; case NUMERIC: data.append(cell.getNumericCellValue() + "|"); break; case STRING: data.append(cell.getStringCellValue() + "|"); break; case BLANK: data.append("" + "|"); break; default: data.append(cell + "|"); } } data.append("\r\n"); } } fos.write(data.toString().getBytes("UTF-8")); fos.close(); } catch (Exception ioe) { LOGGER.error("excel2Csv, {}",ioe); } }
导入的jar:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.3</version> </dependency>
###################################其实还可以优化,本人今后在博客里用JDK8优化下代码和线程#############################