封装的POI导出EXCEL(SXSSFWorkbook),支持单元格合并,多sheet导出,自适应列宽,特殊标题定制,数字类型数据自定义格式导出,大数据量导出,大数据分批导出成一个excel
一.导出对象
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExportDto implements Serializable {
private static final long serialVersionUID = -2194048487324978603L;
private String sheetName;
private String title;
private String titleStyleMethod;
@Builder.Default
private Boolean specialTitle = false;
private List<HashMap<String, Object>> dataList;
private LinkedHashMap<String, String> keyNameMap;
private int rowRowNum;
@Builder.Default
private Boolean needMergedData = false;
private Integer[] mergeBasis;
private Integer[] mergeCells;
private List<ExcelStyleDto> styleList;
二.excel导出特殊格式对象
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.CellStyle;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExcelStyleDto {
@Builder.Default
private Boolean isString = true;
private Integer[] colum;
private String format;
@Builder.Default
private short alignment = CellStyle.ALIGN_CENTER;
@Builder.Default
private Integer length = 0;
}
三.导出工具类
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.shiro.util.CollectionUtils;
import org.springframework.util.ReflectionUtils;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;
public class DownloadExcelUtil implements IDownloadExclUtil {
SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
@Override
public List handlerExcel(Workbook wb) {
return null;
}
public void exportOneSheetSXSS(String fileName, ExportDto exportDto, HttpServletRequest request, HttpServletResponse response) {
try {
oneSheet(exportDto);
exportSXSS(fileName, workbook, response, request);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.dispose();
}
}
}
public void exportNSheetSXSS(String fileName, List<ExportDto> exportDto, HttpServletRequest request, HttpServletResponse response) {
try {
for (ExportDto dto : exportDto) {
oneSheet(dto);
}
exportSXSS(fileName, workbook, response, request);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.dispose();
}
}
}
public void oneSheet(ExportDto exportDto) throws IllegalAccessException, InvocationTargetException {
CellStyle columnTopStyle = IDownloadExclUtil.getColumnTopStyle(workbook);
CellStyle style = IDownloadExclUtil.getStyleXSS(workbook);
SXSSFSheet sheet = workbook.createSheet(exportDto.getSheetName());
String title = exportDto.getTitle();
LinkedHashMap<String, String> keyNameMap = exportDto.getKeyNameMap();
List<HashMap<String, Object>> dataList = exportDto.getDataList();
int columnNum = keyNameMap.size();
int rowRowNum = exportDto.getRowRowNum();
if (StringUtils.isNotBlank(title)) {
Boolean specialTitle = exportDto.getSpecialTitle();
if (specialTitle) {
Method method = ReflectionUtils.findMethod(IDownloadExclUtil.class, exportDto.getTitleStyleMethod(), null);
CellStyle styleTital = (CellStyle) method.invoke(new SXSSFWorkbook(), workbook);
setSpeciaTitleXSS(sheet, styleTital, columnNum, rowRowNum, title);
} else {
setTitleSXSS(sheet, columnTopStyle, columnNum, title);
}
}
Map<Integer, Integer> sizeMap = setHeadColumSXSS(sheet, columnTopStyle, rowRowNum, keyNameMap);
if (!CollectionUtils.isEmpty(dataList)) {
initDataSXSSAndAutoExpandColumWidth(workbook, sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, exportDto.getStyleList());
if (exportDto.getNeedMergedData()) {
merge(sheet, style, rowRowNum, exportDto.getMergeBasis(), exportDto.getMergeCells(), workbook);
}
} else {
setColumWidth(sheet, sizeMap);
}
}
public Map<String, Object> iniTitletAndHeadColum(String sheetName, String title, LinkedHashMap<String, String> keyNameMap, int rowRowNum, SXSSFWorkbook workbook) {
workbook.setCompressTempFiles(true);
CellStyle columnTopStyle = IDownloadExclUtil.getColumnTopStyle(workbook);
CellStyle style = IDownloadExclUtil.getStyleXSS(workbook);
int columnNum = keyNameMap.size();
SXSSFSheet sheet = workbook.createSheet(sheetName);
setTitleSXSS(sheet, columnTopStyle, columnNum, title);
Map<Integer, Integer> sizeMap = setHeadColumSXSS(sheet, columnTopStyle, rowRowNum, keyNameMap);
HashMap<String, Object> map = new HashMap<>();
map.put("sheet", sheet);
map.put("style", style);
map.put("sizeMap", sizeMap);
map.put("workbook", workbook);
return map;
}
public Map<Integer, Integer> initData(SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap) {
if (!CollectionUtils.isEmpty(dataList)) {
sizeMap = setOnlyStringData(sheet, style, rowRowNum, dataList, keyNameMap, sizeMap);
}
return sizeMap;
}
public void autoExpandColumWidth(SXSSFSheet sheet, Map<Integer, Integer> sizeMap) {
setColumWidth(sheet, sizeMap);
}
public void exportBigSXSS(String fileName, SXSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) {
exportSXSS(fileName, workbook, response, request);
}
}
四.底层接口方法
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.shiro.util.CollectionUtils;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
public interface IDownloadExclUtil<T> {
List<T> handlerExcel(Workbook wb);
default void setTitleSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int columnNum, String tileName) {
SXSSFRow rowm = sheet.createRow(0);
SXSSFCell cellTiltle = rowm.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum - 1));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(tileName);
}
default void setSpeciaTitleXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int columnNum, int rowRowNum, String tileName) {
SXSSFRow rowm = sheet.createRow(0);
SXSSFCell cellTiltle = rowm.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, rowRowNum - 1, 0, columnNum - 1));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(tileName);
}
default Map<Integer, Integer> setHeadColumSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int rowRowNum, LinkedHashMap<String, String> keyNameMap) {
SXSSFRow rowRowName = sheet.createRow(rowRowNum);
int sizeIndex = 0;
Map<Integer, Integer> sizeMap = new HashMap<>();
for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
SXSSFCell cellRowName = rowRowName.createCell(sizeIndex);
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
String value = entry.getValue();
cellRowName.setCellValue(value);
cellRowName.setCellStyle(columnTopStyle);
sizeMap.put(sizeIndex, Math.max(0, value.getBytes().length));
++sizeIndex;
}
return sizeMap;
}
default void initDataSXSSAndAutoExpandColumWidth(SXSSFWorkbook workbook, SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, List<ExcelStyleDto> styleList) {
sizeMap = setData(workbook, sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, styleList);
setColumWidth(sheet, sizeMap);
}
default void setColumWidth(SXSSFSheet sheet, Map<Integer, Integer> sizeMap) {
for (Integer cellIndex : sizeMap.keySet()) {
int width = Math.min(65280, (sizeMap.get(cellIndex) + 2) * 256);
sheet.setColumnWidth(cellIndex, width);
}
}
default void exportSXSS(String fileName, SXSSFWorkbook workbook, HttpServletResponse response, HttpServletRequest request) {
try (OutputStream ouputStream = response.getOutputStream()) {
response.setCharacterEncoding("UTF-8");
response.setContentType("multipart/form-data");
String userAgent = request.getHeader("User-Agent");
byte[] bytes = userAgent.contains("MSIE") ? fileName.getBytes() : fileName.getBytes("UTF-8");
String fileNameEncode = new String(bytes, "ISO-8859-1");
response.setHeader("Content-disposition",
String.format("attachment; filename=\"%s\"", fileNameEncode + ".xlsx"));
workbook.write(ouputStream);
ouputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
static CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 11);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontName("Courier New");
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setFont(font);
style.setWrapText(false);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return style;
}
static CellStyle getStyleXSS(SXSSFWorkbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
style.setWrapText(false);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return style;
}
default void merge(SXSSFSheet sheet, CellStyle style, int rowRowNum, Integer[] mergeBasis, Integer[] mergeCells, SXSSFWorkbook workbook) {
List<List<Map<String, Integer>>> arrayList = new ArrayList<>();
if (mergeBasis != null && mergeBasis.length > 0 && mergeCells != null && mergeCells.length > 0) {
for (int i = 0; i < mergeCells.length; i++) {
List<Map<String, Integer>> list = mergedRegionXSS(sheet, mergeCells[i], rowRowNum + 1, sheet.getLastRowNum(), mergeBasis);
arrayList.add(list);
}
}
if (!arrayList.isEmpty()) {
for (List<Map<String, Integer>> list : arrayList) {
mergedCellXSS(sheet, list);
}
}
}
static List<Map<String, Integer>> mergedRegionXSS(SXSSFSheet sheet, int cellLine, int startRow, int endRow, Integer[] mergeBasis) {
String s_will = getCellValue(sheet.getRow(startRow).getCell(cellLine));
int count = 0;
Set<Integer> set = new HashSet<Integer>();
List<Map<String, Integer>> list = new ArrayList<>();
org.apache.commons.collections.CollectionUtils.addAll(set, mergeBasis);
for (int i = startRow + 1; i <= endRow; i++) {
String s_current = getCellValue(sheet.getRow(i).getCell(cellLine));
Map<String, Integer> map = new HashMap<>();
if (s_will.equals(s_current)) {
boolean isMerge = true;
if (!set.contains(cellLine)) {
for (int j = 0; j < mergeBasis.length; j++) {
if (!getCellValue(sheet.getRow(i).getCell(mergeBasis[j]))
.equals(getCellValue(sheet.getRow(i - 1).getCell(mergeBasis[j])))) {
isMerge = false;
}
}
} else {
for (int j = 0; j < mergeBasis.length && mergeBasis[j] < cellLine; j++) {
if (!getCellValue(sheet.getRow(i).getCell(mergeBasis[j]))
.equals(getCellValue(sheet.getRow(i - 1).getCell(mergeBasis[j])))) {
isMerge = false;
}
}
}
if (isMerge) {
count++;
} else {
map.put("startRow", startRow);
map.put("endRow", startRow + count);
map.put("column", cellLine);
list.add(map);
startRow = i;
s_will = s_current;
count = 0;
}
} else {
map.put("startRow", startRow);
map.put("endRow", startRow + count);
map.put("column", cellLine);
list.add(map);
startRow = i;
s_will = s_current;
count = 0;
}
if (i == endRow && count > 0) {
map.put("startRow", startRow);
map.put("endRow", startRow + count);
map.put("column", cellLine);
list.add(map);
}
}
return list;
}
static void mergedCellXSS(SXSSFSheet sheet, List<Map<String, Integer>> list) {
if (!list.isEmpty()) {
for (Map<String, Integer> map : list) {
Integer startRowMerged = map.get("startRow");
Integer endRowMerged = map.get("endRow");
Integer column = map.get("column");
if (endRowMerged - startRowMerged >= 1) {
for (int row = startRowMerged + 1; row <= endRowMerged; row++) {
SXSSFRow rowcell = sheet.getRow(row);
SXSSFCell cell1 = rowcell.getCell(column);
cell1.setCellValue("");
}
sheet.addMergedRegion(new CellRangeAddress(startRowMerged, endRowMerged, column, column));
}
}
}
}
public static String getCellValue(Cell cell) {
String value = "";
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() + "";
break;
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
break;
default:
break;
}
return value.trim();
}
default Map<Integer, Integer> setData(SXSSFWorkbook workbook, SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, List<ExcelStyleDto> styleList) {
if (CollectionUtils.isEmpty(styleList)) {
setOnlyStringData(sheet, style, rowRowNum, dataList, keyNameMap, sizeMap);
} else {
for (int i = 0; i < dataList.size(); i++) {
sheet.createRow(i + rowRowNum + 1);
}
for (ExcelStyleDto excelStyle : styleList) {
Integer[] colum = excelStyle.getColum();
short alignment = excelStyle.getAlignment();
boolean isString = excelStyle.getIsString();
CellStyle styleXSS = getStyleXSS(workbook);
styleXSS.setAlignment(alignment);
if (isString) {
setHasStyleStringData(sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, colum, styleXSS);
} else {
setHasStyleNumberData(workbook, sheet, style, rowRowNum, dataList, keyNameMap, sizeMap, excelStyle, colum, styleXSS);
}
}
}
return sizeMap;
}
default void setHasStyleNumberData(SXSSFWorkbook workbook, SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, ExcelStyleDto excelStyle, Integer[] colum, CellStyle styleXSS) {
DataFormat df = workbook.createDataFormat();
String format = excelStyle.getFormat();
styleXSS.setDataFormat(df.getFormat(format));
for (int a = 0; a < colum.length; a++) {
Integer dataCum = colum[a];
for (int i = 0; i < dataList.size(); i++) {
int cellIndex = 0;
for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
if (cellIndex == dataCum) {
HashMap<String, Object> obj = dataList.get(i);
SXSSFRow row = sheet.getRow(i + rowRowNum + 1);
SXSSFCell cell = row.createCell(cellIndex);
String key = entry.getKey();
Object o = obj.get(key);
if (o != null) {
cell.setCellStyle(styleXSS);
try {
double value = Double.parseDouble(o.toString());
cell.setCellValue(value);
int length = excelStyle.getLength();
int length1 = o.toString().length();
sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), o.toString().length() + length));
} catch (Exception e) {
String s = o.toString();
cell.setCellValue(s);
sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), s.length()));
}
} else {
cell.setCellStyle(style);
cell.setCellValue("");
sizeMap.put(cellIndex, sizeMap.get(cellIndex));
}
}
++cellIndex;
}
}
}
}
default void setHasStyleStringData(SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap, Integer[] colum, CellStyle styleXSS) {
for (int a = 0; a < colum.length; a++) {
Integer dataCum = colum[a];
for (int i = 0; i < dataList.size(); i++) {
int cellIndex = 0;
for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
if (cellIndex == dataCum) {
HashMap<String, Object> obj = dataList.get(i);
SXSSFRow row = sheet.getRow(i + rowRowNum + 1);
SXSSFCell cell = row.createCell(cellIndex);
String key = entry.getKey();
Object o = obj.get(key);
if (o != null) {
cell.setCellStyle(styleXSS);
String value = o.toString();
cell.setCellValue(value);
int length = value.getBytes().length;
sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), value.getBytes().length));
} else {
cell.setCellStyle(style);
cell.setCellValue("");
sizeMap.put(cellIndex, sizeMap.get(cellIndex));
}
}
++cellIndex;
}
}
}
}
default Map<Integer, Integer> setOnlyStringData(SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap, Map<Integer, Integer> sizeMap) {
for (int i = 0; i < dataList.size(); i++) {
HashMap<String, Object> obj = dataList.get(i);
SXSSFRow row = sheet.createRow(i + rowRowNum + 1);
int cellIndex = 0;
for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
SXSSFCell cell = null;
cell = row.createCell(cellIndex);
String key = entry.getKey();
Object o = obj.get(key);
if (o != null) {
String value = o.toString();
cell.setCellValue(value);
sizeMap.put(cellIndex, Math.max(sizeMap.get(cellIndex), value.getBytes().length));
} else {
cell.setCellValue("");
sizeMap.put(cellIndex, sizeMap.get(cellIndex));
}
cell.setCellStyle(style);
++cellIndex;
}
}
return sizeMap;
}
}
五.调用方式
5.1 含有特殊格式的
ExportDto exportDto1 = ExportDto.builder().sheetName("sheet名").keyNameMap(keymap1()).dataList(data).rowRowNum(2).needMergedData(true).mergeBasis(new Integer[]{0}).mergeCells(new Integer[]{0, 1, 2}).title("标题").specialTitle(false).build();
ExcelStyleDto excelStyleDto = ExcelStyleDto.builder().colum(new Integer[]{0, 3, 4, 6, 7}).build();
ExcelStyleDto excelStyleDto2 = ExcelStyleDto.builder().colum(new Integer[]{1, 2, 5}).isString(false).format("0.00").build();
List<ExcelStyleDto> list = new ArrayList<>();
list.add(excelStyleDto);
list.add(excelStyleDto2);
exportDto1.setStyleList(list);
DownloadExcelUtil downloadExcel = new DownloadExcelUtil();
downloadExcel.exportOneSheetSXSS("导出文件名", exportDto1, request, response);
5.2 无特殊格式的
ExportDto exportDto1 = ExportDto.builder().sheetName("sheet名").keyNameMap(keymap1()).dataList(data).rowRowNum(2).needMergedData(true).mergeBasis(new Integer[]{0}).mergeCells(new Integer[]{0, 1, 2}).title("标题").specialTitle(false).build();
DownloadExcelUtil downloadExcel = new DownloadExcelUtil();
downloadExcel.exportOneSheetSXSS("导出文件名", exportDto1, request, response);