exporting gridview to excel issue


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
 
Ad

Advertisements

G

Guest

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


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?
 
J

JohnE

Anon User 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


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?


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
 
J

JohnE

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





Anon User 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


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?
 
J

JohnE

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





Anon User 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


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?
 
G

Guest

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

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
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 -


- Show quoted text -


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

Anyway, glad that it works now for you
 
Ad

Advertisements

S

SAL

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


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?
 
Ad

Advertisements

G

Guest

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top