Reading/writing excel without excel

Question

Hello everyone,

My question to you is if anyone knows how to read/write excel files in .NET without EXCEL installed on your computer. That essentialy means that I do not want to use any libraries as though Microsoft.Interop.Excel (as in fact they require excel installed on your computer, or better say - the target computer).

I was told that it can be done using some Objects (or sort of low level programming). Plus, there must be some way as otherwise how others would write programs that read/write to excel and do not require any prerequisites? I know that it may be time consuming and will require a lot of effort (at least for me, I am not experienced that much) but I am willing to learn. So, if you have any webpages that could give me some hints in terms of this that would be fantastic.

Pete

---

P.

Answer 1

Hey Petka,

You can read and write to Excel and MS Access files using the free Microsoft Access Database Engine Redistributables. You connect to the documents (including Excel) as if they were databases, using the OleDb flavor of ADO.NET (System.Data.OleDb).

Here are the links for downloading the recent versions of the engine:

• 2010: Microsoft Access Database Engine 2010 Redistributable (32- and 64-bit versions)
• 2007: 2007 Office System Driver: Data Connectivity Components (32-bit only)

Here are some examples of how to connect to an Excel file:

• Accessing Excel files on a x64 machine (works the same way for 32-bit)
• Connection strings for Excel 2007 (works the same way for Excel 2010)

...And a quick example in C# (A Windows Forms app with one DataGridView):

using System.Data.OleDb;

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

        public void fillGridFromExcel()
        {
            DataTable sheet1 = new DataTable();
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = @"c:\temp\example.xlsx";
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
            
            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            {
                connection.Open();
                string selectSql = @"SELECT * FROM [Sheet1$]";
                MessageBox.Show(selectSql);
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
                {
                    adapter.Fill(sheet1);
                    dataGridView1.DataSource = sheet1;
                }
                connection.Close();
            }
        }
    }
}

Even more examples are illustrated in C# at: Reading Excel 2003 and 2007 Files Using OLEDB

Just one tip: You must include the dollar sign ($) after the name of the sheet, and you must enclose the sheet name + $ in brackets in your sql statement:

    "SELECT * FROM [Sheet1$]"; // Correct

    "SELECT * FROM Sheet1$";   // Won't work

    "SELECT * FROM Sheet1";    // Won't work

    "SELECT * FROM [Sheet1]";  // Won't work

 

---

jmh

Answer 2

If you want to go another step though, you could directly read and modify the Excel files. Excel, Word, and Powerpoint 2007+ files (xlsx, docx, pptx) follow the Microsoft-developed but open-standard format called Office Open XML, officially codified as Standard ECMA-376. You can download the entire spec documentation from Standard ECMA-376. The current edition is Edition 3, which is packaged in four parts, downloadable in .zip files.

Otherwise you can start playing around on your own by just changing the extension of an .xlsx file to .zip, open it up in Windows Explorer, and tinker away :)

---

jmh

Answer 3

Hi jmh_gr!

 

Thank you for your answer. I think this is the only understandable and reasonable way to read excel files. I think this way I could also save excel documents, right? It's as if I executed "INSERT INTO....". Do you think it would work ?

I have another question regarding that method you presented (the C# code). What if I don't know what is the sheet1 name? How this one could be detected? My excel files unfortunately will have very different and strange names. In addition I might say that we would always take the first "tab" in excel into consideration.

P.s. Thanks for the 2007 + method of creating excels. Sadly I have to deal with 2003 - , essentially everything but I am starting with the oldest.

 

---

P.

Answer 4

To get the names of sheets (and other metadata): The OleDbConnection.GetSchema overloaded method is your friend! You should read through Retrieving Database Schema Information (ADO.NET) to get a more thorough understanding of how to, well, retrieve database schema information using ADO.NET :) Here's a quick example (in a bare Console app) to get the names of the sheets in an Excel workbook:

class Program
    {
        static void Main(string[] args)
        {
            foreach (string tableName in getSheetNames("c:\\temp\\example.xlsx"))
            {
                Console.WriteLine(tableName);
            }
            Console.ReadKey();
        }

        static List<string> getSheetNames(string excelFile)
        {
            List<string> tableList = new List<string>();
            string excelCS = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";" +
                                         "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

            using (OleDbConnection connection = new OleDbConnection(excelCS))
            {
                connection.Open();
                DataTable tableInfo = connection.GetSchema("Tables");
                foreach (DataRow row in tableInfo.Rows)
                {
                    tableList.Add(row["TABLE_NAME"].ToString());
                }
                connection.Close();
            }
            return tableList;
        }
    }

To connect to Excel 2003: You just have to use the older version of the Access Database Engine: Microsoft.Jet.OLEDB.4.0. Thankfully Jet.OleDb.4.0 is well documented. A good place to start is Microsoft OLE DB Provider for Microsoft Jet. The Jet provider is built in to Windows itself as part of the MDAC (Microsoft Data Access Components) API. If for some reason you need a new copy, you can download it at Microsoft Data Access Components (MDAC) 2.8 SP1. You can find lots of examples around of ADO connection strings for connecting to Excel 2003. From a C#.NET perspective, the only thing you change is the connection string.

Inserting data: You can't generally do something like "INSERT INTO" to push data from memory because the memory of your .NET process is not part of the data source. For that you generally use a the InsertCommand and Update method of a DataAdapter. It may take some time to get used to. Spend some time working through Updating Data Sources with DataAdapters (ADO.NET) to get started.

---

jmh

Answer 5

Reading and Writing Excel Spreadsheets Using ADO.NET C# shows how to insert a literal row using "INSERT INTO .. VALUES (...) "

---

jmh

Answer 6

Hi,

If you are reading / writing XLSX files, you can try OpenXML SDK.

If you want multi-format support (XLS, XLSX, CSV, HTML, ODS) and ease of use, you can try this C# / VB.NET Excel library that doesn't use Excel Automation.

Answer 7

Hi,

You may try this read/write excel files library. The component works without Microsoft Excel or OLE Automation.