Create an Excel workbook from a DataSet using Open XML SDK

Here is a simple code snippet for creating Excel workbooks from a DataSet using Open XML SDK. It will create a tab for each DataTable object and apply some basic formatting.

Steps
1.  Open a project in Visual Studio. In my case, I used a Web Application project.
2.  Add a reference to the following assemblies:
     Document.OpenXml (C:\Program Files\Open XML SDK\V2.0\lib)
          (Download here: Open XML SDK 2.0 for Microsoft Office)
     DocumentFormat.OpenXml.Extensions
             (Download here: Simple OOXML)
     WindowsBase (GAC)
3.  Add a class called ExcelUtility with the following code:
using System.Data;
using System.IO;
using System.Linq;
using DocumentFormat.OpenXml.Extensions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace AcmeCompany
{
    public class ExcelUtility
    {
        public static MemoryStream GetStreamFromDataSet(DataSet ds)
        {
            MemoryStream stream = SpreadsheetReader.Create();
            using (SpreadsheetDocument spreadSheet =
                SpreadsheetDocument.Open(stream, true))
            {
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    WorksheetPart newWorkSheetPart =
                        spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                    newWorkSheetPart.Worksheet = new Worksheet(new SheetData());
                    newWorkSheetPart.Worksheet.Save();
                    Sheets sheets =
                        spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                    string relationshipId =
                        spreadSheet.WorkbookPart.GetIdOfPart(newWorkSheetPart);
                    uint sheetId = (uint)i;
                    if (sheets.Elements<Sheet>().Count() > 0)
                    {
                        sheetId = sheets.Elements<Sheet>().Select(s =>
                            s.SheetId.Value).Max() + 1;
                    }
                    Sheet sheet = new Sheet()
                        {
                            Id = relationshipId, SheetId = sheetId,
                            Name = ds.Tables[i].TableName
                        };
                    sheets.Append(sheet);
                    WorksheetWriter writer =
                        new WorksheetWriter(spreadSheet, newWorkSheetPart);
                    SpreadsheetStyle style =
                        SpreadsheetReader.GetDefaultStyle(spreadSheet);
                    style.SetBorder("000000", BorderStyleValues.Thin);
                    style.IsBold = true;
                    for (int x = 0; x < ds.Tables[i].Columns.Count; x++)
                    {
                        string columnName = GetExcelColumnValue(x + 1);                      
                        writer.PasteText(columnName + "1",
                            ds.Tables[i].Columns[x].ColumnName, style);
                    }
                    style.IsBold = false;
                    writer.PasteDataTable(ds.Tables[i], "A2", style);
                    spreadSheet.WorkbookPart.Workbook.Save();
                }
                //Remove first 3 default tabs (Sheet1 ~ Sheet3)
                spreadSheet.WorkbookPart.Workbook.Sheets.FirstChild.Remove();
                spreadSheet.WorkbookPart.Workbook.Sheets.FirstChild.Remove();
                spreadSheet.WorkbookPart.Workbook.Sheets.FirstChild.Remove();
            };
            return stream;
        }

        private static string GetExcelColumnValue(int columnNumber)
        {
            if (columnNumber <= 26)
            {
                return ((char)(columnNumber + 64)).ToString();
            }
            columnNumber--;
            return GetExcelColumnValue(columnNumber / 26) +
                GetExcelColumnValue((columnNumber % 26) + 1);
        }       
    }
}

4.  Create some test code:

using System;
using System.Data;
using System.IO;

namespace AcmeCompany
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataSet ds = GetSampleData();
            string fileName = "test.xlsx";
            MemoryStream ms = ExcelUtility.GetStreamFromDataSet(ds);
            Response.Clear();
            Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", fileName));
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            ms.WriteTo(Response.OutputStream);
            Response.End();

            //string filePath = @"c:\test.xlsx";
            //FileStream fs = File.OpenWrite(filePath);
            //fs.Write(ms.ToArray(), 0, ms.ToArray().Length);
            //fs.Close();
        }

        public DataSet GetSampleData()
        {
            DataSet ds = new DataSet();

            DataTable dt1 = new DataTable();
            dt1.TableName = "Tab 1";
            dt1.Columns.Add("Column 1");
            dt1.Columns.Add("Column 2");
            dt1.Columns.Add("Column 3");
            DataRow r1 = dt1.NewRow();
            r1.ItemArray = new object[] { "asdf1", "asdf1", "asdf1" };
            dt1.Rows.Add(r1);
            r1 = dt1.NewRow();
            r1.ItemArray = new object[] { "asdf2", "asdf2", "asdf2" };
            dt1.Rows.Add(r1);
            r1 = dt1.NewRow();
            r1.ItemArray = new object[] { "asdf3", "asdf3", "asdf3" };
            dt1.Rows.Add(r1);
            r1 = dt1.NewRow();
            r1.ItemArray = new object[] { "asdf3", "asdf3", "asdf3" };
            dt1.Rows.Add(r1);

            DataTable dt2 = new DataTable();
            dt2.TableName = "Tab 2";
            dt2.Columns.Add("Column a");
            dt2.Columns.Add("Column b");
            dt2.Columns.Add("Column c");
            DataRow r = dt2.NewRow();
            r.ItemArray = new object[] { "asdf12", "asdf12", "asdf12" };
            dt2.Rows.Add(r);
            r = dt2.NewRow();
            r.ItemArray = new object[] { "asdf22", "asdf22", "asdf22" };
            dt2.Rows.Add(r);
            r = dt2.NewRow();
            r.ItemArray = new object[] { "asdf32", "asdf32", "asdf32" };
            dt2.Rows.Add(r);
            r = dt2.NewRow();
            r.ItemArray = new object[] { "asdf32", "asdf32", "asdf32" };
            dt2.Rows.Add(r);

            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);

            return ds;
        }
    }
}

Screenshots