How to Export to Excel List<> , Datatable, Dataset ?

Step1: Include this class to Project.

//#define INCLUDE_WEB_FUNCTIONS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Diagnostics;

using System.Data;

using System.Reflection;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml;

namespace ExportToExcel

{

    //

    //  November 2013

    //  http://www.mikesknowledgebase.com

    //

    //  Note: if you plan to use this in an ASP.Net application, remember to add a reference to "System.Web", and to uncomment

    //  the "INCLUDE_WEB_FUNCTIONS" definition at the top of this file.

    //

    //  Release history

    //   - Nov 2013:

    //        Changed "CreateExcelDocument(DataTable dt, string xlsxFilePath)" to remove the DataTable from the DataSet after creating the Excel file.

    //        You can now create an Excel file via a Stream (making it more ASP.Net friendly)

    //   - Jan 2013: Fix: Couldn't open .xlsx files using OLEDB  (was missing "WorkbookStylesPart" part)

    //   - Nov 2012:

    //        List<>s with Nullable columns weren't be handled properly.

    //        If a value in a numeric column doesn't have any data, don't write anything to the Excel file (previously, it'd write a '0')

    //   - Jul 2012: Fix: Some worksheets weren't exporting their numeric data properly, causing "Excel found unreadable content in '___.xslx'" errors.

    //   - Mar 2012: Fixed issue, where Microsoft.ACE.OLEDB.12.0 wasn't able to connect to the Excel files created using this class.

    //

    public class CreateExcelFile

    {

        public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)

        {

            DataSet ds = new DataSet();

            ds.Tables.Add(ListToDataTable(list));

            return CreateExcelDocument(ds, xlsxFilePath);

        }

#region HELPER_FUNCTIONS

        //  This function is adapated from: http://www.codeguru.com/forum/showthread.php?t=450171

        //  My thanks to Carl Quirion, for making it "nullable-friendly".

        public static DataTable ListToDataTable<T>(List<T> list)

        {

            DataTable dt = new DataTable();

            foreach (PropertyInfo info in typeof(T).GetProperties())

            {

                dt.Columns.Add(new DataColumn(info.Name, GetNullableType(info.PropertyType)));

            }

            foreach (T t in list)

            {

                DataRow row = dt.NewRow();

                foreach (PropertyInfo info in typeof(T).GetProperties())

                {

                    if (!IsNullableType(info.PropertyType))

                        row[info.Name] = info.GetValue(t, null);

                    else

                        row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);

                }

                dt.Rows.Add(row);

            }

            return dt;

        }

        private static Type GetNullableType(Type t)

        {

            Type returnType = t;

            if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))

            {

                returnType = Nullable.GetUnderlyingType(t);

            }

            return returnType;

        }

        private static bool IsNullableType(Type type)

        {

            return (type == typeof(string) ||

                    type.IsArray ||

                    (type.IsGenericType &&

                     type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))));

        }

        public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)

        {

            DataSet ds = new DataSet();

            ds.Tables.Add(dt);

            bool result = CreateExcelDocument(ds, xlsxFilePath);

            ds.Tables.Remove(dt);

            return result;

        }

#endregion

