Solved

Making an editable spreadsheet using POI

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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.
The viewer will learn how to count occurrences of each item in an array.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now