CODEWITHSUNDEEP
linqlinq-to-excel
contactmatt
contactmatt

Reputation: 18610

LinqToExcel - Need to start at a specific row

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#

Upvotes: 7

Views: 11609

Answers (5)

podvlada
podvlada

Reputation: 324

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 ;)

Upvotes: 4

Eldho
Eldho

Reputation: 8273

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();

Upvotes: 2

Enigmativity
Enigmativity

Reputation: 117134

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.

Upvotes: 4

contactmatt
contactmatt

Reputation: 18610

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;

Upvotes: 0

Paul
Paul

Reputation: 18597

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

Upvotes: 1

Related Questions