How can I load an MS Excel file, add content and send to user?

Question

I need to load a template MS Excel file, add content to some cells and then download it via the user's browser, so he can open and/or save the file.

I managed to create some MS Excel workbooks in the past, but this seems somewhat different. How could I do it?

Thanks in advance,

gtludwig

Answer 1

There are good tutorials on apache-poi on their official site

final Workbook wb;
fileIn =new FileInputStream(fname);
wb = WorkbookFactory.create(fileIn);//opening a file
final Sheet sheet = wb.createsheet("Report");// create a sheet

Then use Row and Cell classes to add contents in cell

Row row;
Cell cell;
row=sheet.getRow(0); //get existing row
if(row==null)
row=sheet.createRow(0);//if row not present create row
cell=row.getCell(0);
if(cell==null)
cell=row.createCell(0);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(0);//set cell value

UPDATE : I forgot to mention that you need to write contents on file

fout=new FileOutputStream(fname);
wb.write(fout);

and then close fout in finally block

if(fout!=null)
fout.close();

Once you are done with the excel file create a download servlet

File file=new File(fname);
FileInputStream fis=new FileInputStream(file);

        response.setHeader("Content-Length", String.valueOf(file.length()));
            response.setHeader("Content-Disposition",
                             "attachment;filename="+fname+".xlsm");
            ServletContext ctx = getServletContext();
            InputStream input = new BufferedInputStream(new FileInputStream(file), 1024 * 10);
            OutputStream output = new BufferedOutputStream(response.getOutputStream(), 1024 * 10);

            byte[] buffer = new byte[1024 * 10];
            int length;
            while ((length = input.read(buffer)) > 0) {
                output.write(buffer, 0, length);
            }
        output.flush();
        output.close();
        input.close();
        fis.close();