Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dealing with excel

 
Alaa Abutabaq
Greenhorn
Posts: 18
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Friends ,
I exports my data from stream to CSV file , but when I display that By Excel I can't control how data should be display , ex: "001" will be converted to 1. How can I solve it ?
 
Paul Stevens
Ranch Hand
Posts: 2823
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to general computing. This is an excel issue and not a servlet issue. Excel is treating the column as a number and not text.
 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I did a bit of tinkering, and I just can't stop Excel from converting 001 into a number column and stripping the leading zeros.

So I googled, and here's an alternate idea:

Data | Get External Data | Import Text File

This will lead you through a 3 step wizard, during which you can specify comma separated columns, and apply 'text' format to all of them (or just the ones with leading zeros).

Finally, it will import your csv properly.

Here's the magic: Save this file as an Excel template, and provide the template to your end users (probably as a download link). They will need to download this template once.

Then when they want updated data, they can download the latest csv file. When they open the template... they are asked if they'd like to refresh the data from the file. They can browse to where the csv file was saved, and Excel will populate the 'template' with the csv data.

There is even a toolbar that appears with an exclamation point. Clicking this will refresh the spreadsheet from that file again. So to refresh would be a two step for your users... re-download the csv file, and click the exclamation in Excel.

There is also an option to run a web query from excel. You might want to investigate this as well. You could supply two different version for different users. A csv file, plus a very simple HTML web page, containing a simple table layout of your data. The same steps would ensue... save it as a template, and when they open the template, excel would go to the web and get your data.
 
David Weitzman
Ranch Hand
Posts: 1365
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Cool
 
Mike Curwen
Ranch Hand
Posts: 3695
IntelliJ IDE Java Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
will you be updating your list of current cool things?
 
David Weitzman
Ranch Hand
Posts: 1365
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reminder.
 
Alan Phillips
Ranch Hand
Posts: 73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, you can create a macro to drop that file onto your template. I export a table of prices(Natural Gas at different locations accross NA) from a database and check it back to the originals to make sure everything is up-to date before the stuff runs at night.
For some weird reason excel read all my dates as the year 2002, so I surrounded them in quotes on my export, then use Lookups to match everthing. So, I have a template Sheet 1 which is formated the way I want, Sheet 2 looks all nice for anyone to check. I've got a macro that copies the daily csv file onto Sheet 1, paste values, so all the formats stay the same. Works great!
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic