Skip to content

Extract data as tables from Excel. Search columns by their header or index number. Sets conditions for extracting the rows.

License

Notifications You must be signed in to change notification settings

JdeJabali/JXLDataTableExtractor

Repository files navigation

JdeJabali.JXLDataTableExtractor

MIT License GitHub Workflow Status (with event) Nuget

  • Extract data as tables or rows from Excel.
  • Search columns by their header or index number.
  • Sets conditions for extracting the rows.
  • Read one or many workbooks.
  • Select what worksheets should be read, by index number or name.
  • Get the result in a DataTable or in a collection of rows.

Demo

string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
string subFolderPath = Path.Combine(path, "Daily store sales");
string[] workbooks = Directory.GetFiles(subFolderPath, "*.xls");

DataTable dataTable = DataTableExtractor
    .Configure()
    .Workbooks(workbooks)
    .SearchLimits(searchLimitRow: 10, searchLimitColumn: 30)
    .Worksheet(worksheetIndex: 0) // Worksheet indexes in Excel are 0-based.
    .ReadOnlyTheIndicatedSheets()
    .ColumnHeader("Description")
    .ColumnHeader("Sales Value")
    .ColumnHeader("Discounts")

    .ColumnHeader("VAT")
        .ConditionToExtractRow(ConditionalsToExtractRow.HasNumericValueAboveZero)
        
    .ColumnIndex(columnIndex: 7) // Column indexes in Excel are 1-based.

    .CustomColumnHeaderMatch(cellValue => cellValue.Contains("Total"))
        .ConditionToExtractRow(cellValue => !string.IsNullOrEmpty(cellValue))
    .GetDataTable();

Documentation

This line

.SearchLimits(searchLimitRow: 10, searchLimitColumn: 30)

applies to all the worksheets to read.


To read each worksheet in each workbook, simply change this line

.ReadOnlyTheIndicatedSheets()

to this

.ReadAllWorksheets()

If after this line for example

.ColumnHeader("Description")

this other line is not specified

.ConditionToExtractRow(condition)

then the row will always be extracted (although for that all other conditions must be met).


You may want to get a collection of rows instead of a DataTable, for that, change

.GetDataTable();

with

.GetExtractedRows();

at the end.


If you need more details about the rows, for example which workbook or sheet they were extracted from, then you might want to use this line at the end.

.GetWorkbooksData();

That's all


Feedback

If you have any feedback, please reach out to me at jdejabali@outlook.com

Releases

No releases published

Packages

No packages published

Languages