Click here to Skip to main content
15,867,686 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel

Work with Excel Documents on the Server

Rate me:
Please Sign up or sign in to vote.
4.70/5 (7 votes)
17 Aug 2016CPOL2 min read 16.4K   16   5
Very often, Excel spreadsheets are using to transfer data between applications. CSV files are also used for the same purposes.

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:
    C#
    Application excel = new Excel.Application();
    Workbook workBook = excel.Workbooks.Add();
    Worksheet sheet = workBook.ActiveSheet;
  2. Create header row:
    C#
    foreach (DataColumn dc in ds.Tables[0].Columns)
    {
        sheet.Cells[1, c + 1] = dc.Caption;
        c++;
    }
  3. Load the data from dataset:
    C#
    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:
    C#
    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:
    C#
    excelConStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source={0};Extended Properties=""Excel 8.0;""", OutputFileName);
  2. Create and populate spreadsheet:
    C#
    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:
    C#
    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:
    C#
    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:
    C#
    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/).

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionFor OLEDB Automation - you should use Marshall.ReleaseComObject Pin
ignatandrei18-Aug-16 2:37
professionalignatandrei18-Aug-16 2:37 
AnswerRe: For OLEDB Automation - you should use Marshall.ReleaseComObject Pin
Michael Ecklin18-Aug-16 3:38
Michael Ecklin18-Aug-16 3:38 
GeneralRe: For OLEDB Automation - you should use Marshall.ReleaseComObject Pin
ignatandrei18-Aug-16 4:15
professionalignatandrei18-Aug-16 4:15 
SuggestionDon't use OLEDB to read Excel documents Pin
wmjordan17-Aug-16 15:28
professionalwmjordan17-Aug-16 15:28 
GeneralRe: Don't use OLEDB to read Excel documents Pin
Michael Ecklin18-Aug-16 3:22
Michael Ecklin18-Aug-16 3:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.