Click here to Skip to main content
12,350,121 members (26,669 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# C#4.0
Hi,

I am developing a web application with c# in which there there is a page which containins a grodview.Gridview is having integer data and there is also a button to export the gridview data to excel file. Exporting to excel is working fine. But when the gridview is having large integer values, the number is getting formatted in the excel sheet. Can anyone tell me how to preent the formatting of numbers in excel.Please find the code below.

Code to export:
/// <summary>
        /// Export To Excel
        /// </summary>
public static void Export(string fileName, GridView gv)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format(CultureInfo.CurrentCulture, "attachment; filename={0}", fileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";
 
            using (StringWriter sw = new StringWriter(CultureInfo.CurrentCulture))
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {                   
                    Table table = new Table();
                    table.GridLines = gv.GridLines;                   
                    if (gv.HeaderRow != null)
                    {
                        GridViewExportUtility.PrepareControlForExport(gv.HeaderRow);
                        table.Rows.Add(gv.HeaderRow);
                    }                  
                    foreach (GridViewRow row in gv.Rows)
                    {
                        GridViewExportUtility.PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }
                   
                    if (gv.FooterRow != null)
                    {
                        GridViewExportUtility.PrepareControlForExport(gv.FooterRow);
                        table.Rows.Add(gv.FooterRow);
                    }
                   
                    table.RenderControl(htw);
                  
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }
 
/// <summary>
        /// Replace any of the contained controls with literals
/// </summary>
private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is Image)
                {
                    control.Controls.Remove(current);
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                }
               
                if (current.HasControls())
                {
                    GridViewExportUtility.PrepareControlForExport(current);
                }
            }
        }

Gridview Data:

Status Matter Number

Sent 637
Saved 3409009408908908

Excel Data:

Status Matter Number

Sent 637
Saved 3.40901E+15

Please help.

Thanks in advance,
Sruthi
Posted 6-Jun-12 18:43pm
SruthiR1.3K
Edited 6-Jun-12 20:30pm
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

try following with every cell

before Exporting each cell Convert Data to string and concatenate it with "'"
ex:-
if Cell has contain value 123456, then

"'" + (123456).ToString()
  Permalink  
Comments
SruthiR 7-Jun-12 5:48am
   
Tried it. But i am getting the same result..
Keerthi Kumar(Andar) 24-Jan-14 7:02am
   
Am also facing same problem...can you please tell me how to you solved this problem?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi, there are some research on exporting to Excel, you may have a check:
Exporting Data to Excel[^]
9 Solutions to Export Data to Excel for ASP.NET[^]
Export Excel File for C#[^]
  Permalink  
Comments
NekoNao 22-Sep-14 2:02am
   
do you have answer for this?
Export to Excel : Two DataGridView[^

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 7 Jun 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100