OLE objects

xlsgen > overview > OLE objects

xlsgen can extract and insert OLE objects embedded in any spreadsheet.

OLE objects extraction

OLE objects encapsulate two things :

a file, or a link to a file
a picture representing the last known visual state of the object.

An OLE object encapsulated as a file can always be extracted. An OLE object encapsulated as a link to a file can be extracted if the fully qualified filepath can be accessed on the computer running xlsgen.

OLE object extraction in xlsgen :

works with embedded and linked objects
works with XLS and XLSX spreadsheets
returns the location of the object in the spreadsheet, such as (row1,col1,row2,col2) anchors
returns the object, for instance a Word document, for extraction purposes
returns the object file extension, such as "doc", so you know in advance the file format you are dealing with
returns the picture, also for extraction purposes
returns the picture file extension, such as "emf" (Windows enhanced metafile), so you know in advance the file format you are dealing with

The OLE objects interface is exposed at the worksheet level. The objects can be enumerated, and access on each object is available.

Java code

XlsWorkbook wbk = engine.Open( "Book1_ole_linking.xlsx", "");

int nbOleObjects = wbk.getWorksheetByIndex(1).getOLEObjects().getCount();

// grab the first OLE object in the collection
// it is assumed there is one such OLE object in the spreadsheet
XlsOLEObject object = wbk.get_WorksheetByIndex(1).getOLEObjects().getItem(1);

// location
int left = object.getLeftColumn();
int right = object.getRightColumn();
int top = object.getTopCell();
int bottom = object.getBottomCell();

// embedded object file type, for instance "doc" for a Word document
String sFileExtension = object.getFiletype();

// embedded object extraction
object.ExtractToFile("output\\embedded.doc");

// picture file type, for instance "emf" for a Windows enhanced metafile picture
String sPictureFileExtension = object.getSnapshotFiletype();

// picture file extraction
object.ExtractSnapshotToFile("output\\embedded.emf");

VB code

Dim wbk As IXlsWorkbook 
Set wbk = engine.Open ("Book1_ole_linking.xlsx", "")

Dim nbOleObjects
nbOleObjects = wbk.WorksheetByIndex(1).OLEObjects.Count

' grab the first OLE object in the collection
' it is assumed there is one such OLE object in the spreadsheet
Dim object As IXlsOLEObject 
Set object = wbk.WorksheetByIndex(1).OLEObjects.Item(1)

' location
Dim left
left = object.LeftColumn
Dim right
right = object.RightColumn
Dim top
top = object.TopCell
Dim bottom
bottom = object.BottomCell

' embedded object file type, for instance "doc" for a Word document
Dim sFileExtension As String
sFileExtension = object.Filetype

' embedded object extraction
object.ExtractToFile("output\embedded.doc")

' picture file type, for instance "emf" for a Windows enhanced metafile picture
Dim sPictureFileExtension As String
sPictureFileExtension = object.SnapshotFiletype

' picture file extraction
object.ExtractSnapshotToFile("output\embedded.emf")

C# code

IXlsWorkbook wbk = engine.Open( "Book1_ole_linking.xlsx", "");

int nbOleObjects = wbk.get_WorksheetByIndex(1).OLEObjects.Count;

// grab the first OLE object in the collection
// it is assumed there is one such OLE object in the spreadsheet
IXlsOLEObject object = wbk.get_WorksheetByIndex(1).OLEObjects.get_Item(1);

// location
int left = object.LeftColumn;
int right = object.RightColumn;
int top = object.TopCell;
int bottom = object.BottomCell;

// embedded object file type, for instance "doc" for a Word document
string sFileExtension = object.Filetype;

// embedded object extraction
object.ExtractToFile("output\\embedded.doc");

// picture file type, for instance "emf" for a Windows enhanced metafile picture
string sPictureFileExtension = object.SnapshotFiletype;

// picture file extraction
object.ExtractSnapshotToFile("output\\embedded.emf");

C++ code

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"Book1_ole_linking.xlsx", L"");

int nbOleObjects = wbk->WorksheetByIndex[1]->OLEObjects->Count;

// grab the first OLE object in the collection
// it is assumed there is one such OLE object in the spreadsheet
xlsgen::IXlsOLEObjectPtr object = wbk->WorksheetByIndex[1]->OLEObjects->Item[1];

// location
int left = object->LeftColumn;
int right = object->RightColumn;
int top = object->TopCell;
int bottom = object->BottomCell;

// embedded object file type, for instance "doc" for a Word document
_bstr_t sFileExtension = object->Filetype;

// embedded object extraction
object->ExtractToFile(L"output\\embedded.doc");

// picture file type, for instance "emf" for a Windows enhanced metafile picture
_bstr_t sPictureFileExtension = object->SnapshotFiletype;

// picture file extraction
object->ExtractSnapshotToFile(L"output\\embedded.emf");

OLE objects insertion

Any OLE object can be inserted in a spreadsheet using xlsgen.

xlsgen relies on the corresponding OLE server for instantiating the OLE objects so make sure the corresponding OLE server to your OLE object is installed on the computer where xlsgen runs. For example, if you would like to insert a PDF document in a spreadsheet, a PDF reader including an OLE server must be installed on that computer. Usually any PDF reader will be fine. Likewise, if you would like to insert a Word document in a spreadsheet, a licensed version of Word must be installed on that computer.

xlsgen makes it straight forward to insert OLE objects. The filename and location details are what is only needed. In addition to this, xlsgen exposes a dual API : one made with cells and offsets, another made of pixels, so you can choose the one which is the most compatible with your environment.

Here is an exemple of PDF document insertion :

Java code


XlsWorksheet worksheet = workbook.AddWorksheet( "Sheet1" );

XlsOLEObject oleObject = worksheet.getOLEObjects().NewOLEObjectInPixels("C:\\tmp\\cylinders.pdf", 
                                              100, //top
                                              50,  //left
                                              350, //bottom
                                              658 //right
                                              );

VB code


Dim worksheet As IXlsWorksheet 
Set worksheet = workbook.AddWorksheet( "Sheet1" )

Dim oleObject As IXlsOLEObject 
Set oleObject = worksheet.OLEObjects.NewOLEObjectInPixels("C:\tmp\cylinders.pdf", _
                                              100, _ 'top
                                              50,  _ 'left
                                              350, _ 'bottom
                                              658 _ 'right
                                              )

C# code


IXlsWorksheet worksheet = workbook.AddWorksheet( "Sheet1" );

IXlsOLEObject oleObject = worksheet.OLEObjects.NewOLEObjectInPixels("C:\\tmp\\cylinders.pdf", 
                                              100, //top
                                              50,  //left
                                              350, //bottom
                                              658 //right
                                              );

C++ code


xlsgen::IXlsWorksheetPtr worksheet = workbook->AddWorksheet( L"Sheet1" );

xlsgen::IXlsOLEObjectPtr oleObject = worksheet->OLEObjects->NewOLEObjectInPixels(L"C:\\tmp\\cylinders.pdf", 
                                              100, //top
                                              50,  //left
                                              350, //bottom
                                              658 //right
                                              );