Excel and Java Integration

[Ben Jessel]

I have a requirement to create an calendar, in excel, showing people's
availability. This calendar will be an output of a java web-based
process which involves the reading individuals' calendar which is
uploaded via an html form.

My question, is what is the best approach, using java, to solve this.
The individuals' and the resulting combined calendar must be
user-readable, with formatting.

My thought is to have a well formatted calendar in an excel sheet
which gets its data from another excel worksheet ( within the workbook
) which contains unformatted raw data. This raw data will come from
the output of a java date availability crunching component. The data
could be outputted in a csv ( excel readable ) form, that could then
somehow get included into the excel workbook. However, I've no idea
how to actually include the data into an excel workbook. Would apache
poi be the best tool for this? Could I approach it via the java-COM
bridge. Or is there an easier way of doing this?

Thanks

Ben

 

[TechBookReport]

somehow get included into the excel workbook. However, I've no idea
how to actually include the data into an excel workbook. Would apache
poi be the best tool for this? Could I approach it via the java-COM
bridge. Or is there an easier way of doing this?

I've done this sort of thing using the Java Excel API
(http://www.andykhan.com/jexcelapi/), which was fairly easy to use. I
haven't used POI but it looks like it should be OK.

An alternative is to embed links in the Excel file so that it reads the data
from a file you generate from Java. This is probably the quickest option.

Pan

====================================
TechBookReport (http://www.techbookreport.com)

 

[Nathan Zumwalt]

If I'm reading this right, you have a Java-based web application that
users upload their individual availability. The webapp combines these
availabilities into a master calendar, which you need to put into an
Excel spreadsheet.

I think there are a couple ways you could takle this.

If the Excel spreadsheet is simple, you can have your webapp create
it. Just create a JSP, set the content type to Excel
(application/vnd.ms-excel I think), and push out HTML tables... Excel
will interpret the HTML table as cells.

Alternatively, you could use VBA to pull data from the webapp into
your Excel spreadsheet. Create a JSP in your webapp that displays
plain text (content type text/plain) and either display XML (MS has
some really nice XML from HTTP parsing libraries) or CSV.

-Nathan

 

[Brant Barney]

I have a requirement to create an calendar, in excel, showing people's
availability. This calendar will be an output of a java web-based
process which involves the reading individuals' calendar which is
uploaded via an html form.

You can export the calendar to an Excel spreadsheet by formatting it
in an HTML table. Then change the HTTP Response content type to the
MIME type "application/vnd.ms-excel".

Brant

 

[Sudsy]

I have a requirement to create an calendar, in excel, showing people's
availability. This calendar will be an output of a java web-based
process which involves the reading individuals' calendar which is
uploaded via an html form.

What the ....?

Is there a reason for this mismatch? Why can't you simply use a
web page to review/edit the information? There are some nice
RFCs out there (the 2445 series jumps to mind) which define how
to deal with calendars in a platform-agnostic manner.
Why on earth would you need to generate something in a vendor-
specific format?
Doesn't your favorite web browser work?

 

[Ben Jessel]

Great. Yes vCalendar/iCalendar RFC spec does look interesting.
However, I'm interested in a quick knock-up solution that I can, at a
later date, expand.

The reason I chose excel is based on the following assumptions:

a) I don't want to have to use an RDMS ( I am at the mercy of free
ISP's that just support .jsp or .php. The latter of which I would have
to learn ),

b) I want to give non-computer literate users ( or users who are just
about literate in micro$oft productgs ) the advantage of a rich user
interface.

c) Implementing a rich user interface on the web is non-trivial. Rich
user interfaces usually use client side scripting such as Javascript (
......yes not related by java at all, I know ). Javascript can be
fiddly, difficult to maintain, hard to get to work cross browser etc.
It seems to be that using the off-line / familiar features of a tool
that users use every day such as ex-hell and then just merging the
data of a number of files is a lot easier to implementing user
interface for cycling through dates, viewing availability etc.

If someone can come up with a generalized walk through / back of an
envelope idea of a web implementation of my requirements, I would be
interested.

I need users to be able to quickly and efficiently input their
availability dates, and then be able to see a merged calendar of
availabilities of all people available.

I like the idea of converting excel inputted calendars to a format
such as vCalendar. However I don't have time to implement.