如何在Apache POI中将xslm文件另存为xslx-Java 学习之路

所有：

我是Excel和APCH POI的新手，我想知道如何阅读Excel .xlsm文件（Macro Enable excel）并使用Apache POI将其保存为.xlsx文件？

任何例子将不胜感激

1 回答

从我的评论中回答 . 希望它会更清楚 .

以下代码确实从 Workbook.xlsm 模板创建 XSSFWorkbook ，该模板保持不变 .

然后它会在此工作簿中进行更改，并且在将副本保存为 *.xlsx 之前，当前状态将保存为 WorkbookNew.xlsm 文件 . 所以宏保持不变 .

然后将删除 VBA ，内容类型将设置为 XLSX 并进一步改变 . 然后此副本将保存为 WorkbookNew.xlsx 文件 .

之后，将通过从先前保存的 WorkbookNew.xlsm 文件再次创建工作簿来恢复上一个工作簿状态 . 然后将进行进一步的改变，毕竟 WorkbookNew.xlsm 文件的最终状态将被写出 .

所以我们有 Workbook.xlsm 模板， WorkbookNew.xlsx and WorkbookNew.xlsm 然后 .

例：

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;
  Sheet sheet;
  Row row;
  Cell cell;
  FileOutputStream out;

  //create workbook from XLSM template
  workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("Workbook.xlsm"));

  //do changings
  sheet = workbook.getSheetAt(0);
  row = sheet.getRow(0);
  if (row == null) row = sheet.createRow(0);
  cell = row.getCell(0);
  if (cell == null) cell = row.createCell(0);
  cell.setCellValue("changed in XLSM before writing as XLSX");

  //write out the current state
  out = new FileOutputStream("WorkbookNew.xlsm");
  workbook.write(out);
  out.close();

  //save copy as XLSX ----------------START
  //remove VBA
  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);

  //do changings only in XLSX
  sheet = workbook.getSheetAt(0);
  row = sheet.getRow(1);
  if (row == null) row = sheet.createRow(1);
  cell = row.getCell(1);
  if (cell == null) cell = row.createCell(1);
  cell.setCellValue("changed before writing as XLSX");

  //write out the XLSX
  out = new FileOutputStream("WorkbookNew.xlsx");
  workbook.write(out);
  out.close();
  //save copy as XLSX ----------------END

  //get back the previous saved state
  workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("WorkbookNew.xlsm"));

  //do changings
  sheet = workbook.getSheetAt(0);
  row = sheet.getRow(2);
  if (row == null) row = sheet.createRow(1);
  cell = row.getCell(2);
  if (cell == null) cell = row.createCell(1);
  cell.setCellValue("changed in XLSM after writing as XLSX");

  //write out the XLSM
  out = new FileOutputStream("WorkbookNew.xlsm");
  workbook.write(out);
  out.close();

  workbook.close();

 }
}

回复于 2024-04-28T14:50:46+08:00

如何在Apache POI中将xslm文件另存为xslx

1 回答

相关问题