Tagged:

Usually when working with webforms you will find a common requirement of exporting data to excel. There are many approaches to export data to excel of which I know that couple of approaches require Microsoft ACE engine to be installed on the server. In this tutorial we will see about exporting data to excel using OpenXML due to its many advantages.

Advantages of OpenXML

  • Open XML is an open ECMA 376 standard and is also approved as the ISO/IEC 29500 standard that defines a set of XML schemas.
  • Open XML is also used as default format in Microsoft Office Word 2007, Excel 2007 etc.
  • The Open XML file formats use an open standard and are based on well-known technologies: ZIP and XML, so they are useful to developers.
  • Due to the usage of ZIP and XML as basic format of OpenXML it helps in exporting large datasets.
  • Its size is lesser compared to .xls format and also large datasets are opened very quickly.

Using the Code

First of all you will need DocumentFormat.OpenXml.dll which you can get by installing openXML SDK. You can copy DLL to your bin folder and add DLL reference in your project. Namespaces which you will be required to include are:

You can have control over the template by using a template file at runtime.

Advantage of this approach is that you can set custom properties in your Excel template like corporate headers or cell.

Once you have created template excel file save it in your project folder. Then we can start by creating a function that will accept two parameters,

  1. DataTable
  2. DestinationFileName and create desired excel output.

Above code will create simple excel sheet from Datatable in .xlsx format.

You can also apply formatting in each cell of excel sheet. So let’s see how we can apply formatting in excel to make it look beautiful.

  • Rajan

    Thanks for the Code. what version of open XML SDK is the code implemented ?

  • asd

    Looks good, Can you upload an example?