0

I am trying to convert an existing XSSFWorkbook with a specific formatting (a custom table) to an HSSFWorkbook in order to save it as an .xls file.

For this, I am using the following method that copies the data from the XSSFWorkbook and pastes it into a new HSSFWorkbook, before trying to also copy the style. However I cannot use the cloneStyleFrom method as it only works from one XSSFCell to another.

Do you have an idea of how I could proceed to copy the style of each cell and apply it to the new, HSSF ones?

The alternative that I have thought about was to add an .xls file to my project's resources folder with a giant, empty table with the right formatting, that I would then fill with the data taken from the other workbook, and then programatically delete the extra columns and rows remained empty. But I do not really like this solution and wanted to be sure there was no other before implementing it.

public Workbook convert(XSSFWorkbook wb, File inpFn) throws InvalidFormatException,IOException {

    XSSFWorkbook wbIn = wb;

    Workbook wbOut = new HSSFWorkbook();
    int sheetCnt = wbIn.getNumberOfSheets();
    for (int i = 0; i < sheetCnt; i++) {
        Sheet sIn = wbIn.getSheetAt(i);
        Sheet sOut = wbOut.createSheet(sIn.getSheetName());
        Iterator<Row> rowIt = sIn.rowIterator();
        while (rowIt.hasNext()) {
            Row rowIn = rowIt.next();
            Row rowOut = sOut.createRow(rowIn.getRowNum());

            Iterator<Cell> cellIt = rowIn.cellIterator();
            while (cellIt.hasNext()) {
                Cell cellIn = cellIt.next();
                Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType());

                switch (cellIn.getCellType()) {
                case Cell.CELL_TYPE_BLANK: break;

                case Cell.CELL_TYPE_BOOLEAN:
                    cellOut.setCellValue(cellIn.getBooleanCellValue());
                    break;

                case Cell.CELL_TYPE_ERROR:
                    cellOut.setCellValue(cellIn.getErrorCellValue());
                    break;

                case Cell.CELL_TYPE_FORMULA:
                    cellOut.setCellFormula(cellIn.getCellFormula());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    cellOut.setCellValue(cellIn.getNumericCellValue());
                    break;

                case Cell.CELL_TYPE_STRING:
                    cellOut.setCellValue(cellIn.getStringCellValue());
                    break;
                }

                CellStyle styleIn = cellIn.getCellStyle();
                CellStyle styleOut = cellOut.getCellStyle();
                styleOut.setDataFormat(styleIn.getDataFormat());
                //styleOut.cloneStyleFrom(styleIn); Does not work from XSSFCell to HSSFCell
                cellOut.setCellStyle(styleOut);
                }
        }
    }

    return wbOut;

}
zelig
  • 43
  • 7
  • 1
    Please, explain better why you are cloning the workbook: if you copy both data and styles of each cell, the new document will be identical to the old one so you could save the old one directly. – Pino Mar 29 '19 at 11:21
  • https://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/HSSFCellStyle.html - you can create a new HSSFCellStyle (see api doc) and call the setters on it - the getters on the XSSFCellStyle should be similar – PJ Fanning Mar 29 '19 at 11:49
  • @Pino I want to clone it because in the application that I work on, I want to have both options possible, saving the workbook in .xls and .xlsx. But in the java code, the Apache POI methods that I use for formatting only work with XSSFWorkbooks, not HSSFWorkbooks. – zelig Mar 29 '19 at 11:57
  • Someone has already managed the inverse conversion, maybe it's useful: https://stackoverflow.com/questions/7230819/how-to-convert-hssfworkbook-to-xssfworkbook-using-apache-poi – Pino Mar 29 '19 at 12:55
  • "But in the java code, the Apache POI methods that I use for formatting only work with XSSFWorkbooks, not HSSFWorkbooks.". And why do you then think you could copying **all** the formatting from `XSSF` to `HSSF`? This is simply not possible since Office Open XML (`*.xlsx`) provides cell styling options which BIFF ( `*.xls`) not provides. The other way (from `HSSF` to `XSSF`) is possible, even if not simple. – Axel Richter Mar 29 '19 at 14:08
  • @AxelRichter I sure was expecting this, however as `.xls` format enables to save a formatted table (which is what I need to convert) I supposed that this specific formatting would be possible to reproduce in a `HSSF` workbook – zelig Mar 29 '19 at 14:26

0 Answers0