How can I read an Excel 2010 file in my C# code using a DLL?

Question

UPDATE1:

I am using Excel 2010 and I've searched the web and found thousands upon thousands of ways to do this via win form, console, etc. But I can't find a way to do this via DLL. and none of the sample on-line is complete all in bit and pieces.

UPDATE END

I have looked and goggled but did not get the specific what i am looking for, as show below the excel sample sheet.

i'm looking a way to read and store the each cell data in a variable

i have started something like this:

Workbook workbook = open(@"C:\tmp\MyWorkbook.xls");
IWorksheet worksheet = workbook.Worksheets[0];
IRange a1 = worksheet.Cells["A1"];
object rawValue = a1.Value;
string formattedText = a1.Text;
Console.WriteLine("rawValue={0} formattedText={1}", rawValue, formattedText);

Excel sheet

Possible duplicate of http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp — user1071979, Aug 15 '12 at 22:55
none of the solution works and i am using excel 2010 and i get error on missing an assembly reference, i will update my question soon — Nick Kahn, Aug 16 '12 at 14:27

Hassan Boutougha · Answer 1 · 2012-08-16T19:01:46.507

2

Excel.Sheets sheets = workbook.Worksheets;

Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);


System.Array myvalues;


Excel.Range range = worksheet.get_Range("A1", "E1".ToString());

myvalues = (System.Array)range.Cells.Value;

edited Aug 16 '12 at 19:01

answered Aug 15 '12 at 22:43

Hassan Boutougha

3,871
1
17
17

what is m_Excel ? you have in your first line – Nick Kahn Aug 16 '12 at 15:16
dude, you did not even answer my question and asking me to accept it? lol – Nick Kahn Aug 16 '12 at 15:57
lol, data are store in myvalues array what is dismissed to finish answer? – Hassan Boutougha Aug 16 '12 at 16:01
i asked you what is `m_Excel` – Nick Kahn Aug 16 '12 at 16:05
m_Excel is workbook I will change in code it is what I say when I said "you it is your workbook" – Hassan Boutougha Aug 16 '12 at 19:00

hmqcnoesy · Accepted Answer · 2012-08-16T15:23:03.560

Your code can work with a couple changes.

One thing to remember is that Excel worksheets are 1-based, not 0-based (and use Worksheet instead of IWorksheet):

Worksheet worksheet = workbook.Worksheets[1];

And to get a range, it is easiest to call get_Range() on the worksheet object (and use Range instead of IRange):

Range a1 = worksheet.get_Range("A1");

With those two lines of code changed, your example will work fine.

UPDATE

Here is a "complete" example:

Right-click your project in the solution explorer and click "Add Reference".
Click on the COM tab and sort the list by Component Name. Find "Microsoft Excel 14.0 Object Library" in the list and select it. Click OK.
In the code file where you want this to run, add a using Microsoft.Office.Interop.Excel;
Use this code, which I've modified as little as possible from your example:

var excel = new Microsoft.Office.Interop.Excel.Application();
Workbook workbook = excel.Workbooks.Open(@"C:\tmp\MyWorkbook.xls");
Worksheet worksheet = workbook.Worksheets[1];
Range a1 = worksheet.get_Range("A1");
object rawValue = a1.Value;
string formattedText = a1.Text;
Console.WriteLine("rawValue={0} formattedText={1}", rawValue, formattedText);

score 0 · Answer 3 · answered Aug 15 '12 at 23:12

0

If you don't want to be in a war with com components and registering dlls,

the best way to read excel is Excel Reader for .NET

I have been using it for so long time , and I can say it just works.

and excelReader.IsFirstRowAsColumnNames property makes everything easy. You can play your data within a dataset.

answered Aug 15 '12 at 23:12

Erhan A

691
11
21

I see but still you can use the same opensource compopnent.It implements OpenXml format in itself. – Erhan A Aug 16 '12 at 15:17

How can I read an Excel 2010 file in my C# code using a DLL?

3 Answers3