如何使用 Apache POI 将 HSSFWorkbook 转换为 XSSFWorkbook？

Question

如何转换

org.apache.poi.hssf.usermodel.HSSFWorkbook

至

org.apache.poi.xssf.usermodel.XSSFWorkbook

在 Apache POI 中？

环境 ：

• JSE1.6
• JBossAS 4.3.2
• 兴趣点 3.7

原文由 Bhuvanesh Phadnis 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

此代码改编自我在 coderanch 论坛 上找到的内容

public final class ExcelDocumentConverter {
public static XSSFWorkbook convertWorkbookHSSFToXSSF(HSSFWorkbook source) {
    XSSFWorkbook retVal = new XSSFWorkbook();
    for (int i = 0; i < source.getNumberOfSheets(); i++) {
        XSSFSheet xssfSheet = retVal.createSheet();
        HSSFSheet hssfsheet = source.getSheetAt(i);
        copySheets(hssfsheet, xssfSheet);
    }
    return retVal;
}

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

/**
 * @param destination
 *            the sheet to create from the copy.
 * @param the
 *            sheet to copy.
 * @param copyStyle
 *            true copy the style.
 */
public static void copySheets(HSSFSheet source, XSSFSheet destination, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) {
        HSSFRow srcRow = source.getRow(i);
        XSSFRow destRow = destination.createRow(i);
        if (srcRow != null) {
            copyRow(source, destination, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        destination.setColumnWidth(i, source.getColumnWidth(i));
    }
}

/**
 * @param srcSheet
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *            -
 */
public static void copyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow,
        Map<Integer, HSSFCellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a
    // merged zone
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        HSSFCell oldCell = srcRow.getCell(j); // ancienne cell
        XSSFCell newCell = destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell
            copyCell(oldCell, newCell, styleMap);
            // copy les informations de fusion entre les cellules
            // System.out.println("row num: " + srcRow.getRowNum() +
            // " , col: " + (short)oldCell.getColumnIndex());
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                // System.out.println("Selected merged region: " +
                // mergedRegion.toString());
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
                        mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                // System.out.println("New merged region: " +
                // newMergedRegion.toString());
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }

}

/**
 * @param oldCell
 * @param newCell
 * @param styleMap
 */
public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
    if (styleMap != null) {
        int stHashCode = oldCell.getCellStyle().hashCode();
        HSSFCellStyle sourceCellStyle = styleMap.get(stHashCode);
        XSSFCellStyle destnCellStyle = newCell.getCellStyle();
        if (sourceCellStyle == null) {
            sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle();
        }
        destnCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        styleMap.put(stHashCode, sourceCellStyle);
        newCell.setCellStyle(destnCellStyle);
    }
    switch (oldCell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_BLANK:
        newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    default:
        break;
    }

}

/**
 * Récupère les informations de fusion des cellules dans la sheet source
 * pour les appliquer à la sheet destination... Récupère toutes les zones
 * merged dans la sheet source et regarde pour chacune d'elle si elle se
 * trouve dans la current row que nous traitons. Si oui, retourne l'objet
 * CellRangeAddress.
 *
 * @param sheet
 *            the sheet containing the data.
 * @param rowNum
 *            the num of the row to copy.
 * @param cellNum
 *            the num of the cell to copy.
 * @return the CellRangeAddress created.
 */
public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress merged = sheet.getMergedRegion(i);
        if (merged.isInRange(rowNum, cellNum)) {
            return merged;
        }
    }
    return null;
}

/**
 * Check that the merged region has been created in the destination sheet.
 *
 * @param newMergedRegion
 *            the merged region to copy or not in the destination sheet.
 * @param mergedRegions
 *            the list containing all the merged region.
 * @return true if the merged region is already in the list or not.
 */
private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion,
        Set<CellRangeAddressWrapper> mergedRegions) {
    return !mergedRegions.contains(newMergedRegion);
}
}

原文由 Bhuvanesh Phadnis 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

//Added xbean-2.3.0.jar/xmlbeans-2.3.0.jar,poi-ooxml-3.7-20101029.jarpoi-ooxml-schemas-3.7-beta1.jar,
import java.io.*;
import java.util.*;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

public class Xls2Xlsx {

private File path = new File("c:/Integration-TestCases.xls");
private ArrayList<File> inputFiles = new ArrayList<File>();

private int lastColumn = 0;
private HashMap<Integer, XSSFCellStyle> styleMap = new HashMap();

private void getInputFiles() {
    String call = "getInputFiles ";
    if (this.path.isFile()) {
        if (this.path.getAbsolutePath().endsWith(".xls")
                && !new File(this.path.getAbsolutePath() + "x").exists())
            this.inputFiles.add(this.path);
        else {
            System.out
                    .println("Datei endet nicht mit .xls oder XLSX-Datei existiert bereits");
        }
    } else
        for (File f : this.path.listFiles(new FilenameFilter() {
            // anonyme innere Klasse

            @Override
            public boolean accept(File dir, String name) {
                if (name.endsWith(".xls"))
                    return true;
                return false;
            }

        })) {
            if (!new File(f.getAbsoluteFile() + "x").exists()) {
                this.inputFiles.add(f);
            }
        }
    System.out
            .println(call + "Dateien gefunden: " + this.inputFiles.size());
    System.out.println(call + "abgeschlossen");
}

private HSSFWorkbook getWorkBook(File f) throws FileNotFoundException,
        IOException {
    System.out.println("getWorkBook lese " + f.getAbsolutePath());
    POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(
            new FileInputStream(f)));
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    System.out.println("getWorkBook abgeschlossen");
    return workbook;
}

