水一篇 凑个数(啊 水文章好羞耻啊!请原谅我私密马赛!)
@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class ExcelColumn implements Serializable {
@Serial
private static final long serialVersionUID = -14668049202148498L;
private String column; // 字段名
private String columnName; // 列名
private int width; // 列宽
// 可自定义其它表格属性
}
@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class MultipleSheetExcel implements Serializable {
@Serial
private static final long serialVersionUID = -1469875755370531986L;
private String sheetName;
private List<ExcelColumn> columns;
private List<Map<String, Object>> data;
}
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;
}
}
}
毁灭吧!!!