0

Simple question.

I have an excel sheet that I want to use as a database. I use linq-to-excel and it works wonderfully except it only works if the header row is the first row in the sheet and the spreadhseets I need to run on have other (important to the owners) data in the first 7 rows with the header row appearing in the 8th row.

What's the best way I can cut out these first rows through C# temporarily, so I can run my program and then re-insert them back in place after I've changed whatever records/columns/etc I needed to?

varocarbas
  • 12,354
  • 4
  • 26
  • 37
ConnorU
  • 1,379
  • 2
  • 15
  • 27
  • If you want full control on the communication with Excel perhaps you should consider other alternatives (for example: Interop). In any case, what you say sounds more like a restriction derived from the way in which you wrote your code than an absolute limitation of linq. Can you please post your code? – varocarbas Oct 01 '13 at 16:42
  • I'd love to use MS Office Interop but something glitched when I tried installing the package it's in. It won't install now and the references aren't there to use when I look for them. :( – ConnorU Oct 01 '13 at 17:47
  • You don't need to install anything to develop, the libraries are added to your VS with the MS Office installation. If the given Office version is 2007 or older, you might have to install a small program (redistributable interop assemblies) but only in the target computer where you want to use the app. – varocarbas Oct 01 '13 at 17:49
  • If they're supposed to, apparently they weren't because I can't find them anywhere. I looked online at the MSDN site and it suggested I install the distributable (which then glitched and won't work)... – ConnorU Oct 01 '13 at 17:57
  • Ok, nevermind, I somehow found them now(I think I may need to take a break). – ConnorU Oct 01 '13 at 18:04
  • 1
    OK... it seems like you are looking in the wrong place: Project -> Add Reference -> COM -> Microsoft Excel (your version) Object Library. And you will be able to execute any of the free available codes (for example: http://support.microsoft.com/kb/302084/en-us). If you have Office 2007, for example, you would have to install the redistributable (http://www.microsoft.com/en-us/download/details.aspx?id=18346) but only in the target machine. For office 2010 and newer, you don't need that. – varocarbas Oct 01 '13 at 18:05
  • Ah! excellent! Yes, breaks are a good thing when things are not getting as they should ;) – varocarbas Oct 01 '13 at 18:06

1 Answers1

0

You can use LinqToExcel's WorksheetRange() method to select the specific range of cell's you want to select. This also allows you to use the first row of the range as a header row.

Here's a code example:

var excel = new ExcelQueryFactory("excelFileName");
var indianaCompanies = from c in excel.WorksheetRange<Company>("B3", "G10")
                       where c.State == "IN"
                       select c;

And here's the documentation

Paul
  • 18,349
  • 7
  • 49
  • 56
  • You can, but this method won't let you select which specific worksheet to use, it just uses the first one it finds. I haven't found a way to specify a range inside a specific worksheet with lint-to-excel; it's either one or the other.... var articles = from c in excel.Worksheet
    ("Sheet2") select c;
     – ConnorU Oct 01 '13 at 17:46
  • Also, apparently this assumes the sheet to be used is called "Sheet1" so I get an error telling me it's not a vlaid name (because theres no such named sheet). – ConnorU Oct 01 '13 at 17:51
  • And finally, I don't know how many rows there will be in the excel file, and not even how many columns (though I know a maximum) so how do I define an open range? – ConnorU Oct 01 '13 at 17:55
  • You could do a LINQ Count() to get the number of rows, and you could do a GetColumnNames to get the number of columns, but you would still have to know which row the columns begin in. Regarding being able to do a specify a sheetname when doing a WorksheetRange() call, maybe the author will accept that as a reasonable enhancement. Should be trivial to implement. The major limitation to this tool is that it uses ACE and JET as the underlying data readers, and we all know their limitations. – Robert Tanenbaum Oct 08 '13 at 17:25