问题描述
我正在编写一个Java程序,该程序将打开用户定义的excel文件,将其填充数据,然后将其保存在用户指定的路径,文件名和扩展名下.即使输入文件是xlsm,也应该(但不可能)将输出声明为xlsx.如果我尝试使用以下代码进行操作,则打开文件会出现以下错误:
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:
关键代码段:
打开工作簿:
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;
}
}
保存工作簿:
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?
推荐答案
设置WorkbookType
只会更改内容类型,而不会从XLSM
文件内容中删除VBA
项目.
The setting the WorkbookType
does only changing the content type but does not removing the VBA
project from the XLSM
file content.
以下代码通过从软件包中获取和删除vbaProject.bin部分来执行此操作.然后,它还会从包中获取并删除与已删除的vbaProject.bin部分的关系.
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.
此后,新的XLSX
文件不再包含VBA
代码.
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();
}
}
这篇关于Poi:从xlsm打开Excel文件后将其另存为xlsx的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!