CODEWITHSUNDEEP
c#linq-to-excel
Jin Tiger
Jin Tiger

Reputation: 1

LinqToExcel Header Mapping

I am using LinqToExcel to get the content of the excel file out. With header mapping class like the following I can map the property of my class to a column in the excel:

public class Transaction
{
    [ExcelColumn("Trans Id")]
    public string TradeNumber { get; set; }

    [ExcelColumn("Trans Version")]
    public string TransVersion { get; set; }
}

However, sometime the incoming file has different header, for example sometimes it has header "Trans Id" Sometimes it has "Trans ID", the program cannot convert the column when the header is "Trans ID"

Is there a way to make LinqToExcel compare column name in case insensitive mode? Or there is a place to let me override the comparison method of LinqToExcel.

Thanks!

Upvotes: 0

Views: 1525

Answers (2)

kipras
kipras

Reputation: 89

I tried to use the

public void AddTransformation<TSheetData>(Expression<Func<TSheetData, object>> property, Func<string, object> transformation);

part of the library, but that only deals with the value, not the column name.

Not sure if this is the best solution for it, but it worked for me. I tried to find similar ways around it, but if you're unable to control the column names like that

//Get the Header information
//Worksheet title
//List of Columns (can narrow down if you always know the placement)
ExcelQueryFactory HeaderInfo = new ExcelQueryFactory("FILE NAME.xlsx");
List<string> worksheetName = HeaderInfo.GetWorksheetNames().ToList();
IEnumerable<string> columnNames = HeaderInfo.GetColumnNames(worksheetName[0].ToString());

//Get those values that you're looking for.  Pulling in the unedited Excel column name
string TradeNumber_HeaderName = columnNames.Where(a => a.ToUpper().Trim() == "TRANS ID" || a => a.ToUpper().Trim() == "TRANSID").FirstOrDefault() ?? "Trans ID";
string TransVersion_HeaderName = columnNames.Where(a => a.ToUpper().Trim() == "TRANS VERSION").FirstOrDefault() ?? "Trans Version";

//Whatever your new connection is now to , and this will use that column value dynamically.
ExcelQueryFactory ExcelConn = ...
ExcelConn.AddMapping<Transaction>(x => x.TradeNumber, TradeNumber_HeaderName);
ExcelConn.AddMapping<Transaction>(x => x.TransVersion, TransVersion_HeaderName);

Upvotes: 1

Simon Wilson
Simon Wilson

Reputation: 10424

You could define the mapping yourself with:

var excelFile = new ExcelQueryFactory(pathToExcelFile);    
excelFile.AddMapping("Trans Id", "Trans ID");

This is just a suggestion, you would have to create a mapping for each scenario...ughh.

Let us know if the AddMapping works for you.

Upvotes: 0

Related Questions