市住房城乡建设网站,北京网站建设华网天下定制,南京建网站,济南网络科技有限公司背景
业务为实现自定义样式excel的导出#xff0c;常规的做法就是根据数据在代码中进行类似模版的配置#xff1b;这样的体验不是很好#xff0c;只要用户改变下样式的设置不用代码改动就能实现自定义excel的导出更加灵活。
以下是具体实现
pom依赖
dependencyg…背景
业务为实现自定义样式excel的导出常规的做法就是根据数据在代码中进行类似模版的配置这样的体验不是很好只要用户改变下样式的设置不用代码改动就能实现自定义excel的导出更加灵活。
以下是具体实现
pom依赖
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion5.2.3/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion5.2.3/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml-schemas/artifactIdversion4.1.2/version/dependencydependencygroupIdcommons-lang/groupIdartifactIdcommons-lang/artifactIdversion2.6/version/dependencydependencygroupIdcommons-io/groupIdartifactIdcommons-io/artifactIdversion2.11.0/version/dependencydependencygroupIdorg.apache.logging.log4j/groupIdartifactIdlog4j-api/artifactIdversion2.17.2/version/dependencydependencygroupIdorg.apache.logging.log4j/groupIdartifactIdlog4j-core/artifactIdversion2.17.2/version/dependency!-- HTML解析 --dependencygroupIdorg.jsoup/groupIdartifactIdjsoup/artifactIdversion1.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;Autowiredprivate FileInfoService fileInfoService;Autowiredprivate FileUploadConfig fileUploadConfig;Overridepublic FileInformation convertWithConfig(String configJson) throws Exception {log.info(字符串大小:{} 字符串KB:{} 字符串MB:{},configJson.length(),configJson.length()/1024KB,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;}Overridepublic 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);ListMap.EntryString, 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());ListString columnOrder new ArrayList();for (Map.EntryString, Object entry : sortedColumns) {columnOrder.add(entry.getKey());}headerConfig.setColumnOrder(columnOrder);return headerConfig;}private void writeDataToSheet(Sheet sheet, JSONArray dataArray, HeaderConfig headerConfig, boolean addIndex) {ListString columnOrder headerConfig.getColumnOrder();SetString allColumns getAllColumnNames(dataArray);ListString 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();ListString columnOrder headerConfig.getColumnOrder();SetString allColumns getAllColumnNames(dataArray);ListString 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)替代setBoldweightfont.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 格式扩展为 #RRGGBBint 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)替代setBoldweightfont.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 SetString getAllColumnNames(JSONArray dataArray) {SetString 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 ((ComparableObject) val1).compareTo(val2);return sortAscending ? result : -result;}return sortAscending ? val1.toString().compareTo(val2.toString()) : val2.toString().compareTo(val1.toString());});}Datapublic static class Config {private JSONObject header;private JSONArray excelData;private String fileName;private String typeId;}Datapublic static class HeaderConfig {private JSONObject normalSetting;private JSONObject customSetting;private ListString 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%03dcompany.com\,\n \工作地点\: \北京\,\n \状态\: \在职\\n };/* 3. 循环生成 N 条 */int N 5000; // 想压多大就改多大ListString list new ArrayList(N);for (int i 1; i N; i) {list.add(String.format(singleRecord,i, i, i,20 (i % 20), // 年龄 20~398000 (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生成 点点关注不迷路 老铁们
