XlsxHelper 1.0.0

XlsxHelper

XlsxHelper has been crafted with the primary intention of efficiently parsing extensive Excel files. This library is designed to be lightweight, ensuring that it doesn't load the entire dataset into memory all at once. Instead, it adopts a sequential approach, fetching and returning one row per iteration. As a result of this methodology, the memory overhead is minimized.

When to use XlsxHelper

• You need to process large xlsx file.
• You want to read content with very little RAM usage.
• You want full control of mapping/parsing fields to Model.

When to not use XlsxHelper

• You want to read rows in random manner.
• You want to read thing like width of row/column, font size / color etc
• You want to read xls file format.

Example 1

using (var workbook = XlsxReader.OpenWorkbook(filePath))
{
    foreach (var worksheet in workbook.Worksheets)//read all worksheets
    {
        Console.WriteLine($"Worksheet {worksheet.Name}"); //get name of worksheet
        using var worksheetReader = worksheet.WorksheetReader; //get WorksheetReader from worksheet
        await foreach (var row in worksheetReader) //read row from worksheetreader
        {
            Console.WriteLine($"Content of row {row.RowNumber}"); //display current row number
            foreach (var cell in row.Cells) // Display all cell content
            {
                Console.WriteLine($"[{cell.CellValue} at ({cell.ColumnName}{row.RowNumber})]");
            }
            Console.WriteLine($"Content of row {row.RowNumber} ends.");
        }
    }
}

Example 2

using (var workbook = XlsxReader.OpenWorkbook(filePath))
{
    var worksheet = workbook.Worksheets.First();
    bool headerRow = true;
    Dictionary<string, ColumnName> headerLooklup = null;
    await foreach (var row in worksheet.WorksheetReader)
    {
        if (headerRow)
        {
            headerLooklup = ReadHeader(row); //Read all header names from first row
            headerRow = false;
            continue;
        }
        var student = new Student();
        student.FirstName = row[headerLooklup[nameof(Student.FirstName)]].CellValue; //get cell value 
        student.LastName = row[headerLooklup[nameof(Student.LastName)]].CellValue;
        student.Grade = row[headerLooklup[nameof(student.Grade)]].CellValue;
        student.Marks = new Marks
        {
            Biology = row[headerLooklup[nameof(Marks.Biology)]].GetInt32(),
            Chemistry = row[headerLooklup[nameof(Marks.Chemistry)]].GetInt32(),
            Mathematics = row[headerLooklup[nameof(Marks.Mathematics)]].GetInt32(),
            Physics = row[headerLooklup[nameof(Marks.Physics)]].GetInt32()
        };

        //Process student object. 
        //yield return student;
    }

    static Dictionary<string, ColumnName> ReadHeader(Row row) //read header
    {
        Dictionary<string, ColumnName> headerLooklup = new Dictionary<string, ColumnName>();
        foreach (var cell in row.Cells)
        {
            headerLooklup.Add(cell.CellValue, cell.ColumnName);
        }
        return headerLooklup;
    }
}