xlsgen > overview > OpenOffice/LibreOffice

xlsgen can read OpenOffice files whether they are generated from Apache's OpenOffice, The document foundation's LibreOffice and third parties. By OpenOffice files is meant OpenOffice calc files, which are .ODS files. Of course xlsgen supports the in-memory variants, so it can read a memory buffer storing the contents of an .ODS file.

Support for objects from .ODS files is fairly extensive :

• cells
• formatting (number format, alignment, borders, fill, font, other properties)
• rich strings
• formulas
• defined names and external references
• row and column formatting, merged cells, outline
• conditional formattings (cell, formula, date-based, text-based, time-based, top/bottom, duplicate, error-based)
• advanced conditional formattings from LibreOffice (data bars, iconsets and color scales)
• worksheet properties (selection, hidden, ...)
• freeze / split, page breaks
• header and footer
• hyperlinks
• pictures
• charts (all chart types and subtypes, all formatting properties)
• comments, text boxes, vector shapes, complete with their formatting properties

.ODS files, whether created from The document foundation's LibreOffice or Apache's OpenOffice, are evolving at their own pace. The document foundation's LibreOffice is more advanced than Apache's OpenOffice in a number of ways, most notably conditional formattings, where Apache's OpenOffice has support for only two conditional formatting types (cell-based and formula-based). It's up to the corresponding open source project contributors to try to put the two on par. xlsgen reads as much as it can from those files.

Another scenario that is supported is the update of existing .ODS files using xlsgen, by reading and writing back to an .ODS file or memory buffer, as in :

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

' read and write an existing .ODS file
Dim wbk As IXlsWorkbook
Set wbk = engine.Open "MyFile.ods", "MyFile.ods"

wbk.Close

Set engine = Nothing

xlsgen can also migrate an .ODS file to a .XLSX file for instance and vice versa.

xlsgen can generate native Open Office spreadsheets (.ods files), including all major objects from spreadsheets :

• cells
• formatting (number format, alignment, borders, fill, font, other properties)
• rich strings
• formulas
• defined names
• row and column formatting, merged cells
• conditional formatting, data validation, auto-filters
• advanced conditional formattings from LibreOffice (data bars, iconsets and color scales)
• hyperlinks
• pictures
• charts
• comments, text boxes, vector shapes, complete with their formatting properties
• worksheet properties (selection, hidden, ...)
• freeze / split, page breaks
• pivot tables

It is important to know that all such objects are native. Special attention is to supporting both Apache's OpenOffice and The document foundation's libre office.

The OpenOffice generation is exposed at both the workbook level and at the worksheet level.

xlsgen can be used in a number of scenarios such as the generation of .ods files from nothing (New()), the generation of .ods files from existing .xls or .xlsx files (Open()). And also the in-memory variants.

To generate OpenOffice spreadsheets is straight forward :

XlsEngine engine = new XlsEngine();

XlsWorkbook wbk = engine.Open("C:\\input\\MyExistingFile.xls", "");

// 1) export the second worksheet as Open Office
wbk.getWorksheetByIndex(2).getExport().ExportAsOpenOfficeFormat("C:\\output\\filename.ods");

wbk.Close();

// 2) open an existing spreadsheet and export it as Open Office
XlsWorkbook wbk_ = engine.Open("C:\\input\\MyExistingFile.xls", "C:\\output\\filename.ods");

wbk_.Close();

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("C:\input\MyExistingFile.xls", "")

' 1) export the second worksheet as Open Office
wbk.WorksheetByIndex(2).Export.ExportAsOpenOfficeFormat("C:\output\filename.ods")

wbk.Close

' 2) open an existing spreadsheet and export it as Open Office
XlsWorkbook wbk_ = engine.Open("C:\input\MyExistingFile.xls", "C:\output\filename.ods")

wbk_.Close

Set engine = Nothing

xlsgen.CoXlsEngine engine = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = engine.Open( @"C:\input\MyExistingFile.xls", "" );

// 1) export the second worksheet as Open Office
wbk.get_WorksheetByIndex(2).Export.ExportAsOpenOfficeFormat(@"C:\output\filename.ods");

wbk.Close();

// 2) open an existing spreadsheet and export it as Open Office
XlsWorkbook wbk_ = engine.Open(@"C:\input\MyExistingFile.xls", @"C:\output\filename.ods");

wbk_.Close();

xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk = engine->Open(L"C:\\input\\MyExistingFile.xls", L"" );

// 1) export the second worksheet as Open Office
wbk->WorksheetByIndex[2]->Export->ExportAsOpenOfficeFormat(L"C:\\output\\filename.ods");

wbk->Close();

// 2) open an existing spreadsheet and export it as Open Office
XlsWorkbook wbk_ = engine->Open(L"C:\\input\\MyExistingFile.xls", L"C:\\output\\filename.ods");

wbk_->Close();