Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 at 7-Jun-12 5:48am
   
Tried it. But i am getting the same result..
Keerthi Kumar(Andar) at 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  

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 515
1 OriginalGriff 498
2 Abhinav S 305
3 sanket saxena 295
4 thatraja 235
0 Sergey Alexandrovich Kryukov 8,482
1 OriginalGriff 4,830
2 Peter Leow 3,794
3 Maciej Los 3,515
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 7 Jun 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid