• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to stop auto formatting in excel after export

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I have a requirement where we need to show use min to user on web and there is an utility by which user can download information in excel,

problem is if user have

date min used(min:sec)
1/12/2012 2:00
2/1/2012 300:00
when user export this information in excel then

date min used(min:sec)
1/12/2012 2:00
2/1/2012 300:00:00
issue is with excel auto formatting. please advice any solution.

code :
 
Ranch Hand
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Instead of sitting and trying to do the formatting via a program I would convey to the user to do the same via excel itself. Its like catching your nose with your hand behind your head. ;-)
 
sanjay kumar gupta
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes this can be a way,but this is not good way to say something to do by user because we are as programmer we make program to reduce user's work and also may be user do't know about how excel works.it will create bad impression to user.
 
Akhilesh Trivedi
Ranch Hand
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you trying to say that you are giving excel format to a user who doesn't know to use excel? In that case I would prefer to give him HTML/PDF/CSV or simply a text but never an excel.
Rather I would go one more step ahead and ask him what is he going to do with the data, simply read it or process it further, and look for opportunities if I can do that processing for him/her.
 
sanjay kumar gupta
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
no I am saying may be some of user do't know.

Akhilesh Trivedi wrote:Are you trying to say that you are giving excel format to a user who doesn't know to use excel? In that I would prefer to give him HTML/PDF/CSV.

 
Akhilesh Trivedi
Ranch Hand
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

sanjay kumar gupta wrote:no I am saying may be some of user do't know.



I redited my last post. Know who your user is, I think its of no 'use' to put that functionality there.
 
Akhilesh Trivedi
Ranch Hand
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If you are looking for a variety of users, then put a generic/common/basic/typical thing.
 
sanjay kumar gupta
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
sorry Akhilesh ,but I have to go with approach only because this is not my personal requirement ,this is existing tools for this small issue we can't change whole thing.

Akhilesh Trivedi wrote:If you are looking for a variety of users, then put a generic/common/basic/typical thing.

 
Akhilesh Trivedi
Ranch Hand
Posts: 1609
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
First, as far as I see this has nothing to do with servlets. You may have to look into what is the library or how you are processing/generating the excel-file.
 
Sheriff
Posts: 22784
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Akhilesh Trivedi wrote:First, as far as I see this has nothing to do with servlets. You may have to look into what is the library or how you are processing/generating the excel-file.


I agree on both accounts. If you want to prevent Excel auto-formatting its cells, you should explicitly format the cells.

I'll move this thread to a better forum.
 
sanjay kumar gupta
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Still not get any suggestion ,I looking forward for some reply. thanks a lot in advance.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you really creating a file in the XLS format, ie. are you using something like Apache POI to do it? Or it is just a plain text file which you store under a name of UnbilledMinutes.xls?

If you're using a library to create the XLS file, consult that library documentation to find out how to specify the formats.

If you're storing a plain text file, you can prepend an apostrophe in front of a text to stop Excel trying to format it. The apostrophe will be hidden. However, Excel will treat these cells as text and formulas or sums based on these cells won't therefore work. You should also stop using the xls extension if this is the case.
 
sanjay kumar gupta
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No I am not using Apache POI(I can't use),I already tried this apostrophe in front of 300:00 but excel started showing as '300:00 ,Excel is not hiding apostrophe when I running export through the code ,when I am trying to do the same thing in excel it working good .please advice.

"You should also stop using the xls extension if this is the case." please explain what do you mean by this line.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

sanjay kumar gupta wrote:"You should also stop using the xls extension if this is the case." please explain what do you mean by this line.


I understand that when your users download the file, they'll get the default name UnbilledMinutes.xls, which they probably just confirm. They can then double-click that file and it opens in Excel, though it probably (depending on the version of the Excel) displays a warning that the file has the XLS extension, but its format does not match the expectations (XLS is a binary format, your file contains text).

If this understanding of mine is correct, you should stop using the XLS extension and use another one, for example TXT.

I also believe that if you change the extension of the file to TXT and open it in Excel, the apostrophes will disappear. Excel sometimes handles files differently based just on the extension the file has (eg. *.csv files versus *.txt files).

It will probably be less convenient for your users to open the file this way (they'll have to drag it onto an Excel shortcut, for example), but you don't have much choice with the limits you've stated (inability to use Apache POI).
 
I'm a lumberjack and I'm okay, I sleep all night and work all day. Lumberjack ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic