none
Reading Excel file using C# RRS feed

  • Question

  • Hi,

     

    I want to write a program to access cells in an excel file?

    How can I do that?

     

    Thanks

    Monday, September 5, 2011 6:32 AM

Answers

  •  

    Try this code!

    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open("your excel file path", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Worksheet sheet = (Worksheet)wb.Sheets["Sheet name to read"]; Range excelRange = sheet.UsedRange; foreach (Microsoft.Office.Interop.Excel.Range row in excelRange.Rows) { int rowNumber = row.Row; string[] A4D4 = GetRange("A" + rowNumber + ":F" + rowNumber + "", sheet); } public string[] GetRange(string range, Worksheet excelWorksheet) { Microsoft.Office.Interop.Excel.Range workingRangeCells = excelWorksheet.get_Range(range, Type.Missing); //workingRangeCells.Select(); System.Array array = (System.Array)workingRangeCells.Cells.Value2; string[] arrayS = this.ConvertToStringArray(array); return arrayS; }

     



    Monday, September 5, 2011 6:39 AM
  • Hi DarkSeeker,

    Try this:

    static void Main(string[] args)
    {
      IWorkbook workbook = Factory.GetWorkbook(@"C:\tmp\MyWorkbook.xls");
      IWorksheet worksheet = workbook.Worksheets[0];                 
      IRange a1 = worksheet.Cells["A1"]; 
      object rawValue = a1.Value;    
      string formattedText = a1.Text;             
      Console.WriteLine("rawValue={0} formattedText={1}", rawValue, formattedText);         
    } 

     


    Regards, http://shwetamannjain.blogspot.com
    Monday, September 5, 2011 6:54 AM
  • I want to write a program to access cells in an excel file?

     

    Thanks all for your great help and suggestions.

     

    Hi DarkSeeker,

    Welcome to MSDN Forum.

     

    Usually, there are 2 approaches to access Office Excel files in .Net.

    1.       COM Interop approach as the above said.

    FAQ:  How do I use Excel Automation in .NET

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/df02c6d2-e1b5-4731-bb04-2674aed789de

     

    Add Reference to COM component “Microsoft Excel Object Library” into your project.

    Code sample: Get/Set cell value in Excel Spreadsheet.

    using Microsoft.Office.Interop.Excel;

    object oExcel = new Excel.Application;

    object oBook = oExcel.Workbooks.Open("C:\\Book1.xls");
    object
    oSheet = oBook.Worksheets(1);
    // e.g. Read value in A2 cell
    string cellValue = oSheet.Range("A2").Value;
    // e.g. Change value in A2 cell
    oSheet.Range("A2").Value = "";
    oBook.SaveAs("C:\\Book1.xls", true);
    oExcel.Quit();

    2.       OleDb Data Provider approach

    Retrieve Excel Sheet data and bind into DataGridView control, and then access particular cells via DataGridView.

    using System.Data;

    using System.Data.OleDb;


    OleDbConnection conn = new OleDb.OleDbConnection(("provider=Microsoft.Jet.OLEDB.4.0; " + ("data source=C:\\myData.xls; " + "Extended Properties=Excel 8.0;")));
    // Select the data from Sheet1 of the workbook.
    OleDbDataAdapter ada = new OleDbDataAdapter("select * from Sheet1$]", conn);
    DataSet ds = new DataSet();
    ada.Fill(ds);
    dataGridView1.DataSource = ds.Tables[0].DefaultView;
    conn.Close();

    // Access particular cells on DataGridView.

    DataGridView1(RowIndex, ColumnIndex)

    DataGridView1.Rows(RowIndex).Cells(ColumnIndex)


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 6, 2011 5:25 AM
    Moderator

All replies

  • Hello DarkSeeker,

    Hi,

     

    I want to write a program to access cells in an excel file?

    How can I do that?

     

    Thanks


    try to see those links.

    http://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx#Y300

    http://msdn.microsoft.com/en-us/library/aa168292(v=office.11).aspx

    http://support.microsoft.com/kb/302096/it

    Regards.

     


     

    Carmelo La Monica

    Blog http://community.visual-basic.it/carmelolamonica/

    WordPress http://carmelolamonica.wordpress.com/

    Twitter  http://twitter.com/carmelolamonica


    Monday, September 5, 2011 6:38 AM
  •  

    Try this code!

    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open("your excel file path", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Worksheet sheet = (Worksheet)wb.Sheets["Sheet name to read"]; Range excelRange = sheet.UsedRange; foreach (Microsoft.Office.Interop.Excel.Range row in excelRange.Rows) { int rowNumber = row.Row; string[] A4D4 = GetRange("A" + rowNumber + ":F" + rowNumber + "", sheet); } public string[] GetRange(string range, Worksheet excelWorksheet) { Microsoft.Office.Interop.Excel.Range workingRangeCells = excelWorksheet.get_Range(range, Type.Missing); //workingRangeCells.Select(); System.Array array = (System.Array)workingRangeCells.Cells.Value2; string[] arrayS = this.ConvertToStringArray(array); return arrayS; }

     



    Monday, September 5, 2011 6:39 AM
  • Hi DarkSeeker,

    Try this:

    static void Main(string[] args)
    {
      IWorkbook workbook = Factory.GetWorkbook(@"C:\tmp\MyWorkbook.xls");
      IWorksheet worksheet = workbook.Worksheets[0];                 
      IRange a1 = worksheet.Cells["A1"]; 
      object rawValue = a1.Value;    
      string formattedText = a1.Text;             
      Console.WriteLine("rawValue={0} formattedText={1}", rawValue, formattedText);         
    } 

     


    Regards, http://shwetamannjain.blogspot.com
    Monday, September 5, 2011 6:54 AM
  • I want to write a program to access cells in an excel file?

     

    Thanks all for your great help and suggestions.

     

    Hi DarkSeeker,

    Welcome to MSDN Forum.

     

    Usually, there are 2 approaches to access Office Excel files in .Net.

    1.       COM Interop approach as the above said.

    FAQ:  How do I use Excel Automation in .NET

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/df02c6d2-e1b5-4731-bb04-2674aed789de

     

    Add Reference to COM component “Microsoft Excel Object Library” into your project.

    Code sample: Get/Set cell value in Excel Spreadsheet.

    using Microsoft.Office.Interop.Excel;

    object oExcel = new Excel.Application;

    object oBook = oExcel.Workbooks.Open("C:\\Book1.xls");
    object
    oSheet = oBook.Worksheets(1);
    // e.g. Read value in A2 cell
    string cellValue = oSheet.Range("A2").Value;
    // e.g. Change value in A2 cell
    oSheet.Range("A2").Value = "";
    oBook.SaveAs("C:\\Book1.xls", true);
    oExcel.Quit();

    2.       OleDb Data Provider approach

    Retrieve Excel Sheet data and bind into DataGridView control, and then access particular cells via DataGridView.

    using System.Data;

    using System.Data.OleDb;


    OleDbConnection conn = new OleDb.OleDbConnection(("provider=Microsoft.Jet.OLEDB.4.0; " + ("data source=C:\\myData.xls; " + "Extended Properties=Excel 8.0;")));
    // Select the data from Sheet1 of the workbook.
    OleDbDataAdapter ada = new OleDbDataAdapter("select * from Sheet1$]", conn);
    DataSet ds = new DataSet();
    ada.Fill(ds);
    dataGridView1.DataSource = ds.Tables[0].DefaultView;
    conn.Close();

    // Access particular cells on DataGridView.

    DataGridView1(RowIndex, ColumnIndex)

    DataGridView1.Rows(RowIndex).Cells(ColumnIndex)


    Martin Xie [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 6, 2011 5:25 AM
    Moderator
  • DarkSeeker,

    If you are looking for a third party excel binary reader you can try following. It is fully multi-threaded.

    http://fileforum.betanews.com/detail/Net-Excel-Extractor-API/1313863595/1

    Net Excel Extractor API extracts Excel cell location and text, images, and VBA code into .Net DataSet from Excel 95/97/2003, 2007, 2010.

    Thanks!

    Friday, November 11, 2011 1:13 AM
  • I want to write a program to access cells in an excel file?

     

    Thanks all for your great help and suggestions.

     

    Hi DarkSeeker,

    Welcome to MSDN Forum.

     

    Usually, there are 2 approaches to access Office Excel files in .Net.

    1.       COM Interop approach as the above said.

    FAQ:  How do I use Excel Automation in .NET

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/df02c6d2-e1b5-4731-bb04-2674aed789de

     

    Add Reference to COM component “Microsoft Excel Object Library” into your project.

    Code sample: Get/Set cell value in Excel Spreadsheet.

    using Microsoft.Office.Interop.Excel;

    object oExcel = new Excel.Application;

    object oBook = oExcel.Workbooks.Open("C:\\Book1.xls");
    object
    oSheet = oBook.Worksheets(1);
    // e.g. Read value in A2 cell
    string cellValue = oSheet.Range("A2").Value;
    // e.g. Change value in A2 cell
    oSheet.Range("A2").Value = "";
    oBook.SaveAs("C:\\Book1.xls", true);
    oExcel.Quit();

    Hi Mark.

    When I am using this method, I am getting an error.

    I am trying to import data from excel worksheet in my from and here is the code.

    Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
    Excel.Workbook workbook;
    Excel.Worksheet worksheet;
    Excel.Range range;

    workbook = app.Workbooks.Open(filepath,0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);


    Excel.Sheets sheet= workbook.Worksheets;


    worksheet = (Excel.Worksheet)sheet.get_Item(1);

    range = (Excel.Range)worksheet.Cells[1, 1];

    string strData = range.Value2.ToString(); ---------(1)

    (And some other code . . . )

    I am getting an error showing "Object reference not set to an instance of an object" highlighting the statement marked with (1).

    Please help me with this issue. It is very urgent.

    Thanks

    Reply me at "arpit.jdmaheshwari@gmail.com"
    Wednesday, February 15, 2012 8:37 AM
  • HI,

    The easiest and fastest way to work with Excel in C# it is use 3rd party libraries. You may want to look at Elerium Excel .NET component. It does not depend on third-party libraries and Microsoft Office Automation and works with both xls, xlsx formats. 

    Wednesday, February 20, 2013 5:18 AM
  • Hi Martin,

    While using

    object oExcel = new Excel.Application;

     this code statment, i am getting an error that (),{} or [] is needed after the word Application. Please fix up my problem.

    Regards..

    Friday, April 25, 2014 10:22 AM
  • Be advised that the OleDbDataAdapter(), especially with Jet, has a hard limit, even for Office 2010 and above, of 255 columns.

    PaulVA

    Tuesday, May 20, 2014 2:54 PM
  • If you are just reading and writing data, then I recommend using one of the following open source libraries: NetOffice or NPOI.  Both will allow you to read sheets from workbooks, scan through ranges based on filters of formulae, and then process the results.

    If you are interested in developing more complex, custom Excel addin solutions that are portable across different versions of Office (2007-2016), then I recommend combining Net Office with Excel DNA.  For more information, check out my series of tutorials @ Excel Addin Portability .

    Thursday, February 9, 2017 12:42 PM
  • Where is the definition for this.ConvertToStringArray(array)?
    Monday, June 19, 2017 1:14 PM
  • I doubt they will respond after 6 years, try using Array.ConvertAll.

    string[] stringData = Array.ConvertAll(data, o => o.ToString());


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, June 19, 2017 7:02 PM
    Moderator