Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Making an editable spreadsheet using POI

Posted on 2006-06-29
2
Medium Priority
?
719 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…
Suggested Courses

926 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