java使用poi，将xls文件转换为xlsx文件

最近产品有需求，需要在线浏览xls文件，犹豫前端的spreadJs只支持xlsx格式的浏览，所以让我们服务端，将xls文件转换为xlsx。

主要麻烦之处是图片的拷贝，花费了一些时间，先将代码贴在此处。

代码环境：jdk 1.8、poi 4.1.2

poi的目前没有原生的拷贝，所以需要自己一个个sheet、一行行、一个个单元格进行拷贝。

第一步就是选择需要拷贝的sheet，此处，遍历所有的sheet，我这边根据需求，只拷贝了，未隐藏的sheet。如果需要全部拷贝，可以去除判断，再给新的sheet设置是否隐藏。

            int sheetNum = hssfWorkbook.getNumberOfSheets();
            for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
                // 只解析未隐藏的sheet
//                hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
//                xssfSheet = workbook.createSheet(hssfSheet.getSheetName());
//                ExcelUtils.copySheets(hssfSheet, xssfSheet);
                if (SheetVisibility.VISIBLE == hssfWorkbook.getSheetVisibility(sheetIndex)) {
                    hssfSheet = hssfWorkbook.getSheetAt(sheetIndex);
                    xssfSheet = workbook.createSheet(hssfSheet.getSheetName());
                    ExcelUtils.copySheets(hssfSheet, xssfSheet);
                }
            }

第二步就是拷贝sheet。此处，需要遍历行信息，进行行拷贝。在拷贝完所有信息之后，再拷贝图片信息。

    public static void copySheets(HSSFSheet source, XSSFSheet destination) {

        int maxColumnNum = 0;
        // 获取全部的合并单元格
        List<CellRangeAddress> cellRangeAddressList = source.getMergedRegions();
        for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
            HSSFRow srcRow = source.getRow(i);
            XSSFRow destRow = destination.createRow(i);
            if (srcRow != null) {
                // 拷贝行
                copyRow(destination, srcRow, destRow, cellRangeAddressList);
                if (srcRow.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++) {
            destination.setColumnWidth(i, source.getColumnWidth(i));
        }

        // 拷贝图片
        copyPicture(source, destination);
    }

以下是拷贝行信息，和单元格信息的代码。

此处代码不做详细解释，基本就是拷贝合并单元格、拷贝文字样式等。

注：从hssf 转换到xssf 许多样式无法直接利用方法拷贝，需要逐一设置，可能会有遗漏。

单元格文本，此处也是统一复制，所以每个单元格的文本样式是相同的，如果要求比较高，可以使用XSSFRichTextString 进行每个字符的字体大小设置。

// 拷贝行
    public static void copyRow(XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow,
                                List<CellRangeAddress> cellRangeAddressList) {

        // 拷贝行高
        destRow.setHeight(srcRow.getHeight());

        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
            HSSFCell oldCell = srcRow.getCell(j);
            XSSFCell newCell = destRow.getCell(j);
            if (oldCell != null) {
                if (newCell == null) {
                    newCell = destRow.createCell(j);
                }
                // 拷贝单元格
                copyCell(oldCell, newCell, destSheet);

                // 获取原先的合并单元格
                CellRangeAddress mergedRegion = getMergedRegion(cellRangeAddressList, srcRow.getRowNum(),
                        (short) oldCell.getColumnIndex());

                if (mergedRegion != null) {
                    // 参照创建合并单元格
                    CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
                            mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                    destSheet.addMergedRegion(newMergedRegion);
                }
            }
        }

    }

    // 拷贝单元格
    public static void copyCell(HSSFCell oldCell, XSSFCell newCell, XSSFSheet destSheet) {

        HSSFCellStyle sourceCellStyle = oldCell.getCellStyle();
        XSSFCellStyle targetCellStyle = destSheet.getWorkbook().createCellStyle();

        if (sourceCellStyle == null) {
            sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle();
        }

        targetCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor());
        // 设置对齐方式
        targetCellStyle.setAlignment(sourceCellStyle.getAlignment());
        targetCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment());

        // 设置字体
        XSSFFont xssfFont = destSheet.getWorkbook().createFont();
        HSSFFont hssfFont = sourceCellStyle.getFont(oldCell.getSheet().getWorkbook());
        copyFont(xssfFont, hssfFont);
        targetCellStyle.setFont(xssfFont);
        // 文本换行
        targetCellStyle.setWrapText(sourceCellStyle.getWrapText());

        targetCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom());
        targetCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft());
        targetCellStyle.setBorderRight(sourceCellStyle.getBorderRight());
        targetCellStyle.setBorderTop(sourceCellStyle.getBorderTop());
        targetCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor());
        targetCellStyle.setDataFormat(sourceCellStyle.getDataFormat());
        targetCellStyle.setFillBackgroundColor(sourceCellStyle.getFillBackgroundColor());
        targetCellStyle.setFillPattern(sourceCellStyle.getFillPattern());

        targetCellStyle.setHidden(sourceCellStyle.getHidden());
        targetCellStyle.setIndention(sourceCellStyle.getIndention());
        targetCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor());
        targetCellStyle.setLocked(sourceCellStyle.getLocked());
        targetCellStyle.setQuotePrefixed(sourceCellStyle.getQuotePrefixed());
        targetCellStyle.setReadingOrder(ReadingOrder.forLong(sourceCellStyle.getReadingOrder()));
        targetCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor());
        targetCellStyle.setRotation(sourceCellStyle.getRotation());

        newCell.setCellStyle(targetCellStyle);

        switch (oldCell.getCellType()) {
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case BLANK:
                newCell.setCellType(CellType.BLANK);
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            default:
                break;
        }

    }

    // 拷贝字体设置
    public static void copyFont(XSSFFont xssfFont, HSSFFont hssfFont) {
        xssfFont.setFontName(hssfFont.getFontName());
        xssfFont.setBold(hssfFont.getBold());
        xssfFont.setFontHeight(hssfFont.getFontHeight());
        xssfFont.setCharSet(hssfFont.getCharSet());
        xssfFont.setColor(hssfFont.getColor());
        xssfFont.setItalic(hssfFont.getItalic());
        xssfFont.setUnderline(hssfFont.getUnderline());
        xssfFont.setTypeOffset(hssfFont.getTypeOffset());
        xssfFont.setStrikeout(hssfFont.getStrikeout());
    }

    // 根据行列获取合并单元格
    public static CellRangeAddress getMergedRegion(List<CellRangeAddress> cellRangeAddressList, int rowNum, short cellNum) {
        for (int i = 0; i < cellRangeAddressList.size(); i++) {
            CellRangeAddress merged = cellRangeAddressList.get(i);
            if (merged.isInRange(rowNum, cellNum)) {
                // 已经获取过不再获取
                cellRangeAddressList.remove(i);
                return merged;
            }
        }
        return null;
    }