#if INCLUDE_WEB_FUNCTIONS

        /// <summary>

        /// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)

        /// </summary>

        /// <param name="dt">DataTable containing the data to be written to the Excel.</param>

        /// <param name="filename">The filename (without a path) to call the new Excel file.</param>

        /// <param name="Response">HttpResponse of the current page.</param>

        /// <returns>True if it was created succesfully, otherwise false.</returns>

        public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)

        {

            try

            {

                DataSet ds = new DataSet();

                ds.Tables.Add(dt);

                CreateExcelDocumentAsStream(ds, filename, Response);

                ds.Tables.Remove(dt);

                return true;

            }

            catch (Exception ex)

            {

                Trace.WriteLine("Failed, exception thrown: " + ex.Message);

                return false;

            }

        }

        public static bool CreateExcelDocument<T>(List<T> list, string filename, System.Web.HttpResponse Response)

        {

            try

            {

                DataSet ds = new DataSet();

                ds.Tables.Add(ListToDataTable(list));

                CreateExcelDocumentAsStream(ds, filename, Response);

                return true;

            }

            catch (Exception ex)

            {

                Trace.WriteLine("Failed, exception thrown: " + ex.Message);

                return false;

            }

        }

        /// <summary>

        /// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)

        /// </summary>

        /// <param name="ds">DataSet containing the data to be written to the Excel.</param>

        /// <param name="filename">The filename (without a path) to call the new Excel file.</param>

        /// <param name="Response">HttpResponse of the current page.</param>

        /// <returns>Either a MemoryStream, or NULL if something goes wrong.</returns>

        public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponse Response)

        {

            try

            {

                System.IO.MemoryStream stream = new System.IO.MemoryStream();

                using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))

                {

                    WriteExcelFile(ds, document);

                }

                stream.Flush();

                stream.Position = 0;

                Response.ClearContent();

                Response.Clear();

                Response.Buffer = true;

                Response.Charset = "";

                //  NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have

                //  manually added System.Web to this project's References.

                Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);

                Response.AddHeader("content-disposition", "attachment; filename=" + filename);

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                byte[] data1 = new byte[stream.Length];

                stream.Read(data1, 0, data1.Length);

                stream.Close();

                Response.BinaryWrite(data1);

                Response.Flush();

                Response.End();

                return true;

            }

            catch (Exception ex)

            {

                Trace.WriteLine("Failed, exception thrown: " + ex.Message);

                return false;

            }

        }

