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优化下代码和线程#############################
