Excel to ODS Conversion

21 May 2018 / 12 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);
  }
}

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
Print 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