Poi: Saving an excel file as xlsx after opening it

问题:

I'm writing a java program that opens a user-defined excel file, fills it with data, and then saves it under a user-specified path, file name and extension. It should be, but isn't, possible to declare the output to be saved as xlsx, even though the input file was xlsm. If I try that with the code as below, opening the file gives me the error:

The file 'FileName.xlsx' is a macro-free file, but contains macro-enabled content

Key segments of code:

Opening the workbook:

try (Workbook workbook = WorkbookFactory.create( new FileInputStream( templateFile ) )) {
    // ...processing the file here, 
    // including a call of stripMacros, c.f. below
} catch ( IOException | EncryptedDocumentException | InvalidFormatException ex ) {
    throw new TemplateNotFoundException( "Template not found. Please check property templatePath: " + templateFile, ex );
}

Setting the workbook to the right type:

private Workbook stripMacros( final Workbook wb, final String outputFormat ) {
    Workbook workbook = wb;
    if ( "xlsx".equals( outputFormat ) && ( workbook.getClass() == XSSFWorkbook.class ) ) { 
        XSSFWorkbook wbx = (XSSFWorkbook) workbook;
        wbx.setWorkbookType( XSSFWorkbookType.valueOf( "XLSX" ) );
        return wbx;
    } else if ( "xlsm".equals( outputFormat ) && ( workbook.getClass() == XSSFWorkbook.class ) ) {
        XSSFWorkbook wbm = (XSSFWorkbook) workbook;
        wbm.setWorkbookType( XSSFWorkbookType.valueOf( "XLSM" ) );
        return wbm;
    } else {
        return wb;
    }
}

Saving the workbook:

File outFile = new File( destinationPath, fileName + "." + outputFormat ); 
outFile.getParentFile().mkdirs();
if ( workbook != null ) {
    try {
        workbook.write( new FileOutputStream( outFile ) );
        workbook.close();
    } catch ( IOException ex ) {
        throw new FileInaccessibleException( "Workbook could not be saved. Please check if the workbook under " + destinationPath  + fileName + "." + outputFormat + " is not open in any program.", ex );
    }
}

What do I need to add so that my files open properly? Do I need to actually remove the macros by hand, and if so, how?

回答1:

The setting the WorkbookType does only changing the content type but does not removing the VBA project from the XLSM file content.

The following code is doing this by getting and removing the vbaProject.bin part from the package. Also it then gets and removes the relationship to the removed vbaProject.bin part from the package.

After this the new XLSX file does not contains VBA code anymore.

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

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
import org.apache.poi.openxml4j.opc.PackageRelationship;

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

import java.util.regex.Pattern;

class ReadXSLMWriteXLSXWorkbook {

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

  XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("Workbook.xlsm"));

  OPCPackage opcpackage = workbook.getPackage();

  //get and remove the vbaProject.bin part from the package
  PackagePart vbapart = opcpackage.getPartsByName(Pattern.compile("/xl/vbaProject.bin")).get(0);
  opcpackage.removePart(vbapart);

  //get and remove the relationship to the removed vbaProject.bin part from the package
  PackagePart wbpart = workbook.getPackagePart();
  PackageRelationshipCollection wbrelcollection = wbpart.getRelationshipsByType("http://schemas.microsoft.com/office/2006/relationships/vbaProject");
  for (PackageRelationship relship : wbrelcollection) {
   wbpart.removeRelationship(relship.getId());
  }

  //set content type to XLSX
  workbook.setWorkbookType(XSSFWorkbookType.XLSX);

  Sheet sheet = workbook.getSheetAt(0);
  Row row = sheet.getRow(0);
  if (row == null) row = sheet.createRow(0);
  Cell cell = row.getCell(0);
  if (cell == null) cell = row.createCell(0);
  cell.setCellValue("changed");

  workbook.write(new FileOutputStream("Workbook.xlsx"));
  workbook.close();

 }
}