locked
How to read / Write into Excel (.xls) file using C#?

    Question

  • Guys,

    I search how to read and write data into excel file using C# language. And i found more no of results but nothing is working file.

    First i tell my requirement.

    If i want to know the value available in D5 cell then how can i get that value. If i want to write "MSDN" into G6 then how to use the write code to do that.

    I have a requirement like that only, Particular cell i want to go and then read that cell value, and i want to set some value into paricular cell, this is not a sequential way.

    If any option avilable like below then very good for me. like

    i want to get the value of 4th row 5 column value in my excel file. and i want to set some value into some 6th row and 7th column, Actully i want the format instead of (A6) -> (6,1). Bez in coding i can easily increment the value and move into next cell if the (row,column) is available. Othewise it is difficult to process itrations.

    Please provide solution friends,

     

     


    With Regards, DP
    Thursday, July 29, 2010 9:49 AM

Answers

  • Thanks Alan,

    Now i am using Excel 10.0 Com Object and create object for ApplicationClass and access the Excel data content. And now i am not sure about my apps will support Excel 2007 files (.xlsx).

    And i am not sure this is best way of access Excel file contents. And now performance is also little bit low only. I want to improve the performance also. Can u plz suggest any best way

     

     


    With Regards, DP
    • Marked as answer by DP_CHE Thursday, August 05, 2010 5:01 AM
    Wednesday, August 04, 2010 7:10 AM

