用poi把xls格式转换成xlsx格式-Toy模板网

这篇具有很好参考价值的文章主要介绍了用poi把xls格式转换成xlsx格式。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

java中要实现excel新老格式的转换比较麻烦，开源库也没几个好用的。用ChatGpt查询也是推荐直接用POI，下面是借助ChatGPT写出来的代码，经过小小修改，格式转换良好，基本能用，就是效率比较低下。将就着用吧，哎！

package com.yc.cloud.excel.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

@Slf4j
public class ExcelFmtConvert {

    /**
     * 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) {
                        // 复制行高
                        newRow.setHeight(oldRow.getHeight());

                        //遍历列，复制单元格
                        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);
                }

                // 复制列宽
                int columnCount = 0;
                if (newSheet.getRow(0) != null) {
                    // 假设第一行包含所有列，根据第一行的列数获取列数
                    columnCount = newSheet.getRow(0).getLastCellNum();
                }
                for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
                    newSheet.setColumnWidth(columnIndex, oldSheet.getColumnWidth(columnIndex));
                }
            }

            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();

        // 复制对齐方式
        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.setFillPattern(oldCellStyle.getFillPattern());
        newCellStyle.setFillForegroundColor(oldCellStyle.getFillForegroundColor());
        newCellStyle.setFillBackgroundColor(oldCellStyle.getFillBackgroundColor());

        // 复制数据格式
        newCellStyle.setDataFormat(oldCellStyle.getDataFormat());

        newCell.setCellStyle(newCellStyle);
    }
}