How To Export Data To Excel File Using Open XML SDK

Problem

Today I want to cover about how to export data to a spreadsheet (Excel) file. I believe this is a very common feature in most of the application, to extract data from a database and then save the data into an Excel file. However, developers who worked on this feature before knows that this is no easy task.

Options

There are a few ways to export data into an Excel file.

Option #1: Microsoft JET OLE DB Provider

This is one of the component from Microsoft Data Access Engine (MDAC). You need to install MDAC on the server in order to get it works. The JET OLE DB Provider only provide the basic data export with standard data source. If you need more features such as including chart, formatting, formula, calculation, etc in the exported Excel file, see the other options below.

Option #2: Visual Studio Tool for Office

Visual Studio Tool for Office (VSTO) can be used for data export, and it also provide most Office related features, automation and integration. But, it relies on Microsoft Office COM library, the Primary Interop Assemblies (PIA). You need to install PIA on the server in order to get it works.

Option #3: Microsoft Office Interop

Microsoft Office Interop provide even more integration, and it is also relying on PIA. Although this uses the same PIA as VSTO, but their assembly namespaces are different. Microsoft Office Interop can be found and directly referenced from the Visual Studio Extension Library as they are installed in Global Assembly Cache (GAC) after you have installed Microsoft Office or PIA in your machine. 

Option #4: Open XML SDK

This is the component that I want to share today which does not rely on PIA, which mean lesser feature than VSTO and Office Interop, but better flexibility than JET OLE DB Provider. You do not need to install anything on the server, just need to include the assembly library in your application, then it will work. How it works is this SDK generate the XML that following ECMA-376, Second Edition and ISO/IEC 29500 standard which is used to form a spreadsheet which is recognizable by any spreadsheet application such as Microsoft Office or Open Office.

Solution

First, we have to obtain and install the Open XML SDK. It is free, can be downloaded from HERE.

Run the Visual Studio, then create new or open existing project, then add the Open XML SDK reference which is located at the Extensions page from the Reference Manager.

Then, add Windows Base assembly which is required later.

Now, what I am trying to do now is to query a large set of record that I want from the database and export them to Excel file. So, here is how my data access component look like:

public class ContactDAC

{

    public List<Contact> Select()

    {

        List<Contact> result = new List<Contact>();

        using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))

        using (var cmd = new SqlCommand("SELECT * FROM Contacts", con))

        {

            con.Open();

            using (var reader = cmd.ExecuteReader())

            {

                while (reader.Read())

                {

                    Contact contact = new Contact();

                    contact.ID = reader.GetInt64(0);

                    contact.Name = reader.GetString(1);

                    contact.Number = reader.GetString(2);

                    contact.Address = reader.GetString(3);

                    contact.CreatedDate = reader.GetDateTime(4);

                    result.Add(contact);

                }

            }

        }

        return result;

    }

}

Then, I am creating a data export component which is a generic one, can support any entity type and export the data into Excel file.

Before that, I need this namespace reference:

using DocumentFormat.OpenXml;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

public class DataExportComponent<T>

{

    private static string _tableName;

    private static PropertyInfo[] _properties;

    static DataExportComponent()

