Solved

Making an editable spreadsheet using POI

Posted on 2006-06-29
2
698 Views
Last Modified: 2008-01-16
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.getAppProperty("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(HttpServletResponse 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("application/octet-stream");
                  // res.setHeader("Content-disposition", "attachment;filename=Report.xls");

                  // the following will cause browser to display result with excel plug-in
                  res.setContentType("application/x-msexcel");

                  // the following will cause browser to display result with excel plug-in (within IE only)
                  //res.setContentType("application/vnd.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(BufferedOutputStream 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.parseShort("1"));
cell.setCellValue(model.getFieldName());

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???
0
Comment
Question by:OMyStuckAgin
2 Comments
 

Author Comment

by:OMyStuckAgin
ID: 17018000
like the ChurchLady always says.....NeveeerMinnnnnd! I figured this out myself, dopey me. All you need to do is use the first implementation that is found in the comments

                res.setContentType("application/octet-stream");
                res.setHeader("Content-disposition", "attachment;filename=Report.xls");

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.
0
 

Accepted Solution

by:
EE_AutoDeleter earned 0 total points
ID: 17158196
OMyStuckAgin,
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
would like the bottom of this page to be smaller 3 32
tomcat startup error 5 105
Writing and Selling Web App Based on Google Sheets 2 81
Button to go back 3 28
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question