Export Tabular Data in CSV and Excel Formats through the Web

• Download source - 1.3 MB

Introduction

Background

A common task when developing web applications is to export some tabular data to a file that can be opened by Microsoft Excel. This article is to present an example to export the data in both CSV and excel (xlsx) formats. This example uses some "MVC" functions to expose the generated files to web browsers, I would assume the readers have some basic web application development experience using "MVC". If you are new to "MVC", this is a good reference link.

The attached Visual Studio 2010 solution is a simplified "MVC" web application.

• The "Models\StudentRepository.cs" file implements the application's data model.
• The "ExcelUtilities\CSVUtility.cs" file implements an utility class to help exporting the data in "CSV" format.
• The "ExcelUtilities\ExcelUtility.cs" file implements an utility class to help exporting the data in excel format.
• The data files are exported from the "MVC" controller implemented in the "Controllers\HomeController.cs" file.
• The "Default.htm" file is the web interface that triggers the download of the files generated by the utility classes.

To create an excel "xlsx" file, this article uses the "OpenXML SDK 2.0". You can download this SDK from here. In this article, I will first introduce the data model and then introduce the two utility classes. At last, I will show you how to use the utility classes to expose "CSV" and Excel files to the web browsers.

The Data Model

The application's data model is implemented in the "Models\StudentRepository.cs" file:

using System;
using System.Data;
 
namespace WebExcel.Models
{
    public static class StudentRepository
    {
        public static DataTable GetStudents()
        {
            var students = new DataTable();
            students.Columns.Add(new DataColumn("ID", Type.GetType("System.Int32")));
            students.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
            students.Columns.Add(new DataColumn
		("Enrollment", Type.GetType("System.DateTime")));
            students.Columns.Add(new DataColumn("Score", Type.GetType("System.Int32")));
 
            var rand = new Random();
            for (int i = 1; i <= 100; i++)
            {
                Object[] data = new Object[4];
                data[0] = i;
                data[1] = "Student Name No." + i.ToString();
                data[2] = DateTime.Now;
                data[3] = 60 + (int)(rand.NextDouble() * 40);
 
                students.Rows.Add(data);
            }
 
            return students;
        }
    }
}

The "GetStudents" method returns a "DataTable" containing a list of randomly generated students. In this example, we will be exporting the list of the students in both "CSV" and Excel formats.

The "CSV" Utility

The "CSV" utility class is implemented as the following:

using System;
using System.IO;
using System.Text;
using System.Web;
using System.Data;
 
namespace WebExcel.ExcelUtilities
{
    public static class CSVUtility
    {
        public static MemoryStream GetCSV(DataTable data)
        {
            string[] fieldsToExpose = new string[data.Columns.Count];
            for (int i = 0; i < data.Columns.Count; i ++ )
            {
                fieldsToExpose[i] = data.Columns[i].ColumnName;
            }
 
            return GetCSV(fieldsToExpose, data);
        }
 
        public static MemoryStream GetCSV(string[] fieldsToExpose, DataTable data)
        {
            MemoryStream stream = new MemoryStream();
            using (var writer = new StreamWriter(stream))
            {
                for (int i = 0; i < fieldsToExpose.Length; i++)
                {
                    if (i != 0) { writer.Write(","); }
                    writer.Write("\"");
                    writer.Write(fieldsToExpose[i].Replace("\"", "\"\""));
                    writer.Write("\"");
                }
                writer.Write("\n");
 
                foreach (DataRow row in data.Rows)
                {
                    for (int i = 0; i < fieldsToExpose.Length; i++)
                    {
                        if (i != 0) { writer.Write(","); }
                        writer.Write("\"");
                        writer.Write(row[fieldsToExpose[i]].ToString()
                            .Replace("\"", "\"\""));
                        writer.Write("\"");
                    }
 
                    writer.Write("\n");
                }
            }
 
            return stream;
        }
    }
}

The "CSVUtility" class exposes two "overloaded" methods. They both take a "DataTable" as the data input. If we do not want all the fields in the "DataTable" to be exposed in the "CSV" file, we can pass in the "fieldsToExpose" parameter, which contains the column names of the fields that we want to expose. Each method returns a "MemoryStream" which has the data for the generated "CSV" file. The "CSV" format is a very powerful yet simple method to represent tabular data. According to "Wikipedia", the following are the rules to create a "CSV" file:

