How to work with excel files in C#

Nitesh Tarbani

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitesh Tarbani16y ago
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.
0
Sam Hobbs16y ago
Previously you said "if I provide directly path not using variable then its working". 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.
0
Nitesh Tarbani16y ago
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 datarangec,string 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, "\t", 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 < Convert.ToInt16(dc); 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.
0
Sam Hobbs16y ago
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?
0
Nitesh Tarbani16y ago
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, "\t", 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.
0
Honey16y ago
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]));
                }

0