private void transformHSSF(HSSFWorkbook workbookOld ,XSSFWorkbook workbookNew) {
    String call = "transform ";
    System.out.println(call + "Workbook");
    XSSFSheet sheetNew;
    HSSFSheet sheetOld;
    //TODO::workbookNew.setForceFormulaRecalculation(workbookOld.getForceFormulaRecalculation());
    // workbookNew.setHidden(workbookOld.isHidden()); //ST@03.05.2012 -
    // von Apache noch nicht implementiert
    workbookNew.setMissingCellPolicy(workbookOld.getMissingCellPolicy());

    for (int i = 0; i < workbookOld.getNumberOfSheets(); i++) {
        sheetOld =  workbookOld.getSheetAt(i);
        sheetNew = workbookNew.getSheet(sheetOld.getSheetName());
        System.out.println(call + "Sheet Name: " + sheetOld.getSheetName());
        sheetNew = workbookNew.createSheet(sheetOld.getSheetName());
        this.transform(workbookOld,workbookNew,sheetOld, sheetNew);
    }
    System.out.println(call + "Styles size: "        + this.styleMap.size());
    System.out.println(call + "abgeschlossen");
}

    private void transform(HSSFWorkbook workbookOld ,XSSFWorkbook workbookNew,HSSFSheet sheetOld, XSSFSheet sheetNew) {
        System.out.println("transform Sheet");

        sheetNew.setDisplayFormulas(sheetOld.isDisplayFormulas());
        sheetNew.setDisplayGridlines(sheetOld.isDisplayGridlines());
        sheetNew.setDisplayGuts(sheetOld.getDisplayGuts());
        sheetNew.setDisplayRowColHeadings(sheetOld.isDisplayRowColHeadings());
        sheetNew.setDisplayZeros(sheetOld.isDisplayZeros());
        sheetNew.setFitToPage(sheetOld.getFitToPage());
        //
        //TODO::sheetNew.setForceFormulaRecalculation(sheetOld.getForceFormulaRecalculation());
        sheetNew.setHorizontallyCenter(sheetOld.getHorizontallyCenter());
        sheetNew.setMargin(Sheet.BottomMargin,
                sheetOld.getMargin(Sheet.BottomMargin));
        sheetNew.setMargin(Sheet.FooterMargin,
                sheetOld.getMargin(Sheet.FooterMargin));
        sheetNew.setMargin(Sheet.HeaderMargin,
                sheetOld.getMargin(Sheet.HeaderMargin));
        sheetNew.setMargin(Sheet.LeftMargin,
                sheetOld.getMargin(Sheet.LeftMargin));
        sheetNew.setMargin(Sheet.RightMargin,
                sheetOld.getMargin(Sheet.RightMargin));
        sheetNew.setMargin(Sheet.TopMargin, sheetOld.getMargin(Sheet.TopMargin));
        sheetNew.setPrintGridlines(sheetNew.isPrintGridlines());
        //TODO::sheetNew.setRightToLeft(sheetNew.isRightToLeft());
        sheetNew.setRowSumsBelow(sheetNew.getRowSumsBelow());
        sheetNew.setRowSumsRight(sheetNew.getRowSumsRight());
        sheetNew.setVerticallyCenter(sheetOld.getVerticallyCenter());

        XSSFRow rowNew;
        for (Row row : sheetOld) {
            rowNew = sheetNew.createRow(row.getRowNum());
            if (rowNew != null)
                this.transform(workbookOld,workbookNew,(HSSFRow) row, rowNew);
        }

        for (int i = 0; i < this.lastColumn; i++) {
            sheetNew.setColumnWidth(i, sheetOld.getColumnWidth(i));
            sheetNew.setColumnHidden(i, sheetOld.isColumnHidden(i));
        }

        for (int i = 0; i < sheetOld.getNumMergedRegions(); i++) {
            CellRangeAddress merged = sheetOld.getMergedRegion(i);
            sheetNew.addMergedRegion(merged);
        }
    }

    private void transform(HSSFWorkbook workbookOld ,XSSFWorkbook workbookNew,HSSFRow rowOld, XSSFRow rowNew) {
        XSSFCell cellNew;
        rowNew.setHeight(rowOld.getHeight());
        //TODO::if (rowOld.getRowStyle() != null) {
            /*Integer hash = rowOld.getRowStyle().hashCode();
            if (!this.styleMap.containsKey(hash))
                this.transform(workbookOld,workbookNew,hash, (XSSFCellStyle)rowOld.getRowStyle(),(HSSFCellStyle)workbookNew.createCellStyle());
            rowNew.setRowStyle(this.styleMap.get(hash));
        }*/
        for (Cell cell : rowOld) {
            cellNew = rowNew.createCell(cell.getColumnIndex(), cell.getCellType());
            if (cellNew != null)
                this.transform(workbookOld,workbookNew,(HSSFCell) cell, cellNew);
        }
        this.lastColumn = Math.max(this.lastColumn, rowOld.getLastCellNum());
    }

    private void transform(HSSFWorkbook workbookOld,XSSFWorkbook workbookNew,HSSFCell cellOld,XSSFCell cellNew) {
        cellNew.setCellComment(cellOld.getCellComment());

        Integer hash = cellOld.getCellStyle().hashCode();
        if (this.styleMap!=null && !this.styleMap.containsKey(hash)) {
            this.transform(workbookOld,workbookNew,hash, cellOld.getCellStyle(),(XSSFCellStyle)workbookNew.createCellStyle());
        }
        cellNew.setCellStyle(this.styleMap.get(hash));

        switch (cellOld.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            cellNew.setCellValue(cellOld.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            cellNew.setCellValue(cellOld.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            cellNew.setCellValue(cellOld.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            cellNew.setCellValue(cellOld.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            cellNew.setCellValue(cellOld.getStringCellValue());
            break;
        default:
            System.out.println("transform: Unbekannter Zellentyp "
                    + cellOld.getCellType());
        }
    }

    private void transform(HSSFWorkbook workbookOld,XSSFWorkbook workbookNew,Integer hash, HSSFCellStyle styleOld,
            XSSFCellStyle styleNew) {
        styleNew.setAlignment(styleOld.getAlignment());
        styleNew.setBorderBottom(styleOld.getBorderBottom());
        styleNew.setBorderLeft(styleOld.getBorderLeft());
        styleNew.setBorderRight(styleOld.getBorderRight());
        styleNew.setBorderTop(styleOld.getBorderTop());
         styleNew.setDataFormat(this.transform( workbookOld, workbookNew,styleOld.getDataFormat()));
        styleNew.setFillBackgroundColor(styleOld.getFillBackgroundColor());
        styleNew.setFillForegroundColor(styleOld.getFillForegroundColor());
        styleNew.setFillPattern(styleOld.getFillPattern());
        styleNew.setFont(this.transform(workbookNew,styleOld.getFont(workbookOld)));
        styleNew.setHidden(styleOld.getHidden());
        styleNew.setIndention(styleOld.getIndention());
        styleNew.setLocked(styleOld.getLocked());
        styleNew.setVerticalAlignment(styleOld.getVerticalAlignment());
        styleNew.setWrapText(styleOld.getWrapText());
        this.styleMap.put(hash, styleNew);
    }

private short transform(HSSFWorkbook workbookOld,XSSFWorkbook workbookNew,short index) {
    DataFormat formatOld = workbookOld.createDataFormat();
    DataFormat formatNew = workbookNew.createDataFormat();
    return formatNew.getFormat(formatOld.getFormat(index));
}

    private XSSFFont transform(XSSFWorkbook workbookNew,HSSFFont fontOld) {
        XSSFFont fontNew = workbookNew.createFont();
        fontNew.setBoldweight(fontOld.getBoldweight());
        fontNew.setCharSet(fontOld.getCharSet());
        fontNew.setColor(fontOld.getColor());
        fontNew.setFontName(fontOld.getFontName());
        fontNew.setFontHeight(fontOld.getFontHeight());
        fontNew.setItalic(fontOld.getItalic());
        fontNew.setStrikeout(fontOld.getStrikeout());
        fontNew.setTypeOffset(fontOld.getTypeOffset());
        fontNew.setUnderline(fontOld.getUnderline());
        return fontNew;
    }

public static void main(String[] args) throws Exception{
    Xls2Xlsx xls=new Xls2Xlsx();
    //xls.transform();
     InputStream isXls = new FileInputStream("c:/OTD1.xls");
     HSSFWorkbook workbookOld     = new HSSFWorkbook(isXls);

    String dest="c:/OTD.xlsx";

    FileOutputStream out = new FileOutputStream(dest);
    XSSFWorkbook workbookNew = new XSSFWorkbook();
    xls.transformHSSF( workbookOld , workbookNew) ;
    workbookNew.write(out);

    out.close();

//    InputStream isXls = new FileInputStream("c:/OTD1.xls");
//    HSSFWorkbook workbookNew     = new HSSFWorkbook(isXls);

}
}

原文由 Indranil Nag 发布，翻译遵循 CC BY-SA 3.0 许可协议