    {

        //using Reflection to identify all the columns (properties) of my table (entity)

        Type type = typeof(T);

        _tableName = type.Name;

        _properties = type.GetProperties();

    }

Following is the common way to create a new spreadsheet file:

public void Export(string path, List<T> entities)

{

    //Create a new spreadsheet file

    //path is the Excel file location and name

    //My path is C:\temp\OpenXML\contacts.xlsx

    using (SpreadsheetDocument spreadsheet =

        SpreadsheetDocument.Create(path,

        SpreadsheetDocumentType.Workbook))

    {

        WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();

        workbookPart.Workbook = new Workbook();

        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

        SheetData sheetData = new SheetData();

        Sheets sheets = new Sheets();

        Sheet sheet = new Sheet();

        sheet.Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart);

        sheet.SheetId = 1; //sheet Id, anything but unique

        sheet.Name = _tableName; //sheet name

        sheets.Append(sheet);

Then, query the data from the database with the data access component above, with a List<Contact> type, iterate the List and then inject the value into cell column by column then repeat for row by row.

//loop row by row depending on the List count

for (int i = 0; i < entities.Count; i++)

{

    Row row = new Row();

    row.RowIndex = (uint)i+1; //RowIndex must be start with 1, since i = 0

    //loop column by column depending on the entity properties

    for (int column = 0; column < _properties.Count(); column++)

    {

        Cell cell = new Cell();

        //Cell Reference is the Excel row and column index,

        //For example:

        //First row first column: A1

        //Fifth row thrid column: C5

        cell.CellReference = CreateCellReference(column+1) + row.RowIndex.ToString();

                      

        cell.DataType = CellValues.InlineString;

                           

        InlineString inlineString = new InlineString();

        Text text = new Text();

        text.Text = _properties[column].GetValue(entities[i]).ToString();

        inlineString.Text = text; //The value you want to put into cell

        cell.AppendChild(inlineString);

        //Keep appending cell (column) into the same row

        row.AppendChild(cell);

    }

                   

    //Keep appending row into the data sheet

    sheetData.Append(row);

}

worksheetPart.Worksheet = new Worksheet(sheetData);

spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(sheets);

workbookPart.Workbook.Save();

spreadsheet.Close();

The Cell Reference is a bit troublesome as we have to handle the column index increment automatically when the last index 'Z' is used. This is needed when your data contain more than 26 columns.

For example, if you have 27 columns data, at the 26th column, Z is used, then 27th column should be AA. Therefore, I used the following code to handle it for me.

private string CreateCellReference(int column)

{

    string result = string.Empty;

    //First is A

    //After Z, is AA

    //After ZZ, is AAA

    char firstRef = 'A';

    uint firstIndex = (uint)firstRef;

    int mod = 0;

    while (column > 0)

    {

        mod = (column - 1) % 26;

        result += (char)(firstIndex + mod);

        column = (column - mod) / 26;

    }

    return result;

} 

That's all for my generic data export component, now here is how I use it:

ContactDAC dac = new ContactDAC();

List<Contact> entities = dac.Select();

DataExportComponent<Contact> com = new DataExportComponent<Contact>();

com.Export(@"C:\temp\OpenXML\contacts.xlsx", entities);

Testing

I compiled and publish my application to a newly installed server which does not have any MDAC and Microsoft Office product installed, and then run my application. I notice the application run without any error, and I get my Excel file created with data at the location which I had specified in my program.

Challenge

If you have an ASP.NET application for client to export file, the common challenge that you may face is request timeout when the client is trying to export a very large file. In order to prevent the timeout error, we should make the front end application to process the file export in asynchronous manner. How? I have this concept but yet to implement:

In my next post, I may share how to implement this large file export concept if I am not lazy.

Enjoy exporting!

C# - Reflection

Do you ever experience in writing a plugin or given a system requirement that require to load some external class library during runtime? Today's topic covers how to use Reflection (with C#) to dynamically load object into your application during runtime.

Before we jump into coding, we need to know how .NET works. As you may know, the C# or VB code that you have written are actually compiled into MSIL (Microsoft Intermediate Language). The code is also known as managed code. Then, the JIT (Just In Time) compiler will translate the MSIL into native code. Finally, the CLR (Common Language Runtime) will go through some code verification then prepare the environment like allocating system resource, initiate garbage collector, load the system domain and shared domain, create default app domain, load the metadata, create method tables and more (It's crazy to go too detail, more info about CLR intermal, read this), then create a process to execute the native code.

Early Binding

Every class, every method we write in C# are early binding. Early binding mean during the compile time, from your code, the compiler know what are the objects or the types that you have declared or created, what properties and methods you have in the class, knowing every method parameters and return type, etc. So, during compile time, it creates a "map" containing the information of your objects. And, this information is known as metadata.

Late Binding

Any object which is created after compile time is known as late binding. The Reflection can create or manipulate objects during runtime, it also can be used to inspect and retrieve any information of a class library. But, mind you, any object creation or manipulation during runtime is very slow in performance. The reason is these runtime created objects are not in any part of the "map" created by the compiler. It takes some time to locate the assembly and then load the metadata into CLR, etc.

Coding

Assuming you already have a class library that containing a class like this:

public class Cat

{

    public int ID { get; set; }

    public string Name { get; set; }

    public int Leg { get; set; }

    public int Mouth { get; set; }

    public int Eye { get; set; }

    public List<Fish> EatenFish { get; set; }

    public Cat()

    {

        this.EatenFish = new List<Fish>();

    }

    public void Eat(Fish fish)

    {

        EatenFish.Add(fish);

    }

}

You can use Reflection to load and create an instance of it into your application. Following code demonstrate how to create a new instance from the loaded assembly during runtime, but you have to know what class is available in the assembly.

Create Instance

Assembly myAssembly= Assembly.LoadFrom(@"..\PuttyCat\bin\Debug\PuttyCat.dll");

//Late binding

//Must use fully qualified name (including namespace)

object cat = myAssembly.CreateInstance("PuttyCat.Cat");

Above code is actually similar to:

//Early binding

Cat cat = new Cat();

Inspect Object

What if you do not know anything about the loaded assembly, you are still able to retrieve the field, property, method, parameter or any information that are available in the object by exploring the FieldInfo, PropertyInfo, MethodInfo, ParameterInfo and more by calling the method GetFields, GetProperties, GetMethods, GetParameters, etc.

Example below uses GetMethods method to get all the methods of an object with MethodInfo type and GetParameters to get all the parameters for a method with ParameterInfo type:

Type[] types = puttyCat.GetTypes();

foreach (Type type in types)

{

    MethodInfo[] methods = type.GetMethods();

    Console.WriteLine("Type Name: " + type.Name);

    foreach (MethodInfo method in methods)

    {

        ParameterInfo[] parameters = method.GetParameters();

        Console.WriteLine("Method Name: " + method.Name);

        foreach (ParameterInfo parameter in parameters)

        {

            Console.WriteLine("Parameter Name: " + parameter.Name);

            Console.WriteLine("Parameter Type: " + parameter.ParameterType.Name);

        }

    }

}

Invoke Method

After getting the MethodInfo and ParameterInfo, you may want to invoke the object method and passing in the parameter.

//Late binding
//Must use fully qualified name (including namespace)

object cat = myAssembly.CreateInstance("PuttyCat.Cat");

object fish = myAssembly.CreateInstance("PuttyCat.Fish");

//Get the Cat type, from there to get method info

Type catType = myAssembly.GetType("PuttyCat.Cat");

MethodInfo eatMethod = catType.GetMethod("Eat");

//Invoke the method from cat object with parameter fish

eatMethod.Invoke(cat, new object[1] { fish });

Above code is actually similar to:

//Early binding

Cat cat = new Cat();

Fish fish = new Fish();

cat.Eat(fish);

Generic Object

Instantiate Generic Object

Now you may wonder how about generic object? How to instantiate a generic object by using Reflection.

I have modified the Cat class into a generic class.

public class Cat<T>

{

    public int ID { get; set; }

    public string Name { get; set; }

    public int Leg { get; set; }

    public int Mouth { get; set; }

    public int Eye { get; set; }

    public List<Fish> EatenFish { get; set; }

    public Cat()

    {

        this.EatenFish = new List<Fish>();

    }

    public void Eat<T>(Fish fish)

    {

        this.EatenFish.Add(fish);

    }

}

The type name for Cat is now different from the previous non generic type. If you use the Assembly.GetTypes() method to check what is the new type name for this generic class, you will notice it is now become PuttyCat.Cat`1.

//Late binding

Type catType = myAssembly.GetType("PuttyCat.Cat`1");

Type pussyCatType = myAssembly.GetType("PuttyCat.PussyCat");

Type pussyCatGenericType = catType.MakeGenericType(pussyCatType);

object pussyCat = Activator.CreateInstance(pussyCatGenericType);

Above code is actually similar to:

//Early binding

Cat<PussyCat> pussyCat = new Cat<PussyCat>();

Invoke Generic Method

The following code is to invoke generic method:

//Late binding

MethodInfo eatMethod = pussyCatGenericType.GetMethod("Eat");

MethodInfo eatGenericMethod = eatMethod.MakeGenericMethod(pussyCatType);

eatGenericMethod.Invoke(pussyCat, new object[1] { fish });

Above code is actually similar to:

//Early binding

pussyCat.Eat<PussyCat>(fish);

Summary

Reflection give you the flexibility to load, use, inspect, manipulate any runtime loaded objects. But, this flexibility force you to sacrifice the application performance. It is advisable to avoid using it if possible. If you are creating an application that support assembly plugin which can dynamically load and use any assembly file from a specific location, then you have to use Reflection. The best you can do is avoid calling the Reflection method such as Load, GetType, GetProperty, etc frequently, design the code to load the required information once only and keep it somewhere in the memory for reuse.

There is one type call dynamic in C#. This is another one evil performance killer feature in C#. Imagine all the trouble you had done in retrieving object's PropertyInfo, MethodInfo, ParameterInfo and perform the method invoke with Reflection, now you can use the dynamic type to skip all these code.

For example, same scenario as above:

dynamic cat = myAssembly.CreateInstance("PuttyCat.Cat");

dynamic fish = myAssembly.CreateInstance("PuttyCat.Fish");

cat.Eat(fish);

In Visual Studio, when you use the dynamic type, you do not need to worry about getting the strong typed property name or method name error. Your code still compilable despite the property name or method name does not exist. But, if the property or method not found, error would occur during runtime.

Remote Debugging with Visual Studio

Problem

In a normal software development process, when we are done with our software development, we deploy our application to the test environment for system integration testing (SIT) and then user acceptance testing (UAT). Sometime tester found a bug in the SIT or UAT environment, but the bug does not happen in our local machine. And then we would give a typical developer response: "I don't know, it works on my machine.".

The challenge here is due to the environment difference, it is difficult to trace the bug root cause. We can either guess or use trial and error method to fix the bug, otherwise, install Visual Studio into the server and then step into the code. But, we should not install any IDE tool or copy the source code to the server for debug.

Solution

So, today topic is how to remote debugging with Visual Studio. By using remote debugger, developer can remote attach the process in the SIT server and then step into the code from the Visual Studio in local machine.

Tool Setup

Remote Debugger exist since Visual Studio 2003. First, you need to download the Remote Debugger installer base on your Visual Studio version. For my case, I am using Visual Studio 2013, so I need to download Remote Debugger for Visual Studio 2013 from HERE. And then, install it on the server.

Application Deployment

First, compile your source code in Debug mode. Deploy all the compiled assemblies and generated symbol files to the server. The symbol files are those with .pdb extension, it also known as program database, those file contains the debug information that you need to step in to code. These files are normally generated at the same "bin" folder with your dlls.

Server Side Setup

Remote Debugger can be run as a program or windows service. If you prefer to turn on the remote debugger whenever you need it only, then run the Remote Debugger like running a normal program. Otherwise, run Remote Debugger Configuration Wizard to install the Remote Debugger as a windows service.

In today topic, I only cover about running Remote Debugger as a program. So, for the first time, when you run the Remote Debugger, you will be prompted for unblocking some firewall to allow remote debugging.

Just click the Configure remote debugging button to proceed.

The Visual Studio Remote Debugging Monitor must be running all time whenever you need to do remote debugging. Take a look at the description, it actually tells you the port number that it used to listen. You need to connect to that server name and port from Visual Studio.

You can change the port if the default port 4018 is being used by other program by opening the Options from the Tools menu.

And then, by default, Remote Debugger uses Windows Authentication mode. Make sure you have given permission to the account that you use to run Visual Studio. Click the Permissions button and then give the Debug privilege to the account that you want.

Local Machine Setup

Now, open Visual Studio and then open your project, then put some breakpoints in your code. Click the Debug menu, then open the Attach to Process window.

At the Qualifier field, enter the server IP address and port, then click the Find button.

A list of processes on the server will be populated. Now, you can find your process and then click the Attach button.

If you are running a windows application, windows service or console application on your server, you can straight away look for the executable to attach. If you are hosting an ASP.NET application or WCF web service, then you should look for IIS worker process (w3wp.exe) to attach. For IIS worker process, it is recommended to configure running under an application pool identity. So that it is easier to detect and attach the correct IIS worker process.

Once you click the Attach button, the Remote Debugger will show you or someone has connected to it.

Now, you can start playing with your application to purposely reproduce the bug. The breakpoint that you put in your code in Visual Studio will be hit. From there you can debug your code base on the assemblies that running on your server.

Happy debugging!