Excel to ODS Conversion
22 Dec 202212 minutes to read
The Open Document Format for Office Applications (ODF) is also known as OpenDocument. It is an XML-based file format for spreadsheets, charts, presentations, and word processing documents. It was developed with the aim of providing an open and XML-based file format specification for office applications. OpenOffice uses ODF format as its default document format. The OpenDocument Spreadsheet (ODS) is the file format for Excel documents. XlsIO supports conversion of XLS/XLSX documents to ODS.
Saving ODS in different platforms
The following code snippet illustrates the creation of an Excel file and exporting it to ODS format.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Text = "Month";
worksheet.Range["B1"].Text = "Sales";
worksheet.Range["A5"].Text = "Total";
worksheet.Range["A2"].Text = "January";
worksheet.Range["A3"].Text = "February";
worksheet.AutofitColumn(1);
worksheet.Range["B2"].Number = 68878;
worksheet.Range["B3"].Number = 71550;
worksheet.Range["B5"].Formula = "SUM(B2:B4)";
//Comments
IComment comment = worksheet.Range["B5"].AddComment();
comment.RichText.Text = "This cell has formula.";
IRichTextString richText = comment.RichText;
IFont blueFont = workbook.CreateFont();
blueFont.Color = ExcelKnownColors.Blue;
richText.SetFont(0, 13, blueFont);
IFont redFont = workbook.CreateFont();
redFont.Color = ExcelKnownColors.Red;
richText.SetFont(14, 20, redFont);
//Formatting
IStyle style = workbook.Styles.Add("Style1");
style.Color = Color.DarkBlue;
style.Font.Color = ExcelKnownColors.WhiteCustom;
worksheet.Range["A1:B1"].CellStyleName = "Style1";
worksheet.Range["A5:B5"].CellStyleName = "Style1";
//Save in ODS format
workbook.SaveAs("Output.ods");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim worksheet As IWorksheet = workbook.Worksheets(0)
worksheet.Range("A1").Text = "Month"
worksheet.Range("B1").Text = "Sales"
worksheet.Range("A5").Text = "Total"
worksheet.Range("A2").Text = "January"
worksheet.Range("A3").Text = "February"
worksheet.AutofitColumn(1)
worksheet.Range("B2").Number = 68878
worksheet.Range("B3").Number = 71550
worksheet.Range("B5").Formula = "SUM(B2:B4)"
'Comments
Dim comment As IComment = worksheet.Range("B5").AddComment()
comment.RichText.Text = "This cell has formula."
Dim richText As IRichTextString = comment.RichText
Dim blueFont As IFont = workbook.CreateFont()
blueFont.Color = ExcelKnownColors.Blue
richText.SetFont(0, 13, blueFont)
Dim redFont As IFont = workbook.CreateFont()
redFont.Color = ExcelKnownColors.Red
richText.SetFont(14, 20, redFont)
'Formatting
Dim style As IStyle = workbook.Styles.Add("Style1")
style.Color = Color.DarkBlue
style.Font.Color = ExcelKnownColors.WhiteCustom
worksheet.Range("A1:B1").CellStyleName = "Style1"
worksheet.Range("A5:B5").CellStyleName = "Style1"
'Save in ODS format
workbook.SaveAs("Output.ods")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Text = "Month";
worksheet.Range["B1"].Text = "Sales";
worksheet.Range["A5"].Text = "Total";
worksheet.Range["A2"].Text = "January";
worksheet.Range["A3"].Text = "February";
worksheet.AutofitColumn(1);
worksheet.Range["B2"].Number = 68878;
worksheet.Range["B3"].Number = 71550;
worksheet.Range["B5"].Formula = "SUM(B2:B4)";
//Comments
IComment comment = worksheet.Range["B5"].AddComment();
comment.RichText.Text = "This cell has formula.";
IRichTextString richText = comment.RichText;
IFont blueFont = workbook.CreateFont();
blueFont.Color = ExcelKnownColors.Blue;
richText.SetFont(0, 13, blueFont);
IFont redFont = workbook.CreateFont();
redFont.Color = ExcelKnownColors.Red;
richText.SetFont(14, 20, redFont);
//Formatting
IStyle style = workbook.Styles.Add("Style1");
style.Color = Color.FromArgb(255, 72, 61, 139);
style.Font.Color = ExcelKnownColors.WhiteCustom;
worksheet.Range["A1:B1"].CellStyleName = "Style1";
worksheet.Range["A5:B5"].CellStyleName = "Style1";
//Initializes FileSavePicker
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "Output";
savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".ods" });
//Creates a storage file from FileSavePicker
StorageFile storageFile = await savePicker.PickSaveFileAsync();
//Saves changes to the specified storage file
await workbook.SaveAsAsync(storageFile, ExcelSaveType.SaveAsODS);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Text = "Month";
worksheet.Range["B1"].Text = "Sales";
worksheet.Range["A5"].Text = "Total";
worksheet.Range["A2"].Text = "January";
worksheet.Range["A3"].Text = "February";
worksheet.AutofitColumn(1);
worksheet.Range["B2"].Number = 68878;
worksheet.Range["B3"].Number = 71550;
worksheet.Range["B5"].Formula = "SUM(B2:B4)";
//Comments
IComment comment = worksheet.Range["B5"].AddComment();
comment.RichText.Text = "This cell has formula.";
IRichTextString richText = comment.RichText;
IFont blueFont = workbook.CreateFont();
blueFont.Color = ExcelKnownColors.Blue;
richText.SetFont(0, 13, blueFont);
IFont redFont = workbook.CreateFont();
redFont.Color = ExcelKnownColors.Red;
richText.SetFont(14, 20, redFont);
//Formatting
IStyle style = workbook.Styles.Add("Style1");
style.Color = Color.DarkBlue;
style.Font.Color = ExcelKnownColors.WhiteCustom;
worksheet.Range["A1:B1"].CellStyleName = "Style1";
worksheet.Range["A5:B5"].CellStyleName = "Style1";
//Saving the workbook as stream
FileStream stream = new FileStream("Output.ods", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream, ExcelSaveType.SaveAsODS);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1"].Text = "Month";
worksheet.Range["B1"].Text = "Sales";
worksheet.Range["A5"].Text = "Total";
worksheet.Range["A2"].Text = "January";
worksheet.Range["A3"].Text = "February";
worksheet.AutofitColumn(1);
worksheet.Range["B2"].Number = 68878;
worksheet.Range["B3"].Number = 71550;
worksheet.Range["B5"].Formula = "SUM(B2:B4)";
//Comments
IComment comment = worksheet.Range["B5"].AddComment();
comment.RichText.Text = "This cell has formula.";
IRichTextString richText = comment.RichText;
IFont blueFont = workbook.CreateFont();
blueFont.Color = ExcelKnownColors.Blue;
richText.SetFont(0, 13, blueFont);
IFont redFont = workbook.CreateFont();
redFont.Color = ExcelKnownColors.Red;
richText.SetFont(14, 20, redFont);
//Formatting
IStyle style = workbook.Styles.Add("Style1");
style.Color = Syncfusion.Drawing.Color.DarkBlue;
style.Font.Color = ExcelKnownColors.WhiteCustom;
worksheet.Range["A1:B1"].CellStyleName = "Style1";
worksheet.Range["A5:B5"].CellStyleName = "Style1";
//Saving the workbook as stream
MemoryStream outputStream = new MemoryStream();
workbook.SaveAs(outputStream, ExcelSaveType.SaveAsODS);
string fileName = "Output.ods";
outputStream.Position = 0;
//Save the document as file and view the saved document
//The operation in SaveAndView under Xamarin varies between Windows Phone, Android, and iOS platforms. Refer to the xlsio/xamarin section for respective code samples.
if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
{
Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView(fileName, "application/msexcel", outputStream);
}
else
{
Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView(fileName, "application/msexcel", outputStream);
}
}
A complete working example to convert Excel to ODS in C# is present on this GitHub page.
Supported and unsupported elements in ODS conversion
Category |
Subcategory |
Supported |
Formatting |
Font settings |
Yes |
Alignments | Yes (Except indent) | |
Number formatting | Yes (Partial) | |
Border settings |
Yes | |
Fill settings |
Yes | |
RGB colors | Yes | |
Cell gradient |
No | |
Cell styles |
Yes | |
Themes | No | |
Conditional formatting |
Planned | |
Hyperlinks | - | Yes |
Cell Comments | Yes | |
Page setup |
Yes | |
[Margin, Page size] |
Yes | |
Page breaks |
No | |
Background image |
No | |
Print settings [Print area, Print titles, Page order] |
No | |
Header/Footer |
Planned | |
Formulas | Yes (Partial) | |
Table Formulas | No | |
Names | Yes (Partial) | |
Group & Outline | Yes | |
Settings | Window Settings | No |
Sheet/Book settings | No | |
Protection | Sheet Protection | No |
Encryption | N/A | |
Hide/Unhide rows/cols |
Yes | |
Copy/Move worksheet |
Yes | |
Image |
No | |
Data Validation | No | |
Tables | Planned | |
PivotTable | No | |
Charts | Planned | |
Drawing | Planned | |
OLE Objects | No |