Work with Excel Documents on the Server

Introduction

Very often, Excel spreadsheets are used to transfer data between applications. CSV files are also used for the same purpose.

There are a number of methods available to work with Excel spreadsheets and CSV files.

Read and Write Excel Documents using OLEDB Automation

You will need to add references to Microsoft.Office.Interop.Excel library located in C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel or similar folder.

Statement using Excel = Microsoft.Office.Interop.Excel; is also required.

The followed steps could be used to create Excel spreadsheet from dataset:

1. Create Excel objects:

   Application excel = new Excel.Application();
   Workbook workBook = excel.Workbooks.Add();
   Worksheet sheet = workBook.ActiveSheet;

2. Create header row:

   foreach (DataColumn dc in ds.Tables[0].Columns)
   {
       sheet.Cells[1, c + 1] = dc.Caption;
       c++;
   }

3. Load the data from dataset:

   foreach (DataRow dr in ds.Tables[0].Rows)
   {
       foreach (DataColumn dc in ds.Tables[0].Columns)
       {
           sheet.Cells[r + 1, c + 1] = dr[dc].ToString();
           c++;
       }
       r++;
       c=0;
   }

4. Save results in desired format:

   workBook.SaveAs(OutputFileName, XlFileFormat.xlExcel8); 
   workbook.Close();

Application can read and write Excel documents in the latest format, or use 2003 or earlier formats of spreadsheet, can work with CSV or tab-separated documents.

However, this method is not recommended. It is slow, heavy and unreliable. Microsoft Excel must be installed on the server. Developer should expect many bad surprises.

Jet OLEDB Provider

Another way to manipulate with spreadsheets and text files is a JET OLEDB provider.

Steps to create Excel file are:

1. Create connection string:

   excelConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
   Data Source={0};Extended Properties=""Excel 8.0;""", OutputFileName);

2. Create and populate spreadsheet:

   using (OleDbConnection conn = new OleDbConnection(excelConStr))
   {
        conn.Open();
        oledbcmd = new OleDbCommand();
        oledbcmd.Connection = conn;
   
        string ssql = "CREATE TABLE [table1] (";
   
        foreach (DataColumn dc in ds.Tables[0].Columns)
           ssql += "" + dc.Caption + " VARCHAR, ";
       
        ssql += ")";
        ssql = ssql.Replace(", )", ")");
   
        oledbcmd.CommandText = ssql;
        oledbcmd.ExecuteNonQuery();
   
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            ssql = "INSERT INTO [table1] VALUES(";
            foreach(DataColumn dc in ds.Tables[0].Columns)
                ssql += "'" + dr[dc].ToString() + "',";
   
            ssql += ")";
            ssql = ssql.Replace(",)", ")");
   
            oledbcmd.CommandText = ssql;
            oledbcmd.ExecuteNonQuery()
        }
        conn.Close()
   }

This code illustrates how to create Excel spreadsheet from dataset. You will get Excel 2003 format. Different formats could be created by updating extended properties of connection string.

Microsoft Access Database Engine Redistributable

This is not a replacement for the Jet OLEDB Provider in server-side applications, but it has many nice features. For instance, you can use the steps below to read CSV file by using SQL statement:

1. Create connection string:

   string CSVConStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;
   Data Source={0};Extended Properties=""Text;HDR=YES;IMEX=2;FMT=Delimited""", 
   System.IO.Path.GetDirectoryName(FileName));

   The first line of CSV file must have a header.

2. Create dataset from CSV file:

   using (excelCon = new OleDbConnection(CSVConStr))
   {
       excelCon.Open();   
       SQL = "SELECT * FROM [" + System.IO.Path.GetFileName(FileName) + "]";
       cmd = new OleDbCommand(SQL, excelCon);
       ds = new DataSet();
       da = new OleDbDataAdapter(cmd);
       da.Fill(ds, "MyData");
   }

Microsoft Access Database Engine 2010 Redistributable (https://www.microsoft.com/en-us/download/details.aspx?id=13255) should be installed on the host box. Excel installation on the server is not required.

ClosedXML Library

Example below shows how to create Excel 2007/2010 spreadsheet from dataset:

1. Add references to ClosedXML library
2. Add using ClosedXML.Excel
3. Add actual code:

   var wb = new XLWorkbook();
   wb.Worksheets.Add(ds);
   wb.SaveAs(OutputFileName);

SDK and documentation are available here.

This SDK works very fast, and provides a nice object oriented way to manipulate the files, and Excel installation on the server is not required.

Another powerful tool you can consider is EPPlus (https://epplus.codeplex.com/).