Export to MacroEnableExcel File

Export 2 Excel using EPPlus

create table EPPlus
(
Id int,
fname varchar(255),
lname varchar(255),
addr varchar(255),
salary int,
gradute bit,
dob datetime
)
---------------------------------------------------------------------------------------------------------
insert into EPPlus values (1,'harshal','desai','mumbai',10000,1,getdate())

insert into EPPlus values (2,'sneha','gaikwad','mumbai',20000,2,getdate()-10)

insert into EPPlus values (3,'sachin' ,'tendulkar','mumbai',30000,3,getdate()-250)
---------------------------------------------------------------------------------------------------------
CREATE PROCEDURE tableA 
AS   
    SELECT id, fname, lname, addr FROM EPPlus
GO

CREATE PROCEDURE tableB 
AS   
    SELECT id, salary, gradute, dob FROM EPPlus

GO

-----------------------------------------------------------------------------------------------------
---Make Sure You install EPPlus via NugetPackage and Change Query String and create table and SP before execution. Just create empty web site project and below code in page load menthod-----

protected void Page_Load(object sender, EventArgs e)
        {
            //StoreProcedureName and Corresponding sheetName
            Dictionary<string, string> listOfStoreProcedure = new Dictionary<string, string>()
            { { "tableA", "1stSheet" },
              { "tableB", "2ndSheet" }
            };

            string connetionString = null;
            SqlConnection cnn;
            connetionString = @"Data Source=WINDOWS-4MDA05O\SA;Initial Catalog=Quantam;User ID=sa;Password=harshal008";
            cnn = new SqlConnection(connetionString);
            DataSet ds = new DataSet();
            foreach (var spName in listOfStoreProcedure)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(spName.Key, cnn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    DataTable dt = new DataTable();
                    cnn.Open();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    dt.TableName = spName.Key;
                    //Execute 1 by 1 SP and store it in dataset with SP name as a table name
                    ds.Tables.Add(dt);
                    cnn.Close();
                }
                catch (Exception ex)
                {

                }
            }

            ExcelPackage excelTest = new ExcelPackage();
            foreach (DataTable table in ds.Tables)
            {
                //Create Sheet 1 by 1 as per number of tables in Dataset
                var workSheettest = excelTest.Workbook.Worksheets.Add(listOfStoreProcedure[table.TableName]);
                //Basic setting
                workSheettest.TabColor = System.Drawing.Color.Black;
                workSheettest.DefaultRowHeight = 12;
                workSheettest.Row(1).Height = 20;
                workSheettest.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                workSheettest.Row(1).Style.Font.Bold = true;
                int columnCount = table.Columns.Count;
                //Design Column Header
                foreach (DataColumn column in table.Columns)
                {
                    int columnNumber = column.Ordinal + 1;
                    //Cell: First is row number and second is coulmn number
                    workSheettest.Cells[1, columnNumber].Value = column.ColumnName;
                }

                //Data will start with 2nd row in sheet.
                int recordIndexStart = 2;
                foreach (DataRow dr in table.Rows)
                {
                    for (int i = 0; i < columnCount; i++)
                    {
                        workSheettest.Cells[recordIndexStart, i + 1].Value = dr[i];
                    }
                    recordIndexStart++;
                }

                for (int i = 0; i < columnCount; i++)
                {
                    workSheettest.Column(i + 1).AutoFit();
                }
            }

            string excelName = "DataExport2Excel";

            using (var memoryStream = new MemoryStream())
            {
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=" + excelName + ".xlsx");
                excelTest.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }

        }