Skip to content

#518 Allow user defined ReadHeader method to control DataTable column… #577

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
appel1 merged 1 commit into from
Apr 15, 2024
Merged

#518 Allow user defined ReadHeader method to control DataTable column… #577

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
48 changes: 33 additions & 15 deletions src/ExcelDataReader.DataSet/ExcelDataReaderExtensions.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -82,27 +82,45 @@ private static DataTable AsDataTable(IExcelDataReader self, ExcelDataTableConfig
configuration.ReadHeaderRow(self);
}

for (var i = 0; i < self.FieldCount; i++)
if (configuration.ReadHeader != null)
{
if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i))
var dict = configuration.ReadHeader(self);
foreach (var kvp in dict)
{
continue;
var columnIndex = kvp.Key;
var name = kvp.Value;

// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column = new DataColumn(columnName, typeof(object)) { Caption = name };
result.Columns.Add(column);
columnIndices.Add(columnIndex);
}
}
else
{
for (var i = 0; i < self.FieldCount; i++)
{
if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i))
{
continue;
}

var name = configuration.UseHeaderRow
? Convert.ToString(self.GetValue(i), CultureInfo.CurrentCulture)
: null;
var name = configuration.UseHeaderRow
? Convert.ToString(self.GetValue(i), CultureInfo.CurrentCulture)
: null;

if (string.IsNullOrEmpty(name))
{
name = configuration.EmptyColumnNamePrefix + i;
}
if (string.IsNullOrEmpty(name))
{
name = configuration.EmptyColumnNamePrefix + i;
}

// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column = new DataColumn(columnName, typeof(object)) { Caption = name };
result.Columns.Add(column);
columnIndices.Add(i);
// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column = new DataColumn(columnName, typeof(object)) { Caption = name };
result.Columns.Add(column);
columnIndices.Add(i);
}
}

result.BeginLoadData();
Expand Down
11 changes: 10 additions & 1 deletion src/ExcelDataReader.DataSet/ExcelDataTableConfiguration.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,4 @@
namespace ExcelDataReader
namespace ExcelDataReader
{
/// <summary>
/// Processing configuration options and callbacks for AsDataTable().
Expand All @@ -20,6 +20,15 @@ public class ExcelDataTableConfiguration
/// </summary>
public Action<IExcelDataReader> ReadHeaderRow { get; set; }

/// <summary>
/// Gets or sets a callback to allow a custom implementation of header reading.
/// The returned dictionary will be used to construct the resulting DataTable.
/// Each element of the dictionary specifies an index and column name pair.
/// An example use of this would be to combine multiple header rows.
/// NOTE: If this field is set, UseHeaderRow, EmptyColumnNamePrefix, and FilterColumn are ignored.
/// </summary>
public Func<IExcelDataReader, IReadOnlyDictionary<int, string>> ReadHeader { get; set; }

/// <summary>
/// Gets or sets a callback to determine whether to include the current row in the DataTable.
/// </summary>
Expand Down
64 changes: 64 additions & 0 deletions test/ExcelDataReader.Tests/ExcelOpenXmlReaderTest.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -388,5 +388,69 @@ public void GitIssue425()
Assert.That(dataSet.Tables[0].Rows[1].ItemArray[0], Is.EqualTo("text"));
Assert.That(dataSet.Tables[0].Rows[2].ItemArray[0], Is.EqualTo("text text"));
}

[Test]
public void GitIssue518MultipleHeaderRows()
{
using (var reader = OpenReader("Test_git_issue_518"))
{
var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
ReadHeader = self =>
{
var headerNames = new List<string>();

// read first header row
for (var i = 0; i < self.FieldCount; i++)
{
var s = Convert.ToString(self.GetValue(i));
headerNames.Add(s);
}

// append second header row
if (!self.Read()) {
throw new Exception();
}
var result = new Dictionary<int, string>();
for (var i = 0; i < self.FieldCount; i++)
{
var first = headerNames[i];
var second = Convert.ToString(self.GetValue(i));
string name;
if (first.Length == 0)
{
name = second;
}
else if (second.Length == 0)
{
name = first;
}
else
{
name = first + " " + second;
}
if (string.IsNullOrEmpty(name))
{
name = "Column" + i;
}
result.Add(i, name);
}
return result;
}
}
});

var columns = dataSet.Tables[0].Columns;
Assert.That(columns[0].ColumnName.ToString(), Is.EqualTo("ColName1 A"));
Assert.That(columns[1].ColumnName.ToString(), Is.EqualTo("ColName1 B"));
Assert.That(columns[2].ColumnName.ToString(), Is.EqualTo("ColName2 B"));
Assert.That(columns[3].ColumnName.ToString(), Is.EqualTo("FirstOnly"));
Assert.That(columns[4].ColumnName.ToString(), Is.EqualTo("SecondOnly"));
Assert.That(columns[5].ColumnName.ToString(), Is.EqualTo("Another One"));
}
}
}
}
Binary file added test/Resources/Test_git_issue_518.xlsx
View file
Open in desktop
Binary file not shown.