• Office Xml 2003转XLSX

    一、使用到的依赖包

    1、xelem-3.1.jar 下载地址：管网下载地址

    2、poi-3.17.jar 下载地址：https://mvnrepository.com/artifact/org.apache.poi/poi

    二、实现方法

    1、Xml2003公式转XLSX公式算法

    （1）Xml2003函数格式

    SUM(R[-1]C+R[-7]C[-4])

    R代表当前行，C代表当前列，中括号中的数字代表相对于当前行的偏移量。比如当前操作的是第10行第三列6列，那么上面公式的含义就是第6列9行与第2列3行相加。转换成XLSX的公式就是

    SUM(F9-B3)

    （2）代码实现

    1.     private final static String[] ExcelColIndexes = new String[]{
    2.             "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
    3.             "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
    4.             "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM",
    5.             "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ",
    6.             "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM",
    7.             "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ"
    8.     };
    1. private static String convertFormula(String xmlFormula, int rowIndex, int colIndex) {
    2.         if (xmlFormula.equals("=SUM(RC[-2]-RC[-5])")) {
    3.             System.out.println();
    4.         }
    5.         StringBuilder formula = new StringBuilder(xmlFormula.replace("=", ""));
    6.         Pattern flaPattern = Pattern.compile("R(\\[[-]?\\d+\\])?C(\\[[-]?\\d+\\])?");
    7.         Pattern rowPattern = Pattern.compile("R(\\[[-]?\\d+\\])?");
    8.         Pattern colPattern = Pattern.compile("C(\\[[-]?\\d+\\])?");
    9.         Pattern numberPattern = Pattern.compile("[-]?\\d+");
    10.         Matcher flaMatcher = flaPattern.matcher(xmlFormula);
    11.         while (flaMatcher.find()) {
    12.             String subFla = "";
    13.             String content = flaMatcher.group();
    14.             Matcher colMatcher = colPattern.matcher(content);
    15.             if (colMatcher.find()) {
    16.                 String colFla = colMatcher.group();
    17.                 Matcher numberMatcher = numberPattern.matcher(colFla);
    18.                 if (numberMatcher.find()) {
    19.                     int num = Integer.parseInt(numberMatcher.group());
    20.                     subFla = ExcelColIndexes[colIndex + num];
    21.                 } else {
    22.                     subFla = ExcelColIndexes[colIndex];
    23.                 }
    24.             }
    25.             Matcher rowMatcher = rowPattern.matcher(content);
    26.             if (rowMatcher.find()) {
    27.                 String rowFla = rowMatcher.group();
    28.                 Matcher numberMatcher = numberPattern.matcher(rowFla);
    29.                 if (numberMatcher.find()) {
    30.                     int num = Integer.parseInt(numberMatcher.group());
    31.                     subFla += rowIndex + 1 + num;
    32.                 } else {
    33.                     subFla += rowIndex + 1;
    34.                 }
    35.             }
    36.             int start = formula.indexOf(content);
    37.             int end = start + content.length();
    38.             formula.replace(start, end, subFla);
    39.         }
    40.         return formula.toString();
    41.     }

    2、XML2003十六进制颜色向POI颜色转换

    1. private static byte[] convertColorHexToByteArray(String colorStr) {
    2.         colorStr = colorStr.replace("#", "");
    3.         if (colorStr.length() != 6 && colorStr.length() != 8) {
    4.             throw new IllegalArgumentException("Must be of the form 112233 or FFEEDDCC");
    5.         } else {
    6.             byte[] rgb = new byte[colorStr.length() / 2];
    7.  
    8.             for (int i = 0; i < rgb.length; ++i) {
    9.                 String part = colorStr.substring(i * 2, (i + 1) * 2);
    10.                 rgb[i] = (byte) Integer.parseInt(part, 16);
    11.             }
    12.             return rgb;
    13.         }
    14.     }

    3、Xml2003转换XLSX代码

    1.  List mergedRanges = new ArrayList<>();
    2.         Map cellStyleMap = new HashMap<>();
    3.         File xmlFile = new File("C:\\Users\\Daibz\\Desktop\\test2.xls");
    4.         File outFile = new File("C:\\Users\\Daibz\\Desktop\\test.xlsx");
    5.         ExcelReader reader = new ExcelReader();
    6.         Workbook workbook = reader.getWorkbook(new InputSource(new FileInputStream(xmlFile)));
    7.         XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
    8.         DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    9.         DocumentBuilder builder = factory.newDocumentBuilder();
    10.         Document doc = builder.parse(xmlFile);
    11.         NodeList styleList = doc.getElementsByTagName("Style");
    12.         for (int i = 0; i < styleList.getLength(); i++) {
    13.             XSSFCellStyle style = xssfWorkbook.createCellStyle();
    14.             Node node = styleList.item(i);
    15.             NodeList childNodes = node.getChildNodes();
    16.             for (int i1 = 0; i1 < childNodes.getLength(); i1++) {
    17.                 Node childNode = childNodes.item(i1);
    18.                 String name = childNode.getNodeName();
    19.                 switch (name) {
    20.                     case "Alignment": {
    21.                         NamedNodeMap attributes = childNode.getAttributes();
    22.                         for (int i2 = 0; i2 < attributes.getLength(); i2++) {
    23.                             Node attr = attributes.item(i2);
    24.                             String value = attr.getNodeValue();
    25.                             switch (attr.getNodeName()) {
    26.                                 case "ss:Horizontal": {
    27.                                     style.setAlignment(HorizontalAlignment.valueOf(value.toUpperCase()));
    28.                                 }
    29.                                 break;
    30.                                 case "ss:Vertical": {
    31.                                     style.setVerticalAlignment(VerticalAlignment.valueOf(value.toUpperCase()));
    32.                                 }
    33.                                 break;
    34.                                 case "ss:WrapText": {
    35.                                     style.setWrapText("1".equals(value));
    36.                                 }
    37.                                 break;
    38.                             }
    39.                         }
    40.                     }
    41.                     break;
    42.                     case "Borders": {
    43.                         NodeList borders = childNode.getChildNodes();
    44.                         for (int i2 = 0; i2 < borders.getLength(); i2++) {
    45.                             Node border = borders.item(i2);
    46.                             NamedNodeMap attributes = border.getAttributes();
    47.                             if (attributes == null)
    48.                                 continue;
    49.                             for (int i3 = 0; i3 < attributes.getLength(); i3++) {
    50.                                 Node attr = attributes.item(i3);
    51.                                 String value = attr.getNodeValue();
    52.                                 switch (attr.getNodeName()) {
    53.                                     case "ss:Position": {
    54.                                         switch (value) {
    55.                                             case "Bottom":
    56.                                                 style.setBorderBottom(BorderStyle.THIN);
    57.                                                 break;
    58.                                             case "Left":
    59.                                                 style.setBorderLeft(BorderStyle.THIN);
    60.                                                 break;
    61.                                             case "Right":
    62.                                                 style.setBorderRight(BorderStyle.THIN);
    63.                                                 break;
    64.                                             case "Top":
    65.                                                 style.setBorderTop(BorderStyle.THIN);
    66.                                                 break;
    67.                                         }
    68.                                     }
    69.                                     break;
    70.                                     case "ss:LineStyle": {
    71.                                     }
    72.                                     break;
    73.                                     case "ss:Weight": {
    74.                                     }
    75.                                     break;
    76.                                 }
    77.                             }
    78.                         }
    79.                     }
    80.                     break;
    81.                     case "Font": {
    82.                         Font font = xssfWorkbook.createFont();
    83.                         NamedNodeMap attributes = childNode.getAttributes();
    84.                         for (int i2 = 0; i2 < attributes.getLength(); i2++) {
    85.                             Node attr = attributes.item(i2);
    86.                             String value = attr.getNodeValue();
    87.                             switch (attr.getNodeName()) {
    88.                                 case "ss:FontName": {
    89.                                     font.setFontName(value);
    90.                                 }
    91.                                 break;
    92.                                 case "ss:CharSet": {
    93.                                     font.setCharSet(Integer.parseInt(value));
    94.                                 }
    95.                                 break;
    96.                                 case "ss:Size": {
    97.                                     font.setFontHeightInPoints(Short.parseShort(value));
    98.                                 }
    99.                                 break;
    100.                                 case "ss:Bold": {
    101.                                     font.setBold("1".equals(value));
    102.                                 }
    103.                                 break;
    104.                             }
    105.                         }
    106.                         style.setFont(font);
    107.                     }
    108.                     break;
    109.                     case "Interior": {
    110.                         NamedNodeMap attributes = childNode.getAttributes();
    111.                         for (int i2 = 0; i2 < attributes.getLength(); i2++) {
    112.                             Node attr = attributes.item(i2);
    113.                             String value = attr.getNodeValue();
    114.                             switch (attr.getNodeName()) {
    115.                                 case "ss:Color": {
    116.                                     XSSFColor color = new XSSFColor(convertColorHexToByteArray(value),
    117.                                             xssfWorkbook.getStylesSource().getIndexedColors());
    118.                                     style.setFillForegroundColor(color);
    119.                                 }
    120.                                 break;
    121.                                 case "ss:Pattern": {
    122.                                     switch (value) {
    123.                                         case "Solid":
    124.                                             style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    125.                                             break;
    126.                                     }
    127.                                 }
    128.                                 break;
    129.                             }
    130.                         }
    131.                     }
    132.                     break;
    133.                 }
    134.             }
    135.             String id = node.getAttributes().getNamedItem("ss:ID").getNodeValue();
    136.             cellStyleMap.put(id, style);
    137.         }
    138.         for (String sheetName : workbook.getSheetNames()) {
    139.             Worksheet sheet = workbook.getWorksheet(sheetName);
    140.             XSSFSheet xssfSheet = xssfWorkbook.createSheet(sheetName);
    141.             int rowIndex = 0;
    142.             for (Row row : sheet.getRows()) {
    143.                 XSSFRow xssfRow = xssfSheet.createRow(row.getIndex() - 1);
    144.                 int colIndex = 0;
    145.                 int preColIndex = 0;
    146.                 for (Cell cell : row.getCells()) {
    147.                     int mergeAcross = cell.getMergeAcross();
    148.                     int mergeDown = cell.getMergeDown();
    149.                     if (cell.getIndex() - preColIndex != 1) {
    150.                         colIndex += cell.getIndex() - preColIndex - 1;
    151.                     }
    152.                     XSSFCell xssfCell = xssfRow.createCell(colIndex);
    153.                     CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex + mergeDown,
    154.                             colIndex, colIndex + mergeAcross);
    155.                     CellStyle cellStyle = cellStyleMap.get(cell.getStyleID());
    156.                     xssfCell.setCellStyle(cellStyle);
    157.                     if (mergeDown != 0 || mergeAcross != 0) {
    158.                         boolean isMerged = false;
    159.                         for (CellRangeAddress mergedRange : mergedRanges) {
    160.                             if (colIndex >= mergedRange.getFirstColumn()
    161.                                     && colIndex <= mergedRange.getLastColumn()
    162.                                     && rowIndex >= mergedRange.getFirstRow()
    163.                                     && rowIndex <= mergedRange.getLastRow()) {
    164.                                 colIndex += mergeAcross + 1;
    165.                                 isMerged = true;
    166.                                 break;
    167.                             }
    168.                         }
    169.                         if (isMerged) {
    170.                             continue;
    171.                         }
    172.                         mergedRanges.add(range);
    173.                         xssfSheet.addMergedRegion(range);
    174.                     }
    175.                     if (cell.getFormula() != null) {
    176.                         xssfCell.setCellFormula(convertFormula(cell.getFormula(), rowIndex, colIndex));
    177.                     } else {
    178.                         Object data = cell.getData();
    179.                         if (data instanceof String) {
    180.                             xssfCell.setCellValue((String) data);
    181.                         } else if (data instanceof Double) {
    182.                             xssfCell.setCellValue((Double) data);
    183.                         } else if (data instanceof Integer) {
    184.                             xssfCell.setCellValue((Integer) data);
    185.                         } else if (data instanceof Short) {
    186.                             xssfCell.setCellValue((Short) data);
    187.                         } else if (data instanceof Float) {
    188.                             xssfCell.setCellValue((Float) data);
    189.                         } else if (data instanceof Date) {
    190.                             xssfCell.setCellValue((Date) data);
    191.                         } else if (data instanceof Calendar) {
    192.                             xssfCell.setCellValue((Calendar) data);
    193.                         } else if (data instanceof RichTextString) {
    194.                             xssfCell.setCellValue((RichTextString) data);
    195.                         } else {
    196.                             xssfCell.setCellValue(cell.getData$());
    197.                         }
    198.                     }
    199.                     preColIndex = cell.getIndex();
    200.                     xssfSheet.autoSizeColumn(colIndex);
    201.                     colIndex += mergeAcross + 1;
    202.                 }
    203.                 rowIndex++;
    204.             }
    205.             for (CellRangeAddress range : mergedRanges) {
    206.                 CellStyle cellStyle = xssfSheet.getRow(range.getFirstRow()).getCell(range.getFirstColumn()).getCellStyle();
    207.                 RegionUtil.setBorderTop(cellStyle.getBorderBottomEnum(), range, xssfSheet);
    208.                 RegionUtil.setBorderBottom(cellStyle.getBorderBottomEnum(), range, xssfSheet);
    209.                 RegionUtil.setBorderLeft(cellStyle.getBorderBottomEnum(), range, xssfSheet);
    210.                 RegionUtil.setBorderRight(cellStyle.getBorderBottomEnum(), range, xssfSheet);
    211.             }
    212.             mergedRanges.clear();
    213.         }
    214.         xssfWorkbook.write(new FileOutputStream(outFile));
    215.         xssfWorkbook.close();
    216.         Desktop.getDesktop().open(outFile);

  • 相关阅读:
    Obsidian -知识库管理工具使用总结
    Spread for ASP.NET 16.0 中文就是你喜好 Spread.NET
    Shell 脚本常用语法总结
    两军对垒问题及个人的思考
    ABAQUS学习之路
    移动端阻止Touch导致的页面滑动/缩放
    《数据结构、算法与应用C++语言描述》使用C++语言实现二维数组对角矩阵
    7.7 网络（二）
    2.12 IC类元器件的封装应该怎么创建？
    uniapp canvas文字和元素居中
  • 原文地址：https://blog.csdn.net/dbzzcz/article/details/133137931