Solved

export to excel

Posted on 2010-11-30
11
860 Views
Last Modified: 2012-05-10
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
Comment
Question by:IT-VAS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 10

Expert Comment

by:hosneylk
ID: 34237502
this usually happens if there is at least one record with a non numeric or empty value
0
 
LVL 14

Assisted Solution

by:robasta
robasta earned 250 total points
ID: 34237509
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
 
LVL 14

Expert Comment

by:robasta
ID: 34237512
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:IT-VAS
ID: 34237525
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
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 125 total points
ID: 34237555
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
 

Author Comment

by:IT-VAS
ID: 34237585
hi robasta!
how can i replace the code below in visual basic?
string style = @"<style> .text { mso-number-format:\@; } </script> ";


thanks,
0
 
LVL 14

Accepted Solution

by:
robasta earned 250 total points
ID: 34237803
Dim style As String = "<style> .text { mso-number-format:\@; } </script> "
0
 
LVL 4

Assisted Solution

by:Danielcmorris
Danielcmorris earned 125 total points
ID: 34275597
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
 

Author Comment

by:IT-VAS
ID: 34280640
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
 
LVL 29

Expert Comment

by:Kumaraswamy R
ID: 34459420
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question