Way 1. Using ADO.NET - Microsoft.Jet.OleDb.4.0 (xls) and Microsoft.Jet.ACE.DB.*.0 (xlsx) Providers
Provider Microsoft.Jet.OleDb.4.0 is fully native way to read and write XLS files. It is pre-installed on Windows 2000 and later.
Using this, you can read and write XLS files as databases by using SQL queries.
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 System.Data.OleDb;
namespace Read___write_xls___display_in_grid
{
public partial class Form1 : Form
{
OleDbConnection conn;
OleDbDataAdapter adapter;
DataTable dt;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" +
"Data Source=" + Application.StartupPath + "\\test.xls;" +
"Extended Properties=Excel 8.0");
conn.Open();
try
{
string cmdText = "CREATE TABLE [Sheet 1] (text_col MEMO, int_col INT)";
using (OleDbCommand cmd = new OleDbCommand(cmdText, conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
dt = conn.GetSchema("Tables");
for (int i = 0; i < dt.Rows.Count - 1; i++)
{
if (dt.Rows[i].ItemArray[dt.Columns.IndexOf
("TABLE_TYPE")].ToString() == "TABLE" &&
!dt.Rows[i].ItemArray[dt.Columns.IndexOf
("TABLE_NAME")].ToString().Contains("$"))
{
comboBox1.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")]);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
adapter = new OleDbDataAdapter("SELECT * FROM " + comboBox1.SelectedItem.ToString(), conn);
new OleDbCommandBuilder(adapter);
dt = new DataTable();
adapter.Fill(dt);
dataGridView1.DataSource = dt;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dt);
}
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dt.Columns[e.ColumnIndex].DataType == typeof(Double))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for integers and decimals only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
Also, you can use Microsoft.ACE.OLEDB.*.0 providers for XLSX file reading.
One of these providers is included in Microsoft Office / Microsoft Office Primary Interop Assemblies.
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 System.Data.OleDb;
namespace Read___write_XLSX_via_ADO.NET___display_in_GRID
{
public partial class Form1 : Form
{
OleDbConnection conn;
OleDbDataAdapter adapter;
DataTable dt;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Application.StartupPath + "\\test.xlsx;" +
"Extended Properties=Excel 12.0 Xml");
conn.Open();
}
catch
{
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;" +
"Data Source=" + Application.StartupPath + "\\test.xlsx;" +
"Extended Properties=Excel 14.0 Xml");
conn.Open();
}
catch
{
try
{
conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;" +
"Data Source=" + Application.StartupPath + "\\test.xlsx;" +
"Extended Properties=Excel 15.0 Xml");
conn.Open();
}
catch
{
}
}
}
try
{
string cmdText = "CREATE TABLE [Sheet 1] (text_col MEMO, int_col INT)";
using (OleDbCommand cmd = new OleDbCommand(cmdText, conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
dt = conn.GetSchema("Tables");
for (int i = 0; i < dt.Rows.Count - 1; i++)
{
if (dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_TYPE")].ToString() == "TABLE" &&
!dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString().Contains("$"))
{
comboBox1.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")]);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
adapter = new OleDbDataAdapter("SELECT * FROM " + comboBox1.SelectedItem.ToString(), conn);
new OleDbCommandBuilder(adapter);
dt = new DataTable();
adapter.Fill(dt);
dataGridView1.DataSource = dt;
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
if (adapter == null) return;
adapter.Update(dt);
}
private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
if (dt.Columns[e.ColumnIndex].DataType == typeof(Double))
{
Rectangle rectColumn;
rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false);
Rectangle rectRow;
rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false);
toolTip1.ToolTipTitle = "This field is for integers and decimals only.";
toolTip1.Show(" ",
dataGridView1,
rectColumn.Left, rectRow.Top + rectRow.Height);
}
}
private void dataGridView1_MouseDown(object sender, MouseEventArgs e)
{
toolTip1.Hide(dataGridView1);
}
}
}
But note that Jet and ACE providers are using for Excel files NOT that method of reading, which they use for Access databases, so they reading Excel files NOT as fast as databases.
Way 2. Using NPOI Library
NPOI is open-source third-party library which can be using for xls, xlsx, docx files reading and writing.
It doesn't require Microsoft Excel and any other third-party apps/libraries.
XLS
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 System.IO;
using NPOI.HSSF.Model;
using NPOI.HSSF.UserModel;
namespace Read___write_XLS_via_NPOI___display_in_GRID
{
public partial class Form1 : Form
{
HSSFWorkbook wb;
HSSFSheet sh;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
if (!File.Exists("test.xls"))
{
wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());
sh = (HSSFSheet)wb.CreateSheet("Sheet1");
for (int i = 0; i < 3; i++)
{
var r = sh.CreateRow(i);
for (int j = 0; j < 2; j++)
{
r.CreateCell(j);
}
}
using (var fs = new FileStream("test.xls", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
using (var fs = new FileStream("test.xls", FileMode.Open, FileAccess.Read))
{
wb = new HSSFWorkbook(fs);
for (int i = 0; i < wb.Count; i++)
{
comboBox1.Items.Add(wb.GetSheetAt(i).SheetName);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
sh = (HSSFSheet)wb.GetSheet(comboBox1.SelectedItem.ToString());
int i = 0;
while (sh.GetRow(i) != null)
{
if (dataGridView1.Columns.Count < sh.GetRow(i).Cells.Count)
{
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
dataGridView1.Columns.Add("", "");
}
}
dataGridView1.Rows.Add();
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
var cell = sh.GetRow(i).GetCell(j);
if (cell != null)
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Numeric:
dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.String:
dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).StringCellValue;
break;
}
}
}
i++;
}
}
private void button2_Click(object sender, EventArgs e)
{
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
if (sh.GetRow(i) == null)
sh.CreateRow(i);
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
if (sh.GetRow(i).GetCell(j) == null)
sh.GetRow(i).CreateCell(j);
if (dataGridView1[j, i].Value != null)
{
sh.GetRow(i).GetCell(j).SetCellValue(dataGridView1[j, i].Value.ToString());
}
}
}
using (var fs = new FileStream("test.xls", FileMode.Open, FileAccess.Write))
{
wb.Write(fs);
}
}
}
}
XLSX
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 System.IO;
using NPOI.XSSF.UserModel;
namespace Read___write_XLSX_via_NPOI___display_in_GRID
{
public partial class Form1 : Form
{
XSSFWorkbook wb;
XSSFSheet sh;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
if (!File.Exists("test.xlsx"))
{
wb = new XSSFWorkbook();
sh = (XSSFSheet)wb.CreateSheet("Sheet1");
for (int i = 0; i < 3; i++)
{
var r = sh.CreateRow(i);
for (int j = 0; j < 2; j++)
{
r.CreateCell(j);
}
}
using (var fs = new FileStream("test.xlsx", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
using (var fs = new FileStream("test.xlsx", FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(fs);
for (int i = 0; i < wb.Count; i++)
{
comboBox1.Items.Add(wb.GetSheetAt(i).SheetName);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
sh = (XSSFSheet)wb.GetSheet(comboBox1.SelectedItem.ToString());
int i = 0;
while (sh.GetRow(i) != null)
{
if (dataGridView1.Columns.Count < sh.GetRow(i).Cells.Count)
{
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
dataGridView1.Columns.Add("", "");
}
}
dataGridView1.Rows.Add();
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
var cell = sh.GetRow(i).GetCell(j);
if (cell != null)
{
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Numeric:
dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.String:
dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).StringCellValue;
break;
}
}
}
i++;
}
}
private void button2_Click(object sender, EventArgs e)
{
for (int i = 0; i < dataGridView1.RowCount - 1; i++)
{
if (sh.GetRow(i) == null)
sh.CreateRow(i);
for (int j = 0; j < dataGridView1.ColumnCount; j++)
{
if (sh.GetRow(i).GetCell(j) == null)
sh.GetRow(i).CreateCell(j);
if (dataGridView1[j, i].Value != null)
{
sh.GetRow(i).GetCell(j).SetCellValue(dataGridView1[j, i].Value.ToString());
}
}
}
using (var fs = new FileStream("test.xlsx", FileMode.Open, FileAccess.Write))
{
wb.Write(fs);
}
}
}
}
Post Scriptum
I will structure this page content better, add examples of VB.NET and C++/CLI and add information and examples for ReoGrid library - it's a way to show Excel spreadsheets on form in the manner of Microsoft Excel.
But, a bit later.