• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 881
  • Last Modified:

export to excel

I have seen some references on the net, but none were specific enough to solve my problem (I think b/c those solutions were all for DataGrid). I have a GridView, which I export to an Excel sheet via a button in my ASP.NET form. Problem, is, for whatever reason, (randomly it sure appears) after a random amount of records, values in columns that should be numeric, get that green arrow and "are formatted as text". Is there some way I can force all of my numeric columns to ALWAYS be numeric in the exported Excel file?

0
IT-VAS
Asked:
IT-VAS
4 Solutions
 
hosneylkCommented:
this usually happens if there is at least one record with a non numeric or empty value
0
 
robastaCommented:
1. This may depend with your implementation, but here is what I did:

Code:
 
protected void Btn_ExportClick(object sender, EventArgs e)
    {
       string style = @"<style> .text { mso-number-format:\@; } </script> "; 
    //the line above is where the magic is at!, 
    //that mso-number-format specifies the format (see link for more formats)


        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment;
        filename=MyExcelFile.xls");
        Response.ContentType = "application/excel";

        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        gvUsers.RenderControl(htw);
        // Style is added dynamically
        Response.Write(style); 
        Response.Write(sw.ToString());
        Response.End();
    }

Open in new window



2. The following blog has sample format. Choose yours...
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
IT-VASAuthor Commented:
but it is the same column some will numric and the others get it as text
and it is the numric value when i read from the database and it's don't empty
0
 
Alpesh PatelAssistant ConsultantCommented:
Hi,

Pls let me know your export columns has some encrypted column.

If not then any column which has some special characters so the formatting of columns will mash.

To solve just remove that column from you output and try again. It solves your problem.

I had faced same problem.
0
 
IT-VASAuthor Commented:
hi robasta!
how can i replace the code below in visual basic?
string style = @"<style> .text { mso-number-format:\@; } </script> ";


thanks,
0
 
robastaCommented:
Dim style As String = "<style> .text { mso-number-format:\@; } </script> "
0
 
DanielcmorrisCommented:
you  might want to take a look at this:  OfficeOpenXml

DocumentFormat.OpenXml.dll
DocumentFormat.OpenXml.Extensions.dll

It gives you a lot more control over your excel files.


0
 
IT-VASAuthor Commented:
thanks everyone for your answer,

hi PatelAlpesh!
 you are right ,it happens because  the sentence begins with minus sign så in excel it will be change to text format, so i need to formatting the data before exporting to excel but i don't know how i do that , has anyone any idea, here is my code to exporting to Excel:

Response.AddHeader("content-disposition", "attachment;filename=timecollection.xls")
Response.ContentType = "application/vnd.ms-excel"
Response.ContentEncoding = Encoding.GetEncoding("iso-8859-1")
 Me.EnableViewState = False
 Dim objStrWriter As New System.IO.StringWriter
 Dim objHtmlTextWriter As New System.Web.UI.HtmlTextWriter(objStrWriter)
If gvperson.Visible = True Then
   gvperson.RenderControl(objHtmlTextWriter)
End If
Response.Write(Regex.Replace(objStrWriter.ToString(), "(<a[^>]*>)|(</a>)", " ", RegexOptions.IgnoreCase))
Response.End()
0
 
Kumaraswamy RCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now