All replies

  • Hi Vinil,

    Thanks for your reply.  I try the sample. But i have small doubts.

    Can you plase tell what the meaning of myExcelWorksheetToChange.get_Range and System.Reflection.Missing.Value,

    And one more guy mET tells like

    rowIndex = 1; columnIndex = 2;
    excelAPP.Cells(rowIndex, columnIndex) = "Blah"

    Is this possible to do that.

     


    With Regards, DP
    Thursday, July 29, 2010 10:20 AM
  •  

    Try using KbCSV which is a free and easy library written in C# for parsing and writing xml data

    Reading a file is as easy as below

     

    using (var reader = new CsvReader("data.csv"))
    {
      reader.ReadHeaderRecord();
    
      foreach (var record in reader.DataRecords)
      {
        var name = record["Name"];
        var age = record["Age"];
      }
    }

    • Edited by tribal Thursday, July 29, 2010 10:36 AM code to read data
    Thursday, July 29, 2010 10:34 AM
  • Hi Vinil,

    I try the code which from the Marked as answer response.

    But i face the problem, The particular excel file is getting open. But it is not need.

    And we want to give like "A1" to "A7". Is any other way to access (1,2)

     

     


    With Regards, DP
    Thursday, July 29, 2010 10:36 AM
  • Hi Tribal,

    Actullay i want to read / Write options. And this process is not a sequential maner.

    It is a random process, Some time i read data from some cell, and my write some thing to some cell,

    If i have a sequence process then, Oleconnection is help to do that, and we can use queries also. But this one is not suits me.

    Anyway Thanks for your response. KbCSV may useful to some other guy who visit in future. And Thanks, now i know the about KbCSV also.

     


    With Regards, DP
    Thursday, July 29, 2010 12:01 PM
  • Hi DP_CHE,

     

    Welcome to MSDN forum!
    I am interested in helping.

    I think may use ADO.NET to operate your Access!

    I want to give you a link about Ado.net to Access.

    You can get the cell value easy in this way, and then do what you really want.

    http://msdn.microsoft.com/en-us/library/ms971485.aspx

    The main objects you will use in this walkthrough are the OleDbConnection, OleDbCommand, and OleDbDataReader objects, and the DataGrid server control.

     

    Hope these helps, if you have any problems, please feel free to let me know.

     

    Best Regards,

    Alan Chen

    ________________________________________

    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Friday, July 30, 2010 8:54 AM
    Moderator
  • Hi Alan,

    Thanks for show your interest.

    The link which you gave is related to MSAccess, But currently i am using Excel file,

    Actullay i want to read and write the data from excel file using c# based on the row and column index.

    If you find any article related my requirement the plz post the link.

    And the link which you give is really nice who try to access data from MSAccess.

     


    With Regards, DP
    Friday, July 30, 2010 9:04 AM
  • Hi,

     

    You can allways use it like .get_Range("A1", "A7").

    read: http://support.microsoft.com/kb/302084

    Regards,

    Vinil;

    Friday, July 30, 2010 9:55 AM
  • Hi DP,

     

    I am so sorry to misunderstand you.

    I want to introduce the technology of ADO.NET, it can easy to access to your data source. Now you can also use it to access Excel. Please see the link here:

    http://support.microsoft.com/kb/316934

    You can retrieve records from a database by using one of two approaches in ADO.NET: with a Dataset or with a DataReader.

    With ADO.NET, you can insert and update records in a workbook in three ways.

    I am sure you can easy understand these which are shown in the article.

     

    Hope these helps, if you have any problems, please feel free to let me know.

     

    Best Regards,

    Alan Chen

    ________________________________________

    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Monday, August 02, 2010 2:07 AM
    Moderator
  • Hi Alan,

    Thanks for the link. Actullay the sample which you gave in the above URL is working like a Database tables.

    But my Excel is not like a table, I want to randomly Get / Set the data from the excell sheet cells,

    And Dataset is not suitable for my requrement, My requrement is very simple, Just read and set data to the cell based on the index which i gave like,

    excellSheet.Cells[1,2] = "Data 1";      // Set Value

    string val = excellSheet.Cell[4,4].Text.ToString();   / Get Value

    I found one COM componenet for achive this. But i am try to get the best way to read / write data into excel file.

     


    With Regards, DP
    Monday, August 02, 2010 5:40 AM
  • try this http://excelpackage.codeplex.com/ or you could manually try building your own little excel utility using the Open XML 2.0 SDK

    Regards,

    Bhanu

    Monday, August 02, 2010 8:53 AM
  • Hi Bhanu,

    Thanks for the last link from the link, I got one dll and from this dll we can do more excel operations. And I develop one sample and it wont support .xls files. I think it will support .xlsx files only. And i try to disassemble that dll and i can see the original code. But still i can not understand what technique they are using. Technique in the sence, Like Excel 10 COM object or ADO .net Or any other core technique.

    If i know that core technique i can do that for read / write itself. Plz try to get that and let me know bhanu, And i do not know why they are calling as OfficeXML,

     


    With Regards, DP
    Tuesday, August 03, 2010 8:45 AM
  • hi DP_CHE,

     

    I’d like share the link with you. It is relate to Office Open XML Formats

    http://msdn.microsoft.com/en-us/library/bb332058(office.12).aspx

    Hope these helps, if you have any problems, please feel free to let me know.

     

    Best Regards,

    Alan Chen

    ________________________________________

    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Tuesday, August 03, 2010 9:18 AM
    Moderator
  • Thanks Alan,

    Now i am using Excel 10.0 Com Object and create object for ApplicationClass and access the Excel data content. And now i am not sure about my apps will support Excel 2007 files (.xlsx).

    And i am not sure this is best way of access Excel file contents. And now performance is also little bit low only. I want to improve the performance also. Can u plz suggest any best way

     

     


    With Regards, DP
    • Marked as answer by DP_CHE Thursday, August 05, 2010 5:01 AM
    Wednesday, August 04, 2010 7:10 AM
  • Hi DP_CHE,

     

    Glad to hear you’ve solved your problem.

    Discuss the question here is off topic, would you please open a new thread to ask the performance question. In this way you can get more help form this forum!

    By the way, would you please help me to close the post, mark the answer which may help.

    Have a nice day!

    Hope these helps, if you have any problems, please feel free to let me know.

     

    Best Regards,

    Alan Chen

    ________________________________________

    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Thursday, August 05, 2010 2:18 AM
    Moderator
  • Thanks for your guidence Alan

    I created a new post. Please follow the below link

    http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/61c1c92d-95c4-47ec-a7f0-a4fdc47ef34d

     


    With Regards, DP

    • Edited by DP_CHE Thursday, August 05, 2010 5:24 AM Add URL
    Thursday, August 05, 2010 5:01 AM
  • Ok, I know the original question has been answered, but I want to follow up with a modified version of this question: Does anyone know a *performant* way to write to Excel. Using COM interop is *very* slow as is OLEDB/ODBC. I actually took the time to build a utility class to perform CRUD updates on worksheets via OLEDB (thinking it would be faster than COM) only to discover that it was too slow to be useful in my scenario. In that particular case, I ended up using XML data files as a backend storage because it was orders of magnitude faster. I can then, of course, open the XML in Excel. Heck, for all I know, the OLEDB driver uses COM interop to implement the functionality...
    Monday, October 18, 2010 2:32 AM