exporting gridview to excel issue

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I have a gridview that is exported to excel. The code below works. But, the
only drawback is the gridview has 6 pages (sometimes more, sometimes less).
When the export occurs, it takes the grid and the pages and puts it into
excel. I need to have a regular looking spreadsheet (all 112 records)
without the gridview paging look. I have tried putting GridView1.AllowPaging
= false in different spots of the code but it is not working. Here is the
exporting code that I am using.

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
PrepareGridViewForExport(GridView1);
ExportGridToExcel(GridView1, "test");
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified
ASP.NET server control at run time.
return;
}

public void ExportGridToExcel(GridView grdGridView, string fileName)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.xls", fileName));
Response.Charset = "";
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

private void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal lit = new Literal();
string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls.GetType() == typeof(LinkButton))
{
lit.Text = (gv.Controls as LinkButton).Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(DropDownList))
{
lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(CheckBox))
{
lit.Text = (gv.Controls as CheckBox).Checked ? "True" :
"False";
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
if (gv.Controls.HasControls())
{
PrepareGridViewForExport(gv.Controls);
}
}
}

Can someone see the reason the paging is still there in the spreadsheet and
what is needed to rectifiy it?

Thanks.

John
 
I have a gridview that is exported to excel.  The code below works.  But, the
only drawback is the gridview has 6 pages (sometimes more, sometimes less).  
When the export occurs, it takes the grid and the pages and puts it into
excel.  I need to have a regular looking spreadsheet (all 112 records)
without the gridview paging look.  I have tried putting GridView1.AllowPaging
= false in different spots of the code but it is not working.  Here is the
exporting code that I am using.

    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        PrepareGridViewForExport(GridView1);
        ExportGridToExcel(GridView1, "test");
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        // Confirms that an HtmlForm control is rendered for the specified
ASP.NET server control at run time.
        return;
    }

    public void ExportGridToExcel(GridView grdGridView, string fileName)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.xls", fileName));
        Response.Charset = "";
        Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        grdGridView.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

    private void PrepareGridViewForExport(Control gv)
    {
        LinkButton lb = new LinkButton();
        Literal lit = new Literal();
        string name = String.Empty;

        for (int i = 0; i < gv.Controls.Count; i++)
        {
            if (gv.Controls.GetType() == typeof(LinkButton))
            {
                lit.Text = (gv.Controls as LinkButton).Text;
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, lit);
            }
            else if (gv.Controls.GetType() == typeof(DropDownList))
            {
                lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, lit);
            }
            else if (gv.Controls.GetType() == typeof(CheckBox))
            {
                lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
"False";
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, lit);
            }
            if (gv.Controls.HasControls())
            {
                PrepareGridViewForExport(gv.Controls);
            }
        }
    }

Can someone see the reason the paging is still there in the spreadsheet and
what is needed to rectifiy it?

Thanks.

John
Click to expand...


If I were you I would export data using the datasource but not the
grid itself. In this case you don't need to do anything with grid, you
would just loop through the dataset and push all rows to the browser.

Similar to this code
public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment;
filename=" + name + ".csv");
context.Response.End();
}

http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

Regarding your problem. I don't see where GridView1.AllowPaging is
set. Did you try to set PageSize?
 
Alexey Smirnov said:
I have a gridview that is exported to excel. The code below works. But, the
only drawback is the gridview has 6 pages (sometimes more, sometimes less).
When the export occurs, it takes the grid and the pages and puts it into
excel. I need to have a regular looking spreadsheet (all 112 records)
without the gridview paging look. I have tried putting GridView1.AllowPaging
= false in different spots of the code but it is not working. Here is the
exporting code that I am using.

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
PrepareGridViewForExport(GridView1);
ExportGridToExcel(GridView1, "test");
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified
ASP.NET server control at run time.
return;
}

public void ExportGridToExcel(GridView grdGridView, string fileName)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.xls", fileName));
Response.Charset = "";
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

private void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal lit = new Literal();
string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls.GetType() == typeof(LinkButton))
{
lit.Text = (gv.Controls as LinkButton).Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(DropDownList))
{
lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(CheckBox))
{
lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
"False";
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
if (gv.Controls.HasControls())
{
PrepareGridViewForExport(gv.Controls);
}
}
}

