Solved

Making an editable spreadsheet using POI

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
"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,…
This video teaches users how to migrate an existing Wordpress website to a new domain.
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…

758 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

20 Experts available now in Live!

Get 1:1 Help Now