[C#] Open and read Excel file and show value of cell

Tag:[C#] [Excel]
Open and read a excel file and show values of cell Excel.

Microsoft.Office.Interop.Excel assembly need to be referenced when open excel file.

operation for reference

  1. Select "reference" node in solution explorer.
  2. Show popup menu to right button click. and Click "add reference" in popup menu.
  3. Open assembly reference dialog.
  4. Click ".NET" tab in reference dialog and select Microsoft.Office.Interop.Excel in assembly list. Multiple Microsoft.Office.Interop.Excel assembly displays in some environments. In this environment, version 12.0.0.0 and version 14.0.0.0 assembly are installed. New version assembly 14.0.0.0 is refereed.


Code example


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace ExcelHandle
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      if (openFileDialog1.ShowDialog() == DialogResult.OK) {
        textBox1.Text = openFileDialog1.FileName;
      }
    }

    private void button2_Click(object sender, EventArgs e)
    {
      string ExcelBookFileName = textBox1.Text;

      Microsoft.Office.Interop.Excel.Application ExcelApp
        = new Microsoft.Office.Interop.Excel.Application();
      ExcelApp.Visible = false;
      Workbook wb = ExcelApp.Workbooks.Open(ExcelBookFileName,
        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 ws1 = wb.Sheets[1];
      ws1.Select(Type.Missing);
      Range range = ExcelApp.get_Range("A1", Type.Missing);
      if (range != null) {
        var val = range.Value2;
        textBox2.Text += Convert.ToString(val);
      }
      wb.Close(false, Type.Missing, Type.Missing);
      ExcelApp.Quit();
    }
  }
}

Commentary

using Microsoft.Office.Interop.Excel;
Refer Microsoft.Office.Interop.Excel.

Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Create new Excel application object. Specify assembly fullname in WinForm application, because conflict with System.Windows.Froms.Application.
ExcelApp.Visible = false;
Change visible of Excel application. If you set visible to true, Excel application window is displayed. In this case, visible property is set to false, Excel application window is not required.
 

Workbook wb = ExcelApp.Workbooks.Open(ExcelBookFileName,.....)
Open the workbook(Microsoft Excel worksheet file).

Worksheet ws1 = wb.Sheets[1];
ws1.Select(Type.Missing);
Set ws1 to first sheet object of the open workbook. Select this sheet.

Range range = ExcelApp.get_Range("A1", Type.Missing);
Get range. In this case, Get cell "A1" as range.

if (range != null) {
  var val = range.Value2;
  textBox2.Text += Convert.ToString(val);
}
Get value of selected area, selected area is not null, the selection is valid. Displays value in textbox2.


Results

Prepare Excel file. Contents of file is below.


Execute application, select Excel file and click button2. Displays following results.




For C#4.0

For C#4.0, Arguments of Type.Missing can be omitted like the following code.

private void button3_Click(object sender, EventArgs e)
{
  string ExcelBookFileName = textBox1.Text;

  Microsoft.Office.Interop.Excel.Application ExcelApp
    = new Microsoft.Office.Interop.Excel.Application();
  ExcelApp.Visible = false;
  Workbook wb = ExcelApp.Workbooks.Open(ExcelBookFileName);

  Worksheet ws1 = wb.Sheets[1];
  ws1.Select(Type.Missing);
  Range range = ExcelApp.get_Range("A1", Type.Missing);
  if (range != null) {
    var val = range.Value2;
    textBox2.Text += Convert.ToString(val);
  }
  wb.Close(false);
  ExcelApp.Quit();
}

Register date :2011-10-21    Last update :2017-01-17
Tag:[C#] [Excel]
JapaneseEnglish
プライバシー    iPentecについて
iPentec all rights reserverd. (ISDC)