Can someone see the reason the paging is still there in the spreadsheet and
what is needed to rectifiy it?

Thanks.

John
Click to expand...


If I were you I would export data using the datasource but not the
grid itself. In this case you don't need to do anything with grid, you
would just loop through the dataset and push all rows to the browser.

Similar to this code
public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment;
filename=" + name + ".csv");
context.Response.End();
}

http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

Regarding your problem. I don't see where GridView1.AllowPaging is
set. Did you try to set PageSize?
Click to expand...


I will give it a try and let you know the results. From reading what you
say and from the link, it seems this way would be cleaner and simpler. It
may also resolve several other export issues I have with background coloring,
etc.
.... John
 
Alexey, well I gave it a try. I got all the way to excel but no data showed
up. I am providing the info below that I used to get as far as I did. The
table in the Export lin of the btn click errored out until I added the
DataTable table line in the same event. I even found the correct contenttype.

See something wrong that I am missing?
Thanks.
John

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
GridView1.DataSource = bindgrid();
GridView1.DataBind();
DataTable table = new DataTable();
GridView1.AllowPaging = false;
GridView1.PageSize = 1;

PrepareGridViewForExport(GridView1);
//ExportGridToExcel(GridView1, "test");

ExportToSpreadsheet(table, "test");
}

public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();

foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + " ;");
}
context.Response.Write(Environment.NewLine);

foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AppendHeader("content-disposition",
string.Format("attachment;filename={0}.xlsx", name));
context.Response.End();
}





Alexey Smirnov said:
I have a gridview that is exported to excel. The code below works. But, the
only drawback is the gridview has 6 pages (sometimes more, sometimes less).
When the export occurs, it takes the grid and the pages and puts it into
excel. I need to have a regular looking spreadsheet (all 112 records)
without the gridview paging look. I have tried putting GridView1.AllowPaging
= false in different spots of the code but it is not working. Here is the
exporting code that I am using.

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
PrepareGridViewForExport(GridView1);
ExportGridToExcel(GridView1, "test");
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified
ASP.NET server control at run time.
return;
}

public void ExportGridToExcel(GridView grdGridView, string fileName)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.xls", fileName));
Response.Charset = "";
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

private void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal lit = new Literal();
string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls.GetType() == typeof(LinkButton))
{
lit.Text = (gv.Controls as LinkButton).Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(DropDownList))
{
lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(CheckBox))
{
lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
"False";
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
if (gv.Controls.HasControls())
{
PrepareGridViewForExport(gv.Controls);
}
}
}

Can someone see the reason the paging is still there in the spreadsheet and
what is needed to rectifiy it?

Thanks.

John
Click to expand...


If I were you I would export data using the datasource but not the
grid itself. In this case you don't need to do anything with grid, you
would just loop through the dataset and push all rows to the browser.

Similar to this code
public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment;
filename=" + name + ".csv");
context.Response.End();
}

http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

Regarding your problem. I don't see where GridView1.AllowPaging is
set. Did you try to set PageSize?
Click to expand...
 
Alexey, I took a break from it all and got back and got it to work.
Thanks for your patience.
John


JohnE said:
Alexey, well I gave it a try. I got all the way to excel but no data showed
up. I am providing the info below that I used to get as far as I did. The
table in the Export lin of the btn click errored out until I added the
DataTable table line in the same event. I even found the correct contenttype.

See something wrong that I am missing?
Thanks.
John

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
GridView1.DataSource = bindgrid();
GridView1.DataBind();
DataTable table = new DataTable();
GridView1.AllowPaging = false;
GridView1.PageSize = 1;

PrepareGridViewForExport(GridView1);
//ExportGridToExcel(GridView1, "test");

ExportToSpreadsheet(table, "test");
}

public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();

foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + " ;");
}
context.Response.Write(Environment.NewLine);

foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AppendHeader("content-disposition",
string.Format("attachment;filename={0}.xlsx", name));
context.Response.End();
}





