Reading Excel File using Excel Object and C#

[Eng]

Can anyone give an example on reading an excel file using the Excel Object and C#? Most of the example I have seen are in VB.Net and I am unable to convert my VB.Net code into C#. It may not be as simple as translating the VB.Net code line by line (which I am doing) so, I am wondering if there is any tibbit on doing it in C# from all those C# experts out there.

 

All I am trying to do is...using the Excel Object,

1.) Open the workbook (identified by the file path name)

2.) Locate a worksheet (specified by a name)

3.) Read in each cell row-by-row into a local variables (some are strings and some are integer)

 

I had no problem doing it in VB.Net, but yet I am having a lot of problems doing it in C#. It could be the fact that I am only 4 days new to the C# world --- but, can it really be difficult?

 

Any help will be appreciated.

[akiaz]

Please post specific code that you are having trouble with, or you suspect is not working correctly. It may be something really simple that you are just overlooking, as that is easy to do when converting.

[Eng]

Since I am a newbie to C#, I can't say it is really simple or I am just totally way in the left field:-(

 

Here is my code...(all the comments are included within)

////////////////////////////////////////////////////////////////

object oMissing = System.Reflection.Missing.Value; //I found this necessary from another code snippet. Don't know why?

Excel.Application objExcel = new Excel.Application();

Excel.Workbook objBook;

Excel.Worksheet objSheet;

 

DataTable dtbl;

 

//Open the Excel File ("C:\test.xls")

objBook = (Excel.Workbook) objExcel.Workbooks.Open("C:\\test.xls", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

 

//Locate the worksheet ("Field Reps")

objSheet = (Excel.Worksheet) objBook.Worksheets.get_Item("Field Reps");

 

//set up the DataTable Schema to read in the excel cells into datatable

dtbl = dsExcelFiles.Tables.Add("MyTestTable");

dtbl.Columns.Add("Date_Equipment_Installed", typeof(string));

dtbl.Columns.Add("HHLD_ID",typeof(int));

dtbl.Columns.Add("Set_ID", typeof(Int16));

 

//Settin local variables

string Date_Equipment_Installed;

int HHLD_ID;

Int16 Set_ID;

 

//Scan first 20 rows of excel

for (int R=1; R <= 20; R++)

{

//...but skip first 2 rows of header and start reading into the dataset at Row 3

if (R >= 3)

{

//...also QUIT reading into the dataset as soon as the 2nd column is blank.

if (objSheet.Cells[R,2].ToString() == "")

{

Console.WriteLine("Exiting the FOR Loop at Row: {0}", R);

break;

}

 

//Set info of each cell's value into each localvariables so that I can use them to load the datarow

Date_Equipment_Installed = objSheet.Cells[R,2].ToString();

HHLD_ID = (int)objSheet.Cells[R,3]; //*** I am getting Casting error at this point!!!!!

Set_ID = (Int16) objSheet.Cells[R,4].ToString();

 

 

//Building the array using the above variables and load the array into the dataset as a whole row

object[] aMainSets = new object[] {Date_Equipment_Installed, HHLD_ID, Set_ID};

 

//Load the Main Set Data as a row in DataTable

dtbl.LoadDataRow(aMainSets, false);

 

}

}

/////////////////////////////////////////////////////////////////////////////

 

Things that I noticed are:

1.) If I have the Integer casting on "HHLD_ID", it gives me the type casting error. And I don't know much about the type casting...coming from VB world.

 

2.) When I re-set the HHLD_ID to just read in as (string), it looks like it goes throught he EXCEL File, but it doesn't recognize that fact that, at some point, column 2 is missing at Row 17 and it needs to stop reading...yet it doens't stop...it keeps going on and on. (maybe I should set the ""to NULL)??

 

3.) As I am debugging as the code is reading in each row, I don't see the actual values actually being set to the local variables; instead if I hover over the "HHLD_ID" local variable, it shows its value as "System.__ComObjectSystem.__ComObject" (HUH?)

 

