Change name of sheet in export to excel

Question

I have export to excel code which gets data from database then create dynamic gridview and then export that data to excel.

Problem is that i it is taking sheet name same as i mentioned filename. So how do i change sheet name

Here is code i am using

GridView gridView = new GridView();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "SummaryReport " + Helper.GetTime(DateTime.UtcNow).ToString() + ".xls"));
Response.ContentType = "application/ms-excel";
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
gridView.DataSource = ReportExecutor.ReportExportData(startDate, endDate);
gridView.DataBind();
//This will change the header background color
gridView.HeaderRow.Style.Add("background-color", "#FFFFFF"); //
//This will apply style to gridview header cells
for (int index = 0; index < gridView.HeaderRow.Cells.Count; index++)
{
    gridView.HeaderRow.Cells[index].Style.Add("background-color", "#778899"); //Light Slate Gray
    gridView.HeaderRow.Cells[index].Style.Add("foreground-color", "#ffffff"); // White
}
int index2 = 1;
//This will apply style to alternate rows
foreach (GridViewRow gridViewRow in gridView.Rows)
{                   
    gridViewRow.BackColor = Color.White;
    if (index2 <= gridView.Rows.Count)
    {
        if (index2 % 2 != 0)
        {
            for (int index3 = 0; index3 < gridViewRow.Cells.Count; index3++)
            {
                gridViewRow.Cells[index3].Style.Add("background-color", "#e6e6fa");// Lavender
            }
        }
    }
    index2++;
}
gridView.RenderControl(htmlTextWriter);
////  Response.Write(style);
Response.Write(stringWriter.ToString());
Response.End();

Presently you are getting `Helper.GetTime(DateTime.UtcNow).ToString()` name right? — Shreyas Achar, Apr 04 '14 at 05:16
No i am getting filename and sheet name as SummaryReport+Date and Time — Mahajan344, Apr 04 '14 at 05:18
As you can see from code i mentioned filename so it should work only for filename not for sheetname name.. i means i mentioned filename as 'SummaryReport+Date and Time' which is working for filename but problom is it is renaming sheet name with filename name too... And i want sheet name different — Mahajan344, Apr 04 '14 at 05:22
May be this link will help you http://stackoverflow.com/questions/6086185/export-to-excel-and-change-sheet-name — Shreyas Achar, Apr 04 '14 at 05:27

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

I'm afraid you can't change the sheet name of an excel file generated from setting Response content type as excel. Refer accepted answer of SO question and from ASP.NET forum

You could also take a look at EPPlus, which has an option to edit sheetnames in code.

edited May 23 '17 at 10:26

Community

1
1

answered Apr 04 '14 at 05:21

LakshmiNarayanan

1,158
9
12

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

It's not possible to do with StringWriter. You may have to re-open and then rename the sheet once it's successfully written using the StringWriter.

Otherwise, you have to use an alternate method to create the Excel. Here's an example

Export GridView to multiple Excel sheet

edited May 23 '17 at 12:05

Community

1
1

answered Apr 04 '14 at 05:21

Sam

2,917
1
15
28

Change name of sheet in export to excel

2 Answers2