Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

exporting data to excel

i have a gridview and a export to excel button and everything works great but not sure if this is has to do with excel or my code

in one of my gridview column i have "control number" looks looks like this: 66635464654653300000 BUT when i export to excel it looks like this: 6.66355E+19

how can i fix or if any fix?

thanks.
0
nisarkhan
Asked:
nisarkhan
  • 9
  • 6
1 Solution
 
abelCommented:
Excel auto-configures the values. If you select the column and change the appearance to "Text" you will see that the original value is in there.
0
 
nisarkhanAuthor Commented:
the end user is not computer litrate and there is no way that end user will ever be able to understand or follow the instructions.

my question is, how can i modify in code?
i'm passing whole gridview to export the data.
using this code (http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html)
0
 
abelCommented:
It depends a bit on how you create your Excel file. If you do that with only setting the correct headers, like explained here: http://support.microsoft.com/kb/199841/EN-US/, then you should use the formula notation in your <TD> to make sure it is treated as a string:

<td>="66635464654653300000"</td>

If you do not use a normal grid/table and you are programming using the Excel VBA object model, the above might work, but you can also use a different method, that is a bit more elegant in my opinion:

Worksheets("Sheet1").Columns(3).NumberFormat = "Text"

You can of course do that for any Range that you specify, columns, rows etc.

A third approach I was thinking of is changing the width of the column, but that is quite a bit more tricky and many users have reported that their methods didn't always succeed. Also, it is hard to calculate the width of the string, because you don't know the default font used in the Excel target application (and even if you do know it is tricky). The AutoFit property unfortunately does not what it says it does, so you're out of luck there too.

Hope this helps a bit.

-- Abel --

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
nisarkhanAuthor Commented:
i'm not using any vba object
i'm using this code (http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html)

can you tell me how/what do i need to change ?

thanks.
0
 
abelCommented:
Ah, my apologies. I thought that was your own blog and you wanted some hints. But you want a hands-on on the code. No problem, I'll have a look.
0
 
abelCommented:
I've had a look at that code, and unfortunately, it takes the approach of the content-type header for downloading data in a grid as an excel file. All it does is looping through all the controls in the Grid, tries to translate them somehow to something readable (a checked checkbox becomes true / false) and then they become Literals (which means: no controls).

You could add an extra loop and change anything that is parsable as a number into ="[value]", where [value] is the original value. The problem with this approach is, of course, that the benefits of using Excel with all its excellent features for handling numeric data totally disappear.

Maybe you should just add a text on your page saying to people that download it: if the data looks like "1234E678" then doubleclick between the headers to autofit the excel column. I know that's not a solution, but it's something.

There's unfortunately no way to know in advance what values will become too large and what values stay right.

One thing about this method of "transforming" to XLS format: you don't transform anything. The client must have Excel installed and that instance, on the client, will do the transformation for you. That is exactly why you do not have too much possibilities in manipulating the data... Sorry that I don't have some better news. Apparently something that appears simple is, based on chosen method and technology (excel) quite hard to impossible to do....
0
 
nisarkhanAuthor Commented:
abel: thanks for your help but i hope somebody will provide me solution.
0
 
abelCommented:
It's not that hard to make a solution, but you'll have to stop using the tool that your referred to and start using the true Excel libraries (which you will also have to redistribute, but MS allows that, so there shouldn't be a problem)  or any other Excel export tool that gives you enough options.

It is a rather complex problem, with easy solutions if you can change your approach, of if you can change the code (like I said: loop through all cells at the end of the export feature code and change the ones you want).
0
 
nisarkhanAuthor Commented:
mod: please close this question
0
 
abelCommented:
Do I get this right, because you do not like the solution you want to close it? I don't agree. It was considered an "easy" question (125 points) and you got extensive comments on possibilities to tackle this. Also an answer like "cannot be done because..." is still an answer. I spend a lot of time finding this out for you, and I'd be happy to help you further, just don't close questions if you don't like the answer.
0
 
nisarkhanAuthor Commented:
if you spend lot of time means do i have to accept as a answer? and more over you were rambling and i did not get anything from yoru sugguestions.

mod please close the question and refund my points.

thanks.
0
 
abelCommented:
Excuse me? Rambling? I don't mind you disagreeing, but the solution presented here was a solution based on the code you showed me. I tested it and it worked.

Do you remember my first comment? I suggested something about using a string instead of a number:

<td>="66635464654653300000"</td>
Later, you said that wouldn't work for you because you used code from a link, here: http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html. I followed the link, downloaded the code, found out that they used exactly the same approach I was talking about, so we were back at the beginning and I took it from there.

I suggested the following, which can be done at the end of the parsing section of the code of that link. quote:

You could add an extra loop and change anything that is parsable as a number into ="[value]", where [value] is the original value. The problem with this approach is, of course, that the benefits of using Excel with all its excellent features for handling numeric data totally disappear.
Maybe I didn't put that friendly, or maybe you did not like that approach or maybe I was just a bit coarse. In which case, my apologies. However, it is the way to tackle your problem, but it is not a nice one and will render much of your Excel abilities useless. The main problem here is the presentation of the data to the browser, which changes the HTTP Content Type, which will open Excel on the client side and Excel will render this. This, too, was mentioned earlier by me and is a serious limitation:

The client must have Excel installed and that instance, on the client, will do the transformation for you.
Which is why I suggested using a different approach, which would in turn give you all the abilities to change the appearance. The approach chosen by you or your project lead limit your abilities, but gives quick results.

If you still consider this rambling, I would like to invite you to tell me exactly which parts are rambling and why. I gave you a link to Microsoft which explains the same approach you are using: http://support.microsoft.com/kb/199841/EN-US/. This is not directly noticeable, because the smart thing about your code is that it does all this on the fly and you do not need to think about it anymore.

Like I said earlier, i'd love to help you out, and if you don't want me to help you out for whatever reason, that's fine too. But let that not be a reason to throw sensible solutions / approaches down the drain.

No, you do not need to award points if you are not satisfied with an answer, but you do need to help the experts to help you. If you close a thread in the middle just for the sake of closing a thread, I will object to that. You've asked other experts to come up with better ideas. They haven't and probably for good reasons.

My solution to your question remains and I can strongly recommend you to go to the team leader or project manager and request a change in approach. It doesn't take long and it will give you loads of flexibility. You'll even win a lot of time with it in the long run. Now, if that isn't rambling, but it is all for the better, believe me! ;-)
0
 
nisarkhanAuthor Commented:
//
My solution to your question remains and I can strongly recommend you to go to the team leader or project manager and request a change in approach. It doesn't take long and it will give you loads of flexibility. You'll even win a lot of time with it in the long run. Now, if that isn't rambling, but it is all for the better, believe me! ;-)

//

nope i no need to change anything and i able to fix with one line of code :)

0
 
abelCommented:
> nope i no need to change anything and i able to fix with one line of code :)

very good. I showed you a couple, I assume you used one of them, or did you find another even?

But I'm glad you've found a way to fix it.

tx, Vee_Mod, for your looking into this.

tx nisarkhan for the grade.
0
 
abelCommented:
It's a pity you think about it like that. I tested the full solution by hand. You do not have to believe it, but please don't call it rambling, you know it isn't.

There are more paths to a solution. I found you a couple and you didn't like them, you chose another path and you do not share it here. You are long enough around that in such cases it is fair to either share your solution with the rest of us, or to close the question and to award the points to the participating experts.

You should also know that a well-known moderator like Vee_Mod does not do "whatever it feels like" but will take action based his best judgment. A bit of mutual respect to those who volunteer to give others their valuable time shouldn't be too much to be asked.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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