So that is what's happening in a nutshell:-)

[akiaz]

try this...

 

The .Cells method is returning an object that can't directly give you the value of the cell. Instead, cast it as a Range object and then get use the value property like this:

 

Excel.Range r = (Excel.Range)objSheet.Cells[R,3];

HHLD_ID = Convert.ToInt32(r.Value.ToString());

 

that should solve your casting errors. The same needs to be done in your check for end of data. The line:

 

if (objSheet.Cells[R,2].ToString() == "")

 

will never be true because it returns the name 'System.__ComObject' everytime. Do the same method of casting to a range object and checking the value property.

 

You could also use the get_Range method instead but it requires excel type cell names (eg. "C3") like this:

 

HHLD_ID = Convert.ToInt32(objSheet.get_Range("C" + R, "C" + R).Value);

 

Hopefully this will help you complete your project!

[Eng]

Thank you so very much!! I would have never figured that out. That was quite different from the way I got it done in VB. Your response was a great help!!

 

Aside from this specific question, I do have one curiosity. One of the code I saw this evening has declared the "Workbook" and "Worksheet" variables using the following syntax:

Excel._Workbook objBook;

Excel._Worksheet objSheet;

What is the difference between the above declaration and how I declared mine?( I don't use the " _ " <underscore> Class.) I am confused that C# exposed more than VB.NET when handling woth Excel object. I just want to know why these " _ " stuffs are shown in C#. I know it has probably something to do with Object Oriented logic behind it.

 

Also, since you were a big help with my problem, I would like your expert opinion. Is there any books you could recommend in getting me up to speed with C#?

 

Thanks again!

 

WHat is the difference between using

[Eng]

Hi akiaz,

Sorry to bother you again...but, you are the only one who have helped me with this matter. I tried your suggestion and now, I finally get the actual cell values being read in to my local variables. The only problem is I still cannot check for the empty value as (if (((Excel.Range) objSheet.Cells[R,2]).Value.ToString() == ""), as I would in my VB code. I get the error saying "Object reference not set to an instance of an object."

 

I guess if the cell range that I am referring to is missing, it doesn't set the "Range" Object. I dunno...I am trying to make sense of this:-) Umm...anyway, I could catch that with "NullReferenceException" block. But, is there any other way you could think of that maybe better than throw a "NullReferenceException"? How else can I check that cell value?

 

Thanks in advance!

[akiaz]

You are very welcome, I'm glad I could of helped. I am not too familiar with excel and COM interoperability that occurs when doing this, so I cannot tell you the difference with Excel.Workbook and Excel._Workbook. Sorry :confused:

 

As far as books, there are a lot of choices and everybody seems to have thier own rather firm opinion on what's best. I personnaly like books by O'Reilly (publisher) as I used to do a lot of Perl programming and that's where I turned to originally. The C# books by this publisher seem to be pretty good (my opinion). I work with other developers who prefer the Wrox Press books and I like them too. Whatever you choose, I do recommend buying from a online discount retailer like http://www.bookpool.com as you can save lots of $$ versus buying from a local bookstore.

 

The final error you mention does makes sense because there is no data to return to your application from the excel file, thus no object gets passed and then you get the object reference not set error. Yeah, the dreaded NullReferenceException block seems to be the way to go there. I also have a personal dislike for Try...Catch exceptions, I just can't think of an alternative here. Of course you could always do a hack and put the string 'END' or something unique in a cell after your excel data and then check for that.

[Eng]

Thanks for your time by answering all my questions. My task this weekend is to get a good C# book and get the ball rolling!! Thanks again!!

[filip]

hi,

 

you could try to use GemBox spreadsheet component. It's very easy to work with it and it's very fast. Here is a little example how you can do it with this component:

 

ExcelFile ef = new ExcelFile();

ef.LoadXls("filename.xls");

ExcelWorksheet ws = ef.Worksheets[0];

MessageBox.Show(ws.Cells["A1"].Value.ToString());

 

Filip