Windows Vista.NET 1.0C++/CLIDBAWindows 2003Win32Visual Studio 2008.NET 1.1.NET 3.0Windows 2000Windows XP.NET 2.0.NET 3.5C# 2.0C# 3.0Visual StudioWindows.NETC#
C#: Read and Write Excel (*.xls and *.xlsx) Files Content without Excel Automation (using NPOI and ADO.NET)
2 ways to read and write data to *.xls and *.xls files and display it on DataGridView.
- Download Read___write_XLS_via_NPOI___display_in_GRID - 605.8 KB
- Download Read___write_XLS_via_ADO.NET___display_in_GRID - 47.7 KB
- Download Read___write_XLSX_via_NPOI___display_in_GRID - 1.4 MB
- Download Read___write_XLSX_via_ADO.NET___display_in_GRID - 64 KB
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)
{
// connect to xls file
// NOTE: it will be created if not exists
conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" +
"Data Source=" + Application.StartupPath + "\\test.xls;" +
"Extended Properties=Excel 8.0");
conn.Open();
// create a sheet "Sheet1" if not exists
// NOTE: no "id" field needed
// WARNING: spaces in sheet's name are supported if names are in [] (automatically replace with _)
// spaces in column names NOT supported with OleDbCommandBuilder!
try
{
string cmdText = "CREATE TABLE [Sheet 1] (text_col MEMO, int_col INT)";
using (OleDbCommand cmd = new OleDbCommand(cmdText, conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
// get sheets list into combobox
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);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
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)
{
// connect to xls file
// NOTE: it will be created if not exists
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
{
}
}
}
// create a sheet "Sheet1" if not exists
// NOTE: no "id" field needed
// WARNING: spaces in sheet's name are supported if names are in [] (automatically replace with _)
// spaces in column names NOT supported with OleDbCommandBuilder!
try
{
string cmdText = "CREATE TABLE [Sheet 1] (text_col MEMO, int_col INT)";
using (OleDbCommand cmd = new OleDbCommand(cmdText, conn))
{
cmd.ExecuteNonQuery();
}
}
catch { }
// get sheets list into combobox
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);
}
// show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell
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; // InternalWorkbook
using NPOI.HSSF.UserModel; // HSSFWorkbook, HSSFSheet
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)
{
// create xls if not exists
if (!File.Exists("test.xls"))
{
wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());
// create sheet
sh = (HSSFSheet)wb.CreateSheet("Sheet1");
// 3 rows, 2 columns
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);
}
}
// get sheets list from xls
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)
{
// clear grid before filling
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
// get sheet
sh = (HSSFSheet)wb.GetSheet(comboBox1.SelectedItem.ToString());
int i = 0;
while (sh.GetRow(i) != null)
{
// add necessary columns
if (dataGridView1.Columns.Count < sh.GetRow(i).Cells.Count)
{
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
dataGridView1.Columns.Add("", "");
}
}
// add row
dataGridView1.Rows.Add();
// write row value
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
var cell = sh.GetRow(i).GetCell(j);
if (cell != null)
{
// TODO: you can add more cell types capability, e. g. formula
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; // File.Exists()
using NPOI.XSSF.UserModel; // XSSFWorkbook, XSSFSheet
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)
{
// create xls if not exists
if (!File.Exists("test.xlsx"))
{
wb = new XSSFWorkbook();
// create sheet
sh = (XSSFSheet)wb.CreateSheet("Sheet1");
// 3 rows, 2 columns
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);
}
}
// get sheets list from xlsx
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)
{
// clear grid before filling
dataGridView1.Rows.Clear();
dataGridView1.Columns.Clear();
// get sheet
sh = (XSSFSheet)wb.GetSheet(comboBox1.SelectedItem.ToString());
int i = 0;
while (sh.GetRow(i) != null)
{
// add neccessary columns
if (dataGridView1.Columns.Count < sh.GetRow(i).Cells.Count)
{
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
dataGridView1.Columns.Add("", "");
}
}
// add row
dataGridView1.Rows.Add();
// write row value
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
var cell = sh.GetRow(i).GetCell(j);
if (cell != null)
{
// TODO: you can add more cell types capability, e. g. formula
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.