how to convert to dto list if header row is not in first row

[dengyakui]

I have a sample sheet to be import, and for some reason, the header row was not in first row( was export by other system), I had tried the sheet.ToDataTable() API, but the dataTable's ToEntities() API not working

 using (var fs = File.OpenRead(fileName))
            {
                // auto detect workbook type, for .et file, got HSSFWokbook
                var workbook = ExcelHelper.LoadExcel(fs);

                // get default sheet
                var sheet = workbook.GetSheetAt(0);
                var dt = sheet.ToDataTable(1);

                var list = dt.ToEntities<TestEntity>();  // the list was not empty, bug every item's value is empty!!
}

below is the sample file

data.zip

[issue-label-bot]

Issue-Label Bot is automatically applying the label question to this issue, with a confidence of 0.84. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

[WeihanLi]

You can specific the headerRowIndex, you can see api document here ToDataTable(this ISheet sheet, int headerRowIndex)

besides, you can convert excel to entity list directly, you can config on model with attribute [Sheet(SheetIndex = 0, StartRowIndex = 2)], HeaderRowIndex now is StartRowIndex-1, so set StartRowIndex to 2,
and you can set via fluent config also, see sample below

FluentSettings.For<TestEntity>().HasSheetConfiguration(0, "SystemSettingsList", 2, true);

By default, the column name is the property name, if not matched, you need to configure the mapping via attribute or fluent api, you can see the samples in the repo.

// attribute
[Column("Setting Name")]
public string Name { get;set; }

// fluent api
var setting = FluentSettings.For<TestEntity>();
setting.Property(_ => _.Name).HasColumnTitle("Setting Name");

[dengyakui]

Thanks for your reply. Having Tried the above configuration and now I can get the entity list directly from a sheet obj~

[WeihanLi]

Cool