Still working on an Excel dilemma. I decided to simply add
records to a .txt file which opens fine in Excel. Then realized
upon doing this (duh!), that if CF is writing a record to this file
while it is open in Excel, then it bombs. The file would be read
only while I needed write-access.
My question: Using the Apache Jakarta Project's POI, can the
.xls file be open by a human in native Excel mode while it is being
written to by a CF app? My quess would be yes, or what is the
point. If I am correct in my assumption, I will address how later.
But need that answered before I go to the trouble of downloading
Otherwise I will just create a user interface that handles
all the data updates, inserts, etc. But it is more work than I
wanted to get into since it started out as a simple request of
getting data into a format that is accessible to many and they
could update as well.
tclaremont, Jun 12, 2007 11:25 AM
The CFCONTENT approach can only run a query from the existing
database and turn it into EXCEL format. Each time that query is run
it will write over the previous copy. I need it so the CF
application can update while someone perhaps might be working on
the same spreadsheet. The reason is that there are other columns in
the .xls file that the CF app will not touch and if I overwrite it,
they are gone.
OK, put it this way. Will CFCONTENT allow me to append new
data to the next available row in the spreadsheet while someone
else has it open?
Actually, it is really making me curious how the POI thing is
used if it does not allow file sharing/locking .
Thanks for your answer but I really am wondering if this is
possible before I dive in. If anyone can give a good example how
the Apache Jakarta Project's POI, a "Java API To Access Microsoft
Format Files" works and when it is a good idea, I would appreciate
it. Or a link to some good info.
Originally it sounded like you wanted to produce a file that
users could download. Now it sounds like you're trying to do
something different. Before we start talking about POI, can you
describe in general terms what you're trying to achieve - how will
the file be used, etc?
General overview: I have written an app that allows students
to upload files and then adds records to an Access table about the
files that have been submitted for a publication. The advisor of
this project has had to manually transfer this information to an
Excel sheet for a committee of students/faculty to manage. They
vote on the submissions and the spreadsheet is used to keep
additional information (information beyond what the CF app is
placing there) in one central location to create this publication.
The students/faculty members need to be able to edit this
spreadsheet for various reasons. So, in essence, I would like my CF
app to write to the spreadsheet and also allow the committee to
view, edit, add, etc. as they need. The spreadsheet very likely
would be accessed simultaneously, thus the need for sharing. So, I
am looking to have CF "add" records and not overwrite the existing
xls document. Hope this makes sense.
Is the Jakarta thing the way to go for this scenario?
efecto747 - The information you provided looks very useful.
Thank you. I talked to my server person here and he suggested that
whether of not it is useful for the above, it would be good to get
it going and play around with it for other purposes.
I'm relatively certain POI cannot write to a file while its
open. But that's not POI specific. In general you need exclusive
access to write to a file. IIRC, you cannot do this in Excel
either. Lets say FileA.xls is stored on a shared drive and I open
it for editing. Five minutes later you try and open FileA.xls. I
don't think you will be able to modify FileA.xls until after I
close it. Unless later versions of Excel handle this differently.
Maybe someone else has a different idea, but I suspect you
would need to make all edits through the web application and
provide an option to download the latest (complete) information as
a CSV file.