CODEWITHSUNDEEP
c#excelexcel-interop
Jarkid
Jarkid

Reputation: 181

Which way to read a cell from Excel using C#?

I have found two ways those were being used by people if we want to read or write to a cell in an excel.

Declaration:

Excel.Application ExcelApp = new Excel.Application();
Excel.Workbook srcWorkBook = ExcelApp.Workbooks.Open(@"C:\test.xls");
Excel.Worksheet srcWorkSheet = srcWorkBook.Worksheets[1];
Excel.Range srcRange = srcWorkSheet.UsedRange;

Usage 1:

srcWorkSheet.Cells[1, 1].value2 = "foo bar";

Usage 2:

srcRange.Cells[2, 2].Value2 = "foo bar";

Which one is the best way to use ? or it's all fine in .NET ?

Upvotes: 2

Views: 131

Answers (2)

Scott Hannen
Scott Hannen

Reputation: 29312

Either option will work, but they get messy as soon as you have to modify the layout of the worksheet.

If it's an option, I like to add named ranges. This works particularly well if you're using a template where you can modify the "starting" state of the workbook.

Let's say you have a series of columns, and one of them contains the "foo" value. You can add a name like "fooColumn" to the entire column or the top cell in the column.

Then you can get the column number using

worksheet.Range("fooColumn").Column

That protects you from having to manually edit column or row numbers all over the place if you move elements around on your worksheet.

And to second what others have said, use EPPlus instead of Interop. You can still use named ranges. But EPPlus is good and Interop can bring all sorts of pain when COM objects aren't released. Interop automates an application that manipulates the file. EPPlus just works with the file.

Upvotes: 1

Vityata
Vityata

Reputation: 43595

The two ways are very different. 

srcWorkSheet.Cells[1, 1].value2 = "foo bar";

srcRange.Cells[2, 2].Value2 = "foo bar";
  • The 2. usage takes the cell on the 2. row, 2. column of the UsedRange. If the UsedRange in Excel is B2:D5, it would put value at C3:

enter image description here

  • Having a variable named Range as the Range class is really not a great idea. The same goes for WorkSheet and WorkBook.

Upvotes: 5

Related Questions