CodedThought.Core.Data.ExcelSystem 10.0.0

CodedThought.Core.Data.ExcelSystem

A .NET Core Excel and CSV reader and writer for CodedThought.Core.

Requirements

• CodedThought.Core
• DocumentFormat.OpenXml 3.3.0

The package targets net10.0.

Note: Unlike the database providers, this package is not a GenericDataStore provider. It has no connection string, no DatabaseType setting and no dependency on CodedThought.Core.Configuration. It reads and writes files directly.

What Is In The Package

Usage

1. Install the required packages. See requirements above.
2. Annotate the properties of a data aware class with ExcelColumnAttribute.

Note: See the CodedThought.Core package README for more on data aware classes. ToListFromExcel<T> only populates a property that carries both an ExcelColumn and a DataColumn attribute, because it resolves the target type from DataColumnAttribute.ColumnType.

using System.Data;
using CodedThought.Core.Data;
using CodedThought.Core.Data.ExcelSystem;

namespace Sample.Data {

    [DataTable("COUNTRY", "", "dbo")]
    public class Country {

        [ExcelColumn("Country Name", 0)]
        [DataColumn("CountryName", DbType.String, DataColumnOptions.PrimaryKey)]
        public string CountryName { get; set; }

        [ExcelColumn("Region Code", 1)]
        [DataColumn("RegionCode", DbType.String)]
        public string RegionCode { get; set; }

        [ExcelColumn("ISO Code", 2)]
        [DataColumn("CountryCodeISO", DbType.String)]
        public string ISOCode { get; set; }

        [ExcelColumn("Population", 3, typeof(int), "0", ExcelColumnAttribute.ExcelStyleFormats.Numeric)]
        [DataColumn("Population", DbType.Int32)]
        public int Population { get; set; }

        [ExcelColumn(true)]
        public string InternalNotes { get; set; }
    }
}

ExcelColumnAttribute Members

ExcelStyleFormats carries the built in Excel format identifiers: NoStyle, Numeric, NumericTwoDecimals, NumericTenths, NumericTenthsTwoDecimals, Percentage, PercentageTwoDecimals, DateMMDDYYYY, DateDMMMYYYY, DateMMMYYYY and DateMDYYYHMM.

Writing A Workbook

ExcelOpenXmlWriter writes Open XML (.xlsx). Every method is static.

// To a file.
ExcelOpenXmlWriter.CreateExcelDocument(countries, @"C:\Exports\Countries.xlsx");
ExcelOpenXmlWriter.CreateExcelDocument(dataTable, @"C:\Exports\Countries.xlsx");
ExcelOpenXmlWriter.CreateExcelDocument(dataSet, @"C:\Exports\Countries.xlsx");

// To a stream, for example to return from an API action.
string fileName = $"ExcelOutput_{DateTime.Today:yyyyMMddHHmmss}.xlsx";
Stream output = ExcelOpenXmlWriter.CreateExcelDocumentAsStream(model.Countries, fileName);
FileContentResult fcr = new(_common.ConvertToByteArray(output),
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") {
    FileDownloadName = fileName
};

One worksheet is created per DataTable in the DataSet, and DataTable.TableName becomes the worksheet name. Set TableName before writing, because a DataTable built from a list has an empty name. The first row is always written as a header row from the column names.

Note: ExcelOpenXmlWriter has its own ListToDataTable<T> that reflects over every public property and does not read ExcelColumnAttribute. To apply the attribute metadata, build the DataTable with ExcelUtility.ListToDataTable<T> and pass that instead of the list.

Reading A Workbook

ExcelUtility.OpenExcelFileAsDataSet selects the reader from the file extension, .xls for the binary reader and .xlsx or .xlsm for the Open XML reader. The extension must include the leading period. Failures are raised as CodedThoughtException.

DataSet ds = ExcelUtility.OpenExcelFileAsDataSet(@"C:\Imports\Countries.xlsx", hasHeaders: true);
IList<Country> countries = ds.Tables[0].ToListFromExcel<Country>();

For direct control over the reader, use ExcelReaderFactory.

using Stream stream = File.Open(path, FileMode.Open, FileAccess.Read);
using IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream, ReadOption.Loose);
reader.IsFirstRowAsColumnNames = true;
DataSet ds = reader.AsDataSet(convertOADateTime: true, hasHeaders: true);

CreateBinaryReader accepts a ReadOption of Strict or Loose, and a convertOADate flag. CreateOpenXmlReader takes the stream only. Check IsValid and ExceptionMessage on the reader when a read returns no results.

Reading A Delimited File

OpenCSVFileAsDataSet reads any single or multi character delimiter. The non generic overloads type every column as string. The generic overloads resolve each column's type from T, matching header names to ExcelColumnAttribute.ColumnName when hasHeaders is true, and falling back to property order when it is false.

using Stream stream = File.Open(path, FileMode.Open, FileAccess.Read);
DataSet ds = ExcelUtility.OpenCSVFileAsDataSet<Country>(stream, ",", hasHeaders: true);

When a file has no header row, pass hasHeaders and columnCount so the column set can be sized. Both arguments are required on the four parameter overloads.

DataSet ds = ExcelUtility.OpenCSVFileAsDataSet<Country>(stream, ",", false, 4);

File Encoding

Delimited files are read with ExcelUtility.FileEncodingToUse, which is driven by the static ExcelUtility.CurrentFileEncoding property. Set it before reading.

ExcelUtility.CurrentFileEncoding = ExcelUtility.EncodingToUse.ISO;

EncodingToUse supports UTF8 (the default), UTF7 and ISO, where ISO maps to ISO-8859-1.

Note: CurrentFileEncoding is static and therefore process wide. It is not safe to change it concurrently from multiple requests.

Behavior And Limitations

Release Notes

10.0.0-alpha-1