File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes General Computing and the fly likes Dealing with excel Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » General Computing
Bookmark "Dealing with excel " Watch "Dealing with excel " New topic

Dealing with excel

Alaa Abutabaq

Joined: Jan 15, 2002
Posts: 18
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

Joined: May 17, 2001
Posts: 2823
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

Joined: Feb 20, 2001
Posts: 3695

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

Joined: Jul 27, 2001
Posts: 1365
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

will you be updating your list of current cool things?
David Weitzman
Ranch Hand

Joined: Jul 27, 2001
Posts: 1365
Thanks for the reminder.
Alan Phillips
Ranch Hand

Joined: Aug 13, 2002
Posts: 73
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:
subject: Dealing with excel
Similar Threads
Export to excel in jsp
display access tabel in excel format
Excel Float value problem
complex excel report generation
display output table in excel file format