office xml轉xlsx 詳情 - java,xml,office,xlsx chadLi 博客

主要代碼

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.io.FileOutputStream;

public class OfficeXmlToXlsxConverter {
    
    // Office XML命名空間
    private static final String SS_NAMESPACE = "urn:schemas-microsoft-com:office:spreadsheet";
    
    public static void convertOfficeXmlToXlsx(String xmlFilePath, String xlsxFilePath) throws Exception {
        // 解析Office XML文件
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setNamespaceAware(true); // 重要：啓用命名空間支持
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document document = builder.parse(new File(xmlFilePath));
        
        // 創建Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        
        // 獲取Worksheet
        Element worksheet = (Element) document.getElementsByTagNameNS(SS_NAMESPACE, "Worksheet").item(0);
        Element table = (Element) worksheet.getElementsByTagNameNS(SS_NAMESPACE, "Table").item(0);
        
        // 獲取所有行
        NodeList rowNodes = table.getElementsByTagNameNS(SS_NAMESPACE, "Row");
        
        int rowNum = 0;
        for (int i = 0; i < rowNodes.getLength(); i++) {
            Element rowElement = (Element) rowNodes.item(i);
            Row excelRow = sheet.createRow(rowNum++);
            
            // 獲取單元格
            NodeList cellNodes = rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell");
            
            int colNum = 0;
            for (int j = 0; j < cellNodes.getLength(); j++) {
                Element cellElement = (Element) cellNodes.item(j);
                Element dataElement = (Element) cellElement.getElementsByTagNameNS(SS_NAMESPACE, "Data").item(0);
                
                if (dataElement != null) {
                    String cellValue = dataElement.getTextContent();
                    String dataType = dataElement.getAttributeNS(SS_NAMESPACE, "Type");
                    
                    Cell excelCell = excelRow.createCell(colNum++);
                    setCellValueBasedOnType(excelCell, cellValue, dataType);
                }
            }
        }
        
        // 自動調整列寬
        autoSizeColumns(sheet, getMaxColumnCount(rowNodes));
        
        // 寫入文件
        try (FileOutputStream outputStream = new FileOutputStream(xlsxFilePath)) {
            workbook.write(outputStream);
        }
        
        workbook.close();
        System.out.println("Office XML轉換為XLSX完成！");
    }
    
    private static void setCellValueBasedOnType(Cell cell, String value, String dataType) {
        if ("Number".equals(dataType)) {
            try {
                cell.setCellValue(Double.parseDouble(value));
            } catch (NumberFormatException e) {
                cell.setCellValue(value);
            }
        } else if ("Boolean".equals(dataType)) {
            cell.setCellValue(Boolean.parseBoolean(value));
        } else if ("DateTime".equals(dataType)) {
            // 處理日期時間格式
            cell.setCellValue(value);
        } else {
            // 默認作為字符串處理
            cell.setCellValue(value);
        }
    }
    
    private static int getMaxColumnCount(NodeList rowNodes) {
        int maxColumns = 0;
        for (int i = 0; i < rowNodes.getLength(); i++) {
            Element rowElement = (Element) rowNodes.item(i);
            int cellCount = rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell").getLength();
            maxColumns = Math.max(maxColumns, cellCount);
        }
        return maxColumns;
    }
    
    private static void autoSizeColumns(Sheet sheet, int columnCount) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
        }
    }
    
    public static void main(String[] args) {
        try {
            convertOfficeXmlToXlsx("input.xml", "output.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

增強版

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;

public class AdvancedOfficeXmlToXlsxConverter {
    
    private static final String SS_NAMESPACE = "urn:schemas-microsoft-com:office:spreadsheet";
    private Map<String, CellStyle> styleCache = new HashMap<>();
    
    public void convertWithStyles(String xmlFilePath, String xlsxFilePath) throws Exception {
        Document document = parseXmlDocument(xmlFilePath);
        Workbook workbook = new XSSFWorkbook();
        
        // 處理多個工作表
        NodeList worksheetNodes = document.getElementsByTagNameNS(SS_NAMESPACE, "Worksheet");
        
        for (int sheetIndex = 0; sheetIndex < worksheetNodes.getLength(); sheetIndex++) {
            Element worksheet = (Element) worksheetNodes.item(sheetIndex);
            String sheetName = worksheet.getAttributeNS(SS_NAMESPACE, "Name");
            if (sheetName.isEmpty()) {
                sheetName = "Sheet" + (sheetIndex + 1);
            }
            
            Sheet sheet = workbook.createSheet(sheetName);
            processWorksheet(worksheet, sheet, workbook);
        }
        
        saveWorkbook(workbook, xlsxFilePath);
    }
    
    private Document parseXmlDocument(String xmlFilePath) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setNamespaceAware(true);
        return factory.newDocumentBuilder().parse(new File(xmlFilePath));
    }
    
    private void processWorksheet(Element worksheet, Sheet sheet, Workbook workbook) {
        Element table = (Element) worksheet.getElementsByTagNameNS(SS_NAMESPACE, "Table").item(0);
        if (table == null) return;
        
        NodeList rowNodes = table.getElementsByTagNameNS(SS_NAMESPACE, "Row");
        
        for (int i = 0; i < rowNodes.getLength(); i++) {
            Element rowElement = (Element) rowNodes.item(i);
            Row excelRow = sheet.createRow(i);
            
            processRow(rowElement, excelRow, workbook);
        }
        
        // 自動調整列寬
        autoSizeColumns(sheet, getMaxColumnCount(rowNodes));
    }
    
    private void processRow(Element rowElement, Row excelRow, Workbook workbook) {
        NodeList cellNodes = rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell");
        
        for (int j = 0; j < cellNodes.getLength(); j++) {
            Element cellElement = (Element) cellNodes.item(j);
            Cell excelCell = excelRow.createCell(j);
            
            processCell(cellElement, excelCell, workbook);
        }
    }
    
    private void processCell(Element cellElement, Cell excelCell, Workbook workbook) {
        Element dataElement = (Element) cellElement.getElementsByTagNameNS(SS_NAMESPACE, "Data").item(0);
        
        if (dataElement != null) {
            String value = dataElement.getTextContent();
            String dataType = dataElement.getAttributeNS(SS_NAMESPACE, "Type");
            
            setCellValue(excelCell, value, dataType);
            applyCellStyle(cellElement, excelCell, workbook);
        }
        
        // 處理合並單元格
        String mergeAcross = cellElement.getAttributeNS(SS_NAMESPACE, "MergeAcross");
        if (!mergeAcross.isEmpty()) {
            // 這裏可以添加合併單元格的邏輯
        }
    }
    
    private void setCellValue(Cell cell, String value, String dataType) {
        switch (dataType) {
            case "Number":
                try {
                    cell.setCellValue(Double.parseDouble(value));
                } catch (NumberFormatException e) {
                    cell.setCellValue(value);
                }
                break;
            case "Boolean":
                cell.setCellValue(Boolean.parseBoolean(value));
                break;
            case "DateTime":
                cell.setCellValue(value);
                break;
            default:
                cell.setCellValue(value);
        }
    }
    
    private void applyCellStyle(Element cellElement, Cell cell, Workbook workbook) {
        String styleId = cellElement.getAttributeNS(SS_NAMESPACE, "StyleID");
        if (!styleId.isEmpty()) {
            CellStyle style = getOrCreateStyle(styleId, workbook);
            cell.setCellStyle(style);
        }
    }
    
    private CellStyle getOrCreateStyle(String styleId, Workbook workbook) {
        // 這裏可以實現樣式緩存和創建邏輯
        // 實際應用中需要解析Office XML中的Style定義
        return styleCache.computeIfAbsent(styleId, id -> workbook.createCellStyle());
    }
    
    private int getMaxColumnCount(NodeList rowNodes) {
        int maxColumns = 0;
        for (int i = 0; i < rowNodes.getLength(); i++) {
            Element rowElement = (Element) rowNodes.item(i);
            maxColumns = Math.max(maxColumns, 
                rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell").getLength());
        }
        return maxColumns;
    }
    
    private void autoSizeColumns(Sheet sheet, int columnCount) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
        }
    }
    
    private void saveWorkbook(Workbook workbook, String filePath) throws Exception {
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }
}

含合併單元格

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OfficeXmlToXlsxWithMergeConverter {
    
    private static final String SS_NAMESPACE = "urn:schemas-microsoft-com:office:spreadsheet";
    private List<CellRangeAddress> mergeRegions = new ArrayList<>();
    
    public void convertWithMergedCells(String xmlFilePath, String xlsxFilePath) throws Exception {
        Document document = parseXmlDocument(xmlFilePath);
        Workbook workbook = new XSSFWorkbook();
        
        NodeList worksheetNodes = document.getElementsByTagNameNS(SS_NAMESPACE, "Worksheet");
        
        for (int sheetIndex = 0; sheetIndex < worksheetNodes.getLength(); sheetIndex++) {
            Element worksheet = (Element) worksheetNodes.item(sheetIndex);
            String sheetName = worksheet.getAttributeNS(SS_NAMESPACE, "Name");
            if (sheetName.isEmpty()) {
                sheetName = "Sheet" + (sheetIndex + 1);
            }
            
            Sheet sheet = workbook.createSheet(sheetName);
            mergeRegions.clear(); // 清空合併區域列表
            
            processWorksheet(worksheet, sheet, workbook);
            
            // 應用所有合併區域
            applyMergeRegions(sheet);
        }
        
        saveWorkbook(workbook, xlsxFilePath);
        System.out.println("轉換完成，包含合併單元格處理");
    }
    
    private Document parseXmlDocument(String xmlFilePath) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setNamespaceAware(true);
        return factory.newDocumentBuilder().parse(new File(xmlFilePath));
    }
    
    private void processWorksheet(Element worksheet, Sheet sheet, Workbook workbook) {
        Element table = (Element) worksheet.getElementsByTagNameNS(SS_NAMESPACE, "Table").item(0);
        if (table == null) return;
        
        NodeList rowNodes = table.getElementsByTagNameNS(SS_NAMESPACE, "Row");
        
        for (int rowIndex = 0; rowIndex < rowNodes.getLength(); rowIndex++) {
            Element rowElement = (Element) rowNodes.item(rowIndex);
            Row excelRow = sheet.createRow(rowIndex);
            
            processRow(rowElement, excelRow, workbook, rowIndex);
        }
        
        autoSizeColumns(sheet, getMaxColumnCount(rowNodes));
    }
    
    private void processRow(Element rowElement, Row excelRow, Workbook workbook, int rowIndex) {
        NodeList cellNodes = rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell");
        int colIndex = 0;
        
        for (int i = 0; i < cellNodes.getLength(); i++) {
            Element cellElement = (Element) cellNodes.item(i);
            
            // 處理索引屬性（如果有）
            String indexAttr = cellElement.getAttributeNS(SS_NAMESPACE, "Index");
            if (!indexAttr.isEmpty()) {
                colIndex = Integer.parseInt(indexAttr) - 1; // XML索引從1開始
            }
            
            // 確保單元格存在
            while (colIndex >= excelRow.getLastCellNum()) {
                excelRow.createCell(colIndex);
            }
            
            Cell excelCell = excelRow.getCell(colIndex);
            if (excelCell == null) {
                excelCell = excelRow.createCell(colIndex);
            }
            
            processCell(cellElement, excelCell, workbook, rowIndex, colIndex);
            
            // 處理合並單元格
            handleMergedCells(cellElement, rowIndex, colIndex);
            
            colIndex++;
        }
    }
    
    private void processCell(Element cellElement, Cell excelCell, Workbook workbook, int rowIndex, int colIndex) {
        Element dataElement = (Element) cellElement.getElementsByTagNameNS(SS_NAMESPACE, "Data").item(0);
        
        if (dataElement != null) {
            String value = dataElement.getTextContent();
            String dataType = dataElement.getAttributeNS(SS_NAMESPACE, "Type");
            setCellValue(excelCell, value, dataType);
        }
    }
    
    /**
     * 處理合並單元格邏輯
     */
    private void handleMergedCells(Element cellElement, int rowIndex, int colIndex) {
        // 處理水平合併（MergeAcross）
        String mergeAcrossStr = cellElement.getAttributeNS(SS_NAMESPACE, "MergeAcross");
        if (!mergeAcrossStr.isEmpty()) {
            int mergeAcross = Integer.parseInt(mergeAcrossStr);
            if (mergeAcross > 0) {
                CellRangeAddress mergeRegion = new CellRangeAddress(
                    rowIndex,                    // 起始行
                    rowIndex,                    // 結束行（同一行）
                    colIndex,                    // 起始列
                    colIndex + mergeAcross       // 結束列
                );
                mergeRegions.add(mergeRegion);
            }
        }
        
        // 處理垂直合併（MergeDown）
        String mergeDownStr = cellElement.getAttributeNS(SS_NAMESPACE, "MergeDown");
        if (!mergeDownStr.isEmpty()) {
            int mergeDown = Integer.parseInt(mergeDownStr);
            if (mergeDown > 0) {
                CellRangeAddress mergeRegion = new CellRangeAddress(
                    rowIndex,                    // 起始行
                    rowIndex + mergeDown,        // 結束行
                    colIndex,                    // 起始列
                    colIndex                     // 結束列（同一列）
                );
                mergeRegions.add(mergeRegion);
            }
        }
        
        // 處理同時有水平和垂直合併的情況
        if (!mergeAcrossStr.isEmpty() && !mergeDownStr.isEmpty()) {
            int mergeAcross = Integer.parseInt(mergeAcrossStr);
            int mergeDown = Integer.parseInt(mergeDownStr);
            
            if (mergeAcross > 0 && mergeDown > 0) {
                CellRangeAddress mergeRegion = new CellRangeAddress(
                    rowIndex,                    // 起始行
                    rowIndex + mergeDown,        // 結束行
                    colIndex,                    // 起始列
                    colIndex + mergeAcross       // 結束列
                );
                mergeRegions.add(mergeRegion);
            }
        }
    }
    
    /**
     * 應用所有合併區域到工作表
     */
    private void applyMergeRegions(Sheet sheet) {
        for (CellRangeAddress mergeRegion : mergeRegions) {
            // 檢查合併區域是否有效
            if (isValidMergeRegion(mergeRegion)) {
                sheet.addMergedRegion(mergeRegion);
                System.out.println("添加合併區域: " + mergeRegion.formatAsString());
            }
        }
    }
    
    /**
     * 驗證合併區域是否有效
     */
    private boolean isValidMergeRegion(CellRangeAddress mergeRegion) {
        return mergeRegion.getFirstRow() <= mergeRegion.getLastRow() &&
               mergeRegion.getFirstColumn() <= mergeRegion.getLastColumn() &&
               (mergeRegion.getFirstRow() != mergeRegion.getLastRow() ||
                mergeRegion.getFirstColumn() != mergeRegion.getLastColumn());
    }
    
    private void setCellValue(Cell cell, String value, String dataType) {
        if (dataType == null || dataType.isEmpty()) {
            dataType = "String";
        }
        
        switch (dataType) {
            case "Number":
                try {
                    cell.setCellValue(Double.parseDouble(value));
                } catch (NumberFormatException e) {
                    cell.setCellValue(value);
                }
                break;
            case "Boolean":
                cell.setCellValue(Boolean.parseBoolean(value));
                break;
            case "DateTime":
                cell.setCellValue(value);
                // 可以進一步處理日期格式
                break;
            default:
                cell.setCellValue(value);
        }
    }
    
    private int getMaxColumnCount(NodeList rowNodes) {
        int maxColumns = 0;
        for (int i = 0; i < rowNodes.getLength(); i++) {
            Element rowElement = (Element) rowNodes.item(i);
            int cellCount = calculateEffectiveColumns(rowElement);
            maxColumns = Math.max(maxColumns, cellCount);
        }
        return maxColumns;
    }
    
    /**
     * 計算有效的列數（考慮Index屬性和合並單元格）
     */
    private int calculateEffectiveColumns(Element rowElement) {
        NodeList cellNodes = rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell");
        int maxCol = 0;
        
        for (int i = 0; i < cellNodes.getLength(); i++) {
            Element cellElement = (Element) cellNodes.item(i);
            String indexAttr = cellElement.getAttributeNS(SS_NAMESPACE, "Index");
            
            int currentCol;
            if (!indexAttr.isEmpty()) {
                currentCol = Integer.parseInt(indexAttr);
            } else {
                currentCol = i + 1; // 默認從1開始
            }
            
            // 考慮合併單元格的跨度
            String mergeAcrossStr = cellElement.getAttributeNS(SS_NAMESPACE, "MergeAcross");
            if (!mergeAcrossStr.isEmpty()) {
                int mergeAcross = Integer.parseInt(mergeAcrossStr);
                currentCol += mergeAcross;
            }
            
            maxCol = Math.max(maxCol, currentCol);
        }
        
        return maxCol;
    }
    
    private void autoSizeColumns(Sheet sheet, int columnCount) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
        }
    }
    
    private void saveWorkbook(Workbook workbook, String filePath) throws Exception {
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }
    
    public static void main(String[] args) {
        OfficeXmlToXlsxWithMergeConverter converter = new OfficeXmlToXlsxWithMergeConverter();
        try {
            converter.convertWithMergedCells("input.xml", "output.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

包含樣式

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OfficeXmlToXlsxWithStyleConverter {
    
    private static final String SS_NAMESPACE = "urn:schemas-microsoft-com:office:spreadsheet";
    private static final String X_NAMESPACE = "urn:schemas-microsoft-com:office:excel";
    
    private List<CellRangeAddress> mergeRegions = new ArrayList<>();
    private Map<String, CellStyle> styleCache = new HashMap<>();
    private Map<String, Font> fontCache = new HashMap<>();
    private Map<String, Element> styleDefinitions = new HashMap<>();
    
    public void convertWithStyle(String xmlFilePath, String xlsxFilePath) throws Exception {
        Document document = parseXmlDocument(xmlFilePath);
        Workbook workbook = new XSSFWorkbook();
        
        // 首先解析所有樣式定義
        parseStyleDefinitions(document, workbook);
        
        NodeList worksheetNodes = document.getElementsByTagNameNS(SS_NAMESPACE, "Worksheet");
        
        for (int sheetIndex = 0; sheetIndex < worksheetNodes.getLength(); sheetIndex++) {
            Element worksheet = (Element) worksheetNodes.item(sheetIndex);
            String sheetName = worksheet.getAttributeNS(SS_NAMESPACE, "Name");
            if (sheetName.isEmpty()) {
                sheetName = "Sheet" + (sheetIndex + 1);
            }
            
            Sheet sheet = workbook.createSheet(sheetName);
            mergeRegions.clear();
            
            processWorksheet(worksheet, sheet, workbook);
            applyMergeRegions(sheet);
        }
        
        saveWorkbook(workbook, xlsxFilePath);
        System.out.println("轉換完成，包含樣式和合並單元格處理");
    }
    
    private Document parseXmlDocument(String xmlFilePath) throws Exception {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setNamespaceAware(true);
        return factory.newDocumentBuilder().parse(new File(xmlFilePath));
    }
    
    /**
     * 解析樣式定義
     */
    private void parseStyleDefinitions(Document document, Workbook workbook) {
        Element stylesElement = (Element) document.getElementsByTagNameNS(SS_NAMESPACE, "Styles").item(0);
        if (stylesElement == null) return;
        
        NodeList styleNodes = stylesElement.getElementsByTagNameNS(SS_NAMESPACE, "Style");
        for (int i = 0; i < styleNodes.getLength(); i++) {
            Element styleElement = (Element) styleNodes.item(i);
            String styleId = styleElement.getAttributeNS(SS_NAMESPACE, "ID");
            styleDefinitions.put(styleId, styleElement);
        }
    }
    
    private void processWorksheet(Element worksheet, Sheet sheet, Workbook workbook) {
        Element table = (Element) worksheet.getElementsByTagNameNS(SS_NAMESPACE, "Table").item(0);
        if (table == null) return;
        
        // 處理列寬
        processColumnWidths(table, sheet);
        
        NodeList rowNodes = table.getElementsByTagNameNS(SS_NAMESPACE, "Row");
        
        for (int rowIndex = 0; rowIndex < rowNodes.getLength(); rowIndex++) {
            Element rowElement = (Element) rowNodes.item(rowIndex);
            Row excelRow = sheet.createRow(rowIndex);
            
            // 處理行高
            processRowHeight(rowElement, excelRow);
            
            processRow(rowElement, excelRow, workbook, rowIndex);
        }
    }
    
    /**
     * 處理列寬
     */
    private void processColumnWidths(Element table, Sheet sheet) {
        NodeList columnNodes = table.getElementsByTagNameNS(SS_NAMESPACE, "Column");
        for (int i = 0; i < columnNodes.getLength(); i++) {
            Element columnElement = (Element) columnNodes.item(i);
            String widthStr = columnElement.getAttributeNS(SS_NAMESPACE, "Width");
            if (!widthStr.isEmpty()) {
                try {
                    double width = Double.parseDouble(widthStr);
                    // Office XML寬度單位與POI不同，需要轉換
                    int poiWidth = (int) (width * 256); // 近似轉換
                    sheet.setColumnWidth(i, poiWidth);
                } catch (NumberFormatException e) {
                    // 忽略格式錯誤
                }
            }
        }
    }
    
    /**
     * 處理行高
     */
    private void processRowHeight(Element rowElement, Row excelRow) {
        String heightStr = rowElement.getAttributeNS(SS_NAMESPACE, "Height");
        if (!heightStr.isEmpty()) {
            try {
                float height = Float.parseFloat(heightStr);
                excelRow.setHeightInPoints(height);
            } catch (NumberFormatException e) {
                // 忽略格式錯誤
            }
        }
    }
    
    private void processRow(Element rowElement, Row excelRow, Workbook workbook, int rowIndex) {
        NodeList cellNodes = rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell");
        int colIndex = 0;
        
        for (int i = 0; i < cellNodes.getLength(); i++) {
            Element cellElement = (Element) cellNodes.item(i);
            
            String indexAttr = cellElement.getAttributeNS(SS_NAMESPACE, "Index");
            if (!indexAttr.isEmpty()) {
                colIndex = Integer.parseInt(indexAttr) - 1;
            }
            
            while (colIndex >= excelRow.getLastCellNum()) {
                excelRow.createCell(colIndex);
            }
            
            Cell excelCell = excelRow.getCell(colIndex);
            if (excelCell == null) {
                excelCell = excelRow.createCell(colIndex);
            }
            
            processCell(cellElement, excelCell, workbook, rowIndex, colIndex);
            handleMergedCells(cellElement, rowIndex, colIndex);
            
            colIndex++;
        }
    }
    
    private void processCell(Element cellElement, Cell excelCell, Workbook workbook, int rowIndex, int colIndex) {
        Element dataElement = (Element) cellElement.getElementsByTagNameNS(SS_NAMESPACE, "Data").item(0);
        
        if (dataElement != null) {
            String value = dataElement.getTextContent();
            String dataType = dataElement.getAttributeNS(SS_NAMESPACE, "Type");
            setCellValue(excelCell, value, dataType, workbook);
        }
        
        // 應用單元格樣式
        applyCellStyle(cellElement, excelCell, workbook);
    }
    
    /**
     * 應用單元格樣式
     */
    private void applyCellStyle(Element cellElement, Cell cell, Workbook workbook) {
        String styleId = cellElement.getAttributeNS(SS_NAMESPACE, "StyleID");
        if (!styleId.isEmpty() && styleDefinitions.containsKey(styleId)) {
            CellStyle style = getOrCreateStyle(styleId, workbook);
            cell.setCellStyle(style);
        }
    }
    
    /**
     * 創建或獲取緩存的樣式
     */
    private CellStyle getOrCreateStyle(String styleId, Workbook workbook) {
        return styleCache.computeIfAbsent(styleId, id -> {
            Element styleElement = styleDefinitions.get(id);
            if (styleElement == null) {
                return workbook.createCellStyle();
            }
            return createCellStyleFromElement(styleElement, workbook);
        });
    }
    
    /**
     * 從XML元素創建單元格樣式
     */
    private CellStyle createCellStyleFromElement(Element styleElement, Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        
        // 處理對齊方式
        processAlignment(styleElement, style);
        
        // 處理邊框
        processBorders(styleElement, style, workbook);
        
        // 處理字體
        processFont(styleElement, style, workbook);
        
        // 處理背景顏色
        processBackground(styleElement, style);
        
        // 處理數字格式
        processNumberFormat(styleElement, style);
        
        return style;
    }
    
    /**
     * 處理對齊方式
     */
    private void processAlignment(Element styleElement, CellStyle style) {
        Element alignmentElement = (Element) styleElement.getElementsByTagNameNS(SS_NAMESPACE, "Alignment").item(0);
        if (alignmentElement != null) {
            String horizontal = alignmentElement.getAttributeNS(SS_NAMESPACE, "Horizontal");
            String vertical = alignmentElement.getAttributeNS(SS_NAMESPACE, "Vertical");
            String wrapText = alignmentElement.getAttributeNS(SS_NAMESPACE, "WrapText");
            
            // 水平對齊
            if ("Center".equals(horizontal)) {
                style.setAlignment(HorizontalAlignment.CENTER);
            } else if ("Right".equals(horizontal)) {
                style.setAlignment(HorizontalAlignment.RIGHT);
            } else if ("Left".equals(horizontal)) {
                style.setAlignment(HorizontalAlignment.LEFT);
            }
            
            // 垂直對齊
            if ("Center".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.CENTER);
            } else if ("Top".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.TOP);
            } else if ("Bottom".equals(vertical)) {
                style.setVerticalAlignment(VerticalAlignment.BOTTOM);
            }
            
            // 自動換行
            if ("1".equals(wrapText)) {
                style.setWrapText(true);
            }
        }
    }
    
    /**
     * 處理邊框
     */
    private void processBorders(Element styleElement, CellStyle style, Workbook workbook) {
        Element bordersElement = (Element) styleElement.getElementsByTagNameNS(SS_NAMESPACE, "Borders").item(0);
        if (bordersElement != null) {
            processBorder(bordersElement, "Left", style, workbook);
            processBorder(bordersElement, "Right", style, workbook);
            processBorder(bordersElement, "Top", style, workbook);
            processBorder(bordersElement, "Bottom", style, workbook);
        }
    }
    
    private void processBorder(Element bordersElement, String borderType, CellStyle style, Workbook workbook) {
        Element borderElement = (Element) bordersElement.getElementsByTagNameNS(SS_NAMESPACE, borderType).item(0);
        if (borderElement != null) {
            String lineStyle = borderElement.getAttributeNS(SS_NAMESPACE, "LineStyle");
            String weight = borderElement.getAttributeNS(SS_NAMESPACE, "Weight");
            String color = borderElement.getAttributeNS(SS_NAMESPACE, "Color");
            
            BorderStyle poiStyle = convertBorderStyle(lineStyle, weight);
            
            switch (borderType) {
                case "Left":
                    style.setBorderLeft(poiStyle);
                    break;
                case "Right":
                    style.setBorderRight(poiStyle);
                    break;
                case "Top":
                    style.setBorderTop(poiStyle);
                    break;
                case "Bottom":
                    style.setBorderBottom(poiStyle);
                    break;
            }
            
            if (!color.isEmpty()) {
                setBorderColor(borderType, style, color, workbook);
            }
        }
    }
    
    private BorderStyle convertBorderStyle(String lineStyle, String weight) {
        if ("Continuous".equals(lineStyle)) {
            if ("1".equals(weight)) return BorderStyle.THIN;
            if ("2".equals(weight)) return BorderStyle.MEDIUM;
            if ("3".equals(weight)) return BorderStyle.THICK;
            return BorderStyle.THIN;
        } else if ("Double".equals(lineStyle)) {
            return BorderStyle.DOUBLE;
        }
        return BorderStyle.NONE;
    }
    
    private void setBorderColor(String borderType, CellStyle style, String color, Workbook workbook) {
        // 簡化的顏色處理，實際需要更復雜的顏色轉換
        try {
            short rgb = convertColorToIndex(color);
            switch (borderType) {
                case "Left": style.setLeftBorderColor(rgb); break;
                case "Right": style.setRightBorderColor(rgb); break;
                case "Top": style.setTopBorderColor(rgb); break;
                case "Bottom": style.setBottomBorderColor(rgb); break;
            }
        } catch (Exception e) {
            // 顏色轉換失敗
        }
    }
    
    /**
     * 處理字體
     */
    private void processFont(Element styleElement, CellStyle style, Workbook workbook) {
        Element fontElement = (Element) styleElement.getElementsByTagNameNS(SS_NAMESPACE, "Font").item(0);
        if (fontElement != null) {
            Font font = createFontFromElement(fontElement, workbook);
            style.setFont(font);
        }
    }
    
    private Font createFontFromElement(Element fontElement, Workbook workbook) {
        String fontKey = getFontKey(fontElement);
        return fontCache.computeIfAbsent(fontKey, key -> {
            Font font = workbook.createFont();
            
            String fontName = fontElement.getAttributeNS(SS_NAMESPACE, "FontName");
            String fontSize = fontElement.getAttributeNS(SS_NAMESPACE, "Size");
            String bold = fontElement.getAttributeNS(SS_NAMESPACE, "Bold");
            String italic = fontElement.getAttributeNS(SS_NAMESPACE, "Italic");
            String color = fontElement.getAttributeNS(SS_NAMESPACE, "Color");
            
            if (!fontName.isEmpty()) font.setFontName(fontName);
            if (!fontSize.isEmpty()) font.setFontHeightInPoints(Float.parseFloat(fontSize));
            if ("1".equals(bold)) font.setBold(true);
            if ("1".equals(italic)) font.setItalic(true);
            
            if (!color.isEmpty()) {
                try {
                    short colorIndex = convertColorToIndex(color);
                    font.setColor(colorIndex);
                } catch (Exception e) {
                    // 顏色轉換失敗
                }
            }
            
            return font;
        });
    }
    
    private String getFontKey(Element fontElement) {
        return fontElement.getAttributeNS(SS_NAMESPACE, "FontName") + "|" +
               fontElement.getAttributeNS(SS_NAMESPACE, "Size") + "|" +
               fontElement.getAttributeNS(SS_NAMESPACE, "Bold") + "|" +
               fontElement.getAttributeNS(SS_NAMESPACE, "Italic") + "|" +
               fontElement.getAttributeNS(SS_NAMESPACE, "Color");
    }
    
    /**
     * 處理背景顏色
     */
    private void processBackground(Element styleElement, CellStyle style) {
        Element interiorElement = (Element) styleElement.getElementsByTagNameNS(SS_NAMESPACE, "Interior").item(0);
        if (interiorElement != null) {
            String color = interiorElement.getAttributeNS(SS_NAMESPACE, "Color");
            String pattern = interiorElement.getAttributeNS(SS_NAMESPACE, "Pattern");
            
            if (!color.isEmpty()) {
                try {
                    short colorIndex = convertColorToIndex(color);
                    style.setFillForegroundColor(colorIndex);
                    if ("Solid".equals(pattern)) {
                        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    }
                } catch (Exception e) {
                    // 顏色轉換失敗
                }
            }
        }
    }
    
    /**
     * 處理數字格式
     */
    private void processNumberFormat(Element styleElement, CellStyle style) {
        Element numberFormatElement = (Element) styleElement.getElementsByTagNameNS(SS_NAMESPACE, "NumberFormat").item(0);
        if (numberFormatElement != null) {
            String format = numberFormatElement.getAttributeNS(SS_NAMESPACE, "Format");
            if (!format.isEmpty()) {
                style.setDataFormat(style.getWorkbook().createDataFormat().getFormat(format));
            }
        }
    }
    
    /**
     * 簡化的顏色轉換（實際需要更復雜的處理）
     */
    private short convertColorToIndex(String color) {
        if (color.startsWith("#")) {
            // 處理十六進制顏色
            try {
                String hex = color.substring(1);
                if (hex.length() == 6) {
                    // 簡化的顏色映射
                    return IndexedColors.BLACK.getIndex(); // 實際需要更復雜的映射
                }
            } catch (Exception e) {
                // 忽略錯誤
            }
        }
        return IndexedColors.BLACK.getIndex();
    }
    
    private void setCellValue(Cell cell, String value, String dataType, Workbook workbook) {
        if (dataType == null || dataType.isEmpty()) {
            dataType = "String";
        }
        
        switch (dataType) {
            case "Number":
                try {
                    cell.setCellValue(Double.parseDouble(value));
                } catch (NumberFormatException e) {
                    cell.setCellValue(value);
                }
                break;
            case "Boolean":
                cell.setCellValue(Boolean.parseBoolean(value));
                break;
            case "DateTime":
                // 創建日期格式
                CellStyle dateStyle = workbook.createCellStyle();
                dateStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
                cell.setCellStyle(dateStyle);
                cell.setCellValue(value);
                break;
            default:
                cell.setCellValue(value);
        }
    }
    
    // 合併單元格處理（保持與之前相同）
    private void handleMergedCells(Element cellElement, int rowIndex, int colIndex) {
        String mergeAcrossStr = cellElement.getAttributeNS(SS_NAMESPACE, "MergeAcross");
        String mergeDownStr = cellElement.getAttributeNS(SS_NAMESPACE, "MergeDown");
        
        if (!mergeAcrossStr.isEmpty() || !mergeDownStr.isEmpty()) {
            int mergeAcross = mergeAcrossStr.isEmpty() ? 0 : Integer.parseInt(mergeAcrossStr);
            int mergeDown = mergeDownStr.isEmpty() ? 0 : Integer.parseInt(mergeDownStr);
            
            if (mergeAcross > 0 || mergeDown > 0) {
                CellRangeAddress mergeRegion = new CellRangeAddress(
                    rowIndex,
                    rowIndex + mergeDown,
                    colIndex,
                    colIndex + mergeAcross
                );
                if (isValidMergeRegion(mergeRegion)) {
                    mergeRegions.add(mergeRegion);
                }
            }
        }
    }
    
    private void applyMergeRegions(Sheet sheet) {
        for (CellRangeAddress mergeRegion : mergeRegions) {
            sheet.addMergedRegion(mergeRegion);
        }
    }
    
    private boolean isValidMergeRegion(CellRangeAddress mergeRegion) {
        return mergeRegion.getFirstRow() <= mergeRegion.getLastRow() &&
               mergeRegion.getFirstColumn() <= mergeRegion.getLastColumn();
    }
    
    private int getMaxColumnCount(NodeList rowNodes) {
        int maxColumns = 0;
        for (int i = 0; i < rowNodes.getLength(); i++) {
            Element rowElement = (Element) rowNodes.item(i);
            maxColumns = Math.max(maxColumns, calculateEffectiveColumns(rowElement));
        }
        return maxColumns;
    }
    
    private int calculateEffectiveColumns(Element rowElement) {
        NodeList cellNodes = rowElement.getElementsByTagNameNS(SS_NAMESPACE, "Cell");
        int maxCol = 0;
        
        for (int i = 0; i < cellNodes.getLength(); i++) {
            Element cellElement = (Element) cellNodes.item(i);
            int currentCol = getCellColumnIndex(cellElement, i);
            
            String mergeAcrossStr = cellElement.getAttributeNS(SS_NAMESPACE, "MergeAcross");
            if (!mergeAcrossStr.isEmpty()) {
                currentCol += Integer.parseInt(mergeAcrossStr);
            }
            
            maxCol = Math.max(maxCol, currentCol);
        }
        
        return maxCol;
    }
    
    private int getCellColumnIndex(Element cellElement, int defaultIndex) {
        String indexAttr = cellElement.getAttributeNS(SS_NAMESPACE, "Index");
        return indexAttr.isEmpty() ? defaultIndex + 1 : Integer.parseInt(indexAttr);
    }
    
    private void autoSizeColumns(Sheet sheet, int columnCount) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
        }
    }
    
    private void saveWorkbook(Workbook workbook, String filePath) throws Exception {
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }
    
    public static void main(String[] args) {
        OfficeXmlToXlsxWithStyleConverter converter = new OfficeXmlToXlsxWithStyleConverter();
        try {
            converter.convertWithStyle("input.xml", "output.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

顏色工具

import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.regex.Matcher;

/**
 * 增強的顏色轉換工具類
 * 支持Office XML中可能出現的各種顏色格式
 */
public class EnhancedColorConverter {
    
    private static final Map<String, XSSFColor> COLOR_CACHE = new HashMap<>();
    
    // Office XML顏色格式模式
    private static final Pattern OFFICE_COLOR_PATTERN = 
        Pattern.compile("^#?([0-9A-Fa-f]{6}|[0-9A-Fa-f]{3})$");
    
    private static final Pattern INDEXED_COLOR_PATTERN = 
        Pattern.compile("^(\\d+)$");
    
    private static final Pattern THEME_COLOR_PATTERN = 
        Pattern.compile("^Theme\\((\\d+)\\)$", Pattern.CASE_INSENSITIVE);
    
    private static final Pattern TINTED_COLOR_PATTERN = 
        Pattern.compile("^Tint\\((\\d+\\.?\\d*)\\)$", Pattern.CASE_INSENSITIVE);
    
    // 常見Office顏色名稱映射
    private static final Map<String, String> OFFICE_COLOR_MAP = new HashMap<>();
    static {
        OFFICE_COLOR_MAP.put("Automatic", "#000000");
        OFFICE_COLOR_MAP.put("Black", "#000000");
        OFFICE_COLOR_MAP.put("White", "#FFFFFF");
        OFFICE_COLOR_MAP.put("Red", "#FF0000");
        OFFICE_COLOR_MAP.put("Green", "#00FF00");
        OFFICE_COLOR_MAP.put("Blue", "#0000FF");
        OFFICE_COLOR_MAP.put("Yellow", "#FFFF00");
        OFFICE_COLOR_MAP.put("Magenta", "#FF00FF");
        OFFICE_COLOR_MAP.put("Cyan", "#00FFFF");
        OFFICE_COLOR_MAP.put("DarkRed", "#800000");
        OFFICE_COLOR_MAP.put("DarkGreen", "#008000");
        OFFICE_COLOR_MAP.put("DarkBlue", "#000080");
        OFFICE_COLOR_MAP.put("Olive", "#808000");
        OFFICE_COLOR_MAP.put("Purple", "#800080");
        OFFICE_COLOR_MAP.put("Teal", "#008080");
        OFFICE_COLOR_MAP.put("Silver", "#C0C0C0");
        OFFICE_COLOR_MAP.put("Gray", "#808080");
        OFFICE_COLOR_MAP.put("Grey", "#808080");
        OFFICE_COLOR_MAP.put("Maroon", "#800000");
        OFFICE_COLOR_MAP.put("Navy", "#000080");
        OFFICE_COLOR_MAP.put("Aqua", "#00FFFF");
        OFFICE_COLOR_MAP.put("Lime", "#00FF00");
        OFFICE_COLOR_MAP.put("Fuchsia", "#FF00FF");
    }
    
    /**
     * 轉換Office XML顏色字符串為XSSFColor
     */
    public static XSSFColor convertOfficeColor(String colorStr, XSSFWorkbook workbook) {
        if (colorStr == null || colorStr.trim().isEmpty()) {
            return null;
        }
        
        colorStr = colorStr.trim();
        
        // 檢查緩存
        if (COLOR_CACHE.containsKey(colorStr)) {
            return COLOR_CACHE.get(colorStr);
        }
        
        XSSFColor color = null;
        
        // 1. 處理十六進制顏色
        if (isHexColor(colorStr)) {
            color = convertHexColor(colorStr);
        }
        // 2. 處理索引顏色
        else if (isIndexedColor(colorStr)) {
            color = convertIndexedColor(colorStr, workbook);
        }
        // 3. 處理主題顏色
        else if (isThemeColor(colorStr)) {
            color = convertThemeColor(colorStr, workbook);
        }
        // 4. 處理命名顏色
        else if (isNamedColor(colorStr)) {
            color = convertNamedColor(colorStr);
        }
        // 5. 處理帶色調的顏色
        else if (isTintedColor(colorStr)) {
            color = convertTintedColor(colorStr, workbook);
        }
        
        // 緩存結果
        if (color != null) {
            COLOR_CACHE.put(colorStr, color);
        }
        
        return color;
    }
    
    private static XSSFColor convertHexColor(String hexColor) {
        try {
            String cleanHex = hexColor.startsWith("#") ? hexColor.substring(1) : hexColor;
            
            if (cleanHex.length() == 3) {
                cleanHex = String.format("%c%c%c%c%c%c",
                    cleanHex.charAt(0), cleanHex.charAt(0),
                    cleanHex.charAt(1), cleanHex.charAt(1),
                    cleanHex.charAt(2), cleanHex.charAt(2));
            }
            
            if (cleanHex.length() == 6) {
                int r = Integer.parseInt(cleanHex.substring(0, 2), 16);
                int g = Integer.parseInt(cleanHex.substring(2, 4), 16);
                int b = Integer.parseInt(cleanHex.substring(4, 6), 16);
                
                byte[] rgb = new byte[]{(byte) r, (byte) g, (byte) b};
                return new XSSFColor(rgb, null);
            }
        } catch (Exception e) {
            // 忽略轉換錯誤
        }
        return null;
    }
    
    private static XSSFColor convertIndexedColor(String colorStr, XSSFWorkbook workbook) {
        try {
            int index = Integer.parseInt(colorStr);
            if (index >= 0 && index < 64) { // IndexedColors範圍
                return new XSSFColor(IndexedColors.fromInt(index).getIndexed(), null);
            }
        } catch (Exception e) {
            // 忽略轉換錯誤
        }
        return null;
    }
    
    private static XSSFColor convertThemeColor(String colorStr, XSSFWorkbook workbook) {
        // 簡化處理：將主題顏色映射為相近的固定顏色
        try {
            Matcher matcher = THEME_COLOR_PATTERN.matcher(colorStr);
            if (matcher.matches()) {
                int themeIndex = Integer.parseInt(matcher.group(1));
                // 主題顏色映射表
                String[] themeColors = {
                    "#000000", "#FFFFFF", "#FF0000", "#00FF00", "#0000FF",
                    "#FFFF00", "#FF00FF", "#00FFFF", "#800000", "#008000"
                };
                if (themeIndex >= 0 && themeIndex < themeColors.length) {
                    return convertHexColor(themeColors[themeIndex]);
                }
            }
        } catch (Exception e) {
            // 忽略轉換錯誤
        }
        return null;
    }
    
    private static XSSFColor convertNamedColor(String colorStr) {
        String hexColor = OFFICE_COLOR_MAP.get(colorStr);
        if (hexColor != null) {
            return convertHexColor(hexColor);
        }
        return null;
    }
    
    private static XSSFColor convertTintedColor(String colorStr, XSSFWorkbook workbook) {
        // 簡化處理：忽略色調，返回基礎顏色
        return convertHexColor("#000000"); // 默認返回黑色
    }
    
    private static boolean isHexColor(String colorStr) {
        return OFFICE_COLOR_PATTERN.matcher(colorStr).matches();
    }
    
    private static boolean isIndexedColor(String colorStr) {
        return INDEXED_COLOR_PATTERN.matcher(colorStr).matches();
    }
    
    private static boolean isThemeColor(String colorStr) {
        return THEME_COLOR_PATTERN.matcher(colorStr).matches();
    }
    
    private static boolean isNamedColor(String colorStr) {
        return OFFICE_COLOR_MAP.containsKey(colorStr);
    }
    
    private static boolean isTintedColor(String colorStr) {
        return TINTED_COLOR_PATTERN.matcher(colorStr).matches();
    }
    
    public static void clearCache() {
        COLOR_CACHE.clear();
    }
}

解析Border

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import java.util.HashMap;
import java.util.Map;

/**
 * 邊框樣式解析器
 */
public class BorderStyleParser {
    
    private static final String SS_NAMESPACE = "urn:schemas-microsoft-com:office:spreadsheet";
    
    // 邊框線型映射表
    private static final Map<String, BorderStyle> LINE_STYLE_MAP = new HashMap<>();
    private static final Map<String, Short> BORDER_WEIGHT_MAP = new HashMap<>();
    
    static {
        // 初始化線型映射
        LINE_STYLE_MAP.put("Continuous", BorderStyle.THIN);
        LINE_STYLE_MAP.put("Dot", BorderStyle.DOTTED);
        LINE_STYLE_MAP.put("Dash", BorderStyle.DASHED);
        LINE_STYLE_MAP.put("DashDot", BorderStyle.DASH_DOT);
        LINE_STYLE_MAP.put("DashDotDot", BorderStyle.DASH_DOT_DOT);
        LINE_STYLE_MAP.put("Double", BorderStyle.DOUBLE);
        LINE_STYLE_MAP.put("SlantDashDot", BorderStyle.DASH_DOT);
        LINE_STYLE_MAP.put("None", BorderStyle.NONE);
        
        // 邊框權重映射（Office XML權重到POI樣式的映射）
        BORDER_WEIGHT_MAP.put("0", (short) 0);
        BORDER_WEIGHT_MAP.put("1", (short) 1);  // 細線
        BORDER_WEIGHT_MAP.put("2", (short) 2);  // 中等
        BORDER_WEIGHT_MAP.put("3", (short) 3);  // 粗線
    }
    
    /**
     * 解析並應用邊框樣式
     */
    public static void processBorders(Element styleElement, CellStyle cellStyle, XSSFWorkbook workbook) {
        Element bordersElement = (Element) styleElement.getElementsByTagNameNS(SS_NAMESPACE, "Borders").item(0);
        if (bordersElement == null) {
            return;
        }
        
        // 解析各個方向的邊框
        processBorderSide(bordersElement, "Left", cellStyle, workbook);
        processBorderSide(bordersElement, "Right", cellStyle, workbook);
        processBorderSide(bordersElement, "Top", cellStyle, workbook);
        processBorderSide(bordersElement, "Bottom", cellStyle, workbook);
        
        // 處理對角線邊框（如果有）
        processDiagonalBorders(bordersElement, cellStyle, workbook);
    }
    
    /**
     * 解析單個方向的邊框
     */
    private static void processBorderSide(Element bordersElement, String side, CellStyle cellStyle, XSSFWorkbook workbook) {
        Element borderElement = (Element) bordersElement.getElementsByTagNameNS(SS_NAMESPACE, side).item(0);
        if (borderElement != null) {
            BorderStyle borderStyle = getBorderStyle(borderElement);
            XSSFColor borderColor = getBorderColor(borderElement, workbook);
            
            // 設置邊框樣式
            setBorderStyle(cellStyle, side, borderStyle);
            
            // 設置邊框顏色
            if (borderColor != null) {
                setBorderColor(cellStyle, side, borderColor);
            }
        }
    }
    
    /**
     * 處理對角線邊框
     */
    private static void processDiagonalBorders(Element bordersElement, CellStyle cellStyle, XSSFWorkbook workbook) {
        Element diagonalElement = (Element) bordersElement.getElementsByTagNameNS(SS_NAMESPACE, "Diagonal").item(0);
        if (diagonalElement != null) {
            // 獲取對角線類型
            String diagonalType = diagonalElement.getAttributeNS(SS_NAMESPACE, "LineStyle");
            BorderStyle diagonalStyle = convertLineStyle(diagonalType);
            
            // 獲取對角線顏色
            String colorStr = diagonalElement.getAttributeNS(SS_NAMESPACE, "Color");
            XSSFColor diagonalColor = ColorConverter.convertToXSSFColor(colorStr, workbook);
            
            // 設置對角線（Excel對對角線的支持有限，這裏簡化處理）
            if (diagonalStyle != BorderStyle.NONE) {
                // 可以根據需要設置對角線樣式
            }
        }
    }
    
    /**
     * 獲取邊框樣式
     */
    private static BorderStyle getBorderStyle(Element borderElement) {
        String lineStyle = borderElement.getAttributeNS(SS_NAMESPACE, "LineStyle");
        String weightStr = borderElement.getAttributeNS(SS_NAMESPACE, "Weight");
        
        BorderStyle baseStyle = convertLineStyle(lineStyle);
        
        // 根據權重調整樣式
        if (weightStr != null && !weightStr.isEmpty()) {
            return adjustBorderStyleByWeight(baseStyle, weightStr);
        }
        
        return baseStyle;
    }
    
    /**
     * 轉換線型
     */
    private static BorderStyle convertLineStyle(String lineStyle) {
        if (lineStyle == null || lineStyle.isEmpty()) {
            return BorderStyle.NONE;
        }
        
        BorderStyle style = LINE_STYLE_MAP.get(lineStyle);
        return style != null ? style : BorderStyle.THIN;
    }
    
    /**
     * 根據權重調整邊框樣式
     */
    private static BorderStyle adjustBorderStyleByWeight(BorderStyle baseStyle, String weightStr) {
        if (baseStyle == BorderStyle.NONE) {
            return BorderStyle.NONE;
        }
        
        try {
            int weight = Integer.parseInt(weightStr);
            
            switch (baseStyle) {
                case THIN:
                    if (weight >= 2) return BorderStyle.MEDIUM;
                    if (weight >= 3) return BorderStyle.THICK;
                    break;
                case MEDIUM:
                    if (weight >= 3) return BorderStyle.THICK;
                    if (weight == 1) return BorderStyle.THIN;
                    break;
                case DASHED:
                    if (weight >= 2) return BorderStyle.MEDIUM_DASHED;
                    if (weight >= 3) return BorderStyle.THICK;
                    break;
                case DOTTED:
                    if (weight >= 2) return BorderStyle.MEDIUM_DASHED; // 近似處理
                    break;
            }
        } catch (NumberFormatException e) {
            // 忽略格式錯誤
        }
        
        return baseStyle;
    }
    
    /**
     * 獲取邊框顏色
     */
    private static XSSFColor getBorderColor(Element borderElement, XSSFWorkbook workbook) {
        String colorStr = borderElement.getAttributeNS(SS_NAMESPACE, "Color");
        if (colorStr != null && !colorStr.isEmpty()) {
            return ColorConverter.convertToXSSFColor(colorStr, workbook);
        }
        return null;
    }
    
    /**
     * 設置邊框樣式
     */
    private static void setBorderStyle(CellStyle cellStyle, String side, BorderStyle borderStyle) {
        switch (side) {
            case "Left":
                cellStyle.setBorderLeft(borderStyle);
                break;
            case "Right":
                cellStyle.setBorderRight(borderStyle);
                break;
            case "Top":
                cellStyle.setBorderTop(borderStyle);
                break;
            case "Bottom":
                cellStyle.setBorderBottom(borderStyle);
                break;
        }
    }
    
    /**
     * 設置邊框顏色
     */
    private static void setBorderColor(CellStyle cellStyle, String side, XSSFColor color) {
        if (color == null) return;
        
        switch (side) {
            case "Left":
                cellStyle.setLeftBorderColor(color);
                break;
            case "Right":
                cellStyle.setRightBorderColor(color);
                break;
            case "Top":
                cellStyle.setTopBorderColor(color);
                break;
            case "Bottom":
                cellStyle.setBottomBorderColor(color);
                break;
        }
    }
    
    /**
     * 檢查是否有任何邊框設置
     */
    public static boolean hasBorders(Element styleElement) {
        Element bordersElement = (Element) styleElement.getElementsByTagNameNS(SS_NAMESPACE, "Borders").item(0);
        if (bordersElement == null) {
            return false;
        }
        
        // 檢查各個方向是否有邊框設置
        return hasBorderSide(bordersElement, "Left") ||
               hasBorderSide(bordersElement, "Right") ||
               hasBorderSide(bordersElement, "Top") ||
               hasBorderSide(bordersElement, "Bottom") ||
               hasBorderSide(bordersElement, "Diagonal");
    }
    
    /**
     * 檢查特定方向是否有邊框
     */
    private static boolean hasBorderSide(Element bordersElement, String side) {
        Element borderElement = (Element) bordersElement.getElementsByTagNameNS(SS_NAMESPACE, side).item(0);
        if (borderElement == null) {
            return false;
        }
        
        String lineStyle = borderElement.getAttributeNS(SS_NAMESPACE, "LineStyle");
        return lineStyle != null && !lineStyle.isEmpty() && !"None".equals(lineStyle);
    }
    
    /**
     * 獲取邊框的詳細配置信息（用於調試）
     */
    public static Map<String, String> getBorderDetails(Element borderElement) {
        Map<String, String> details = new HashMap<>();
        
        if (borderElement != null) {
            details.put("LineStyle", borderElement.getAttributeNS(SS_NAMESPACE, "LineStyle"));
            details.put("Weight", borderElement.getAttributeNS(SS_NAMESPACE, "Weight"));
            details.put("Color", borderElement.getAttributeNS(SS_NAMESPACE, "Color"));
        }
        
        return details;
    }
    
    /**
     * 從單元格元素直接解析邊框（如果單元格有獨立的邊框設置）
     */
    public static void processCellBorders(Element cellElement, CellStyle cellStyle, XSSFWorkbook workbook) {
        // 檢查單元格是否有獨立的邊框設置
        Element borderElement = (Element) cellElement.getElementsByTagNameNS(SS_NAMESPACE, "Borders").item(0);
        if (borderElement != null) {
            processBorders(borderElement, cellStyle, workbook);
        }
    }
}

chadLi 博客

chadLi 博客

博客 / 詳情

office xml轉xlsx

主要代碼

增強版

含合併單元格

包含樣式

顏色工具

解析Border

發佈評論

Product

Company

Support

Company

博客 / 詳情

office xml轉xlsx

主要代碼

增強版

含合併單元格

包含樣式

顏色工具

解析Border

發佈 評論

發佈評論