#endif      //  End of "INCLUDE_WEB_FUNCTIONS" section

        /// <summary>

        /// Create an Excel file, and write it to a file.

        /// </summary>

        /// <param name="ds">DataSet containing the data to be written to the Excel.</param>

        /// <param name="excelFilename">Name of file to be written.</param>

        /// <returns>True if successful, false if something went wrong.</returns>

        public static bool CreateExcelDocument(DataSet ds, string excelFilename)

        {

            try

            {

                using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))

                {

                    WriteExcelFile(ds, document);

                }

                Trace.WriteLine("Successfully created: " + excelFilename);

                return true;

            }

            catch (Exception ex)

            {

                Trace.WriteLine("Failed, exception thrown: " + ex.Message);

                return false;

            }

        }

        private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)

        {

            //  Create the Excel file contents.  This function is used when creating an Excel file either writing

            //  to a file, or writing to a MemoryStream.

            spreadsheet.AddWorkbookPart();

            spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

            //  My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)

            spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));

            //  If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !

            WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");

            Stylesheet stylesheet = new Stylesheet();

            workbookStylesPart.Stylesheet = stylesheet;

            //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.

            uint worksheetNumber = 1;

            foreach (DataTable dt in ds.Tables)

            {

                //  For each worksheet you want to create

                string workSheetID = "rId" + worksheetNumber.ToString();

                string worksheetName = dt.TableName;

                WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();

                newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();

                // create sheet data

                newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());

                // save worksheet

                WriteDataTableToExcelWorksheet(dt, newWorksheetPart);

                newWorksheetPart.Worksheet.Save();

                // create the worksheet to workbook relation

                if (worksheetNumber == 1)

                    spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());

                spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()

                {

                    Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),

                    SheetId = (uint)worksheetNumber,

                    Name = dt.TableName

                });

                worksheetNumber++;

            }

            spreadsheet.WorkbookPart.Workbook.Save();

        }

        private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)

        {

            var worksheet = worksheetPart.Worksheet;

            var sheetData = worksheet.GetFirstChild<SheetData>();

            string cellValue = "";

            //  Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.

            //

            //  We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual

            //  cells of data, we'll know if to write Text values or Numeric cell values.

            int numberOfColumns = dt.Columns.Count;

            bool[] IsNumericColumn = new bool[numberOfColumns];

            string[] excelColumnNames = new string[numberOfColumns];

            for (int n = 0; n < numberOfColumns; n++)

                excelColumnNames[n] = GetExcelColumnName(n);

            //

            //  Create the Header row in our Excel Worksheet

            //

            uint rowIndex = 1;

            var headerRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet

            sheetData.Append(headerRow);

            for (int colInx = 0; colInx < numberOfColumns; colInx++)

            {

                DataColumn col = dt.Columns[colInx];

                AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);

                IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");

            }

            //

            //  Now, step through each row of data in our DataTable...

            //

            double cellNumericValue = 0;

            foreach (DataRow dr in dt.Rows)

            {

                // ...create a new row, and append a set of this row's data to it.

                ++rowIndex;

                var newExcelRow = new Row { RowIndex = rowIndex };  // add a row at the top of spreadsheet

                sheetData.Append(newExcelRow);

                for (int colInx = 0; colInx < numberOfColumns; colInx++)

                {

                    cellValue = dr.ItemArray[colInx].ToString();

                    // Create cell with data

                    if (IsNumericColumn[colInx])

                    {

                        //  For numeric cells, make sure our input data IS a number, then write it out to the Excel file.

                        //  If this numeric value is NULL, then don't write anything to the Excel file.

                        cellNumericValue = 0;

                        if (double.TryParse(cellValue, out cellNumericValue))

                        {

                            cellValue = cellNumericValue.ToString();

                            AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);

                        }

                    }

                    else

                    {

                        //  For text cells, just write the input data straight out to the Excel file.

                        AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);

                    }

                }

            }

        }

        private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)

        {

            //  Add a new Excel Cell to our Row

            Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };

            CellValue cellValue = new CellValue();

            cellValue.Text = cellStringValue;

            cell.Append(cellValue);

            excelRow.Append(cell);

        }

        private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)

        {

            //  Add a new Excel Cell to our Row

            Cell cell = new Cell() { CellReference = cellReference };

            CellValue cellValue = new CellValue();

            cellValue.Text = cellStringValue;

            cell.Append(cellValue);

            excelRow.Append(cell);

        }

        private static string GetExcelColumnName(int columnIndex)

        {

            //  Convert a zero-based column index into an Excel column reference  (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)

            //

            //  eg  GetExcelColumnName(0) should return "A"

            //      GetExcelColumnName(1) should return "B"

            //      GetExcelColumnName(25) should return "Z"

            //      GetExcelColumnName(26) should return "AA"

            //      GetExcelColumnName(27) should return "AB"

            //      ..etc..

            //

            if (columnIndex < 26)

                return ((char)('A' + columnIndex)).ToString();

            char firstChar = (char)('A' + (columnIndex / 26) - 1);

            char secondChar = (char)('A' + (columnIndex % 26));

            return string.Format("{0}{1}", firstChar, secondChar);

        }

    }

}

Step2: How to Use this

Introduction

It's amazing that even now, in 2013, there are so many developers still asking for help on how to write C# and VB.NET code to export their data to Excel.

Even worse, a lot of them will stumble on articles suggesting that they should write their data to a comma-separated file, but to give the file a .xls extension.

So today, I'm going to walkthrough how to use my C# "Export to Excel" class which you can add to your C# WinForms / WPF / ASP.NET application, using one line of code.

Depending on whether your data is stored in a DataSet, DataTable, or List<>, you simply need to call one of these three functions, and tell them what (Excel) file name you want to write to.

 Collapse | Copy Code