Alexey Smirnov said:
I have a gridview that is exported to excel. The code below works. But, the
only drawback is the gridview has 6 pages (sometimes more, sometimes less).
When the export occurs, it takes the grid and the pages and puts it into
excel. I need to have a regular looking spreadsheet (all 112 records)
without the gridview paging look. I have tried putting GridView1.AllowPaging
= false in different spots of the code but it is not working. Here is the
exporting code that I am using.

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
PrepareGridViewForExport(GridView1);
ExportGridToExcel(GridView1, "test");
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified
ASP.NET server control at run time.
return;
}

public void ExportGridToExcel(GridView grdGridView, string fileName)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.xls", fileName));
Response.Charset = "";
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

private void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal lit = new Literal();
string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls.GetType() == typeof(LinkButton))
{
lit.Text = (gv.Controls as LinkButton).Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(DropDownList))
{
lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(CheckBox))
{
lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
"False";
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
if (gv.Controls.HasControls())
{
PrepareGridViewForExport(gv.Controls);
}
}
}

Can someone see the reason the paging is still there in the spreadsheet and
what is needed to rectifiy it?

Thanks.

John
Click to expand...


If I were you I would export data using the datasource but not the
grid itself. In this case you don't need to do anything with grid, you
would just loop through the dataset and push all rows to the browser.

Similar to this code
public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment;
filename=" + name + ".csv");
context.Response.End();
}

http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

Regarding your problem. I don't see where GridView1.AllowPaging is
set. Did you try to set PageSize?
Click to expand...
Click to expand...
 
Alexey, I took a break from it all and got back and got it to work.  
Thanks for your patience.
John



JohnE said:
Alexey, well I gave it a try.  I got all the way to excel but no datashowed
up.  I am providing the info below that I used to get as far as I did..  The
table in the Export lin of the btn click errored out until I added the
DataTable table line in the same event.  I even found the correct contenttype.
Click to expand...
See something wrong that I am missing?
Thanks.
John
Click to expand...
    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        GridView1.DataSource = bindgrid();
        GridView1.DataBind();
        DataTable table = new DataTable();
        GridView1.AllowPaging = false;
        GridView1.PageSize = 1;
Click to expand...
        PrepareGridViewForExport(GridView1);
        //ExportGridToExcel(GridView1, "test");
Click to expand...
        ExportToSpreadsheet(table, "test");
    }
Click to expand...
    public static void ExportToSpreadsheet(DataTable table, string name)
    {
        HttpContext context = HttpContext.Current;
        context.Response.Clear();
Click to expand...
        foreach (DataColumn column in table.Columns)
        {
            context.Response.Write(column.ColumnName + " ;");
        }
        context.Response.Write(Environment.NewLine);
Click to expand...
        foreach (DataRow row in table.Rows)
        {
            for (int i = 0; i < table.Columns.Count; i++)
            {
                context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
            }
            context.Response.Write(Environment.NewLine);
        }
        context.Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        context.Response.AppendHeader("content-disposition",
string.Format("attachment;filename={0}.xlsx", name));
        context.Response.End();
    }
Click to expand...

I have a gridview that is exported to excel.  The code below works.  But, the
only drawback is the gridview has 6 pages (sometimes more, sometimes less).  
When the export occurs, it takes the grid and the pages and puts itinto
excel.  I need to have a regular looking spreadsheet (all 112 records)
without the gridview paging look.  I have tried putting GridView1..AllowPaging
= false in different spots of the code but it is not working.  Here is the
exporting code that I am using.
    protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        PrepareGridViewForExport(GridView1);
        ExportGridToExcel(GridView1, "test");
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        // Confirms that an HtmlForm control is rendered for the specified
ASP.NET server control at run time.
        return;
    }
    public void ExportGridToExcel(GridView grdGridView, string fileName)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.xls", fileName));
        Response.Charset = "";
        Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        StringWriter stringWrite = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        grdGridView.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
    private void PrepareGridViewForExport(Control gv)
    {
        LinkButton lb = new LinkButton();
        Literal lit = new Literal();
        string name = String.Empty;
        for (int i = 0; i < gv.Controls.Count; i++)
        {
            if (gv.Controls.GetType() == typeof(LinkButton))
            {
                lit.Text = (gv.Controls as LinkButton).Text;
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, lit);
            }
            else if (gv.Controls.GetType() == typeof(DropDownList))
            {
                lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, lit);
            }
            else if (gv.Controls.GetType() == typeof(CheckBox))
            {
                lit.Text = (gv.Controls as CheckBox)..Checked ? "True" :
"False";
                gv.Controls.Remove(gv.Controls);
                gv.Controls.AddAt(i, lit);
            }
            if (gv.Controls.HasControls())
            {
                PrepareGridViewForExport(gv.Controls);
            }
        }
    }
