locked
Reading and Writing Data to/from Excel File RRS feed

  • Question

  • User-1824505924 posted

    I need to read and write data from excel sheet. Firstly for example I need to use the concept of accessing workgroup, sheet and then cells whatever I want.

    For example I need to use cell A1+B1 and put the result in C1

    alternatively I can use the same function above and put data in some other sheet of same excel file.

    Please reply the answer either using C#.NET or asp.net using C#.NET

    Monday, May 16, 2011 11:14 AM

Answers

  • User-1696077569 posted

    try [Sheet1$] instead of [Sheet1]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 17, 2011 4:46 AM

All replies

  • User1043796333 posted

    I don't see any logic in what you're saying. Can you depict more clearly what you want?

    Monday, May 16, 2011 3:22 PM
  • User-1824505924 posted

    I need to read and write data to some specific cells in an excel file.
    I want to send some values to cells from front end i.e asp.net web page and after calculation from excel the value should be send again to asp.net

    Example if cell A1 = 10 Cell B1 = 20 then C1 cell should have value of A1 + B1 i.e 30 should be displayed and this cell C1 value should be fetched and displayed in ASP.NET Page or some front end

    Secondly is it possible to use formula

    Monday, May 16, 2011 9:31 PM
  • User1043796333 posted

    I don't get it. Can you "draw" the sequence of your activities, something like a flowchart in words?

    Are you going to be in real time? What's the need of it? Why Excel and not a database?

    I can't think any practical usage for your idea; it seems quite odd to me

    Tuesday, May 17, 2011 1:30 AM
  • User-1696077569 posted

    Breaking your problem into 2 steps..

    1.) Load excel data into dataset

     

     string connstr ="Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\aaa.xls;Extended Properties=Excel 8.0"/> 
            OleDbConnection conn = new OleDbConnection(connstr); 
            string strSQL = "SELECT * FROM [Sheet$]"; 
     
            OleDbCommand cmd = new OleDbCommand(strSQL, conn); 
            DataSet ds = new DataSet(); 
            OleDbDataAdapter da = new OleDbDataAdapter(cmd); 
            da.Fill(ds); 
    

    2.) Add column 1 and column 2 into column 3. FOr this add a new column to datatable and set Expression property

    ds.Tables[0].Columns.Add("Column3");

    ds.Tables[0].Columns["Column3"].Expression = "Col1Name + Col2Name";

    that's it...

     

     

    Tuesday, May 17, 2011 1:54 AM
  • User-1824505924 posted

    It is opening the file but I am unable to read from excel file.

    Can us show the full codes

    Actually

    I need to write and read data from Sheet [some number], then into some specific cells of an excel file and want to display the same in front end interface.

    The code is

    strFilePath = Application.CommonAppDataPath.ToString() + @"\Excel\ITR1_2011_12_R1\sheet1.xlsx";

                string connstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source="+ strFilePath + ";Extended Properties=Excel 8.0";
                OleDbConnection conn = new OleDbConnection(connstr);
                string strSQL = "SELECT * FROM [Sheet1]";
        
                OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(ds);
               
                dataGridView1.DataSource = da;

     

    Please try to help with full codes

    Tuesday, May 17, 2011 4:33 AM
  • User-1696077569 posted

    try [Sheet1$] instead of [Sheet1]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 17, 2011 4:46 AM