LinqToExcel - start with a specific line

Question

I am using the LinqToExcel library . So far, it works fine, except that I need to run the query on a specific line. This is because the excel spreadsheet from the client uses some images and “header” information at the top of the excel file before the actual start of the data.

The data itself will be easy to read and fairly general, I just need to know how to tell ExcelQueryFactory to start from a specific line.

I am aware of the WorksheetRange<Company>("B3", "G10") parameter WorksheetRange<Company>("B3", "G10") , but I do not want to specify the ending line, only where to start reading the file.

Using the latest version of LinqToExcel with C #

Answer 1

I suppose you already solved this, but maybe for others - it looks like you can use

 var excel = new ExcelQueryFactory(path); var allRows = excel.WorksheetNoHeader(); //start from 3rd row (zero-based indexing), length = allRows.Count() or computed range of rows you want for (int i = 2; i < length; i++) { RowNoHeader row = allRows.ElementAtOrDefault(i); //process the row - access columns as you want - also zero-based indexing } 

Not as simple as specifying some Range ("B3", ...), but also a path. I hope this helps at least someone;)

Answer 2

I just tried this code and it seemed to work fine:

 var book = new LinqToExcel.ExcelQueryFactory(@"E:\Temporary\Book1.xlsx"); var query = from row in book.WorksheetRange("A4", "B16384") select new { Name = row["Name"].Cast<string>(), Age = row["Age"].Cast<int>(), }; 

I just returned the rows with the data.

Answer 3

I tried this, works great for my scenario.

 //get the sheets info var faceWrksheet = excel.Worksheet(facemechSheetName); // get the total rows count. int _faceMechRows = faceWrksheet.Count(); // append with End Range. var faceMechResult = excel.WorksheetRange<ExcelFaceMech>("A5", "AS" + _faceMechRows.ToString(), SheetName). Where(i => i.WorkOrder != null).Select(x => x).ToList(); 

Answer 4

Have you tried WorksheetRange<Company>("B3", "G")

Answer 5

Unforunatly, at the moment and iterating over the LinqToExcel framework, there seems to be no way to do this.

To get around this, we require that the client has data that will be loaded into its own "sheet" in the excel document. The title bar in the first line and the data below it. If they need any “metadata”, they will need to include this in another sheet. The following is an example from the LinqToExcel documentation on how to request a separate sheet.

 var excel = new ExcelQueryFactory("excelFileName"); var oldCompanies = from c in repo.Worksheet<Company>("US Companies") //worksheet name = 'US Companies' where c.LaunchDate < new DateTime(1900, 0, 0) select c;