Hello,
We need to import an Excel worksheet into a Word document. As prescribed (in this post), we are first exporting the worksheet to HTML and then importing the HTML into the Word document using the DocumentBuilder class. However, when the worksheet contains an embedded chart, the chart is shrunk down and bounded to a single cell and the resulting table in the Word document looks bad.
You can observe this behavior using the attached Excel file (“EmbeddedChart.xlsx”) and the following code (which runs on the latest versions of Aspose Cells and Apsose Words):
private static void exportExcel2WordTest() {
try {
Workbook wb = Workbook wb = new Workbook(“EmbeddedChart.xlsx”);
Worksheet sheet = wb.getWorksheets().get(“Sheet1”);
wb.getWorksheets().setActiveSheetIndex(sheet.getIndex());
// export the worksheet
HtmlSaveOptions options = new HtmlSaveOptions(SaveFormat.HTML);
options.setExportActiveWorksheetOnly(true);
String html = null;
ByteArrayOutputStream byOut = new ByteArrayOutputStream();
try {
wb.save(byOut, options);
html = new String(byOut.toByteArray());
// TODO: there seems to be a bug in Aspose where some extra bytes are
// prepended prior to the first ‘<’ in the HTML so we remove it here:
html = html.substring(html.indexOf(’<’));
// remove the temporary directory which is always generated
// TODO: This is returning null for some reason
String tempDir = options.getAttachedFilesDirectory();
if (tempDir != null) {
File dir = new File(tempDir);
if (dir.exists()) {
dir.delete();
}
}
} finally {
byOut.close();
}
Document wdDoc = new Document();
DocumentBuilder builder = new DocumentBuilder(wdDoc);
Paragraph para = wdDoc.getLastSection().getBody().getLastParagraph();
builder.moveTo(para);
builder.startBookmark(“Xl_Content”);
// TODO: writeln needed for the html to be added within the bookmark,
// but also adds an empty line
builder.writeln();
builder.insertHtml(html, false);
builder.endBookmark(“Xl_Content”);
wdDoc.save(“Excel2Doc.docx”);
} catch (Exception ex) {
System.out.println("Unexpected EXCEPTION: " + ex.getMessage());
ex.printStackTrace();
}
System.out.println(“End of exportExcel2WordTest method”);
}
If you compile and run the above code (and download the attached “EmbeddedChart.xlsx” file), you should see that although the embedded chart from the worksheet does get imported, it is shrunk and bounded to a single cell producing unexpected results in the Word document.
Additionally (and less importantly), there are some follow up questions I have related to the TODO items in the code above:
- The HTML generated from the Excel file always seems to have extra byte characters before the opening “<” character. These are manually removed and don’t seem to affect the imported content, but I wonder why it exists.
- The export of the worksheet to HTML always generates a folder. For example the code above produces a new folder named “EmbeddedChart_files”. While I understand the need for the folder, I would like to remove after the import to Word is complete. Is there any way to avoid having the folder generated? If not, how can I determine the folder name from Aspose so that I can remove it manually.
- I want to put the worksheet content within a bookmark. However, it seems it’s necessary to add en empty line (via “builder.writeln()”) in order to accomplish this. This results in an extra line at the end of the imported content - which I don’t need. Is there any way to insert the HTML without having to insert the extra line?
oraspose: