我必须更新以嵌入其中的excel电子表格以编辑表的形式嵌入的Word文档。我必须以程序性的方式使用Java更新此表,以替换文档中嵌入的Excel表中的值。我研究了Apache POI(hwpfdocument方法)的所有可能选项以获取Excel电子表格对象的句柄,但是到目前为止,还没有找到任何可以使我控制此对象的方法。
我遇到了我在时间上寻找的相同问题,但是我找不到清晰的答案,我们如何将嵌入式的excel文件更新到Word文件中,在尝试了许多解决方案后,我找到了一个解决方案使用POI将数据更新或复制到Excel嵌入式文件中的方法,也许很晚,但我希望它能帮助其他面临同一问题的人。下面的代码示例基于此链接中的代码
在此示例中,我们有一个Word Document(Microsoft Office 2007及以上),它具有两个嵌入式Excel文件,我们将更新每个内容,每个文件都有不同的内容(数据),
您可以尝试修改此代码以支持Word 2003
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import java.io.*;
import java.util.List;
import java.util.logging.Logger;
public class UpdateEmbeddedDoc {
private XWPFDocument doc;
private File docFile;
private static final String BINARY_EXTENSION = "xls";
private static final String OPENXML_EXTENSION = "xlsx";
private final static Logger LOGGER =
Logger.getLogger(UpdateEmbeddedDoc.class.getName());
// Here in the contractor we give the path of the word document (docFilePath) that it contains the excel embedded files
public UpdateEmbeddedDoc(String docFilePath) throws FileNotFoundException,
IOException {
this.docFile = new File(docFilePath);
if (!this.docFile.exists()) {
throw new FileNotFoundException("The Word document " + docFilePath + " does not exist.");
}
try (FileInputStream fis = new FileInputStream(this.docFile)) {
// Open the Word document file and instantiate the XWPFDocument class.
this.doc = new XWPFDocument(fis);
}
}
public void updateEmbeddedDoc(String ExcelFile1, String ExcelFile2) throws OpenXML4JException, IOException {
List<PackagePart> embeddedDocs = this.doc.getAllEmbeddedParts();
//in this example we have two embedded excel files the first one it is in first index 0
//the second excel file will be on the second index 1 (0 based index)
PackagePart pPart0 = embeddedDocs.get(0);
PackagePart pPart1 = embeddedDocs.get(1);
LOGGER.info("embedded first part : " + embeddedDocs.get(0));
LOGGER.info("embedded second part : " + embeddedDocs.get(1));
//Here we are calling copyWorkbook method and give the path of the excel file (ExcelFile1 and ExcelFile2 )
// that it contains the data that we want to copy to embedded execl files (pPart0 and pPart1 )
copyWorkbook(ExcelFile1, pPart0);
copyWorkbook(ExcelFile2, pPart1);
if (!embeddedDocs.isEmpty()) {
try (FileOutputStream fos = new FileOutputStream(this.docFile)) {
this.doc.write(fos);
}
}
}
public void copyWorkbook(String inputExelFilePath, PackagePart pPart) {
try {
FileInputStream file = new FileInputStream(inputExelFilePath);
Workbook workbookInput = new XSSFWorkbook(file);
String ext = pPart.getPartName().getExtension();
if (BINARY_EXTENSION.equals(ext) || OPENXML_EXTENSION.equals(ext)) {
try (
OutputStream os = pPart.getOutputStream()) {
//Here we write our workbook (excel file) to the outputStream that refer to excel embedded file
workbookInput.write(os);
} catch (IOException e) {
LOGGER.severe("Could not update the excel file : " + e.getMessage());
}
}
} catch (IOException e) {
LOGGER.severe("Could not update the excel file : " + e.getMessage());
}
}
测试代码:
public class VdslmopApplication {
private final static Logger logger = Logger.getLogger(VdslmopApplication.class.getName());
public static void main(String[] args) {
// here we provide all the path for the files word document file and the two excel files
//its better to provide relative path to the files
String docFilePath = "docfile/docTest.docx";
String excelFile1Path = "excelFile/excelFile1.xlsx";
String excelFile2Path = "excelFile/excelFile2.xlsx";
try {
UpdateEmbeddedDoc ued = new UpdateEmbeddedDoc(docFilePath);
ued.updateEmbeddedDoc(excelFile1Path, excelFile2Path);
logger.info("Doc file has been successfully updated ");
} catch (IOException e) {
e.printStackTrace();
} catch (OpenXML4JException e) {
e.printStackTrace();
}
}
}