SXSSFWorkbook write xlsm file

Question

I copy XSSFWorkbook to SXSSFWorkbook. In this process, I create the new workbooks I use like this:

XSSFWorkbook readOnlyWb = (XSSFWorkbook) WorkbookFactory.create(f, null, true);
SXSSFWorkbook writeOnlyWb = new SXSSFWorkbook();

When writing to disk, the content type of the SXSSFWorkbook is always

/xl/workbook.xml - Content Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml

This is no issue, if the source workbook is an .xlsx file which has the same content type.

But I also have .xlsm files which I want to copy and they should remain to be .xlsm files. These files have the content type

/xl/workbook.xml - Content Type: application/vnd.ms-excel.sheet.macroEnabled.main+xml

So basically the issue I have is, to dynamically get the right content type for each file type while using SXSSFWorkbook.

How can I change the content type for the SXSSFWorkbook dynamically?

Answer 1

You cannot create a SXSSFWorkbook having content type of *.xlsm from scratch, as well as you cannot create a XSSFWorkbook having content type of *.xlsm from scratch. But you can create a XSSFWorkbook form a *.xlsm file and this will preserve all the contents inclusive the content types and the vbaProject.bin VBA macro project as well. Then you can create the SXSSFWorkbook from that template XSSFWorkbook using constructor SXSSFWorkbook(XSSFWorkbook workbook). After that the SXSSFWorkbook also will preserve all the contents.

If the need is changing some parts of the template XSSFWorkbook, then this must be done before the SXSSFWorkbook was created from that template. The SXSSFWorkbook is not able changing rows which already was in the template. This could be done in memory whithout changing the template file when the XSSFWorkbook is fully contained im memory. That is the case if it was created using a InputStream.

Following code shows that. It creates a XSSFWorkbookfrom a *.xlsm file using FileInputStream. Then it changes some parts of the template before it creates a SXSSFWorkbook from it. Then the big amout of date is streamed in in the SXSSFWorkbook. The result is a *.xlsm file having the correct content type and also having the vbaProject.bin VBA macro project form the template as well.

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.streaming.*;

public class CreateExcelSXSSFFromXLSM {

 public static void main(String[] args) throws Exception {

  XSSFWorkbook templateWorkbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("Workbook.xlsm"));
  Sheet sheet = templateWorkbook.getSheet("Sheet1");
  for (Row row : sheet) {
   for (Cell cell : row) {
    System.out.println(cell);
    if (cell.getColumnIndex() == 0) cell.setCellValue("changed in template");
   }
  }

  int lastRowInTemplate = sheet.getLastRowNum();

  SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(templateWorkbook); 
  SXSSFSheet sxssfSheet = sxssfWorkbook.getSheet("Sheet1");
  for (int r = lastRowInTemplate + 1; r < lastRowInTemplate + 10; r++) {
   SXSSFRow row = sxssfSheet.createRow(r);
   for (int c = 0; c < 10; c++) {
    SXSSFCell cell = row.createCell(c);
    cell.setCellValue("R" + (r+1) + "C" + (c+1));
   }
  }

  FileOutputStream out = new FileOutputStream("WorkbookNew.xlsm");
  sxssfWorkbook.write(out);
  out.close();
  sxssfWorkbook.close();
  sxssfWorkbook.dispose();
 }
}