C#: Read and Write Excel (*.xls and *.xlsx) Files Content without Excel Automation (using NPOI and ADO.NET)

• 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.