Solved

Making an editable spreadsheet using POI

Posted on 2006-06-29
2
696 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 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