Column reference in LinqToExcel using sequence number

Question

I am in an unusual position, having two different templates for loading Excel, one of which is "human-friendly" and the other is machine-friendly. Thus, the headers of the data columns are different and difficult to align.

Thus, I would like to refer to column mappings using the ordinal position, rather than the "name" of the column. I currently have this:

var excel = new ExcelQueryFactory(excelFileName); excel.AddMapping<Student>(x => x.FirstName, "First Name"); excel.AddMapping<Student>(x => x.LastName, "Last Name"); excel.AddMapping<Student>(x => x.LastFour, "Last 4 Student ID"); excel.AddMapping<Student>(x => x.LastDate, "Last Date"); 

and I would like to do something like this:

 var excel = new ExcelQueryFactory(excelFileName); excel.AddMapping<Student>(x => x.FirstName, "A"); excel.AddMapping<Student>(x => x.LastName, "C"); excel.AddMapping<Student>(x => x.LastFour, "G"); excel.AddMapping<Student>(x => x.LastDate, "H"); 

where letters are column links in Excel.

Is there any way to do this?

Answer 1

If you order the same, you can call

  var columnnames= excel.GetColumnNames("worksheetName"); excel.AddMapping<Student>(x => x.FirstName, columnnames[0]); excel.AddMapping<Student>(x => x.FirstName, columnnames[1]); excel.AddMapping<Student>(x => x.LastFour, columnnames[2]); 

Answer 2

Using the WorksheetNoHeader method is what you are looking for. Here is a sample code:

 var students = new List<Student>(); var excel = new ExcelQueryFactory("excelFileName"); var rows = excel.WorksheetNoHeader().ToList(); //Skip the first row since it the header for (int rowIndex = 1; i < rows.Count; rowIndex++) { var row = rows[rowIndex]; var student = new Student(); // row[0] refers to the value in the first column of the row student.FirstName = row[0]; student.LastName = row[1]; student.LastFour = row[2]; student.LastDate = row[3]; students.Add(student); } 

Answer 3

From what I see, you have a mapping from 1 to 1 between the convenient column name and ordinal. My suggestion is to try to execute Dictionary<string,string> , where the key is the column name and the value is the column letter in Excel. Of course, the dictionary needs to be initialized only once, therefore, possibly through a singleton or as a static readonly object.

 //Initialize dictionary Dictionary<string,string> columnHeaders=new Dictionary<string,string>(); columnHeaders.Add("First Name", "A"); columnHeaders.Add("Last Name", "C"); columnHeaders.Add("Last 4 Student ID", "G"); columnHeaders.Add("Last Date", "H"); 

Then your call will be

 var excel = new ExcelQueryFactory(excelFileName); excel.AddMapping<Student>(x => x.FirstName, columnHeaders["First Name"]); excel.AddMapping<Student>(x => x.LastName, columnHeaders["Last Name"]); excel.AddMapping<Student>(x => x.LastFour, columnHeaders["Last 4 Student ID"]); excel.AddMapping<Student>(x => x.LastDate, columnHeaders["Last Date"]);