最后就是让我最烦恼的地方，图片的拷贝。

最艰难的地方就是，hssf和xssf两种 对图片偏移的解析。

poi官方文档，两者的注释是一样的，我也被误导了。最后翻看源代码发现

hssf的偏移是根据系统按比例来的。单元格无论多宽，总宽度都为1024，总高度都为256。

xssf的偏移则是以emu为单位的。

所以hssf转xssf 正确的公式应该如下：

图片偏移x坐标 = hssf单元格宽度 / 1024 * hssf图片偏移比例 * 每个像素对应的emu

图片偏移y坐标 = hssf单元格高度 / 256 * hssf图片偏移比例 * 每个Point对应的emu

注：此处获取单元格宽度还是有问题，默认的获取方法有。getColumnWidthInPixels 和 getColumnWidth。getColumnWidthInPixels 是单元格像素宽度，getColumnWidth也是一种相对宽度。但是实际测试下来会有问题，

此处 cw 与 def 不相等，最后 获取的像素宽度是 getDefaultColumnWidth * 256 / PX_MODIFIED,

但实际结果应该是 getDefaultColumnWidth * 256 / PX_DEFAULT 

原因未查明，此处我在代码中放弃了getColumnWidthInPixels 改用了  getDefaultColumnWidth * 256 / PX_DEFAULT

@Override
public float getColumnWidthInPixels(int column){
    int cw = getColumnWidth(column);
    int def = getDefaultColumnWidth()*256;
    float px = (cw == def ? PX_DEFAULT : PX_MODIFIED);

    return cw/px;
}

最后结果代码如下：

// 拷贝图片
    public static void copyPicture(HSSFSheet source, XSSFSheet destination) {
        // 获取sheet中的图片信息
        List<Map<String, Object>> mapList = getPicturesFromHSSFSheet(source);

        XSSFDrawing drawing = destination.createDrawingPatriarch();

        for (Map<String, Object> pictureMap: mapList) {

            HSSFClientAnchor hssfClientAnchor = (HSSFClientAnchor) pictureMap.get("pictureAnchor");

            HSSFRow startRow = source.getRow(hssfClientAnchor.getRow1());
            float startRowHeight = startRow == null ? source.getDefaultRowHeightInPoints() : startRow.getHeightInPoints();

            HSSFRow endRow = source.getRow(hssfClientAnchor.getRow1());

            float endRowHeight = endRow == null ? source.getDefaultRowHeightInPoints() : endRow.getHeightInPoints();

            // hssf的单元格，每个单元格无论宽高，都被分为 宽 1024个单位 高 256个单位。
            // 32.00f 为默认的单元格单位宽度 单元格宽度 / 默认宽度 为像素宽度
            XSSFClientAnchor xssfClientAnchor = drawing.createAnchor(
                    (int) (source.getColumnWidth(hssfClientAnchor.getCol1()) / 32.00f
                            / 1024 * hssfClientAnchor.getDx1() * Units.EMU_PER_PIXEL),
                    (int) (startRowHeight / 256 * hssfClientAnchor.getDy1() * Units.EMU_PER_POINT),
                    (int) (source.getColumnWidth(hssfClientAnchor.getCol2()) / 32.00f
                            / 1024 * hssfClientAnchor.getDx2() * Units.EMU_PER_PIXEL),
                    (int) (endRowHeight / 256 * hssfClientAnchor.getDy2() * Units.EMU_PER_POINT),
                    hssfClientAnchor.getCol1(),
                    hssfClientAnchor.getRow1(),
                    hssfClientAnchor.getCol2(),
                    hssfClientAnchor.getRow2());
            xssfClientAnchor.setAnchorType(hssfClientAnchor.getAnchorType());

            drawing.createPicture(xssfClientAnchor,
                    destination.getWorkbook().addPicture((byte[])pictureMap.get("pictureByteArray"),
                            Integer.parseInt(pictureMap.get("pictureType").toString())));

            System.out.println("imageInsert");
        }
    }

    /**
     * 获取图片和位置 (xls)
     */
    public static List<Map<String, Object>> getPicturesFromHSSFSheet (HSSFSheet sheet) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                Map<String, Object> map = new HashMap<>();
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = picture.getClientAnchor();
                HSSFPictureData pdata = picture.getPictureData();
                map.put("pictureAnchor", cAnchor);
                map.put("pictureByteArray", pdata.getData());
                map.put("pictureType", pdata.getPictureType());
                map.put("pictureSize", picture.getImageDimension());
                mapList.add(map);
            }
        }
        return mapList;
    }

}