How to work with excel files in C#

Question

Hi friends I want to add item in combo box from excel file and Items are column name. So if Anybody knows please help me out. Thanks

Nitesh Tarbani · Answer

Here fp is string variable and code is not working. Actually I am browsing the file and opening the file which have been selected by user. I am storing that path in fp variable. And then i am using this fp variable in .open method. If i use directly path instead of fp then it works. Suppose if there would have written c:\file.xls instead of fp the code surely would have worked. As there is variable(fp) containing path code is not working.

Sam Hobbs · Answer

Previously you said  if I provide directly path not using variable then its workin g. So that means the code you posted is working and whatever is not working is not shown in this thread. I will be very surprised if anyone can solve the problem using the information provided so far. I think the problem must be that there is a problem in the contents of fp so that is why I am emphasizing that there is not likely a problem with anything you have shown here so far.

Nitesh Tarbani · Answer

I am also displaying contents of fp in textbox. Filename and path are correct in that textbox. So I thought that there may be special syntax for doing this. like fp.(something) etc.... following is my code. public void opt(string textranger string textrangec string dataranger string datarangecstring filepath) { string tr = textranger; string tc = textrangec; string dr = dataranger; string dc = datarangec; string fp = filepath; textBox1.Text = fp; Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Range range; string str; int rCnt = 0; int cCnt = 0; xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open(fp 0 true 5   true Microsoft.Office.Interop.Excel.XlPlatform.xlWindows 	 false false 0 true 1 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); range = xlWorkSheet.UsedRange; rCnt =Convert.ToInt16(tr); for (cCnt = Convert.ToInt16(tc); cCnt { str=(string)(range.Cells[2 1] as Excel.Range).Value2; textBox1.Text = str; } } And also wanna know how to convert string into integer. I mean if textbox contain row no of excel sheet and I have to use integer to contain that value then how to load that value from textbox to integer. As text in textbox will be in text format. I have tried convert.ToInt16/32() but i think its not working. Thanks.

Sam Hobbs · Answer

It seems that the problem is that you are not setting fp properly. It does not have the path and filename correctly and it is not possible for any of us to see the problem since we don't know what the value of fp is. Do you still neeed help?

Nitesh Tarbani · Answer

thanks For the reply. I am using string variable as a file name. I dont how to use it ????? I tried the following code but its nt working xlApp = new Excel.ApplicationClass(); xlWorkBook = xlApp.Workbooks.Open( fp  0 true 5   true Microsoft.Office.Interop.Excel.XlPlatform.xlWindows 	 false false 0 true 1 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); where fp is string variable. if I provide directly path not using variable then its working. If someone knows answer to this question then help me out.

Honey · Answer

Hi Nitesh try this code... hope it will help you.... //connection string for 2003 excel sheet OleDbConnection con = new OleDbConnection(Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';); //connection string for 2007 excel sheet OleDbConnection con = new OleDbConnection(Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\test1.xlsx;Extended Properties='Excel 12.0;HDR=YES';); con.Open(); //Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference DataSet myDataSet = new DataSet(); OleDbDataAdapter myCommand = new OleDbDataAdapter(SELECT * FROM [Sheet1$] con); myCommand.Fill(myDataSet); con.Close(); //Travers through each row in the dataset foreach (DataRow myDataRow in myDataSet.Tables[0].Rows) { Object[] cells = myDataRow.ItemArray; comboBox1.Items.Add(Convert.ToString(cells[1])); }