Referencing columns in LinqToExcel using ordinal position

Question

I am in the unusual position of having two different templates for an Excel upload, one that is 'human friendly' and one that is machine generated. As such, the column data headers are different and it is difficult to align them.

As such, I would like to reference the column mappings using the ordinal position rather than the 'name' of the column. Currently I 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 the letters are the column references in Excel.

Is there a way to do this?

score 14 · Accepted Answer · edited Apr 25 '15 at 14:33

14

if you order is 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]);

edited Apr 25 '15 at 14:33

Appulus

18,630
11
38
46

answered Jun 04 '14 at 21:36

COLD TOLD

13,513
3
35
52

This is the one I actually ended up using because it was so close to my original code. You can't use indexers with generic IEnumerables, though. I needed to use columnnames.ElementAt(1) – Bill Sempf Jun 10 '14 at 17:15
2

@BillSempf I know I'm coming late, but to solve the `ElementAt()` issue you can always stick a call to `ToList()` right after that `GetColumnNames()`, no? – s.m. Oct 09 '14 at 07:52
Or you can simply convert IEnumerable to e.g. array. `excel.GetColumnNames("worksheetName").ToArray();` – Tomas Paul Oct 29 '21 at 12:50

score 7 · Answer 2 · answered Jun 04 '14 at 22:07

Using the WorksheetNoHeader method is what you're looking for. Here's a code example:

var students = new List<Student>();
var excel = new ExcelQueryFactory("excelFileName");
var rows = excel.WorksheetNoHeader().ToList();

//Skip the first row since it's 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);
}

score 1 · Answer 3 · answered Jun 04 '14 at 21:31

From what I see, you have a 1 to 1 mapping between the human-friendly column name and the ordinal letter. My suggestion is to try implementing a Dictionary<string,string>, where the key is the name of the column and the value is the column letter in Excel. Of course, the dictionary needs to be initialized only once, so maybe 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 would 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"]);

Referencing columns in LinqToExcel using ordinal position

3 Answers3

Linked