Univer Excel主页链接：安装步骤
1. 安装
使用预设模式的包管理器安装
- 预设模式：可以理解为开包即用的模式，省去很多配置，当然自由度不如插件模式
pnpm add @univerjs/presets @univerjs/preset-sheets-core
2. 前端代码
<template>
<div ref="container" style="height: 70vh"></div>
</template>
<script lang="ts" setup>
import { onMounted, onBeforeUnmount, ref, watch, reactive } from 'vue';
import { createUniver, FUniver, LocaleType, merge, Univer } from '@univerjs/presets';
import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core';
import UniverPresetSheetsCoreZhCN from '@univerjs/preset-sheets-core/lib/locales/zh-CN';
import '@univerjs/preset-sheets-core/lib/index.css';
const props = defineProps({
excelData: { // 父组件传递的excel数据
type: Object,
default: () => ({}),
},
isEdit: { // 是否是编辑模式
type: Boolean,
default: false,
},
});
const container = ref<HTMLElement | null>(null);
let univerInstance: Univer | null = null;
let univerAPIInstance: FUniver | null = null;
onMounted(() => {
const { univer, univerAPI } = createUniver({
locale: LocaleType.ZH_CN,
locales: {
[LocaleType.ZH_CN]: merge({}, UniverPresetSheetsCoreZhCN),
},
presets: [
UniverSheetsCorePreset({
container: container.value as HTMLElement,
// 设置excel上方的menu菜单
menu: {
// 隐藏加粗按钮
// 'sheet.command.set-range-bold': {
// hidden: true,
// },
},
}),
],
});
// 监听attachments变化，重新加载数据
watch(
() => props.excelData,
(newAttachments) => {
if (newAttachments) {
// univerAPI.createWorkbook(newAttachments);
univerAPI.createWorkbook(workbook1); // 开始可以使用数据workbook0或者workboot1来测试是否正常加载数据
const permission = univerAPI.getPermission();
const workbookEditablePermission = permission.permissionPointsDefinition.WorkbookEditablePermission;
// unitId 是工作簿的 id, WorkbookEditablePermission 是权限点位, false 表示该权限不可使用
// 权限：如果是查看则不可编辑，由props.isEdit确定是编辑或查看模式
permission.setWorkbookPermissionPoint(newAttachments.id, workbookEditablePermission, props.isEdit);
}
}
);
univerInstance = univer;
univerAPIInstance = univerAPI;
});
// 添加一个方法来获取当前表格数据
const getExcelData = () => {
if (!univerAPIInstance) return null;
// 获取当前活动的工作簿
const workbook = univerAPIInstance.getActiveWorkbook();
if (!workbook) return null;
// 获取工作簿数据（JSON格式）
const workbookData = workbook.getSnapshot();
return workbookData;
};
// 如果此组件是在modal中展示，则用户在点击modal的确认按钮后，会调用此方法获取当前表格数据
defineExpose({
getExcelData,
});
// 最简单的可显示的数据，最重要的地方是sheelt1和里面Id的字符串必须一致
const workbook0 = reactive({
id: '1',
sheets: {
sheelt1: {
id: 'sheelt1',
name: '工作簿的名字1',
cellData: {
// 第一行
0: {
// 第一列
0: { v: 'A1' },
// 第二列
1: { v: 'B1' },
},
// 第二行
1: {
// 第一列
0: { v: 'A2' },
// 第二列
1: { v: 'B2' },
},
},
},
},
});
// 官网提供的数据，比较复杂，包含了很多样式
// const workbook1 = reactive({
// id: 'gyI0JO',
// sheetOrder: ['RSfWjJFv4opmE1JaiRj80'],
// name: '',
// appVersion: '0.5.0',
// locale: 'zhCN',
// styles: {},
// sheets: {
// RSfWjJFv4opmE1JaiRj80: {
// id: 'RSfWjJFv4opmE1JaiRj80',
// name: '测试',
// tabColor: '',
// hidden: 0,
// rowCount: 30,
// columnCount: 10,
// zoomRatio: 1,
// freeze: {
// startRow: -1,
// startColumn: -1,
// ySplit: 0,
// xSplit: 0,
// },
// scrollTop: 0,
// scrollLeft: 0,
// defaultColumnWidth: 73,
// defaultRowHeight: 23,
// mergeData: [],
// cellData: {
// // 第一行
// 0: {
// // 第一列
// 0: { v: 'A1' },
// // 第二列
// 1: { v: 'B1' },
// },
// // 第二行
// 1: {
// // 第一列
// 0: { v: 'A2' },
// // 第二列
// 1: { v: 'B2' },
// },
// },
// rowData: {},
// columnData: {
// 0: {
// w: 125,
// hd: 0,
// },
// 1: {
// w: 125,
// hd: 0,
// },
// 2: {
// w: 125,
// hd: 0,
// },
// 3: {
// w: 125,
// hd: 0,
// },
// 4: {
// w: 125,
// hd: 0,
// },
// 5: {
// w: 125,
// hd: 0,
// },
// 6: {
// w: 125,
// hd: 0,
// },
// 7: {
// w: 125,
// hd: 0,
// },
// 8: {
// w: 125,
// hd: 0,
// },
// 9: {
// w: 125,
// hd: 0,
// },
// },
// showGridlines: 1,
// rowHeader: {
// width: 46,
// hidden: 0,
// },
// columnHeader: {
// height: 20,
// hidden: 0,
// },
// selections: ['A1'],
// rightToLeft: 0,
// },
// },
// resources: [
// {
// name: 'SHEET_DEFINED_NAME_PLUGIN',
// data: '',
// },
// ],
// });
onBeforeUnmount(() => {
univerInstance?.dispose();
univerAPIInstance?.dispose();
univerInstance = null;
univerAPIInstance = null;
});
</script>
特别需要注意的地方
单元格样式
工具栏菜单项
3. 文件的保存
官网收费项目
前端发送数据给后端，后端通过判断各种样式将数据使用poi保存成文件，在此只实现了简单的样式保存，供参考
文件实体类
class ExcelVo {
private String id;
private String fileName;
private Map<String, Object> jsonExcel;
}
保存和查看文件的方法
import java.io.*;
import java.lang.reflect.Method;
import java.util.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.regex.Pattern;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.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.Value;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.web.multipart.MultipartFile;
/**
* @Description: Excel文件操作服务
* @Date: 2025-06-15
* @Version: V1.0
*
*/
@Slf4j
@Service
public class ExcelSavaAndReadServiceImpl {
// 读取yml配置文件中的路径
@Value("${jeecg.path.upload:}")
private String uploadPath;
// save&updata数据
public void updateExcelJson(ExcelVo fileData) {
if(StringUtils.isBlank(fileData.getId())) {
// 此处应该使用自定义错误
throw new Exception("id为空");
}
String fileName = fileData.getFileName();
Map<String, Object> univerData = fileData.getJsonExcel();
// log.info("univerData==：" + univerData);
if (StringUtils.isBlank(fileName)) {
throw new Exception("文件名称为空");
}
Path filePath = Paths.get(uploadPath, fileName);
Workbook workbook = null;
try {
if (Files.exists(filePath)) {
// 文件存在：打开并更新
try (InputStream is = new FileInputStream(filePath.toFile())) {
workbook = WorkbookFactory.create(is);
}
updateWorkbookFromUniver(workbook, univerData, false); // 更新模式
} else {
// 文件不存在：新建完整工作簿
workbook = createWorkbook(fileName); // 根据扩展名创建空工作簿
updateWorkbookFromUniver(workbook, univerData, true); // 完整创建模式
}
// 保存工作簿
try (FileOutputStream fos = new FileOutputStream(filePath.toFile())) {
workbook.write(fos);
}
log.info("Excel文件{}成功: {}", Files.exists(filePath) ? "更新" : "创建", fileName);
} catch (Exception e) {
log.error("处理Excel文件失败: {}", fileName, e);
throw new RuntimeException("Excel操作失败: " + e.getMessage(), e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
log.error("关闭工作簿失败", e);
}
}
}
}
// 读取excel数据
public Map<String, Object> getUniverSheetData(String fileName) {
//获取文件
Path filePath = Paths.get(uploadPath + "/" + fileName);
if (!Files.exists(filePath)) {
throw new RuntimeException("Excel文件不存在");
}
try (FileInputStream fis = new FileInputStream(filePath.toFile());
//从输入流 fis 中创建一个 Excel 工作簿对象 Workbook，用于后续读取和解析 Excel 文件内容。
//根据文件输入流自动识别 Excel 格式（如 .xls 或 .xlsx），并创建对应的 HSSFWorkbook 或 XSSFWorkbook 实例
Workbook workbook = WorkbookFactory.create(fis)) {
//解析Excel文件 转成 Univer识别格式
return convertToUniverFormat(workbook);
} catch (Exception e) {
throw new RuntimeException("Excel解析失败", e);
}
}
private Map<String, Object> convertToUniverFormat(Workbook workbook) {
Map<String, Object> univerData = new LinkedHashMap<>();
univerData.put("id", "workbook-" + UUID.randomUUID());
Map<String, Map<String, Object>> sheetsMap = new LinkedHashMap<>();
HSSFPalette hssfPalette = null;
if (workbook instanceof HSSFWorkbook) {
hssfPalette = ((HSSFWorkbook) workbook).getCustomPalette();
}
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
String sheetId = "sheet_" + i;
Map<String, Object> sheetData = new LinkedHashMap<>();
sheetData.put("id", sheetId);
sheetData.put("name", sheet.getSheetName());
// 合并单元格处理
List<Map<String, Integer>> mergeData = new ArrayList<>();
for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
CellRangeAddress region = sheet.getMergedRegion(j);
Map<String, Integer> mergeInfo = new HashMap<>();
mergeInfo.put("startRow", region.getFirstRow());
mergeInfo.put("startColumn", region.getFirstColumn());
mergeInfo.put("endRow", region.getLastRow());
mergeInfo.put("endColumn", region.getLastColumn());
mergeData.add(mergeInfo);
}
sheetData.put("mergeData", mergeData);
Map<String, Map<String, Map<String, Object>>> cellData = new HashMap<>();
Map<String, Map<String, Object>> cellStyleMap = new HashMap<>();
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
// 公式共享ID生成器
Map<String, String> formulaIdMap = new HashMap<>();
for (int r = 0; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null) continue;
Map<String, Map<String, Object>> rowData = new HashMap<>();
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
Map<String, Object> cellInfo = new HashMap<>();
// 处理单元格值
switch (cell.getCellType()) {
case STRING:
cellInfo.put("v", cell.getStringCellValue());
cellInfo.put("t", "text");
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
cellInfo.put("v", cell.getDateCellValue().getTime());
cellInfo.put("t", "date");
} else {
cellInfo.put("v", cell.getNumericCellValue());
cellInfo.put("t", "number");
}
break;
case BOOLEAN:
cellInfo.put("v", cell.getBooleanCellValue());
cellInfo.put("t", "boolean");
break;
case FORMULA:
// 添加=前缀
String formula = "=" + cell.getCellFormula();
cellInfo.put("f", formula);
cellInfo.put("t", "formula");
// 生成公式共享ID
String formulaKey = r + ":" + c + ":" + formula;
if (!formulaIdMap.containsKey(formulaKey)) {
formulaIdMap.put(formulaKey, "fid_" + UUID.randomUUID().toString().substring(0, 8));
}
cellInfo.put("si", formulaIdMap.get(formulaKey));
try {
switch (cell.getCachedFormulaResultType()) {
case NUMERIC:
cellInfo.put("v", cell.getNumericCellValue());
break;
case STRING:
cellInfo.put("v", cell.getStringCellValue());
break;
case BOOLEAN:
cellInfo.put("v", cell.getBooleanCellValue());
break;
default:
cellInfo.put("v", "");
}
} catch (Exception e) {
cellInfo.put("v", "");
}
break;
default:
cellInfo.put("v", "");
cellInfo.put("t", "text");
}
// ============== 样式重构 ==============
CellStyle style = cell.getCellStyle();
Map<String, Object> styleMap = new HashMap<>();
Font font = workbook.getFontAt(style.getFontIndex());
String fontColor = getFontColor(workbook, font);
// 1. 背景色
if (style.getFillPattern() != FillPatternType.NO_FILL) {
String bgColorHex = getColorHex(style.getFillForegroundColor(), hssfPalette);
if (bgColorHex != null) {
Map<String, String> bgColorMap = new HashMap<>();
bgColorMap.put("rgb", bgColorHex);
styleMap.put("bg", bgColorMap);
}
}
// 2. 字体样式 - 使用文档要求的属性名
//Map<String, Object> fontMap = new HashMap<>();
styleMap.put("ff", font.getFontName()); // 字体名称
styleMap.put("fs", font.getFontHeightInPoints()); // 字体大小
styleMap.put("bl", font.getBold() ? 1 : 0); // 加粗
styleMap.put("it", font.getItalic() ? 1 : 0); // 斜体
// 3. 下划线样式 - 符合Univer格式
Map<String, Object> ulMap = new HashMap<>();
ulMap.put("s", (font.getUnderline() != Font.U_NONE) ? 1 : 0); // 是否显示
ulMap.put("c", 0); // 颜色不跟随字体
ulMap.put("t", 0); // 下划线类型
if (fontColor != null) {
Map<String, String> ulColorMap = new HashMap<>();
ulColorMap.put("rgb", fontColor);
ulMap.put("cl", ulColorMap);
}
styleMap.put("ul", ulMap);
// 4. 删除线样式 - 符合Univer格式
Map<String, Object> stMap = new HashMap<>();
stMap.put("s", font.getStrikeout() ? 1 : 0); // 是否显示
stMap.put("c", 0); // 颜色不跟随字体
stMap.put("t", 0); // 删除线类型
if (fontColor != null) {
Map<String, String> stColorMap = new HashMap<>();
stColorMap.put("rgb", fontColor);
stMap.put("cl", stColorMap);
}
styleMap.put("st", stMap);
// 5. 上标/下标
short typeOffset = font.getTypeOffset();
if (typeOffset == Font.SS_SUPER) {
styleMap.put("va", 3); // 上标
} else if (typeOffset == Font.SS_SUB) {
styleMap.put("va", 2); // 下标
} else {
styleMap.put("va", 1); // 正常
}
//styleMap.put("font", fontMap);
// 6. 文字颜色
if (fontColor != null) {
Map<String, String> textColorMap = new HashMap<>();
textColorMap.put("rgb", fontColor);
styleMap.put("cl", textColorMap);
}
// 7. 文字旋转
Map<String, Object> trMap = new HashMap<>();
trMap.put("a", style.getRotation()); // 旋转角度
trMap.put("v", 0); // 0=水平，1=垂直
styleMap.put("tr", trMap);
// 8. 对齐方式
HorizontalAlignment alignment = style.getAlignment();
VerticalAlignment verticalAlignment = style.getVerticalAlignment();
if (alignment != null) {
switch (alignment) {
case LEFT:
styleMap.put("ht", 1);
break;
case CENTER:
case CENTER_SELECTION:
styleMap.put("ht", 2);
break;
case RIGHT:
styleMap.put("ht", 3);
break;
default:
styleMap.put("ht", 1);
}
} else {
styleMap.put("ht", 1);
}
if (verticalAlignment != null) {
switch (verticalAlignment) {
case TOP:
styleMap.put("vt", 1);
break;
case CENTER:
styleMap.put("vt", 2);
break;
case BOTTOM:
styleMap.put("vt", 3);
break;
default:
styleMap.put("vt", 2);
}
} else {
styleMap.put("vt", 2);
}
// 9. 文本截断方式
styleMap.put("tb", style.getWrapText() ? 3 : 1);
// 10. 内边距（示例值）
Map<String, Integer> pdMap = new HashMap<>();
pdMap.put("t", 2); // 上
pdMap.put("b", 2); // 下
pdMap.put("l", 3); // 左
pdMap.put("r", 3); // 右
styleMap.put("pd", pdMap);
// 11. 边框样式
Map<String, Map<String, Object>> borderMap = new HashMap<>();
addBorderStyle(borderMap, "t", style.getBorderTop(), style.getTopBorderColor(), hssfPalette);
addBorderStyle(borderMap, "r", style.getBorderRight(), style.getRightBorderColor(), hssfPalette);
addBorderStyle(borderMap, "b", style.getBorderBottom(), style.getBottomBorderColor(), hssfPalette);
addBorderStyle(borderMap, "l", style.getBorderLeft(), style.getLeftBorderColor(), hssfPalette);
if (!borderMap.isEmpty()) {
styleMap.put("bd", borderMap);
}
cellInfo.put("s", styleMap);
// ============== 样式结束 ==============
cellStyleMap.put(r + ":" + c, styleMap);
rowData.put(String.valueOf(c), cellInfo);
}
if (!rowData.isEmpty()) {
cellData.put(String.valueOf(r), rowData);
}
}
// 处理合并单元格样式
for (CellRangeAddress region : mergedRegions) {
int firstRow = region.getFirstRow();
int firstCol = region.getFirstColumn();
String sourceKey = firstRow + ":" + firstCol;
Map<String, Object> sourceStyle = cellStyleMap.get(sourceKey);
if (sourceStyle != null) {
for (int r = region.getFirstRow(); r <= region.getLastRow(); r++) {
for (int c = region.getFirstColumn(); c <= region.getLastColumn(); c++) {
if (r == firstRow && c == firstCol) continue;
String rowKey = String.valueOf(r);
String colKey = String.valueOf(c);
if (cellData.containsKey(rowKey)) {
Map<String, Map<String, Object>> rowData = cellData.get(rowKey);
if (rowData.containsKey(colKey)) {
rowData.get(colKey).put("s", sourceStyle);
} else {
Map<String, Object> newCell = new HashMap<>();
newCell.put("v", "");
newCell.put("t", "text");
newCell.put("s", sourceStyle);
rowData.put(colKey, newCell);
}
} else {
Map<String, Map<String, Object>> newRow = new HashMap<>();
Map<String, Object> newCell = new HashMap<>();
newCell.put("v", "");
newCell.put("t", "text");
newCell.put("s", sourceStyle);
newRow.put(colKey, newCell);
cellData.put(rowKey, newRow);
}
}
}
}
}
sheetData.put("cellData", cellData);
sheetsMap.put(sheetId, sheetData);
}
univerData.put("sheets", sheetsMap);
return univerData;
}
// 添加边框样式（符合Univer格式）
private void addBorderStyle(Map<String, Map<String, Object>> borderMap, String position,
BorderStyle borderStyle, short colorIndex, HSSFPalette hssfPalette) {
if (borderStyle == BorderStyle.NONE) return;
Map<String, Object> borderStyleMap = new HashMap<>();
borderStyleMap.put("s", convertBorderStyle(borderStyle));
String colorHex = getColorHex(colorIndex, hssfPalette);
if (colorHex == null) colorHex = "#000000";
Map<String, String> colorMap = new HashMap<>();
colorMap.put("rgb", colorHex);
borderStyleMap.put("cl", colorMap);
borderMap.put(position, borderStyleMap);
}
// 边框样式转换方法（返回数字枚举）
private int convertBorderStyle(BorderStyle borderStyle) {
if (borderStyle == null) return 0;
switch (borderStyle) {
case NONE: return 0; // 无边框
case THIN: return 1; // 细实线
case MEDIUM: return 2; // 中等实线
case DASHED: return 3; // 虚线
case DOTTED: return 4; // 点线
case THICK: return 5; // 粗实线
case DOUBLE: return 6; // 双实线
case HAIR: return 7; // 极细线
case MEDIUM_DASHED: return 8; // 中等虚线
case DASH_DOT: return 9; // 点划线
case MEDIUM_DASH_DOT: return 10; // 中等点划线
case DASH_DOT_DOT: return 11; // 点点划线
case MEDIUM_DASH_DOT_DOT: return 12; // 中等点点划线
case SLANTED_DASH_DOT: return 13; // 斜点划线
default: return 0;
}
}
// 获取字体颜色（支持XSSF和HSSF）
private String getFontColor(Workbook workbook, Font font) {
// 处理XSSF格式（.xlsx）
if (workbook instanceof XSSFWorkbook) {
try {
Method getXSSFColorMethod = font.getClass().getMethod("getXSSFColor");
XSSFColor color = (XSSFColor) getXSSFColorMethod.invoke(font);
if (color != null && color.getRGB() != null) {
return String.format("#%02X%02X%02X",
color.getRGB()[0] & 0xFF,
color.getRGB()[1] & 0xFF,
color.getRGB()[2] & 0xFF);
}
} catch (Exception e) {
return "#000000";
}
}
// 处理HSSF格式（.xls）
else if (workbook instanceof HSSFWorkbook && font instanceof HSSFFont) {
HSSFFont hssfFont = (HSSFFont) font;
if (hssfFont.getColor() == HSSFFont.COLOR_NORMAL) {
return "#000000";
}
HSSFColor color = ((HSSFWorkbook) workbook).getCustomPalette().getColor(hssfFont.getColor());
if (color != null) {
short[] rgb = color.getTriplet();
return String.format("#%02X%02X%02X", rgb[0], rgb[1], rgb[2]);
}
}
return "#000000";
}
// 统一颜色处理方法
private String getColorHex(Color color, HSSFPalette hssfPalette) {
if (color == null) return null;
if (color instanceof XSSFColor) {
byte[] rgb = ((XSSFColor) color).getRGB();
if (rgb != null) {
return String.format("#%02X%02X%02X", rgb[0] & 0xFF, rgb[1] & 0xFF, rgb[2] & 0xFF);
}
}
else if (color instanceof HSSFColor) {
HSSFColor hssfColor = (HSSFColor) color;
short[] rgb = hssfColor.getTriplet();
if (rgb != null) {
return String.format("#%02X%02X%02X", rgb[0], rgb[1], rgb[2]);
}
}
return null;
}
// 获取颜色值的重载方法（支持索引）
private String getColorHex(short colorIndex, HSSFPalette hssfPalette) {
if (hssfPalette == null) return null;
HSSFColor color = hssfPalette.getColor(colorIndex);
return (color != null) ?
String.format("#%02X%02X%02X",
color.getTriplet()[0],
color.getTriplet()[1],
color.getTriplet()[2]) :
null;
}
// 读取已存在的Excel文件
private Workbook getExistingWorkbook(Path filePath) throws Exception {
try (InputStream is = new FileInputStream(filePath.toFile())) {
return WorkbookFactory.create(is);
}
}
// 单元格样式创建方法
private Workbook createWorkbook(String fileName) throws Exception {
// 根据文件扩展名创建对应类型的工作簿
if (fileName.toLowerCase().endsWith(".xlsx")) {
return new XSSFWorkbook();
} else if (fileName.toLowerCase().endsWith(".xls")) {
return new HSSFWorkbook();
} else {
throw new IllegalArgumentException("不支持的文件类型: " + fileName);
}
}
@SuppressWarnings("unchecked")
private void updateWorkbookFromUniver(Workbook workbook, Map<String, Object> univerData, boolean isNewFile) {
// 获取工作表顺序和所有工作表数据
List<String> sheetOrder = (List<String>) univerData.get("sheetOrder");
Map<String, Map<String, Object>> sheets = (Map<String, Map<String, Object>>) univerData.get("sheets");
Map<String, Map<String, Object>> styles = (Map<String, Map<String, Object>>) univerData.get("styles");
Map<String, CellStyle> styleCache = new HashMap<>();
for (String sheetId : sheetOrder) {
Map<String, Object> sheetData = sheets.get(sheetId);
if (sheetData == null) continue;
String sheetName = (String) sheetData.get("name");
Sheet sheet;
if (isNewFile) {
sheet = workbook.createSheet(sheetName);
// 设置默认行高列宽
int defaultRowHeight = getInt(sheetData, "defaultRowHeight", 24);
int defaultColumnWidth = getInt(sheetData, "defaultColumnWidth", 88);
sheet.setDefaultRowHeightInPoints(defaultRowHeight);
sheet.setDefaultColumnWidth(defaultColumnWidth);
} else {
sheet = workbook.getSheet(sheetName);
if (sheet == null) {
log.warn("工作表不存在: {}, 跳过更新", sheetName);
continue;
}
}
// ========= 关键修复1：正确处理合并单元格 =========
List<Map<String, Integer>> mergeData = (List<Map<String, Integer>>) sheetData.get("mergeData");
if (mergeData != null) {
// 移除现有合并区域
for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
sheet.removeMergedRegion(i);
}
// 添加新合并区域
for (Map<String, Integer> mergeInfo : mergeData) {
CellRangeAddress region = new CellRangeAddress(
mergeInfo.getOrDefault("startRow", 0),
mergeInfo.getOrDefault("endRow", 0),
mergeInfo.getOrDefault("startColumn", 0),
mergeInfo.getOrDefault("endColumn", 0)
);
sheet.addMergedRegion(region);
}
}
// 处理单元格数据
Map<String, Map<String, Map<String, Object>>> cellData =
(Map<String, Map<String, Map<String, Object>>>) sheetData.get("cellData");
if (cellData != null) {
for (Map.Entry<String, Map<String, Map<String, Object>>> rowEntry : cellData.entrySet()) {
int rowIndex = safeParseInt(rowEntry.getKey(), -1);
if (rowIndex < 0) continue;
Row row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
Map<String, Map<String, Object>> rowData = rowEntry.getValue();
for (Map.Entry<String, Map<String, Object>> cellEntry : rowData.entrySet()) {
int colIndex = safeParseInt(cellEntry.getKey(), -1);
if (colIndex < 0) continue;
Cell cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
Map<String, Object> cellInfo = cellEntry.getValue();
updateCellFromUniver(workbook, cell, cellInfo, styles, styleCache, isNewFile);
}
}
}
}
}
private void updateCellFromUniver(Workbook workbook, Cell cell, Map<String, Object> cellInfo,
Map<String, Map<String, Object>> styles,
Map<String, CellStyle> styleCache, boolean isNewFile) {
// 设置单元格值
String cellType = String.valueOf(cellInfo.get("t"));
Object value = cellInfo.get("v");
switch (cellType) {
case "1": // 文本
cell.setCellValue(value != null ? value.toString() : "");
break;
case "2": // 数字
if (value instanceof Number) {
cell.setCellValue(((Number) value).doubleValue());
} else if (value != null) {
try {
cell.setCellValue(Double.parseDouble(value.toString()));
} catch (NumberFormatException e) {
cell.setCellValue(value.toString());
}
}
break;
case "3": // 布尔值
if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if ("true".equalsIgnoreCase(String.valueOf(value))) {
cell.setCellValue(true);
} else if ("false".equalsIgnoreCase(String.valueOf(value))) {
cell.setCellValue(false);
}
break;
case "date": // 日期
if (value instanceof Number) {
cell.setCellValue(new Date(((Number) value).longValue()));
}
break;
case "formula": // 公式
if (cellInfo.containsKey("f")) {
String formula = (String) cellInfo.get("f");
if (formula.startsWith("=")) {
formula = formula.substring(1);
}
cell.setCellFormula(formula);
}
break;
default:
// 保持原值
}
// ========= 关键修复2：支持内联样式和样式引用 =========
Object styleObj = cellInfo.get("s");
if (styleObj != null) {
Map<String, Object> styleMap = null;
// 情况1：直接内联样式 (如: s={bg={...}, cl={...}})
if (styleObj instanceof Map) {
styleMap = (Map<String, Object>) styleObj;
}
// 情况2：样式ID引用 (如: s="b4_FtO")
else if (styleObj instanceof String) {
String styleId = (String) styleObj;
if (!"null".equals(styleId) && styles != null) {
styleMap = styles.get(styleId);
}
}
if (styleMap != null) {
CellStyle newStyle = workbook.createCellStyle();
// 克隆原有样式（更新模式）
if (!isNewFile && cell.getCellStyle() != null) {
newStyle.cloneStyleFrom(cell.getCellStyle());
}
applyFullStyle(workbook, newStyle, styleMap);
cell.setCellStyle(newStyle);
}
}
}
private byte convertUnderlineType(int univerType) {
switch (univerType) {
case 0: return FontUnderline.SINGLE.getByteValue();
case 1: return FontUnderline.DOUBLE.getByteValue();
case 2: return FontUnderline.SINGLE_ACCOUNTING.getByteValue();
case 3: return FontUnderline.DOUBLE_ACCOUNTING.getByteValue();
default: return FontUnderline.SINGLE.getByteValue();
}
}
// 新建文件时应用完整样式
@SuppressWarnings("unchecked")
private void applyFullStyle(Workbook workbook, CellStyle cellStyle, Map<String, Object> styleMap) {
// 字体处理
// 1. 处理字体
Font font = workbook.createFont();
boolean fontModified = false;
// 字体属性可能在顶层，也可能在font子对象中
/* Map<String, Object> fontMap = null;
if (styleMap.containsKey("font")) {
fontMap = (Map<String, Object>) styleMap.get("font");
} else {
// 检查顶层是否有字体属性
if (styleMap.containsKey("ff") || styleMap.containsKey("fs") ||
styleMap.containsKey("bold") || styleMap.containsKey("italic")) {
fontMap = styleMap;
}
}*/
// 字体属性可能在顶层，也可能在font子对象中
Map<String, Object> fontMap = styleMap.containsKey("font") ?
(Map<String, Object>) styleMap.get("font") : styleMap;
// 字体名称
if (fontMap.containsKey("ff") || fontMap.containsKey("name")) {
String fontName = getString(fontMap, "ff", getString(fontMap, "name", "Arial"));
font.setFontName(fontName);
fontModified = true;
}
if (fontMap != null) {
// 字体名称
if (fontMap.containsKey("ff") || fontMap.containsKey("name")) {
String fontName = getString(fontMap, "ff", getString(fontMap, "name", "Arial"));
font.setFontName(fontName);
fontModified = true;
}
// 字体大小
if (fontMap.containsKey("fs") || fontMap.containsKey("size")) {
Object size = fontMap.get("fs");
if (size == null) size = fontMap.get("size");
if (size instanceof Number) {
font.setFontHeightInPoints(((Number) size).shortValue());
fontModified = true;
}
}
// 粗体 (bl 或 bold)
if (fontMap.containsKey("bl") || fontMap.containsKey("bold")) {
Object bold = fontMap.get("bl");
if (bold == null) bold = fontMap.get("bold");
if (bold instanceof Boolean) {
font.setBold((Boolean) bold);
fontModified = true;
} else if (bold instanceof Number) {
font.setBold(((Number) bold).intValue() == 1);
fontModified = true;
}
}
// 斜体 (it 或 italic)
if (fontMap.containsKey("it") || fontMap.containsKey("italic")) {
Object italic = fontMap.get("it");
if (italic == null) italic = fontMap.get("italic");
if (italic instanceof Boolean) {
font.setItalic((Boolean) italic);
fontModified = true;
} else if (italic instanceof Number) {
font.setItalic(((Number) italic).intValue() == 1);
fontModified = true;
}
}
// 下划线 (ul)
if (fontMap.containsKey("ul")) {
Object ulObj = fontMap.get("ul");
if (ulObj instanceof Map) {
Map<String, Object> ulMap = (Map<String, Object>) ulObj;
if (ulMap.containsKey("s") && getInt(ulMap, "s", 0) == 1) {
// 设置下划线类型
int underlineType = getInt(ulMap, "t", FontUnderline.SINGLE.getByteValue());
font.setUnderline((byte) underlineType);
// 设置下划线颜色（如果指定）
if (ulMap.containsKey("cl") && !getBoolean(ulMap, "c", true)) {
Object clObj = ulMap.get("cl");
if (clObj instanceof Map) {
Map<String, String> clMap = (Map<String, String>) clObj;
String rgb = clMap.get("rgb");
if (isValidHexColor(rgb)) {
setFontColor(workbook, font, rgb);
}
}
}
fontModified = true;
}
}
}
// 删除线 (st)
if (fontMap.containsKey("st")) {
Object stObj = fontMap.get("st");
if (stObj instanceof Map) {
Map<String, Object> stMap = (Map<String, Object>) stObj;
if (stMap.containsKey("s") && getInt(stMap, "s", 0) == 1) {
font.setStrikeout(true);
// 设置删除线颜色（如果指定）
if (stMap.containsKey("cl") && !getBoolean(stMap, "c", true)) {
Object clObj = stMap.get("cl");
if (clObj instanceof Map) {
Map<String, String> clMap = (Map<String, String>) clObj;
String rgb = clMap.get("rgb");
if (isValidHexColor(rgb)) {
// 删除线颜色通过字体颜色设置（POI限制）
setFontColor(workbook, font, rgb);
}
}
}
fontModified = true;
}
}
} else if (fontMap.containsKey("strikeThrough")) {
// 兼容旧版
Object strike = fontMap.get("strikeThrough");
if (strike instanceof Boolean) {
font.setStrikeout((Boolean) strike);
fontModified = true;
}
}
// 上划线 (ol) - POI不支持，但我们可以模拟实现
if (fontMap.containsKey("ol")) {
Object olObj = fontMap.get("ol");
if (olObj instanceof Map) {
Map<String, Object> olMap = (Map<String, Object>) olObj;
if (olMap.containsKey("s") && getInt(olMap, "s", 0) == 1) {
// POI不支持上划线，使用下划线并调整位置模拟
font.setUnderline((byte) 8); // 使用双下划线模拟
fontModified = true;
}
}
}
// 字体颜色 (顶层cl属性)
if (styleMap.containsKey("cl")) {
Object clObj = styleMap.get("cl");
if (clObj instanceof Map) {
Map<String, String> clMap = (Map<String, String>) clObj;
String rgb = clMap.get("rgb");
if (isValidHexColor(rgb)) {
setFontColor(workbook, font, rgb);
fontModified = true;
}
}
}
if (fontModified) {
cellStyle.setFont(font);
}
// 2. 背景色 (顶层bg属性)
if (styleMap.containsKey("bg")) {
Object bgObj = styleMap.get("bg");
if (bgObj instanceof Map) {
Map<String, String> bgMap = (Map<String, String>) bgObj;
String rgb = bgMap.get("rgb");
if (isValidHexColor(rgb)) {
setBackgroundColor(workbook, cellStyle, rgb);
}
}
}
// 3. 水平对齐 (顶层ht属性)
if (styleMap.containsKey("ht")) {
Object ht = styleMap.get("ht");
if (ht instanceof Number) {
int align = ((Number) ht).intValue();
switch (align) {
case 1:
cellStyle.setAlignment(HorizontalAlignment.LEFT);
break;
case 2:
cellStyle.setAlignment(HorizontalAlignment.CENTER);
break;
case 3:
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
break;
case 4:
cellStyle.setAlignment(HorizontalAlignment.JUSTIFY);
break;
case 5:
cellStyle.setAlignment(HorizontalAlignment.FILL);
break;
}
}
}
// 4. 垂直对齐 (顶层vt属性)
if (styleMap.containsKey("vt")) {
Object vt = styleMap.get("vt");
if (vt instanceof Number) {
int verticalAlign = ((Number) vt).intValue();
switch (verticalAlign) {
case 1:
cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
break;
case 2:
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
break;
case 3:
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
break;
case 4:
cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
break;
}
}
}
// 5. 自动换行 (顶层wrap属性)
if (styleMap.containsKey("wrap")) {
Object wrap = styleMap.get("wrap");
if (wrap instanceof Boolean) {
cellStyle.setWrapText((Boolean) wrap);
} else if (wrap instanceof Number) {
cellStyle.setWrapText(((Number) wrap).intValue() == 1);
}
}
// 6. 文字旋转 (tr)
if (styleMap.containsKey("tr")) {
Object trObj = styleMap.get("tr");
if (trObj instanceof Map) {
Map<String, Object> trMap = (Map<String, Object>) trObj;
// 垂直文本
if (trMap.containsKey("v") && getInt(trMap, "v", 0) == 1) {
cellStyle.setRotation((short) 255); // 垂直文本
}
// 旋转角度
else if (trMap.containsKey("a")) {
int angle = getInt(trMap, "a", 0);
// POI角度范围：-90° 到 90°，转换为0-180度
if (angle > 90) angle = 90;
if (angle < -90) angle = -90;
cellStyle.setRotation((short) angle);
}
}
}
// 7. 边框处理 (顶层bd属性)
if (styleMap.containsKey("bd")) {
Map<String, Map<String, Object>> borderMap = (Map<String, Map<String, Object>>) styleMap.get("bd");
for (Map.Entry<String, Map<String, Object>> entry : borderMap.entrySet()) {
String position = entry.getKey();
Map<String, Object> borderStyle = entry.getValue();
Object styleObj = borderStyle.get("s");
int borderStyleValue = styleObj instanceof Number ? ((Number) styleObj).intValue() : 0;
String rgb = null;
Object colorObj = borderStyle.get("cl");
if (colorObj instanceof Map) {
Map<String, String> colorMap = (Map<String, String>) colorObj;
rgb = colorMap.get("rgb");
}
applyBorderStyle(cellStyle, position, borderStyleValue, rgb, workbook);
}
}
// 8. 文本方向 (tb) - 1: 从左到右, 2: 从右到左
if (styleMap.containsKey("tb")) {
Object tb = styleMap.get("tb");
if (tb instanceof Number) {
int textDirection = ((Number) tb).intValue();
if (textDirection == 2) {
cellStyle.setRotation((short) 180); // 从右到左
}
}
}
// 9. 内边距 (pd)
if (styleMap.containsKey("pd")) {
Object pdObj = styleMap.get("pd");
if (pdObj instanceof Map) {
Map<String, Object> pdMap = (Map<String, Object>) pdObj;
// POI 不支持直接设置内边距，但可以通过缩进模拟
int left = getInt(pdMap, "l", 0);
if (left > 0) {
cellStyle.setIndention((short) (left / 3)); // 近似转换
}
}
}
}
}
// 新增辅助方法
private boolean getBoolean(Map<String, Object> map, String key, boolean defaultValue) {
Object value = map.get(key);
if (value instanceof Boolean) {
return (Boolean) value;
} else if (value instanceof Number) {
return ((Number) value).intValue() == 1;
} else if (value != null) {
return "true".equalsIgnoreCase(value.toString());
}
return defaultValue;
}
private String getString(Map<String, Object> map, String key, String defaultValue) {
Object value = map.get(key);
return value != null ? value.toString() : defaultValue;
}
// 只更新特定的样式属性
@SuppressWarnings("unchecked")
private void applyPartialStyle(Workbook workbook, CellStyle newStyle, Map<String, Object> styleMap) {
// 字体处理（只更新提供的属性）
if (styleMap.containsKey("font")) {
Font font = workbook.createFont();
Map<String, Object> fontMap = (Map<String, Object>) styleMap.get("font");
// 保留原字体
Font originalFont = workbook.getFontAt(newStyle.getFontIndex());
if (originalFont != null) {
font.setFontName(originalFont.getFontName());
font.setFontHeightInPoints(originalFont.getFontHeightInPoints());
font.setBold(originalFont.getBold());
font.setItalic(originalFont.getItalic());
font.setUnderline(originalFont.getUnderline());
font.setColor(originalFont.getColor());
}
// 更新Univer提供的属性
if (fontMap.containsKey("name")) {
font.setFontName(fontMap.get("name").toString());
}
if (fontMap.containsKey("size")) {
Object size = fontMap.get("size");
if (size instanceof Number) {
font.setFontHeightInPoints(((Number) size).shortValue());
}
}
if (fontMap.containsKey("bold")) {
Object bold = fontMap.get("bold");
if (bold instanceof Boolean) {
font.setBold((Boolean) bold);
}
}
if (fontMap.containsKey("italic")) {
Object italic = fontMap.get("italic");
if (italic instanceof Boolean) {
font.setItalic((Boolean) italic);
}
}
if (fontMap.containsKey("underline")) {
Object underline = fontMap.get("underline");
if (underline instanceof Number) {
font.setUnderline(((Number) underline).byteValue());
}
}
if (fontMap.containsKey("color")) {
Object colorObj = fontMap.get("color");
if (colorObj instanceof Map) {
Map<String, String> colorMap = (Map<String, String>) colorObj;
String rgb = colorMap.get("rgb");
if (isValidHexColor(rgb)) {
setFontColor(workbook, font, rgb);
}
}
}
newStyle.setFont(font);
}
// 背景色（只更新提供的）
if (styleMap.containsKey("bg")) {
Object bgObj = styleMap.get("bg");
if (bgObj instanceof Map) {
Map<String, String> bgMap = (Map<String, String>) bgObj;
String rgb = bgMap.get("rgb");
if (isValidHexColor(rgb)) {
setBackgroundColor(workbook, newStyle, rgb);
}
}
}
// 对齐方式（只更新提供的）
if (styleMap.containsKey("ht")) {
Object ht = styleMap.get("ht");
if (ht instanceof Number) {
int align = ((Number) ht).intValue();
switch (align) {
case 1: newStyle.setAlignment(HorizontalAlignment.LEFT); break;
case 2: newStyle.setAlignment(HorizontalAlignment.CENTER); break;
case 3: newStyle.setAlignment(HorizontalAlignment.RIGHT); break;
}
}
}
// 垂直对齐（只更新提供的）
if (styleMap.containsKey("vt")) {
Object vt = styleMap.get("vt");
if (vt instanceof Number) {
int verticalAlign = ((Number) vt).intValue();
switch (verticalAlign) {
case 1: newStyle.setVerticalAlignment(VerticalAlignment.TOP); break;
case 2: newStyle.setVerticalAlignment(VerticalAlignment.CENTER); break;
case 3: newStyle.setVerticalAlignment(VerticalAlignment.BOTTOM); break;
}
}
}
// 自动换行（只更新提供的）
if (styleMap.containsKey("wrap")) {
Object wrap = styleMap.get("wrap");
if (wrap instanceof Boolean) {
newStyle.setWrapText((Boolean) wrap);
}
}
}
@SuppressWarnings("unchecked")
private void applyUniverStyleToCell(Workbook workbook, CellStyle cellStyle, Map<String, Object> styleMap) {
// 字体处理
if (styleMap.containsKey("font")) {
Map<String, Object> fontMap = (Map<String, Object>) styleMap.get("font");
Font font = workbook.createFont();
Object name = fontMap.get("name");
if (name != null) font.setFontName(name.toString());
Object size = fontMap.get("size");
if (size instanceof Number) {
font.setFontHeightInPoints(((Number) size).shortValue());
} else if (size != null) {
try {
font.setFontHeightInPoints(Short.parseShort(size.toString()));
} catch (NumberFormatException ignored) {}
}
Object bold = fontMap.get("bold");
if (bold instanceof Boolean) {
font.setBold((Boolean) bold);
} else if ("true".equalsIgnoreCase(String.valueOf(bold))) {
font.setBold(true);
}
Object italic = fontMap.get("italic");
if (italic instanceof Boolean) {
font.setItalic((Boolean) italic);
} else if ("true".equalsIgnoreCase(String.valueOf(italic))) {
font.setItalic(true);
}
Object underline = fontMap.get("underline");
if (underline instanceof Number) {
font.setUnderline(((Number) underline).byteValue());
} else if (underline != null) {
try {
font.setUnderline(Byte.parseByte(underline.toString()));
} catch (NumberFormatException ignored) {}
}
// 字体颜色
if (fontMap.containsKey("color")) {
Object colorObj = fontMap.get("color");
if (colorObj instanceof Map) {
Map<String, String> colorMap = (Map<String, String>) colorObj;
String rgb = colorMap.get("rgb");
if (rgb != null && isValidHexColor(rgb)) {
setFontColor(workbook, font, rgb);
}
}
}
cellStyle.setFont(font);
}
// 背景颜色
if (styleMap.containsKey("bg")) {
Object bgObj = styleMap.get("bg");
if (bgObj instanceof Map) {
Map<String, String> bgMap = (Map<String, String>) bgObj;
String rgb = bgMap.get("rgb");
if (rgb != null && isValidHexColor(rgb)) {
setBackgroundColor(workbook, cellStyle, rgb);
}
}
}
// 对齐方式
Object ht = styleMap.get("ht");
if (ht instanceof Number) {
int align = ((Number) ht).intValue();
switch (align) {
case 1: cellStyle.setAlignment(HorizontalAlignment.LEFT); break;
case 2: cellStyle.setAlignment(HorizontalAlignment.CENTER); break;
case 3: cellStyle.setAlignment(HorizontalAlignment.RIGHT); break;
default: cellStyle.setAlignment(HorizontalAlignment.LEFT);
}
}
Object vt = styleMap.get("vt");
if (vt instanceof Number) {
int verticalAlign = ((Number) vt).intValue();
switch (verticalAlign) {
case 1: cellStyle.setVerticalAlignment(VerticalAlignment.TOP); break;
case 2: cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); break;
case 3: cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM); break;
default: cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
}
}
// 自动换行
Object wrap = styleMap.get("wrap");
if (wrap instanceof Boolean) {
cellStyle.setWrapText((Boolean) wrap);
} else if ("true".equalsIgnoreCase(String.valueOf(wrap))) {
cellStyle.setWrapText(true);
} else if ("false".equalsIgnoreCase(String.valueOf(wrap))) {
cellStyle.setWrapText(false);
}
// 边框处理
if (styleMap.containsKey("bd")) {
Map<String, Map<String, Object>> borderMap =
(Map<String, Map<String, Object>>) styleMap.get("bd");
for (Map.Entry<String, Map<String, Object>> entry : borderMap.entrySet()) {
String position = entry.getKey();
Map<String, Object> borderStyle = entry.getValue();
Object styleObj = borderStyle.get("s");
int borderStyleValue = styleObj instanceof Number ? ((Number) styleObj).intValue() : 0;
String rgb = null;
Object colorObj = borderStyle.get("cl");
if (colorObj instanceof Map) {
Map<String, String> colorMap = (Map<String, String>) colorObj;
rgb = colorMap.get("rgb");
}
applyBorderStyle(cellStyle, position, borderStyleValue, rgb, workbook);
}
}
}
private void applyBorderStyle(CellStyle cellStyle, String position, int styleValue, String rgb, Workbook workbook) {
BorderStyle borderStyle = convertToPoiBorderStyle(styleValue);
// 处理无边框情况
if (borderStyle == BorderStyle.NONE) {
switch (position) {
case "t": cellStyle.setBorderTop(BorderStyle.NONE); break;
case "r": cellStyle.setBorderRight(BorderStyle.NONE); break;
case "b": cellStyle.setBorderBottom(BorderStyle.NONE); break;
case "l": cellStyle.setBorderLeft(BorderStyle.NONE); break;
}
return;
}
if (workbook instanceof XSSFWorkbook) {
// XSSF (xlsx) 处理
XSSFCellStyle xssfStyle = (XSSFCellStyle) cellStyle;
XSSFColor color = null;
if (rgb != null && isValidHexColor(rgb)) {
byte[] rgbBytes = parseHexColor(rgb);
color = new XSSFColor(rgbBytes, null);
}
switch (position) {
case "t":
xssfStyle.setBorderTop(borderStyle);
if (color != null) xssfStyle.setTopBorderColor(color);
break;
case "r":
xssfStyle.setBorderRight(borderStyle);
if (color != null) xssfStyle.setRightBorderColor(color);
break;
case "b":
xssfStyle.setBorderBottom(borderStyle);
if (color != null) xssfStyle.setBottomBorderColor(color);
break;
case "l":
xssfStyle.setBorderLeft(borderStyle);
if (color != null) xssfStyle.setLeftBorderColor(color);
break;
}
} else {
// HSSF (xls) 处理
short colorIndex = IndexedColors.BLACK.getIndex();
if (rgb != null && isValidHexColor(rgb)) {
colorIndex = getColorIndex(workbook, rgb);
}
switch (position) {
case "t":
cellStyle.setBorderTop(borderStyle);
cellStyle.setTopBorderColor(colorIndex);
break;
case "r":
cellStyle.setBorderRight(borderStyle);
cellStyle.setRightBorderColor(colorIndex);
break;
case "b":
cellStyle.setBorderBottom(borderStyle);
cellStyle.setBottomBorderColor(colorIndex);
break;
case "l":
cellStyle.setBorderLeft(borderStyle);
cellStyle.setLeftBorderColor(colorIndex);
break;
}
}
}
private BorderStyle convertToPoiBorderStyle(int styleValue) {
// 根据Univer官方文档映射边框样式
switch (styleValue) {
case 0: return BorderStyle.NONE; // 无边框
case 1: return BorderStyle.THIN; // 细实线
case 2: return BorderStyle.MEDIUM; // 中等实线
case 3: return BorderStyle.DASHED; // 虚线
case 4: return BorderStyle.DOTTED; // 点线
case 5: return BorderStyle.THICK; // 粗实线
case 6: return BorderStyle.DOUBLE; // 双实线
case 7: return BorderStyle.HAIR; // 极细线
case 8: return BorderStyle.MEDIUM_DASHED; // 中等虚线
case 9: return BorderStyle.DASH_DOT; // 点划线
case 10: return BorderStyle.MEDIUM_DASH_DOT; // 中等点划线
case 11: return BorderStyle.DASH_DOT_DOT; // 点点划线
case 12: return BorderStyle.MEDIUM_DASH_DOT_DOT; // 中等点点划线
case 13: return BorderStyle.SLANTED_DASH_DOT; // 斜点划线
default: return BorderStyle.NONE;
}
}
private short getColorIndex(Workbook workbook, String rgb) {
if (!isValidHexColor(rgb)) {
return IndexedColors.BLACK.getIndex();
}
try {
byte[] rgbBytes = parseHexColor(rgb);
int r = rgbBytes[0] & 0xFF;
int g = rgbBytes[1] & 0xFF;
int b = rgbBytes[2] & 0xFF;
if (workbook instanceof HSSFWorkbook) {
HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
HSSFPalette palette = hssfWorkbook.getCustomPalette();
return findOrCreateHSSFColor(palette, r, g, b);
} else if (workbook instanceof XSSFWorkbook) {
// XSSF使用直接RGB值，返回任意值（实际使用XSSFColor）
return IndexedColors.BLACK.getIndex();
}
} catch (Exception e) {
log.error("颜色转换失败: {}", rgb, e);
}
return IndexedColors.BLACK.getIndex();
}
private void setBackgroundColor(Workbook workbook, CellStyle cellStyle, String rgb) {
if (!isValidHexColor(rgb)) return;
try {
byte[] rgbBytes = parseHexColor(rgb);
int r = rgbBytes[0] & 0xFF;
int g = rgbBytes[1] & 0xFF;
int b = rgbBytes[2] & 0xFF;
if (workbook instanceof XSSFWorkbook) {
XSSFColor color = new XSSFColor(new java.awt.Color(r, g, b), null);
((XSSFCellStyle) cellStyle).setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
} else if (workbook instanceof HSSFWorkbook) {
HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
HSSFPalette palette = hssfWorkbook.getCustomPalette();
// 查找相似颜色或创建新索引
short colorIndex = findOrCreateHSSFColor(palette, r, g, b);
cellStyle.setFillForegroundColor(colorIndex);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
} catch (Exception e) {
log.error("设置背景色失败: {}", rgb, e);
}
}
// 辅助方法：处理HSSF颜色
private short findOrCreateHSSFColor(HSSFPalette palette, int r, int g, int b) {
// 尝试查找相似颜色
HSSFColor color = palette.findSimilarColor(r, g, b);
if (color != null) {
return color.getIndex();
}
// 创建新颜色（从56开始的自定义索引）
for (short i = 56; i < 64; i++) {
if (palette.getColor(i) == null) {
palette.setColorAtIndex(i, (byte) r, (byte) g, (byte) b);
return i;
}
}
// 没有可用槽位则使用默认
return IndexedColors.GREY_25_PERCENT.getIndex();
}
private void setFontColor(Workbook workbook, Font font, String rgb) {
if (!isValidHexColor(rgb)) return;
try {
byte[] rgbBytes = parseHexColor(rgb);
if (workbook instanceof XSSFWorkbook && font instanceof XSSFFont) {
XSSFColor color = new XSSFColor(rgbBytes, null);
((XSSFFont) font).setColor(color);
} else if (workbook instanceof HSSFWorkbook) {
HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
HSSFPalette palette = hssfWorkbook.getCustomPalette();
HSSFColor color = palette.findSimilarColor(
rgbBytes[0] & 0xFF,
rgbBytes[1] & 0xFF,
rgbBytes[2] & 0xFF
);
font.setColor(color.getIndex());
}
} catch (Exception e) {
log.error("设置字体颜色失败: {}", rgb, e);
}
}
// 辅助方法
private boolean isValidHexColor(String color) {
return color != null && Pattern.matches("^#[0-9A-Fa-f]{6}$", color);
}
private byte[] parseHexColor(String hexColor) {
if (!isValidHexColor(hexColor)) {
return new byte[]{0, 0, 0}; // 默认黑色
}
return new byte[]{
(byte) Integer.parseInt(hexColor.substring(1, 3), 16),
(byte) Integer.parseInt(hexColor.substring(3, 5), 16),
(byte) Integer.parseInt(hexColor.substring(5, 7), 16)
};
}
private int safeParseInt(String str, int defaultValue) {
try {
return Integer.parseInt(str);
} catch (NumberFormatException e) {
return defaultValue;
}
}
private int getInt(Map<String, Object> map, String key, int defaultValue) {
Object value = map.get(key);
if (value instanceof Number) {
return ((Number) value).intValue();
} else if (value != null) {
try {
return Integer.parseInt(value.toString());
} catch (NumberFormatException e) {
return defaultValue;
}
}
return defaultValue;
}
}