SpreadSheetTasks 0.4.0

SpreadSheetTasks

The .NET library for fast reading and writing Excel files (.xlsx, .xlsb). Some methods/ideas based on great libraries :

• https://github.com/MarkPflug/Sylvan.Data.Excel
• https://github.com/MarkPflug/Sylvan
• https://github.com/ExcelDataReader/ExcelDataReader

Installation

https://www.nuget.org/packages/SpreadSheetTasks/

Install-Package SpreadSheetTasks

dotnet add package SpreadSheetTasks

Usage

Read

using (XlsxOrXlsbReadOrEdit excelFile = new XlsxOrXlsbReadOrEdit())
{
   excelFile.Open("file.xlsx");
   excelFile.ActualSheetName = "sheet1";
   object[]? row = null;
   while (excelFile.Read())
   {
       row ??= new object[excelFile.FieldCount];
       excelFile.GetValues(row);
   }
}

Write from IDataReader (best performance)

using (XlsbWriter xlsx = new XlsbWriter("file.xlsb"))
{
  xlsx.AddSheet("sheetName");
  xlsx.WriteSheet(dataReader);
}

Write from List

// Write data from Lists
List<string> headers = new List<string> { "col1", "col2" };
List<TypeCode> typeCodes = new List<TypeCode> { TypeCode.Int32, TypeCode.String };
List<object[]> data = [ [1,"x"], [2,"y"], [3,"z"], [4,null], [null,"dda"]];

using (XlsbWriter writer = new XlsbWriter("output.xlsb"))
{
   writer.AddSheet("sheetName");
   writer.WriteSheet(headers, typeCodes, data, doAutofilter: true);
}

Write from DataTable

// Create sample DataTable
DataTable dataTable = new DataTable();
dataTable.Columns.Add("COL1_INT", typeof(int));
dataTable.Columns.Add("COL2_TXT", typeof(string));
dataTable.Columns.Add("COL3_DATETIME", typeof(DateTime));
dataTable.Columns.Add("COL4_DOUBLE", typeof(double));

// Add some data
dataTable.Rows.Add(1, "Text1", DateTime.Now, 1.23);
dataTable.Rows.Add(2, "Text2", DateTime.Now.AddDays(1), 4.56);

// Write to Excel
using (var excel = new XlsxWriter("output.xlsx"))
{
    excel.AddSheet("sheetName");
    excel.WriteSheet(dataTable.CreateDataReader(), doAutofilter: true);
}

More Examples

Write Multiple Sheets

using (var excel = new XlsxWriter("multisheet.xlsx"))
{
    excel.AddSheet("Sheet1");
    excel.WriteSheet(dataReader1, doAutofilter: true);
    
    excel.AddSheet("Sheet2");
    excel.WriteSheet(dataReader2, doAutofilter: true);
}

Write to MemoryStream

using (var memoryStream = new MemoryStream())
{
    var excel = new XlsxWriter(memoryStream);
    excel.AddSheet("Sheet1");
    excel.WriteSheet(dataReader, doAutofilter: true);
    excel.Dispose();

    // Use the MemoryStream
    memoryStream.Seek(0, SeekOrigin.Begin);
    // Save to file if needed
    using (var fileStream = File.Open("output.xlsb", FileMode.Create))
    {
        memoryStream.CopyTo(fileStream);
    }
}

XLSX vs XLSB Format

// format based on the file extension 
using (var excel = ExcelWriter.CreateWriter("file_path"))
{
    excel.AddSheet("Sheet1");
    excel.WriteSheet(dataReader);
}

// XLSX format
using (var excel = new XlsxWriter("file.xlsx"))
{
    excel.AddSheet("Sheet1");
    excel.WriteSheet(dataReader);
}

// XLSB format (better performance)
using (var excel = new XlsbWriter("file.xlsb"))
{
    excel.AddSheet("Sheet1");
    excel.WriteSheet(dataReader);
}

Read Sheet Names

using (var excelFile = new XlsxOrXlsbReadOrEdit())
{
    excelFile.Open("file.xlsx");
    var sheetNames = excelFile.GetScheetNames();
    foreach (var sheetName in sheetNames)
    {
        Console.WriteLine(sheetName);
    }
}

Benchamarks and more

XLSX Read

XLSB Read

(v1 means using UseMemoryStreamInXlsb property, v2 means using UseMemoryStreamInXlsb = false, v1 is faster but uses more memory)

XLSB/XLSX Write

https://github.com/KrzysztofDusko/SpreadSheetTasks