hey there,
i am trying to extract ole objects from excel file and also insert back. i found this documentation and tried to work with the given example: Managing OLE Objects|Documentation
but when i tried to open the extracted file on ubuntu OS using libreoffice it didn’t open and then i tried to open the extracted file on windows 10 OS it didn’t open as well.
i am adding the file to this conversation.
recursive.zip (13.1 KB)
can you help to solve the problem?
Thanks for the sample XLSX file and details.
After an initial test, I am able to reproduce the issue as you mentioned. I found the issue with extracting OLE Objects from Excel file. The extracted file (XLSX) does not open into MS Excel.
String dataDir1 = "d:\\files\\";
// Instantiating a Workbook object
Workbook workbook = new Workbook("d:\\files\\recursive.xlsx");
// Get the OleObject Collection in the first worksheet.
OleObjectCollection oles = workbook.getWorksheets().get(0).getOleObjects();
// Loop through all the ole objects and extract each object. in the worksheet.
for (int i = 0; i < oles.getCount(); i++) {
if (oles.get(i).getMsoDrawingType() == MsoDrawingType.OLE_OBJECT) {
OleObject ole = (OleObject) oles.get(i);
// Specify the output filename.
String fileName = dataDir1 + "tempBook1_ole" + i + ".";
// Specify each file format based on the oleformattype.
switch (ole.getFileFormatType()) {
case FileFormatType.DOC:
fileName += "doc";
break;
case FileFormatType.EXCEL_97_TO_2003:
fileName += "Xls";
break;
case FileFormatType.XLSX:
fileName += "xlsx";
break;
case FileFormatType.PPT:
fileName += "Ppt";
break;
case FileFormatType.PDF:
fileName += "Pdf";
break;
case FileFormatType.UNKNOWN:
fileName += "Jpg";
break;
default:
fileName += "data";
break;
}
// Save the oleobject as a new excel file if the object type is xls.
if (ole.getFileFormatType() == FileFormatType.EXCEL_97_TO_2003) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
if (ole.getObjectData() != null) {
baos.write(ole.getObjectData(), 0, ole.getObjectData().length);
Workbook oleBook = new Workbook(new ByteArrayInputStream(baos.toByteArray()));
oleBook.getSettings().setHidden(false);
oleBook.save(dataDir1+ "outOle" + i + ".out.xls");
} else if (ole.isLink()) {
Workbook olebook = new Workbook(ole.getObjectSourceFullName());
olebook.save(dataDir1 + "line_file" + i + "_out.xls");
}
}
// Create the files based on the oleobject format types.
else {
if (ole.getObjectData() != null) {
FileOutputStream fos = new FileOutputStream(fileName);
fos.write(ole.getObjectData(), 0, ole.getObjectData().length);
fos.close();
}
}
}
}
We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-45995
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@Lidor
The ole object is a hidden xlsx file.
Please change your codes as :
if (ole.getFileFormatType() == FileFormatType.EXCEL_97_TO_2003
|| ole.getFileFormatType() == FileFormatType.XLSX) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
if (ole.getObjectData() != null) {
baos.write(ole.getObjectData(), 0, ole.getObjectData().length);
Workbook oleBook = new Workbook(new ByteArrayInputStream(baos.toByteArray()));
oleBook.getSettings().setHidden(false);
if (ole.getFileFormatType() == FileFormatType.EXCEL_97_TO_2003)
{
oleBook.save(dir +"outOle" + i + ".out.xlsx");
}else
{
oleBook.save(dir +"outOle" + i + ".out.xls");
}
} else if (ole.isLink()) {
Workbook olebook = new Workbook(ole.getObjectSourceFullName());
olebook.save(dir +"line_file" + i + "_out.xls");
}
}