Can someone see the reason the paging is still there in the spreadsheet and
what is needed to rectifiy it?
Thanks.
John
If I were you I would export data using the datasource but not the
grid itself. In this case you don't need to do anything with grid, you
would just loop through the dataset and push all rows to the browser.
Similar to this code
public static void ExportToSpreadsheet(DataTable table, string name)
{
   HttpContext context = HttpContext.Current;
   context.Response.Clear();
   foreach (DataColumn column in table.Columns)
   {
    context.Response.Write(column.ColumnName + ";");
   }
   context.Response.Write(Environment.NewLine);
   foreach (DataRow row in table.Rows)
   {
    for (int i = 0; i < table.Columns.Count; i++)
    {
     context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
    }
    context.Response.Write(Environment.NewLine);
   }
   context.Response.ContentType = "text/csv";
   context.Response.AppendHeader("Content-Disposition", "attachment;
filename=" + name + ".csv");
   context.Response.End();
}
http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-d....
Regarding your problem. I don't see where GridView1.AllowPaging is
set. Did you try to set PageSize?- Hide quoted text -
Click to expand...
Click to expand...


- Show quoted text -
Click to expand...


I think the problem was here: DataTable table = new DataTable();

Anyway, glad that it works now for you
 
The article below is incorrect. A CSV file is a comma separated file not a
semi-colon separated file. The author incorrectly inserts semi-colons rather
than commas.

S

I have a gridview that is exported to excel. The code below works. But,
the
only drawback is the gridview has 6 pages (sometimes more, sometimes
less).
When the export occurs, it takes the grid and the pages and puts it into
excel. I need to have a regular looking spreadsheet (all 112 records)
without the gridview paging look. I have tried putting
GridView1.AllowPaging
= false in different spots of the code but it is not working. Here is the
exporting code that I am using.

protected void btnExportToExcel_Click(object sender, EventArgs e)
{
PrepareGridViewForExport(GridView1);
ExportGridToExcel(GridView1, "test");
}

public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified
ASP.NET server control at run time.
return;
}

public void ExportGridToExcel(GridView grdGridView, string fileName)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.xls", fileName));
Response.Charset = "";
Response.ContentType =
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdGridView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}

private void PrepareGridViewForExport(Control gv)
{
LinkButton lb = new LinkButton();
Literal lit = new Literal();
string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls.GetType() == typeof(LinkButton))
{
lit.Text = (gv.Controls as LinkButton).Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(DropDownList))
{
lit.Text = (gv.Controls as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
else if (gv.Controls.GetType() == typeof(CheckBox))
{
lit.Text = (gv.Controls as CheckBox).Checked ? "True" :
"False";
gv.Controls.Remove(gv.Controls);
gv.Controls.AddAt(i, lit);
}
if (gv.Controls.HasControls())
{
PrepareGridViewForExport(gv.Controls);
}
}
}

Can someone see the reason the paging is still there in the spreadsheet
and
what is needed to rectifiy it?

Thanks.

John
Click to expand...


If I were you I would export data using the datasource but not the
grid itself. In this case you don't need to do anything with grid, you
would just loop through the dataset and push all rows to the browser.

Similar to this code
public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment;
filename=" + name + ".csv");
context.Response.End();
}

http://www.webpronews.com/expertarticles/2006/11/28/aspnet-export-a-datatable-to-excel

Regarding your problem. I don't see where GridView1.AllowPaging is
set. Did you try to set PageSize?
 
The article below is incorrect. A CSV file is a comma separated file not a
semi-colon separated file. The author incorrectly inserts semi-colons rather
than commas.
Click to expand...

This is correct for all countries where semi-colon is defined as a
list separator (in Europe, for instance). US uses comma, you can see
it if you go to Control Panel - Regional Options - List Separator
 
Back
Top