ToDataTable throwing exception

[grosch-intl]

I need to compare two spreadsheets in separate workbooks. The columns are in different orders though, so I thought I'd create a data table for each and then compare based on column header name. I tried the below code, but it's throwing an InvalidOperationException saying "Value cannot be null, row: 8, col: 56"

That cell is in fact blank, which is perfectly fine. It's also not the first cell on that line that is blank, so I'm not sure why it is bailing.

public DataTable Get(string path) {
	using var package = new ExcelPackage(new FileInfo(@"....xlsx"));
	var ws = package.Workbook.Worksheets[0];
	var table = ws.Tables[0];

	var opt = ToDataTableOptions.Create(x => {
		x.ExcelErrorParsingStrategy = ExcelErrorParsingStrategy.HandleExcelErrorsAsBlankCells;
		x.FirstRowIsColumnNames = true;
	});
	
	return table.ToDataTable(opt);
}

[swmal]

This is an error message that EPPlus throws because it doesnt allow null values per default. With the current version you can override this behaviour by adding a mapping for each column that needs to allow null values. Use the following method signature:

c.Mappings.Add(int zeroBasedIndexInRange, string columnName, bool allowNull)

(where columnName is the name of your DataTableColumn).

Or if there is a risk that all column values are null which makes it impossible for EPPlus to detect the column data type:

c.Mappings.Add(int zeroBasedIndexInRange, string columnName, Type columnDataType, bool allowNull)

I'm already working on #1068 which partly adresses null handling in ToDataTable. A better solution for this would be to add a boolean flag to ToDataTableOptions, something like this:

var opt = ToDataTableOptions.Create(x => {
		x.ExcelErrorParsingStrategy = ExcelErrorParsingStrategy.HandleExcelErrorsAsBlankCells;
		x.FirstRowIsColumnNames = true;
                x.AllowNull = true;
	});

[swmal]

Have added an AlwaysAllowNull property to ToDataTableOptions which will allow null values in all columns of the exported range.

Note that AllowNull on mappings corresponds to the System.Data.DataColumn.AllowDBNull property.

This will be available in the next versions of EPPlus 6 and EPPlus 7.

For more detailed info on exporting DataTables from spreadsheets see this wiki page

[swmal]

This will be included in the next versions of EPPlus 6 and EPPlus 7. Until then it can be tested via our development nuget feed