LinqToExcel - Need to start at a specific row

Question

I'm using the LinqToExcel library. Working great so far, except that I need to start the query at a specific row. This is because the excel spreadsheet from the client uses some images and "header" information at the top of the excel file before the data actually starts.

The data itself will be simple to read and is fairly generic, I just need to know how to tell the ExcelQueryFactory to start at a specific row.

I am aware of the WorksheetRange<Company>("B3", "G10") option, but I don't want to specify an ending row, just where to start reading the file.

Using the latest v. of LinqToExcel with C#

score 4 · Answer 1 · answered Sep 03 '12 at 01:28

I just tried this code and it seemed to work just 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 only got back the rows with data.

podvlada · Answer 2 · 2015-03-13T06:32:54.360

4

I suppose that you already solved this, but maybe for others - 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 the way. Hope this helps at least somebody ;)

edited Mar 13 '15 at 06:32

answered Dec 16 '13 at 17:01

podvlada

315
2
10

where you assign the `length` variable? – CodeArtist Mar 12 '15 at 04:07
Well, usually it would be `allRows.Count` or `allRows.Count()` or `allRows.Length` (don't know right now, sorry), if you want to read all lines. Or as a substitution to specifying (non-existing) `Range("B3", "G10")` you have to compute it. `Length` was used just for showing purposes. Back then, there was no simple solution AFAIK... Good point, edited original post. – podvlada Mar 13 '15 at 06:30

score 2 · Answer 3 · answered Jun 19 '14 at 10:45

2

I had tried this, works fine 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();

answered Jun 19 '14 at 10:45

Eldho

7,795
5
40
77

I just found this very useful but wanted to point out you need to add the start row value to the count of records or you miss that number of lines at the end so it should be int _faceMechRows = faceWrksheet.Count()+5; – David Molyneux Apr 13 '17 at 11:38
@DavidMolyneux im not sure about this was 2 yr back, but in my case it was to skip some header records. – Eldho Apr 13 '17 at 12:12
Hi you are correct I have looked in to this a bit more, if you have data in you top rows then it works fine. If like me they are just blank rows then they are not loaded, and the total row count is short by the amount of empty rows. So if you have 5 blank rows and 15 rows of data (20 total rows) the total row count is 15 so your range would go from row 5 to row 15 missing the last 5 rows adding in the skipped rows will bring you back to the correct total. – David Molyneux Apr 18 '17 at 12:08

score 1 · Answer 4 · answered Aug 31 '12 at 18:22

1

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

answered Aug 31 '12 at 18:22

Paul

18,349
7
49
56

Yea, unfortuantly it gives me an error "EndRange argument 'G' is invalid format for cell name" – contactmatt Aug 31 '12 at 21:06

score 0 · Answer 5 · answered Sep 02 '12 at 14:10

Unforunatly, at this moment and iteration in the LinqToExcel framework, there does not appear to be any way to do this.

To get around this we are requiring the client to have the data to be uploaded in it's own "sheet" within the excel document. The header row at the first row and the data under it. If they want any "meta data" they will need to include this in another sheet. Below is an example from the LinqToExcel documentation on how to query off a specific 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;

LinqToExcel - Need to start at a specific row

5 Answers5