apache poi excel export
水一篇 凑个数（啊 水文章好羞耻啊！请原谅我私密马赛！）
1 ExcelColumn
@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class ExcelColumn implements Serializable {@Serialprivate static final long serialVersionUID = -14668049202148498L;private String column; // 字段名private String columnName; // 列名private int width; // 列宽// 可自定义其它表格属性
}
2 MultipleSheetExcel
@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class MultipleSheetExcel implements Serializable {@Serialprivate static final long serialVersionUID = -1469875755370531986L;private String sheetName;private List<ExcelColumn> columns;private List<Map<String, Object>> data;
}
3 ExcelExport
public class ExcelExport {public static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");public static final int MAX_ROW_2003 = 65536;public static final int MAX_ROW_2007 = 1048576;public static final String DEFAULT_SHEET = "sheet";private static final Integer DEFAULT_COLUMN_WIDTH = 3000;private static final String DEFAULT_CELL_VALUE= "";/*** export multiple sheet excel* @param response* @param columns* @param data* @throws Exception*/public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns,List<Map<String, Object>> data) throws Exception {if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {return;}exportExcel(response, generateName(), columns, data);}/*** export excel* @param response* @param fileName* @param columns* @param data* @throws Exception*/public static void exportExcel(HttpServletResponse response, String fileName, List<ExcelColumn> columns,List<Map<String, Object>> data) throws Exception {if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {return;}if (!StringUtils.hasText(fileName)) {fileName = generateName();}exportMultipleExcel(response, buildMultipleSheetExcel(columns, data, null), fileName);}/*** export excel* @param response* @param columns* @param data* @param excelType* @throws Exception*/public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns,List<Map<String, Object>> data, String excelType) throws Exception {if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {return;}List<MultipleSheetExcel> list = buildMultipleSheetExcel(columns, data, excelType);exportMultipleExcel(response, generateName(), excelType,list, calculateSize(list));}/*** export multiple sheet excel* @param response* @param list* @throws Exception*/public static void exportMultipleExcel(HttpServletResponse response, List<MultipleSheetExcel> list) throws Exception {exportMultipleExcel(response, list, generateName());}/*** export multiple sheet excel* @param response* @param excelType* @param list* @throws Exception*/public static void exportMultipleExcel(HttpServletResponse response, String excelType, List<MultipleSheetExcel> list) throws Exception {exportMultipleExcel(response, generateName(), excelType, list, calculateSize(list));}/*** export multiple sheet excel* @param response* @param list* @param fileName* @throws Exception*/public static void exportMultipleExcel(HttpServletResponse response, List<MultipleSheetExcel> list, String fileName) throws Exception {if (!StringUtils.hasText(fileName)) {fileName = generateName();}exportMultipleExcel(response, fileName, ExcelTypeEnum.XLSX.getValue(), list, calculateSize(list));}/*** export multiple sheet excel* @param response* @param fileName* @param excelType* @param list* @param dataSize* @throws Exception*/public static void exportMultipleExcel(HttpServletResponse response, String fileName, String excelType,List<MultipleSheetExcel> list, int dataSize)throws Exception {Workbook workbook = createWorkbook(excelType, dataSize);CellStyle titleStyle = workbook.createCellStyle();Font titleFont = workbook.createFont();titleFont.setFontHeightInPoints((short) 16);titleStyle.setFont(titleFont);titleStyle.setAlignment(HorizontalAlignment.CENTER);CellStyle dataStyle = workbook.createCellStyle();dataStyle.setAlignment(HorizontalAlignment.CENTER);String sheetName;List<ExcelColumn> columns;List<Map<String, Object>> data;Sheet sheet;Row title;Cell titleCell;Row heads;for (MultipleSheetExcel sheetExcel : list) {if (CollectionUtils.isEmpty(columns = sheetExcel.getColumns()) || CollectionUtils.isEmpty(data = sheetExcel.getData())) {continue;}if (!StringUtils.hasText(sheetName = sheetExcel.getSheetName())) {sheetName = DEFAULT_SHEET;}sheet = workbook.createSheet(sheetName);title = sheet.createRow(0);titleCell = title.createCell(0);sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.size()));titleCell.setCellValue(sheetName);titleCell.setCellStyle(titleStyle);heads = sheet.createRow(1);Cell headCell;CellStyle headStyle = workbook.createCellStyle();Font headFont = workbook.createFont();headFont.setFontHeightInPoints((short) 12);headStyle.setFont(headFont);headStyle.setAlignment(HorizontalAlignment.CENTER);int width;ExcelColumn excelColumn;for (int i = 0; i < columns.size(); i++) {excelColumn = columns.get(i);if ((width = excelColumn.getWidth()) <= 0) {width = DEFAULT_COLUMN_WIDTH;}headCell = heads.createCell(i);headCell.setCellValue(excelColumn.getColumnName());headCell.setCellStyle(headStyle);sheet.setColumnWidth(i, width);}Row row;Map<String, Object> map;for (int i = 0; i < data.size(); i++) {map = data.get(i);row = sheet.createRow(i + 2);Object value;Cell dataCell;ExcelColumn column;for (int j = 0; j < columns.size(); j++) {column = columns.get(j);if (ObjectUtils.isEmpty(value = map.get(column.getColumn()))) {value = DEFAULT_CELL_VALUE;}dataCell = row.createCell(j);dataCell.setCellValue(String.valueOf(value));dataCell.setCellStyle(dataStyle);}}}downloadExcel(response, fileName, excelType, workbook);}/*** download* @param response* @param fileName* @param excelType* @param workbook* @throws IOException*/public static void downloadExcel(HttpServletResponse response, String fileName, String excelType, Workbook workbook) throws IOException {try {response.setCharacterEncoding("UTF-8");response.setHeader("Content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName + "." + excelType, StandardCharsets.UTF_8));workbook.write(response.getOutputStream());} catch (Exception e) {throw new IOException(e.getMessage());}}/*** create workbook* @param excelType* @param size* @return*/private static Workbook createWorkbook(String excelType, int size) {if (ExcelTypeEnum.XLS.getValue().equals(excelType)) {return new HSSFWorkbook();} else if (size < 100000) {return new XSSFWorkbook();} else {return new SXSSFWorkbook();}}/*** build MultipleSheetExcel* @param columns* @param data* @param excelType* @return*/private static List<MultipleSheetExcel> buildMultipleSheetExcel(List<ExcelColumn> columns,List<Map<String, Object>> data, String excelType) {int maxSize;if (StringUtils.hasText(excelType) && ExcelTypeEnum.XLS.getValue().equals(excelType)) {maxSize = MAX_ROW_2003;} else {maxSize = MAX_ROW_2007;}int sheets;if ((data.size() % maxSize) > 0) {sheets = (data.size() / maxSize) + 1;} else {sheets = data.size() / maxSize;}int remainNumber = data.size(), fromIndex, toIndex;List<MultipleSheetExcel> list = new ArrayList<>(sheets);for (int i = 0; i < sheets; i++) {fromIndex = i * maxSize;toIndex = fromIndex + Math.min(remainNumber, maxSize);list.add(MultipleSheetExcel.builder().sheetName(DEFAULT_SHEET + i).columns(columns).data(data.subList(fromIndex, toIndex)).build());remainNumber = remainNumber - (toIndex - fromIndex);}return list;}/*** calculate data size* @param list* @return*/private static int calculateSize(List<MultipleSheetExcel> list) {int size = 0;List<Map<String, Object>> data;for (MultipleSheetExcel sheetExcel : list) {if (CollectionUtils.isEmpty(data = sheetExcel.getData())) {continue;}size += data.size();}return size;}/*** generate name* @return*/private static String generateName() {LocalDateTime now = LocalDateTime.now();return DATE_TIME_FORMATTER.format(now);}/*** excel type enum*/enum ExcelTypeEnum {XLS("xls"),XLSX("xlsx");private String value;ExcelTypeEnum(String value) {this.value = value;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}}
}
毁灭吧！！！