How to format numbers as string data-types while exporting to Excel in ASP.NET?

Question

In my ASP.NET Web-form Project I have an Event which Export Data (List<Profit>) to Excel . I'm using the old fashion way , writing to HTML file with .XLS extenstionmode

Profit Entity has some string properties which has numeric data like "100000561234"

When I export to Excel , Those Columns appear like 1.00001E+11 in Excel columns, So the user has to Right-Click on Excel column and change format-cell.

Any help appreciated

How exactly are you doing the export to Excel? Whatever library you're using should have a mechanism to set the style on the cell as you export. If you're doing it the niaeve 'write an HTML file with an .XLS extension' way you're probably be out of luck. — Rup
– Rup, Commented Apr 11, 2012 at 8:32
Right - in that case you can either try and get an HTML export from Excel with a cell formatted properly and look for the mso attributes that it puts on the cell to indicate the formatting (and declare the mso namespace at the top in the same way too) or you can switch to one of the Excel generation libraries in this question which will have support for formatting cells properly too. — Rup
– Rup, Commented Apr 11, 2012 at 8:37
I found EPPlus to be reliable and easy to use. epplus.codeplex.com — Sascha
– Sascha, Commented Apr 11, 2012 at 8:39

Arun Singh · Accepted Answer · 2012-07-02 05:40:47Z

4

Simply Add the following line before your HTML code, it will resolve the issue. This style sheet will be applied on every cell and no need to format the cell as string manually.

Response.Write("<style> TD { mso-number-format:\@; } </style>");

answered Jul 2, 2012 at 5:40

Arun Singh

1,5464 gold badges19 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Priyansh Gandhi · Accepted Answer · 2025-01-08 12:00:06Z

0

Simply Add the following code in VB.net from Code Behind

    For Each row As GridViewRow In grdvDummyExport.Rows

     For Each cell As TableCell In row.Cells
       Dim columnName As String = GetColumnNameOfTableCell(cell)
         If columnName = "Loan Number" Then
              'cell.CssClass = "textmode"
               cell.Style("mso-number-format") = "\@"
         End If

     Next
   Next


  Private Function GetColumnNameOfTableCell(ByVal tc As TableCell) 
    As String
    Return(CType((CType(tc,DataControlFieldCell)).ContainingField, BoundField)).DataField

  End Function

edited Jan 8 at 12:00

answered Jan 8 at 11:53

Priyansh Gandhi

11 bronze badge

Collectives™ on Stack Overflow

How to format numbers as string data-types while exporting to Excel in ASP.NET?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related