Class property does not map to Column Header with trailing space about linqtoexcel

[paulyoder]

Try using the property to column
mappinghttps://github.com/paulyoder/LinqToExcel#property-to-column-mappingfeature.

var excel = new ExcelQueryFactory("excelFileName");
excel.AddMapping(x => x.State, "Providence"); //maps the
"State" property to the "Providence" column
excel.AddMapping("Employees", "Employee Count"); //maps the
"Employees" property to the "Employee Count" column

var indianaCompanies = from c in excel.Worksheet()
where c.State == "IN" && c.Employees > 500
select c;

On Tue, May 29, 2012 at 2:56 AM, danielc777888 <
[email protected]

wrote:

I have a class with property "AVG_BAL", in the excel file the header name
is "AVG_BAL " with a trailing space.
AVG_BAL property on object result is always null. I do no have control
over the file therefore I cannot change the column name.

Is there a way for LinqToExcel to ignore any leading or trailing spaces?

---

Reply to this email directly or view it on GitHub:
#9

[BenPG]

I had this same issue... source software exports Excel with trailing spaces, but if a user saves from excel, no trailing spaces.

Resolved with this:

            var worksheetName = excel.GetWorksheetNames().FirstOrDefault();

            if(worksheetName != null)
            {

                //For Some columns, source software exports them with a column name that has a trailing space.
                //If these are opened in Excel and then saved, the trailing space is removed
                //Ensure the mappings account for both instances.
                foreach(var colName in excel.GetColumnNames(worksheetName))
                {
                    switch (colName)
                    {
                        case "Vehicle Id ":
                            excel.AddMapping<Ticket>(t => t.VehicleId, "Vehicle Id ", d => d.ToString());//column in excel file has trailing space
                            break;
                        case "Vehicle Id":
                            excel.AddMapping<Ticket>(t => t.VehicleId, "Vehicle Id", d => d.ToString());
                            break;
                        case "Id ":
                            excel.AddMapping<Ticket>(t => t.VendorId, "Id ");//column in excel file has trailing space
                            break;
                        case "Id":
                            excel.AddMapping<Ticket>(t => t.VendorId, "Id");
                            break;
                        default:
                            continue;
                    }


                }
            }

Would be cool if there were an option to trim column names

[Hussain08]

Thank you so much guys for such an elegant library!

@danielc777888 This is the workaround I added to circumvent the leading and trailing spaces in the column mapping.

private ExcelQueryFactory MapSpreadsheetColumnsToInvoiceModel(ExcelQueryFactory excel)
{
    var columnNames = excel.GetColumnNames(WorkSheetName).ToList();
    excel.AddMapping<InvoiceModel>(x => x.Quantity, columnNames.First(x => AreStringsCaseInsensitiveEqual("Quantity", x.Trim())));
    excel.AddMapping<InvoiceModel>(x => x.Changes, columnNames.First(x => AreStringsCaseInsensitiveEqual("Changes", x.Trim())));
    excel.AddMapping<InvoiceModel>(x => x.VendorNumber, columnNames.First(x => AreStringsCaseInsensitiveEqual("Vendor Number", x.Trim())));

    return excel;
}

private bool AreStringsCaseInsensitiveEqual(string val1, string val2)
{
    return string.Equals(val1, val2, StringComparison.OrdinalIgnoreCase);
}