Export data to multiple excel sheets

Hi,

I will suggest you to use open XML to create Excel, you can use the below code to create the same. Open XML is the cleanest way to create Excel. use the Mermory stream that is returned from CreateExcel in Response.OutputStream to download.

string filename = "fileName_" + DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx";
                       //Create a Instance of Class which have below methods
                       //pass Dataset to Create Excel 
                        ms = excel.CreateExcel(ds);
                        ms.WriteTo(Response.OutputStream);
                        Response.Clear();
                        Response.ContentType = "application/force-download";
                        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", filename));
                        Response.BinaryWrite(ms.ToArray());
                        Response.End();

In class for Excel Operations.

 public MemoryStream CreateExcel(DataSet dset)
        {
            MemoryStream ms = new MemoryStream();
            using (SpreadsheetDocument Excelfile = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
            {
                var workbookpart = Excelfile.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                workbookpart.AddNewPart<sharedstringtablepart>();
                var stringTablePart = workbookpart.GetPartsOfType<sharedstringtablepart>().FirstOrDefault();
                var stringTable = new SharedStringTable();
                stringTablePart.SharedStringTable = stringTable;


                foreach (DataTable dt in dset.Tables)
                {
                    CreateNewExcelSheet(dt, dt.TableName, workbookpart, true);
                }
                workbookpart.Workbook.Save();
            }
            return ms;
        }
 protected void CreateNewExcelSheet(DataTable table, string sheetName, WorkbookPart workbk, bool addHeader)
        {
            var stringTable = workbk.GetPartsOfType<sharedstringtablepart>().FirstOrDefault();
            WorksheetPart sheetPart = workbk.AddNewPart<worksheetpart>();
            sheetPart.Worksheet = new Worksheet(new SheetData());
            var sheets = workbk.Workbook.GetFirstChild<sheets>();
            if (sheets == null)
            {
                sheets = workbk.Workbook.AppendChild(new Sheets());
            }

            string relationshipId = workbk.GetIdOfPart(sheetPart);
            var sheetData = sheetPart.Worksheet.GetFirstChild<sheetdata>();
            //ID for Sheet
            uint sheetId = 1;
            if (sheets != null)
            {
                if (sheets.Elements<sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }
            }
            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            uint rowIndex = 0;
            //creates new Row
            var row = new Row { RowIndex = ++rowIndex };
            //Adds Header Row
            if (addHeader)
                AddColumnHeaderRow(table, row, sheetData, stringTable);
            NumberItem nc = new NumberItem();

            foreach (DataRow dr in table.Rows)
            {
                row = new Row { RowIndex = ++rowIndex };
                for (int i = 0; i < dr.ItemArray.Count(); i++)
                {
                    row.AppendChild(GetCellValue(dr[i].ToString(), stringTable.SharedStringTable));
                }
                sheetData.AppendChild(row);

            }
            sheetPart.Worksheet.Save();

        }

   protected Cell GetCellValue(string value, SharedStringTable stringTable)
        {
            Regex decreg = new Regex("^[0-9]([.][0-9]{1,20)?$", RegexOptions.None);
            decimal dummy;
            int dummyint;
            DateTime dummydt;
            var cell = new Cell();

            if (decimal.TryParse(value, out dummy))
            {
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue(value);
            }
            else if (DateTime.TryParse(value, out dummydt))
            {
                cell.DataType = CellValues.SharedString;
                var item = stringTable.AppendChild(new SharedStringItem(new Text(value)));
                cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString());

            }
            else if (int.TryParse(value, out dummyint))
            {
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue(value);
            }
            else if (regexName.IsMatch(value))
            {
                cell.DataType = CellValues.SharedString;
                var item = stringTable.AppendChild(new SharedStringItem(new Text(value)));
                cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString());
            }

            return cell;


        }
 protected static void AddColumnHeaderRow(DataTable table, Row row, SheetData sheetData, SharedStringTablePart stringTable)
        {
            for (var i = 0; i < table.Columns.Count; i++)
            {

                var cell = new Cell { DataType = CellValues.SharedString };
                var item = stringTable.SharedStringTable.AppendChild(new SharedStringItem(new Text(table.Columns[i].ColumnName)));
                cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString());
                row.AppendChild(cell);
            }

            //Append new row to the sheet data
            sheetData.AppendChild(row);
        }
</sheet></sheet></sheetdata></sheets></worksheetpart></sharedstringtablepart></sharedstringtablepart></sharedstringtablepart>