C# program that interfaces with MS Excel: READ data from an individual Excel cell

1. Open a new Visual C# .NET windows application. Name the project ReadExcel.
2. In Solution Explorer, right-click the References node and select Add Reference. On the COM tab, select “Microsoft Excel 10.0 Object Library”, and then click OK.
3. Design a form. Add two Buttons and four Textboxes.
4. Set Name property of Button to “btnOpenExcel” and text property to “Open Excel File”.
5. Set Name property of Button to “btnReadExcel” and text property to “Read Excel”.
6. Set Name property of Button to “txtColumn” and text property to “A”.
7. Set Name property of Button to “txtRow” and text property to “2”.
8. Set Name property of Button to “txtSheet” and text property to “1”.
9. Set Name property of Button to “txtValue” and text property to “1200”.
10. From Toolbox expand Dialogs group double click on the “OpenFileDialog”.
11. In the code behind file import the library Microsoft Excel 10.0 Object Library using: using Excel;
12. Before the Form constructor add the following code.

//Add a reference to excel.application

private Excel.Application exc;

//Add a reference to workbooks

private Workbooks workbooks;



13. Add class disposed event handler “Form1_Disposed”. Inside the form disposed event handler add the following code.

if (exc != null)
exc = null;


14. Double click on the Button “btnOpenExcel” and pest the following code into the “btnOpenExcel_Click” event.

try

{

//Creating new excel.application

exc = new Excel.Application();


//Prepare open file dialog to only search for excel files

this.openFileDialog1.FileName = "*.xls";

if (this.openFileDialog1.ShowDialog() == DialogResult.OK)

{

//To get the workbooks collection

Workbooks workbooks = exc.Workbooks;

// Here is the call to Open a Workbook in Excel

// It uses most of the default values

workbooks.Open(openFileDialog1.FileName, 0, true, 5,

"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0,

true, 0, 0);


// Get opened excel file name

label2.Text = openFileDialog1.FileName;

//To make application visible

exc.Visible = true;

}

}

catch (Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}

finally

{

exc = null;

}


15. Double click on the Button “btnReadExcel” and pest the following code into the “btnReadExcel_Click” event.

if (exc != null)

{

try

{

//To get the workbook

_Workbook workbook = workbooks.get_Item(1);

//To get the worksheets collection

Sheets sheets = workbook.Worksheets;

_Worksheet worksheet =

(_Worksheet)sheets.get_Item(Convert.ToInt32(txtSheet.Text));


//To get the value for cell

Range range1 = worksheet.get_Range(txtColumn.Text + txtRow.Text, txtColumn.Text + txtRow.Text);

txtValue.Text = range1.Text.ToString();

}

catch(Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}

}


Press F5 to build and run the project.

16. Click on the Button “Open Excel File” and select an excel file using “OpenFileDialog” window and Click on the Button “Open”. Your selected Excel file will be open.
17. Change the Column, Row and Sheet of TextBox(es) and Click on the Button “btnReadExcel”. In the textbox “txtValue” you will see new value added from your Excel file defined by the Form Textbox(es) (Column, Row, Sheet).

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working