• Each record is one line terminated by a line feed (ASCII/LF=0x0A) or a carriage return and line feed pair (ASCII/CRLF=0x0D 0x0A), however, line-breaks can be embedded.
• Fields are separated by commas. (In locales where the comma is used as a decimal separator, the semicolon is used instead as a delimiter. The different delimiters cause problems when CSV files are exchanged, for example, between France and USA.)
• In some CSV implementations, leading and trailing spaces or tabs, adjacent to commas, are trimmed. This practice is contentious and in fact is specifically prohibited by RFC 4180, which states, "Spaces are considered part of a field and should not be ignored."
• Fields with embedded commas must be enclosed within double-quote characters.
• Fields with embedded double-quote characters must be enclosed within double-quote characters, and each of the embedded double-quote characters must be represented by a pair of double-quote characters.
• Fields with embedded line breaks must be enclosed within double-quote characters.
• In CSV implementations that trim leading or trailing spaces, fields with such spaces must be enclosed within double-quote characters.
• Fields may always be enclosed within double-quote characters, whether necessary or not.
• The first record in a CVS file may contain column names in each of the fields.

You may notice that the "CSVUtility" class in this example is a simplified version of the "CSV" format. But in most of the practical uses, the "CSVUtility" class is sufficient.

The Excel Utility

The Excel utility class is implemented as the following:

using System;
using System.Web;
using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
 
namespace WebExcel.ExcelUtilities
{
    public class ExcelUtility
    {
        // Get the excel column letter by index
        public static string ColumnLetter(int intCol)
        {
            int intFirstLetter = ((intCol) / 676) + 64;
            int intSecondLetter = ((intCol % 676) / 26) + 64;
            int intThirdLetter = (intCol % 26) + 65;
 
            char FirstLetter = (intFirstLetter > 64) ? (char)intFirstLetter : ' ';
            char SecondLetter = (intSecondLetter > 64) ? (char)intSecondLetter : ' ';
            char ThirdLetter = (char)intThirdLetter;
 
            return string.Concat(FirstLetter, SecondLetter, ThirdLetter).Trim();
        }
 
        // Create a text cell
        private static Cell CreateTextCell(string header, UInt32 index, string text)
        {
            var cell = new Cell { DataType = CellValues.InlineString, 
				CellReference = header + index };
            var istring = new InlineString();
            var t = new Text { Text = text };
            istring.Append(t);
            cell.Append(istring);
            return cell;
        }
 
        public static MemoryStream GetExcel(DataTable data)
        {
            string[] fieldsToExpose = new string[data.Columns.Count];
            for (int i = 0; i < data.Columns.Count; i++)
            {
                fieldsToExpose[i] = data.Columns[i].ColumnName;
            }
 
            return GetExcel(fieldsToExpose, data);
        }
 
        public static MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
        {
            MemoryStream stream = new MemoryStream();
            UInt32 rowcount = 0;
 
            // Create the Excel document
            var document = SpreadsheetDocument.Create
			(stream, SpreadsheetDocumentType.Workbook);
            var workbookPart = document.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            var relId = workbookPart.GetIdOfPart(worksheetPart);
 
            var workbook = new Workbook();
            var fileVersion = new FileVersion 
				{ ApplicationName = "Microsoft Office Excel" };
            var worksheet = new Worksheet();
            var sheetData = new SheetData();
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
 
            var sheets = new Sheets();
            var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
            sheets.Append(sheet);
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            document.WorkbookPart.Workbook = workbook;
            document.WorkbookPart.Workbook.Save();
 
            // Add header to the sheet
            var row = new Row { RowIndex = ++rowcount };
            for (int i = 0; i < fieldsToExpose.Length; i++)
            {
                row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
            }
            sheetData.AppendChild(row);
            worksheetPart.Worksheet.Save();
             
            // Add data to the sheet
            foreach (DataRow dataRow in data.Rows)
            {
                row = new Row { RowIndex = ++rowcount };
                for (int i = 0; i < fieldsToExpose.Length; i++)
                {
                    row.Append(CreateTextCell(ColumnLetter(i), rowcount,
                        dataRow[fieldsToExpose[i]].ToString()));
                }
                sheetData.AppendChild(row);
            }
            worksheetPart.Worksheet.Save();
 
            document.Close();
            return stream;
        }
    }
}

