how to export datatable in excel 2007 in asp.net c#?

Hardik Patel

i am using below code but it's not working properly for MS Excel 2007..

DataTable dt = new DataTable();

string str1;

str1 = "SELECT Testt1.StudentCode, Student.StudentID,Student.Name, Testt1.FMark,'' as 'LMark','' as'Status',''as 'Promote' FROM Testt1 INNER JOIN School ON Testt1.SchoolCode = School.SchoolCode INNER JOIN Student ON Testt1.StudentCode = Student.StudentCode where Testt1.Promote ='Yes' and School.SchoolCode =" + DropDownList3.SelectedValue + "";

dt = ObjConn.GetDataTable(str1);

HttpResponse response = HttpContext.Current.Response;

response.Clear();

//response.Charset = "";

response.ContentType = "application/vnd.ms-excel";

response.AddHeader("Content-Disposition", "attachment;filename=Smarks.xls");

//response.AddHeader("Content-Disposition", "C:\"" + fileName + "\"");

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

DataGrid dg = new DataGrid();

dg.DataSource = dt;

dg.DataBind();

dg.RenderControl(htw);

response.Write(sw.ToString());

response.End();

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mukesh Kumar Tiwari13y ago
hi hardik,,,

try this code. its working for me..
----------------------------------

                try
                {
                    string xlsxfile = DateTime.Now.ToString("-dd-MM-yyyy-HH-mm-ss");

                    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    //ExcelApp.Name = "Model Master Report";
                    ExcelApp.Application.Workbooks.Add(Type.Missing);
                    ExcelApp.Columns.ColumnWidth = 20;
                    for (int i = 1; i <= grid_cutomerReport.Columns.Count; i++)
                    {
                        ExcelApp.Cells[1, i] = grid_cutomerReport.Columns[i - 1].HeaderText;
                    }

                    for (int i = 0; i <= grid_cutomerReport.Rows.Count - 2; i++)
                    {
                        for (int j = 0; j <= grid_cutomerReport.Columns.Count - 1; j++)
                        {
                            ExcelApp.Cells[i + 2, j + 1] = grid_cutomerReport.Rows[i].Cells[j].Value.ToString();
                        }
                    }
                    ExcelApp.ActiveWorkbook.SaveCopyAs("D:\\Report\\Customer_Report" + xlsxfile + ".xlt");
                    ExcelApp.ActiveWorkbook.Saved = true;
                    ExcelApp.Quit();
                    MessageBox.Show("Excel file generated successfully ad this Location=D:\\Report\\Customer_Report ", "RBS Customer Support System", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    clear_control();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "RBS Customer Support System", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
0
Hardik Patel13y ago
when i export it that time my sheet name is also get changed..it should be remain static like "Sheet1".
0
Sanjeeb Lenka13y ago
hi,

provide what the error you are getting in which line.
0