背景
业务为实现自定义样式excel的导出，常规的做法就是根据数据在代码中进行类似模版的配置；这样的体验不是很好，只要用户改变下样式的设置不用代码改动就能实现自定义excel的导出更加灵活。
以下是具体实现
pom依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.11.0</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.17.2</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.17.2</version>
</dependency>
<!-- HTML解析 -->
<dependency>
<groupId>org.jsoup</groupId>
<artifactId>jsoup</artifactId>
<version>1.8.1</version>
</dependency>
控制器代码
package com.longshare.microservice.file.support.service.impl;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.longshare.microservice.file.support.ProblemCode;
import com.longshare.microservice.file.support.config.FileUploadConfig;
import com.longshare.microservice.file.support.service.FileConvertService;
import com.longshare.microservice.file.support.service.FileInfoService;
import com.longshare.microservice.file.support.utils.FileUtils;
import com.longshare.rest.core.problem.ProblemSolver;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.*;
import java.util.*;
import java.util.stream.Collectors;
import com.longshare.file.share.model.vo.FileInformation;
import org.springframework.web.multipart.MultipartFile;
@Service
@Slf4j
public class FileConvertServiceImpl implements FileConvertService {
private final String DEFAULT_SHEET_NAME = "Sheet1";
private final String DEFAULT_FONT_NAME = "微软雅黑";
private final short DEFAULT_FONT_SIZE = 12;
// 修复默认表头背景色（原代码"#"会导致解析错误）
private final String DEFAULT_HEADER_BACKGROUND = "#F0F0F0";
@Autowired
private FileInfoService fileInfoService;
@Autowired
private FileUploadConfig fileUploadConfig;
@Override
public FileInformation convertWithConfig(String configJson) throws Exception {
log.info("字符串大小:{} 字符串KB:{} 字符串MB:{}",configJson.length(),configJson.length()/1024+"KB",configJson.length()/1024/1024+"");
boolean addIndex = false;
boolean sortAscending = true;
String sortBy = "排序号";
Config config = parseConfig(configJson);
String filename = config.getFileName();
String typeId = config.getTypeId();
if (StringUtils.isBlank(typeId)) {
throw ProblemSolver.client(ProblemCode.NOT_TYPE_ID).build();
}
String outputDir = "D:\\tmp\\microframe\\upload\\";
// String outputDir = fileUploadConfig.getBasePath();
log.info("outputDir:{}", outputDir);
if (Objects.isNull(filename)) {
filename = "configured_export";
}
File outputDirFile = new File(outputDir);
if (!outputDirFile.exists()) {
outputDirFile.mkdirs();
}
String outputPath = outputDir + File.separator + filename + ".xlsx";
JSONArray dataArray = config.getExcelData();
FileInformation fileInformation = new FileInformation();
if (sortBy != null && !sortBy.isEmpty()) {
sortDataArray(dataArray, sortBy, sortAscending);
}
Workbook workbook = new XSSFWorkbook();
FileOutputStream fos = null;
File tempFile = new File(outputPath);
try {
fos = new FileOutputStream(tempFile);
Sheet sheet = workbook.createSheet(filename != null ? filename : DEFAULT_SHEET_NAME);
HeaderConfig headerConfig = parseHeaderConfig(config.getHeader());
writeDataToSheet(sheet, dataArray, headerConfig, addIndex);
applyStyles(sheet, dataArray, headerConfig, addIndex);
workbook.write(fos);
log.info("Excel文件已生成:{}", outputPath);
MultipartFile multipartFile = FileUtils.fileToMultipartFile(tempFile);
log.info("multipartFile完成:{}", multipartFile.getOriginalFilename());
fileInformation = fileInfoService.upload(multipartFile, null, typeId);
log.info("上传到文件中心完成---->:{}", fileInformation);
} catch (Exception e) {
log.error("处理Excel文件时发生错误: " + e.getMessage(), e);
throw e;
} finally {
// if (fos != null) {
// try {
// fos.close();
// } catch (IOException e) {
// log.error("关闭文件流时发生错误: " + e.getMessage(), e);
// }
// }
// if (tempFile.delete()) {
// log.info("临时文件已删除: " + outputPath);
// } else {
// log.error("尝试删除临时文件失败");
// }
}
return fileInformation;
}
@Override
public FileInformation htmlToWorld(MultipartFile multipartFile) {
return null;
}
private static Config parseConfig(String configJson) throws Exception {
try {
boolean validJson = isValidJson(configJson);
if (!validJson) {
throw ProblemSolver.client(ProblemCode.NOT_JSON).build();
}
JSONObject configObj = JSON.parseObject(configJson);
if (!configObj.containsKey("header") || !configObj.containsKey("excelData") || !configObj.containsKey("typeId")) {
throw ProblemSolver.client(ProblemCode.NOT_TYPE_ID).build();
}
Config config = new Config();
config.setHeader(configObj.getJSONObject("header"));
config.setExcelData(configObj.getJSONArray("excelData"));
config.setFileName(String.valueOf(configObj.get("fileName")));
config.setTypeId(String.valueOf(configObj.get("typeId")));
return config;
} catch (Exception e) {
log.error("解析配置文件失败", e);
throw new Exception("解析配置文件失败: " + e.getMessage(), e);
}
}
/**
* 校验字符串是否是有效的json字符串
*
* @param jsonStr
* @return
*/
public static boolean isValidJson(String jsonStr) {
if (jsonStr == null || jsonStr.trim().isEmpty()) {
return false;
}
jsonStr = jsonStr.trim();
if (!(jsonStr.startsWith("{") && jsonStr.endsWith("}")) &&
!(jsonStr.startsWith("[") && jsonStr.endsWith("]"))) {
return false;
}
try {
if (jsonStr.startsWith("{")) {
JSON.parseObject(jsonStr);
} else {
JSON.parseArray(jsonStr);
}
return true;
} catch (JSONException e) {
return false;
} catch (Exception e) {
return false;
}
}
private HeaderConfig parseHeaderConfig(JSONObject headerObj) {
HeaderConfig headerConfig = new HeaderConfig();
JSONObject normalSetting = headerObj.getJSONObject("normal_setting");
if (normalSetting == null) {
normalSetting = new JSONObject();
}
headerConfig.setNormalSetting(normalSetting);
JSONObject customSetting = headerObj.getJSONObject("custom_setting");
if (customSetting == null) {
customSetting = new JSONObject();
}
headerConfig.setCustomSetting(customSetting);
List<Map.Entry<String, Object>> sortedColumns = customSetting.entrySet().stream()
.sorted((e1, e2) -> {
JSONObject conf1 = (JSONObject) e1.getValue();
JSONObject conf2 = (JSONObject) e2.getValue();
int index1 = conf1.getIntValue("index");
int index2 = conf2.getIntValue("index");
return Integer.compare(index1, index2);
})
.collect(Collectors.toList());
List<String> columnOrder = new ArrayList<>();
for (Map.Entry<String, Object> entry : sortedColumns) {
columnOrder.add(entry.getKey());
}
headerConfig.setColumnOrder(columnOrder);
return headerConfig;
}
private void writeDataToSheet(Sheet sheet, JSONArray dataArray, HeaderConfig headerConfig, boolean addIndex) {
List<String> columnOrder = headerConfig.getColumnOrder();
Set<String> allColumns = getAllColumnNames(dataArray);
List<String> finalColumnOrder = new ArrayList<>();
for (String col : columnOrder) {
if (allColumns.contains(col)) {
finalColumnOrder.add(col);
}
}
for (String col : allColumns) {
if (!finalColumnOrder.contains(col)) {
finalColumnOrder.add(col);
}
}
Row headerRow = sheet.createRow(0);
headerRow.setHeightInPoints((float) Double.parseDouble(headerConfig.getNormalSetting().getString("header-row-height")));
int colIndex = addIndex ? 1 : 0;
if (addIndex) {
Cell indexCell = headerRow.createCell(0);
indexCell.setCellValue("索引");
}
for (String column : finalColumnOrder) {
Cell cell = headerRow.createCell(colIndex++);
cell.setCellValue(column);
}
for (int rowIndex = 0; rowIndex < dataArray.size(); rowIndex++) {
Row dataRow = sheet.createRow(rowIndex + 1);
colIndex = addIndex ? 1 : 0;
if (addIndex) {
Cell indexCell = dataRow.createCell(0);
indexCell.setCellValue(rowIndex + 1);
}
JSONObject dataObj = dataArray.getJSONObject(rowIndex);
for (String column : finalColumnOrder) {
Cell cell = dataRow.createCell(colIndex++);
if (dataObj.containsKey(column)) {
Object value = dataObj.get(column);
if (value != null) {
setCellValue(cell, value);
}
}
}
}
}
/**
* 应用样式设置
*
* @param sheet
* @param dataArray
* @param headerConfig
* @param addIndex
*/
private void applyStyles(Sheet sheet, JSONArray dataArray, HeaderConfig headerConfig, boolean addIndex) {
JSONObject normalSetting = headerConfig.getNormalSetting();
JSONObject customSetting = headerConfig.getCustomSetting();
List<String> columnOrder = headerConfig.getColumnOrder();
Set<String> allColumns = getAllColumnNames(dataArray);
List<String> finalColumnOrder = new ArrayList<>();
for (String col : columnOrder) {
if (allColumns.contains(col)) {
finalColumnOrder.add(col);
}
}
for (String col : allColumns) {
if (!finalColumnOrder.contains(col)) {
finalColumnOrder.add(col);
}
}
int colOffset = addIndex ? 1 : 0;
for (int i = 0; i < finalColumnOrder.size(); i++) {
String columnName = finalColumnOrder.get(i);
int colIdx = i + colOffset;
JSONObject colConfig = customSetting.getJSONObject(columnName);
if (colConfig == null) {
colConfig = new JSONObject();
}
if (colConfig.containsKey("column_width")) {
int pixelWidth = colConfig.getIntValue("column_width");
double excelWidth = pixelWidth / 7.0;
sheet.setColumnWidth(colIdx, (int) (excelWidth * 256));
} else if (normalSetting.containsKey("width")) {
int pixelWidth = normalSetting.getIntValue("width");
double excelWidth = pixelWidth / 7.0;
sheet.setColumnWidth(colIdx, (int) (excelWidth * 256));
}
}
Row headerRow = sheet.getRow(0);
if (headerRow != null) {
for (int colIdx = 0; colIdx < finalColumnOrder.size(); colIdx++) {
Cell headerCell = headerRow.getCell(colIdx + colOffset);
if (headerCell != null) {
applyHeaderCellStyle(headerCell.getSheet().getWorkbook(), headerCell, normalSetting);
}
}
}
for (int rowIdx = 1; rowIdx <= dataArray.size(); rowIdx++) {
Row dataRow = sheet.getRow(rowIdx);
if (dataRow != null) {
for (int colIdx = 0; colIdx < finalColumnOrder.size(); colIdx++) {
Cell cell = dataRow.getCell(colIdx + colOffset);
if (cell != null) {
String columnName = finalColumnOrder.get(colIdx);
JSONObject colConfig = customSetting.getJSONObject(columnName);
if (colConfig == null) {
colConfig = new JSONObject();
}
applyDataCellStyle(cell.getSheet().getWorkbook(), cell, normalSetting, colConfig);
}
}
}
}
}
/**
* 数据单元格样式设置
*
* @param workbook
* @param cell
* @param normalSetting
* @param colConfig
*/
private void applyDataCellStyle(Workbook workbook, Cell cell, JSONObject normalSetting, JSONObject colConfig) {
try {
String fontName = normalSetting.containsKey("font-name") ?
normalSetting.getString("font-name") : DEFAULT_FONT_NAME;
short fontSize = DEFAULT_FONT_SIZE;
if (normalSetting.containsKey("font-size")) {
fontSize = (short) normalSetting.getIntValue("font-size");
}
boolean fontBold = normalSetting.containsKey("font-weight") &&
"bold".equals(normalSetting.getString("font-weight"));
Font font = workbook.createFont();
font.setFontName(fontName);
font.setFontHeightInPoints(fontSize);
// POI 5.2.3使用setBold(boolean)替代setBoldweight
font.setBold(fontBold);
if (normalSetting.containsKey("font-color")) {
String fontColor = normalSetting.getString("font-color");
// if (fontColor.startsWith("#")) {
// fontColor = fontColor.substring(1);
// }
try {
if (workbook instanceof XSSFWorkbook) {
byte[] rgb = getRgb(fontColor);
if (rgb == null) {
rgb = new byte[]{(byte) 0, (byte) 0, (byte) 0};
}
// POI 5.2.3 XSSFColor构造兼容处理
((XSSFFont) font).setColor(new XSSFColor(rgb, null));
}
} catch (Exception e) {
log.error("解析数据字体颜色失败: " + fontColor, e);
}
}
// 使用HorizontalAlignment枚举替代CellStyle常量
HorizontalAlignment hAlign;
String align = colConfig.containsKey("column_align") ?
colConfig.getString("column_align") : normalSetting.getString("align");
switch (align) {
case "center":
hAlign = HorizontalAlignment.CENTER;
break;
case "right":
hAlign = HorizontalAlignment.RIGHT;
break;
default:
hAlign = HorizontalAlignment.LEFT;
}
CellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(hAlign);
// 使用VerticalAlignment枚举替代CellStyle常量
style.setVerticalAlignment(VerticalAlignment.CENTER);
if (normalSetting.containsKey("background-color")) {
String bgColor = normalSetting.getString("background-color");
try {
if (workbook instanceof XSSFWorkbook) {
byte[] rgb = getRgb(bgColor);
if (rgb != null) {
XSSFColor color = new XSSFColor(rgb, null);
((XSSFCellStyle) style).setFillForegroundColor(color);
// 使用FillPatternType枚举替代CellStyle常量
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
}
} catch (Exception e) {
log.error("解析数据背景颜色失败: " + bgColor, e);
}
}
style.setWrapText(normalSetting.getBoolean("line-wrap"));
cell.setCellStyle(style);
} catch (Exception e) {
log.error("应用数据单元格样式错误: " + e.getMessage(), e);
}
}
/**
* 更灵活的安全解析HTML颜色值为RGB数组
*
* @param colorStr 颜色字符串，如 "#RRGGBB" 或 "#RGB"
* @return RGB值数组 [R, G, B]，解析失败时返回 null
*/
public static byte[] getRgb(String colorStr) {
if (colorStr == null || !colorStr.startsWith("#")) {
// log.error("颜色值格式不正确: {}", colorStr);
return null;
}
String hex = colorStr.substring(1); // 去掉 # 符号
int length = hex.length();
try {
if (length == 6) {
// 标准的情况 #RRGGBB 格式
int r = Integer.parseInt(hex.substring(0, 2), 16);
int g = Integer.parseInt(hex.substring(2, 4), 16);
int b = Integer.parseInt(hex.substring(4, 6), 16);
return new byte[]{(byte) r, (byte) g, (byte) b}; // 转换为 byte 类型
} else if (length == 3) {
// 若存在简写 #RGB 格式，扩展为 #RRGGBB
int r = Integer.parseInt(hex.substring(0, 1) + hex.substring(0, 1), 16);
int g = Integer.parseInt(hex.substring(1, 2) + hex.substring(1, 2), 16);
int b = Integer.parseInt(hex.substring(2, 3) + hex.substring(2, 3), 16);
return new byte[]{(byte) r, (byte) g, (byte) b}; // 转换为 byte 类型
} else {
log.error("不支持的颜色格式: {}", colorStr);
return null;
}
} catch (NumberFormatException | StringIndexOutOfBoundsException e) {
log.error("解析颜色值失败: {}", colorStr, e);
return null;
}
}
private void applyHeaderCellStyle(Workbook workbook, Cell cell, JSONObject normalSetting) {
try {
String fontName = normalSetting.containsKey("header-font-name") ?
normalSetting.getString("header-font-name") : DEFAULT_FONT_NAME;
short fontSize = DEFAULT_FONT_SIZE;
if (normalSetting.containsKey("header-font-size")) {
fontSize = (short) normalSetting.getIntValue("header-font-size");
}
boolean fontBold = normalSetting.containsKey("header-font-weight") &&
"bold".equals(normalSetting.getString("header-font-weight"));
Font font = workbook.createFont();
font.setFontName(fontName);
font.setFontHeightInPoints(fontSize);
// POI 5.2.3使用setBold(boolean)替代setBoldweight
font.setBold(fontBold);
if (normalSetting.containsKey("header-font-color")) {
String fontColor = normalSetting.getString("header-font-color");
try {
if (workbook instanceof XSSFWorkbook) {
byte[] rgb = getRgb(fontColor);
if (rgb == null) {
//表头字体黑色
rgb = new byte[]{(byte) 0, (byte) 0, (byte) 0};
}
((XSSFFont) font).setColor(new XSSFColor(rgb, null));
}
} catch (Exception e) {
log.error("解析表头字体颜色失败: " + fontColor, e);
}
}
// 使用HorizontalAlignment枚举替代CellStyle常量
HorizontalAlignment hAlign;
String align = normalSetting.getString("header-align");
switch (align) {
case "center":
hAlign = HorizontalAlignment.CENTER;
break;
case "right":
hAlign = HorizontalAlignment.RIGHT;
break;
default:
hAlign = HorizontalAlignment.LEFT;
}
CellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setAlignment(hAlign);
// 使用VerticalAlignment枚举替代CellStyle常量
style.setVerticalAlignment(VerticalAlignment.CENTER);
String bgColor = DEFAULT_HEADER_BACKGROUND;
if (normalSetting.containsKey("header-background-color")) {
bgColor = normalSetting.getString("header-background-color");
}
try {
if (workbook instanceof XSSFWorkbook) {
byte[] rgb = getRgb(bgColor);
if (rgb == null) {
//表头白色背景
rgb = new byte[]{(byte) 255, (byte) 255, (byte) 255};
}
XSSFColor color = new XSSFColor(rgb, null);
((XSSFCellStyle) style).setFillForegroundColor(color);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
} else {
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
} catch (Exception e) {
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
// POI 5.2.3使用BorderStyle枚举替代short值
BorderStyle borderStyle = BorderStyle.THIN; // 默认细边框
if (normalSetting.containsKey("header-border")) {
int borderThick = normalSetting.getIntValue("header-border");
// 根据数值映射边框样式（1=细，2=中，3=粗）
switch (borderThick) {
case 2:
borderStyle = BorderStyle.MEDIUM;
break;
case 3:
borderStyle = BorderStyle.THICK;
break;
default:
borderStyle = BorderStyle.THIN;
}
}
style.setBorderTop(borderStyle);
style.setBorderBottom(borderStyle);
style.setBorderLeft(borderStyle);
style.setBorderRight(borderStyle);
cell.setCellStyle(style);
} catch (Exception e) {
log.error("应用表头单元格样式错误: " + e.getMessage(), e);
}
}
private void setCellValue(Cell cell, Object value) {
if (value instanceof Number) {
cell.setCellValue(((Number) value).doubleValue());
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
} else {
cell.setCellValue(value.toString());
}
}
private Set<String> getAllColumnNames(JSONArray dataArray) {
Set<String> columns = new LinkedHashSet<>();
for (int i = 0; i < dataArray.size(); i++) {
JSONObject obj = dataArray.getJSONObject(i);
if (obj != null) {
columns.addAll(obj.keySet());
}
}
return columns;
}
private void sortDataArray(JSONArray dataArray, String sortBy, boolean sortAscending) {
dataArray.sort((o1, o2) -> {
JSONObject obj1 = (JSONObject) o1;
JSONObject obj2 = (JSONObject) o2;
Object val1 = obj1.get(sortBy);
Object val2 = obj2.get(sortBy);
if (val1 == null && val2 == null) {
return 0;
}
if (val1 == null) {
return sortAscending ? -1 : 1;
}
if (val2 == null) {
return sortAscending ? 1 : -1;
}
if (val1 instanceof Comparable && val2 instanceof Comparable) {
@SuppressWarnings("unchecked")
int result = ((Comparable<Object>) val1).compareTo(val2);
return sortAscending ? result : -result;
}
return sortAscending ? val1.toString().compareTo(val2.toString()) : val2.toString().compareTo(val1.toString());
});
}
@Data
public static class Config {
private JSONObject header;
private JSONArray excelData;
private String fileName;
private String typeId;
}
@Data
public static class HeaderConfig {
private JSONObject normalSetting;
private JSONObject customSetting;
private List<String> columnOrder;
}
public static void main(String[] args) throws Exception {
/* ============ 1. 原始配置骨架（excelData 先留空数组） ============ */
String baseJson = "{\n" +
" \"typeId\":\"10801\",\n" +
" \"fileName\":\"测试文件1\",\n" +
" \"header\":{\n" +
" \"normal_setting\":{\n" +
" \"align\": \"center\",\n" +
" \"font-name\":\"宋体\",\n" +
" \"font-size\": 12,\n" +
" \"font-weight\": \"\",\n" +
" \"font-color\": \"#333333\",\n" +
" \"background-color\": \"\",\n" +
" \"width\": 510,\n" +
" \"line-wrap\": true,\n" +
" \"header-align\": \"\",\n" +
" \"header-row-height\": 150,\n" +
" \"header-font-size\": 121,\n" +
" \"header-font-weight\": \"bold\",\n" +
" \"header-font-color\": \"#0f0f0f\",\n" +
" \"header-background-color\": \"#ADD8E6\"\n" +
" },\n" +
" \"custom_setting\":{\n" +
" \"序号\":{\"index\":3,\"column_align\":\"center\",\"column_width\":500},\n" +
" \"员工编号\":{\"index\":2,\"column_align\":\"left\",\"column_width\":50},\n" +
" \"姓名\":{\"index\":1,\"column_align\":\"center\",\"column_width\":50},\n" +
" \"年龄\":{\"index\":4,\"column_width\":50},\n" +
" \"部门\":{\"index\":5,\"column_align\":\"center\",\"column_width\":200},\n" +
" \"职位\":{\"index\":6,\"column_align\":\"center\",\"column_width\":100},\n" +
" \"薪资\":{\"index\":7,\"column_width\":100},\n" +
" \"绩效评级\":{\"index\":8,\"column_width\":100},\n" +
" \"工作年限\":{\"index\":9,\"column_width\":100},\n" +
" \"入职日期\":{\"index\":10,\"column_width\":100},\n" +
" \"联系电话\":{\"index\":11,\"column_align\":\"left\",\"column_width\":100},\n" +
" \"邮箱\":{\"index\":12,\"column_width\":100},\n" +
" \"工作地点\":{\"index\":13,\"column_width\":100},\n" +
" \"状态\":{\"index\":14,\"column_align\":\"left\",\"column_width\":100}\n" +
" }\n" +
" },\n" +
" \"excelData\": %s\n" +
"}";
/* ============ 2. 单条数据模板 ============ */
String singleRecord = "{\n" +
" \"排序号\": %d,\n" +
" \"员工编号\": \"E%03d\",\n" +
" \"姓名\": \"员工%d\",\n" +
" \"年龄\": %d,\n" +
" \"部门\": \"技术部\",\n" +
" \"职位\": \"工程师\",\n" +
" \"薪资\": %d,\n" +
" \"绩效评级\": \"A\",\n" +
" \"工作年限\": %.1f,\n" +
" \"入职日期\": \"2022-01-01\",\n" +
" \"联系电话\": \"138****%04d\",\n" +
" \"邮箱\": \"emp%03d@company.com\",\n" +
" \"工作地点\": \"北京\",\n" +
" \"状态\": \"在职\"\n" +
"}";
/* ============ 3. 循环生成 N 条 ============ */
int N = 5000; // 想压多大就改多大
List<String> list = new ArrayList<>(N);
for (int i = 1; i <= N; i++) {
list.add(String.format(singleRecord,
i, i, i,
20 + (i % 20), // 年龄 20~39
8000 + (i * 10), // 随薪资递增
1.0 + (i % 10), // 工作年限
i % 10000, i));
}
/* ============ 4. 最终 JSON 字符串 ============ */
String hugeJson = String.format(baseJson, list.toString());
/* ============ 5. 调接口测试 ============ */
FileConvertServiceImpl service = new FileConvertServiceImpl();
FileInformation info = service.convertWithConfig(hugeJson);
System.out.println("生成对象大小：" + hugeJson.length() / 1024 + " KB");
}
}
postman测试
好了 至此 java之json转excel生成 点点关注不迷路 老铁们！！！！！