Similar to the "CSVUtility" class, the "ExcelUtility" class also exposes two "overloaded" public methods. If we do not want to expose all the fields in the Excel file, we can pass in the "fieldsToExpose" parameter. Compared with the "CSV" format, the Excel file is much more complex and supports more features. The "ExcelUtility" class is built upon the "OpenXML SDK 2.0". Using this SDK, you can create complex Excel files. In this example, the "ExcelUtility" only creates plain "xlsx" files.

The "MVC" Controller

The "MVC" controller in this example is implemented as the following:

using System;
using System.Web;
using System.Web.Mvc;
using WebExcel.Models;
using WebExcel.ExcelUtilities;
using System.IO;
 
namespace WebExcel.Controllers
{
    [HandleError]
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            var students = StudentRepository.GetStudents();
            return new RedirectResult("~/Default.htm");
        }
 
        public void GetCSV()
        {
            var students = StudentRepository.GetStudents();
            MemoryStream stream = CSVUtility.GetCSV(students);
 
            var filename = "ExampleCSV.csv";
            var contenttype = "text/csv";
            Response.Clear();
            Response.ContentType = contenttype;
            Response.AddHeader("content-disposition", "attachment;filename=" + filename);
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.BinaryWrite(stream.ToArray());
            Response.End();
        }
 
        public void GetExcel()
        {
            var students = StudentRepository.GetStudents();
            MemoryStream stream = ExcelUtility.GetExcel(students);
 
            var filename = "ExampleExcel.xlsx";
            var contenttype = 
		"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.Clear();
            Response.ContentType = contenttype;
            Response.AddHeader("content-disposition", "attachment;filename=" + filename);
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.BinaryWrite(stream.ToArray());
            Response.End();
        }
    }
}

Using the two utility classes, we can easily export the "CSV" and Excel files to the web browser using the "MVC" actions methods. The "GetCSV" method exports the "CSV" file, and the "GetExcel" exports the "xlsx" file.

The "Default.htm" File

The "Default.htm" file is implemented as the following:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Web Excel Test</title>
    <link href="Content/Site.css" rel="stylesheet" type="text/css" />
</head>
 
<body>
<div>
<a href="Home/GetCSV" class="siteButton">Get CSV</a>
<a href="Home/GetExcel" class="siteButton">Get Excel</a>
</div>
</body>
</html>

This is a very simple HTML file. Each of the hyperlink points to one of the "MVC" action methods to download the "CSV" or the "xlsx" files. The CSS class "siteButton" makes the two hyperlinks look like buttons in the web browser.

Run the Application

Now we complete the development of this example application and we are ready to test it. When the application launches, the two buttons are shown in the web browser.

We can then click on the buttons to get the files. The following picture shows the Excel file obtained from this example application.

Points of Interest

• This article presented an example to export tabular data through the web in "CSV" as well as Excel formats.
• Although this example showed you how to export files through the web, you can easily use the two utility classes to create files in your desktop applications.
• The two utility classes take the data from "DataTable" objects. It may not always be the most convenient method. If you find other ways to pass the data to the utility classes more convenient, please feel free to make changes to the classes.
• This article showed the methods to export both "CSV" and Excel files. In most of the cases, I find that exporting "CSV" files can be more convenient to the users since it is supported by not only Microsoft Excel, but many other data loading software applications and databases as well.
• If you run the application yourself, you may need to have the correct version of Microsoft Office on your computer to open the files. In my own test, I noticed that the version of "Open Office" on my computer failed to open the "xlsx" files. In the computer which has Microsoft Office 2007 installed, I do not have any problem to open the files.
• I hope you like my postings and I hope this article can help you one way or the other.

History

• First revision - 10/3/2011