Apache poi XLSX to XLS

Question

I'm working with apache poi, i read some xlsx file, process it and then export them in xlsx format as well. But now i have the requirement of the export format to be XLS (this is to support old devices). Is there an easy way of convert the code-generated xlsx file to xls?

The all process is made with XSSF implementation.

Thanks in advance.

Answer 1

You will need to switch to the "ss" implementation which allows to transparently work with both HSSF (=XLS) and XSSF (=XSLX), see http://poi.apache.org/spreadsheet/converting.html for some details of the original HSSF -> SS switch which should also shed some light on supporting it for the other way around.

Then only the two constructors for HSSFWorkbook/XSSFWorkbook are needed to decide which of the two formats you want to produce.

Answer 2

I agree with centic answer, but I want to add a few lines of code.

You said that you are using XSSF implementation.

So, for the workbook that you are saving do the following changes: change XSSFWorkbook x = new XSSFWorkbook(); to Workbook x = new HSSFWorkbook(); where Workbook is import from org.apache.poi.ss.usermodel.Workbook;

Similarly Change XSSFRow instantiation from

XSSFRow r = newXSSF();

to Row r = new HSSFRow(); and import the Row from org.apache.poi.ss.usermodel.Row;

Like the same way, change Cell instantiation to ss.usermodel package.

And finally save your HSSF workbook with .xls extension.

Answer 3

I have faced same scenario and implemented below code for Coverting XLSX to XLS using Java

Below code will read it from directory and process using apache camel (polling file from path) and apace poi

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import java.util.Optional;

import org.apache.camel.Exchange;
import org.apache.camel.Processor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@Component
public class ExcelFileProcessor implements Processor {
    final Logger logger = LoggerFactory.getLogger(getClass());
    
    @Value("${test.dir.in}")
    private String inDir;
    
    @Override
    public void process(Exchange exchange) throws Exception {
        logger.info("Entry-ExcelFileProcessor- Process method");
        long start = System.currentTimeMillis();
        
        String fileNameWithExtn=(String) exchange.getIn().getHeader("camelFileName");
        Long originalFileSize = (Long) exchange.getIn().getHeader("CamelFileLength");
        String fileNameWithOutExtn = fileNameWithOutExtn(fileNameWithExtn);
        
        logger.info("fileNameWithExtn:{}" ,fileNameWithExtn);
        logger.info("fileNameWithOutExtn:{}" ,fileNameWithOutExtn);
        logger.info("originalFileSize:{}" ,originalFileSize);
        
        try(InputStream in = exchange.getIn().getBody(InputStream.class);
            XSSFWorkbook wbIn = new XSSFWorkbook(in);
            Workbook wbOut = new HSSFWorkbook();) {
            
          int sheetCnt = wbIn.getNumberOfSheets();
          for (int i = 0; i < sheetCnt; i++) {
              Sheet sIn = wbIn.getSheetAt(0);
              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());
                      cellOut.setCellComment(cellIn.getCellComment());
                     }
              }
          }
          File outF = new File(inDir+fileNameWithOutExtn+".xls");
          try(OutputStream out = new BufferedOutputStream(new FileOutputStream(outF));){
              wbOut.write(out);
          }
      }catch (Exception e) {
            logger.info("Error during Excel file process:{}",e.getMessage());
      }
        long end = System.currentTimeMillis();
        logger.info("Total time processed for file in - {} ms", (end - start));
        logger.info("Exit-FileProcessor- Process method");
     }
    public String fileNameWithOutExtn(String fileName) {
        return Optional.of(fileName.lastIndexOf('.')).filter(i-> i >= 0)
                .map(i-> fileName.substring(0, i)).orElse(fileName);
    }
}

If you are not using camel and want to get inputstream from file use below snippet

    String inpFn = "input.xlsx"; 
    String outFn = "output.xls"; 

    InputStream in = new BufferedInputStream(new FileInputStream(inpFn));
    try {
        Workbook wbIn = new XSSFWorkbook(in);
        File outF = new File(outFn);
        if (outF.exists())
            outF.delete();

        Workbook wbOut = new HSSFWorkbook();
        //continue with above code