OMyStuckAgin
asked on
Making an editable spreadsheet using POI
This is my first time using POI in a production app. I am reading a predefined template from a web share and filling it with data obtained from a database. My question and my problem is I can display the excel sheet easily enough, hower when the spreadsheet pops up, it is a readonly sheet. Is there a way to read a sheet and allow data served from a web application to fill it up. Here is what I have
Open the file:
String templatepath = ConfigurationManager.getAp pProperty( "URL");
filename = templatepath + template;
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)) ;
book = new HSSFWorkbook(fs);
} catch (FileNotFoundException fnfe) {
throw fnfe;
} catch (IOException ioe) {
throw ioe;
}
heres how I display the sheet to the user....
public void display(HttpServletRespons e res) throws Exception {
try {
// the following will download excel file within a stream and pop up a "save as" dialog to
// save the data...... see http://www.onjava.com/pub/a/onjava/excerpt/jebp_3/index3.html
// res.setContentType("applic ation/octe t-stream") ;
// res.setHeader("Content-dis position", "attachment;filename=Repor t.xls");
// the following will cause browser to display result with excel plug-in
res.setContentType("applic ation/x-ms excel");
// the following will cause browser to display result with excel plug-in (within IE only)
//res.setContentType("appl ication/vn d.ms-excel ");
OutputStream os = (OutputStream) res.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(os);
writeWorkBook(bos);
bos.close();
os.close();
} catch (Exception e) {
throw e;
}
public void writeWorkBook(BufferedOutp utStream bos) throws IOException {
try {
book.write(bos);
} catch (IOException ioe) {
}
and finally here is a sample of how I want to prefill with data retrieved from our database..
HSSFSheet sheet = book.getSheet("TDP");
HSSFRow row = sheet.getRow(4);
HSSFCell cell = row.getCell(Short.parseSho rt("1"));
cell.setCellValue(model.ge tFieldName ());
no matter what I do, when the spreadsheet comes up it is always a readonly copy, I really need something that is editable. Anybody have any ideas???
Open the file:
String templatepath = ConfigurationManager.getAp
filename = templatepath + template;
try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename))
book = new HSSFWorkbook(fs);
} catch (FileNotFoundException fnfe) {
throw fnfe;
} catch (IOException ioe) {
throw ioe;
}
heres how I display the sheet to the user....
public void display(HttpServletRespons
try {
// the following will download excel file within a stream and pop up a "save as" dialog to
// save the data...... see http://www.onjava.com/pub/a/onjava/excerpt/jebp_3/index3.html
// res.setContentType("applic
// res.setHeader("Content-dis
// the following will cause browser to display result with excel plug-in
res.setContentType("applic
// the following will cause browser to display result with excel plug-in (within IE only)
//res.setContentType("appl
OutputStream os = (OutputStream) res.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(os);
writeWorkBook(bos);
bos.close();
os.close();
} catch (Exception e) {
throw e;
}
public void writeWorkBook(BufferedOutp
try {
book.write(bos);
} catch (IOException ioe) {
}
and finally here is a sample of how I want to prefill with data retrieved from our database..
HSSFSheet sheet = book.getSheet("TDP");
HSSFRow row = sheet.getRow(4);
HSSFCell cell = row.getCell(Short.parseSho
cell.setCellValue(model.ge
no matter what I do, when the spreadsheet comes up it is always a readonly copy, I really need something that is editable. Anybody have any ideas???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
res.setContentType("applic
res.setHeader("Content-dis
where "Report.xls" is the name that you wish your template to be named when instantiated. The other two implementations (the one that use the Excel-plug-in) are both read only. Moderator please close this for me, and post this as the accepted answer.
.ps the article (http://www.onjava.com/pub/a/onjava/excerpt/jebp_3/index3.html) is excellent and actually explains this if you take the time to read it as oppsed to what I did, which was just skim over it.