Since I am a newbie to C#, I cant 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. Dont 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 cells 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 dont 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 doesnt recognize that fact that, at some point, column 2 is missing at Row 17 and it needs to stop reading...yet it doenst 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 dont 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 whats happening in a nutshell