public static bool CreateExcelDocument<T>(List<T> list, string ExcelFilename

public static bool CreateExcelDocument(DataTable dt, string ExcelFilename

public static bool CreateExcelDocument(DataSet ds, string ExcelFilename)

Here's a simple example.

We create a DataSet, fill it with some data (just add your own CreateSampleData function), then call theCreateExcelFile.CreateExcelDocument function, passing it the DataSet and a filename:

 Collapse | Copy Code

// Step 1: Create a DataSet, and put some sample data in it

DataSet ds = CreateSampleData();

// Step 2: Create the Excel .xlsx file

try

{

    CreateExcelFile.CreateExcelDocument(ds, "C:\\Sample.xlsx");

}

catch (Exception ex)

{

    MessageBox.Show("Couldn't create Excel file.\r\nException: " + ex.Message);

    return;

}  

And that's all you have to do. The CreateExcelDocument function will create a "real" Excel file for you.

For example, if you had a created a DataSet containing three DataTables called:

·         Drivers

·         Vehicles,

·         Vehicle Owners,

..then here's what your Excel file would look like. The class would create one worksheet per DataTable, and each worksheet would contain the data from that DataTable.

The full source code for this C# class is freely downloadable here (click on the "Browse code" link in the left-hand panel), or you can download this full demo, plus a VB.NET version of the class, from my blog,MikesKnowledgeBase.

Adding the library to your application

The C# code above shows how easy it is to call the CreateExcelFile class.

 Collapse | Copy Code

DataSet ds = CreateSampleData();

CreateExcelFile.CreateExcelDocument(ds, "C:\\Sample.xlsx");

However, to use this library, you'll need to add two files from the free Microsoft OpenXML SDK:

·         DocumentFormat.OpenXml.dll: From the free Microsoft Open XML SDK library

·         WindowsBase.dll: From the Microsoft .NET Framework library

Add these two DLLs to your project's References section, and remember to set them to "Copy Local".

Then, just download the CreateExcelFile.cs file (via the "Browse code" link in CodeProject's left-hand panel), and add it to your application. 

And that's it.

Regardless of if your data is stored in a List<>, DataTable, or DataSet, you can export it to a "real" Office 2007 Excel .xlsx file using that one line of code.

And because it is created using the OpenXML library, you can run this code on machines which don't have Excel installed.

ASP.NET

For ASP.NET developers, I've added three extra functions.

 Collapse | Copy Code

public static bool CreateExcelDocument<T>(List<T> list, string filename, System.Web.HttpResponse Response)

public static bool CreateExcelDocument(DataTable dt, string filename, System.Web.HttpResponse Response)

public static bool CreateExcelDocument(DataSet ds, string filename, System.Web.HttpResponse Response)

Rather than creating the Excel file in a temporary directory, then having to load in the file and output it to the webpage's HttpResponse, you can get the library to write directly to the HttpResponse.

However, by default, this functionality is disabled (to prevent build issues for the non ASP.NET developers). To enable these three functions, you need to make two changes.

First, uncomment the top line of the CreateExcelFile.cs code, so that it now reads:

 Collapse | Copy Code

#define INCLUDE_WEB_FUNCTIONS  

Now, you need to add a Reference to the System.Web .NET library:

Once you've done these two steps, the CreateExcelFile library is ready to go.

For example, in this example, my ASP.NET C# code has a list of Employee records, stored in aList<Employeee>. I add an "Export to Excel" button to my webpage, and when the user clicks on it, I just need one simple call to the CreateExcelFile class.

 Collapse | Copy Code

// In this example, I have a defined a List of my Employee objects.<br />class Employee;

List<Employee> listOfEmployees = new List<Employee>();

...

// The following ASP.Net code gets run when I click on my "Export to Excel" button.

protected void btnExportToExcel_Click(object sender, EventArgs e)

{

    // It doesn't get much easier than this...

    CreateExcelFile.CreateExcelDocument(listOfEmployees, "Employees.xlsx", Response);

}

And that's it. A real Excel file, in one line of code.

Going forward

You'll notice that this library is excellent for one job - writing plain, boring data to an Excel file. I haven't attempted to add any classes to add formatting, colors, pivot tables or anything else.

However, this class is an excellent way to get started (without paying for third-party software to create the Excel file for you), and if you want to take this further, you'll soon find that Googling will easily find you extra source code to add on top of this.

For example, if you wanted to add a background color to some of the cells in the Excel file, simply Google "open XML background color" and you'll have many articles showing you how to do this.

The reason I wrote this article is that I found that it was very hard to find a free, easy to use C# library which actually created the OpenXML Excel file in the first place.

-------------------------------------------------------------------------------------------

This amazing code is taken from : CodeProject by Mike Gledhill. we all thank him for this amazing code for sharing.