Excel files are widely used by people all over the world for various tasks related to organization, analysis, and storage of tabular data. Since excel files are so common, we sometimes need to read data from an excel file or generate a report in excel format
In this article, I’ll show you how to read excel files step by step in C# Windows Form using a very simple example. And you will find all of the code in bottom of article
C# read Excel file in Windows Form
Step 1: Add a reference by right clicking to the “References” in the Solution Explorer.
Step 2: Then we will see the Reference Manager in the panel. We have to add the Microsoft Excel XX.XX Object Library into our project as a reference for exporting to the Excel.
Step 3: After the adding is finished, let’s start the project. Add the namespace to using
1 2 3 | using ExcelApp = Microsoft.Office.Interop.Excel; |
Step 4: Then design the form as following
Step 6: Double click to button1 and add following codes.(Button1_Click event). Don’t forget changing excel file path( line 10 I saved in drive E as E:\readExample.xlsx)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | //Create COM Objects. ExcelApp.Application excelApp = new ExcelApp.Application(); if (excelApp == null) { Console.WriteLine("Excel is not installed!!"); return; } ExcelApp.Workbook excelBook = excelApp.Workbooks.Open(@"E:\readExample.xlsx"); ExcelApp._Worksheet excelSheet = excelBook.Sheets[1]; ExcelApp.Range excelRange = excelSheet.UsedRange; int rows = excelRange.Rows.Count; int cols = excelRange.Columns.Count; dataGridView1.RowCount = rows; dataGridView1.ColumnCount = cols; for (int i = 1; i <= rows; i++) { // read new line for (int j = 1; j <= cols; j++) { //write to cell if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null) dataGridView1.Rows[i - 1].Cells[j - 1].Value = excelRange.Cells[i, j].Value2.ToString(); } } //after reading, relaase the excel project excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); |
After finished it will shown as following
All codes:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | 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 ExcelApp = Microsoft.Office.Interop.Excel; namespace CsharpExcel { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { //Create COM Objects. ExcelApp.Application excelApp = new ExcelApp.Application(); if (excelApp == null) { Console.WriteLine("Excel is not installed!!"); return; } ExcelApp.Workbook excelBook = excelApp.Workbooks.Open(@"E:\readExample.xlsx"); ExcelApp._Worksheet excelSheet = excelBook.Sheets[1]; ExcelApp.Range excelRange = excelSheet.UsedRange; int rows = excelRange.Rows.Count; int cols = excelRange.Columns.Count; dataGridView1.RowCount = rows; dataGridView1.ColumnCount = cols; for (int i = 1; i <= rows; i++) { // read new line for (int j = 1; j <= cols; j++) { //write to cell if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null) dataGridView1.Rows[i - 1].Cells[j - 1].Value = excelRange.Cells[i, j].Value2.ToString(); } } //after reading, relaase the excel project excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } } } |
