Sylvan.Data.Ods 0.1.0-b0001

Sylvan.Data.Ods

A cross-platform .NET library for reading OpenDocument spreadsheet data files.

OdsDataReader provides readonly, row by row, forward-only access to the data. It provides a familiar API via DbDataReader, which is ideal for accessing rectangular, tabular data sets.

The library is a purely managed implementation with no external dependencies.

If you encounter any issues while using this library, please report an issue in the github repository. Be aware that I will be unlikely to investigate any issue unless an example file can be provided reproducing the issue.

Installing

Sylvan.Data.Ods Nuget Package

Install-Package Sylvan.Data.Ods

OdsDataReader

OdsDataReader derives from DbDataReader, so it exposes an API that should be familiar for anyone who has worked with ADO.NET before. The field accessors allow reading data in an efficient, strongly-typed manner: GetString, GetInt32, GetDateTime, GetBoolean, etc.

The GetOdsDataType method allows inspecting the native Ods data type of a cell, which may vary from row to row. FieldCount, returns the number of columns in the header row, and doesn't change while processing each row in a sheet. RowFieldCount returns the number of fields in the current row, which might vary from row to row, and can be used to access cells in a "jagged", non-rectangular file.

Reading Data Manually

The OdsDataReader provides a forward only, row by row access to the data in a worksheet. It allows iterating over sheets using the NextResult() method, and iterating over rows using the Read() method. Fields are accessed using standard accessors, most commonly GetString(). GetString() is designed to not throw an exception, except in the case that a cell contains a formula error.

using Sylvan.Data.Ods;

// OdsDataReader derives from System.Data.DbDataReader
using OdsDataReader edr = OdsDataReader.Create("data.ods");

do 
{
	var sheetName = edr.WorksheetName;
	// enumerate rows in current sheet.
	while(edr.Read())
	{
		// iterate cells in row.
		// can use edr.RowFieldCount when sheet contains jagged, non-rectangular data
		for(int i = 0; i < edr.FieldCount; i++)
		{
			var value = edr.GetString(i);
		}
		// Can use other strongly-typed accessors
		// bool flag = edr.GetBoolean(0);
		// DateTime date = edr.GetDateTime(1);
		// decimal amt = edr.GetDecimal(2);
	}
	// iterates sheets
} while(edr.NextResult());

Bind Ods data to objects using Sylvan.Data

The Sylvan.Data library (a separate nuget package) includes a general-purpose data binder that can bind a DbDataReader to objects. This can be used to easily read an Ods file as a series of strongly typed objects.

using Sylvan.Data;
using Sylvan.Data.Ods;

using var edr = OdsDataReader.Create("data.ods");
foreach (MyRecord item in edr.GetRecords<MyRecord>())
{
    Console.WriteLine($"{item.Name} {item.Quantity}");
}

class MyRecord
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? Quantity { get; set; }
    public DateTime Date { get; set; }
}

Converting Ods data to CSV(s)

The Sylvan.Data.Csv library can be used to convert Ods worksheet data to CSV.

using Sylvan.Data.Ods;
using Sylvan.Data.Csv;

using var edr = OdsDataReader.Create("data.ods");

do 
{
	var sheetName = edr.WorksheetName;
	using CsvDataWriter cdw = CsvDataWriter.Create("data-" + sheetName + ".csv")
	cdw.Write(edr);
} while(edr.NextResult());