用poi把xls格式转换成xlsx格式

java中要实现excel新老格式的转换比较麻烦,开源库也没几个好用的。用ChatGpt查询也是推荐直接用POI,下面是借助ChatGPT写出来的代码,经过小小修改,格式转换良好,基本能用,就是效率比较低下。将就着用吧,哎!
 /**
     * Excel格式从xls转换成xlsx格式
     *
     * @param xlsInputStream   xls格式的输入流
     * @param xlsxOutputStream xlsx格式的输出流
     */
    public static void convertXlsToXlsxByStream(InputStream xlsInputStream, OutputStream xlsxOutputStream) {
        try {
            HSSFWorkbook oldWorkbook = new HSSFWorkbook(xlsInputStream);
            XSSFWorkbook newWorkbook = new XSSFWorkbook();

            for (int i = 0; i < oldWorkbook.getNumberOfSheets(); i++) {
                HSSFSheet oldSheet = oldWorkbook.getSheetAt(i);
                XSSFSheet newSheet = newWorkbook.createSheet(oldSheet.getSheetName());

                // 复制单元格值、样式和格式
                for (int j = 0; j <= oldSheet.getLastRowNum(); j++) {
                    HSSFRow oldRow = oldSheet.getRow(j);
                    XSSFRow newRow = newSheet.createRow(j);

                    if (oldRow != null) {
                        for (int k = 0; k < oldRow.getLastCellNum(); k++) {
                            HSSFCell oldCell = oldRow.getCell(k);
                            XSSFCell newCell = newRow.createCell(k);

                            if (oldCell != null) {
                                try {
                                    setCellValue(newCell, oldCell);
                                    copyCellStyle(newWorkbook, newCell, oldWorkbook, oldCell);
                                } catch (Exception ex) {
                                    log.warn("单元格拷贝异常:", ex);
                                }
                            }
                        }
                    }
                }

                // 复制单元格合并信息
                List<CellRangeAddress> mergedRegions = oldSheet.getMergedRegions();
                for (CellRangeAddress mergedRegion : mergedRegions) {
                    CellRangeAddress targetMergedRegion = new CellRangeAddress(
                            mergedRegion.getFirstRow(),
                            mergedRegion.getLastRow(),
                            mergedRegion.getFirstColumn(),
                            mergedRegion.getLastColumn()
                    );
                    newSheet.addMergedRegion(targetMergedRegion);
                }
            }

            newWorkbook.write(xlsxOutputStream);
            oldWorkbook.close();
            newWorkbook.close();
        } catch (Exception e) {
            log.error("excel格式转换(xls->xlsx)异常:", e);
        }
    }

    private static void setCellValue(XSSFCell newCell, HSSFCell oldCell) {
        if (oldCell == null) {
            return;
        }
        switch (oldCell.getCellType()) {
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(oldCell)) {
                    newCell.setCellValue(oldCell.getDateCellValue());
                } else {
                    newCell.setCellValue(oldCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case FORMULA:
                newCell.setCellValue(oldCell.getCellFormula());
                break;
            default:
        }
    }

    private static void copyCellStyle(XSSFWorkbook xssfWorkbook, XSSFCell newCell, HSSFWorkbook hssfWorkbook, HSSFCell oldCell) {
        HSSFCellStyle oldCellStyle = oldCell.getCellStyle();


        // 创建一个XSSFCellStyle(新Excel格式)
        XSSFCellStyle newCellStyle = xssfWorkbook.createCellStyle();

        // 复制HSSFCellStyle的样式属性到XSSFCellStyle
        newCellStyle.setAlignment(oldCellStyle.getAlignment());
        newCellStyle.setVerticalAlignment(oldCellStyle.getVerticalAlignment());

        // 复制字体属性
        XSSFFont newFont = xssfWorkbook.createFont();
        HSSFFont oldFont = oldCellStyle.getFont(hssfWorkbook);
        newFont.setFontName(oldFont.getFontName());
        newFont.setFontHeightInPoints(oldFont.getFontHeightInPoints());
        newFont.setColor(oldFont.getColor());
        newCellStyle.setFont(newFont);

        newCellStyle.setFillForegroundColor(oldCellStyle.getFillForegroundColor());
        newCellStyle.setFillPattern(oldCellStyle.getFillPattern());

        // 设置单元格类型
        newCellStyle.setDataFormat(oldCellStyle.getDataFormat());

        newCell.setCellStyle(newCellStyle);
    }

 

热门相关:藏娇记事   大妆   未来兽世:买来的媳妇,不生崽   酒吧   妻子的情人2