Click here to Skip to main content
15,667,014 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I m using with c#.
I have two data tables which fetch some data from sql data base.
Now I want to export these data to excel sheet in two sheets.
Can any one help me ?
protected void btn_download_Click(object sender, EventArgs e)
        from = (fromCalendar.SelectedDate);
        to = (ToCalendar.SelectedDate);
        client_id = Convert.ToInt32(drp_Client.SelectedValue);
        Compaign = drp_Compaign.SelectedValue;


                SqlCommand cmd_down_tw = new SqlCommand("proc_GetSemDataFromToDate", con);
                cmd_down_tw.CommandType = CommandType.StoredProcedure;

                cmd_down_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
                cmd_down_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
                cmd_down_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
                cmd_down_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

                SqlDataAdapter da_down_tw = new SqlDataAdapter(cmd_down_tw);
                da_down_tw.SelectCommand = cmd_down_tw;
                DataTable dt_down_tw = new DataTable();

                SqlCommand cmd_comp_tw = new SqlCommand("proc_GetAllSemDataFromToDate", con);
                cmd_comp_tw.CommandType = CommandType.StoredProcedure;

                cmd_comp_tw.Parameters.Add("@FromDate", System.Data.SqlDbType.DateTime).Value = (from);
                cmd_comp_tw.Parameters.Add("@ToDate", System.Data.SqlDbType.DateTime).Value = (to);
                cmd_comp_tw.Parameters.Add("@ClientId", System.Data.SqlDbType.Int).Value = (client_id);
                cmd_comp_tw.Parameters.Add("@Compaign", System.Data.SqlDbType.VarChar).Value = (Compaign);

                da_comp_tw = new SqlDataAdapter(cmd_comp_tw);
                da_comp_tw.SelectCommand = cmd_comp_tw;
                dt_comp_tw = new DataTable();


                ExportDataSetToExcel(dt_down_tw, "Report.xls");


    public void ExportDataSetToExcel(DataTable dt, string filename)
        HttpResponse response = HttpContext.Current.Response;

        // first let's clean up the response.object   
        response.Charset = "";

        // set the response mime type for excel   
        response.ContentType = "application/";
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

        // create a string writer   
        using (StringWriter sw = new StringWriter())
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                // instantiate a datagrid   
                DataGrid dg = new DataGrid();
                dg.DataSource = dt;


Please modify this code and make it as i expect. Its urgent .
Updated 20-Nov-12 0:07am
StackQ 6-Nov-12 5:47am    
u r using code at ExportDataSetToExcel,but i don't know anything about ,so it's difficult for me, but i can give u logic to save in 2 sheets
faisal23 6-Nov-12 5:52am    
ok bro no problem i will manage this ok thankx
StackQ 6-Nov-12 5:53am    
ok,try.. gudluck
virus131 21-Nov-12 0:35am    
i have to add large text file in ms access dataBase field(ie in one column).but i have problem of data shifting.Plz help.The problem is only when i insert large file up to 10,000 lines. .............waiting for reply
faisal23 21-Nov-12 0:50am    
why are you ask in comment. Ask this question separately.


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);
                        Response.ContentType = "application/force-download";
                        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", filename));

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();
                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);
            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 };
            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));



   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());

            //Append new row to the sheet data
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900