Create Excel reports by programming from templates

I'm using "Apache POI" to generate Excel report. I've a well designed Excel template. I want to create a report by f开发者_StackOverflow中文版illing the data into predefined locations of the template. That is to say, I do not want to care about the formats of the report. Is that possible? Could you give me some instructions?

I got my answer. I can use the "Cell Naming" utility in Microsoft Excel and then use the following code to locate the cell and do something.

    CellReference[] crefs = this.getCellsByName(wb, cName);

    // Locate the cell position
    Sheet sheet = wb.getSheet(crefs[0].getSheetName());
    Row row = sheet.getRow(crefs[0].getRow());
    Cell cell = row.getCell(crefs[0].getCol());

    // Write in data
    cell.setCellValue(cellRegion.getContent());

"cName" is the cell's name predefined in Microsoft Excel.

You can have a look at jXLS, I think that's what you are looking for.

It takes a Excel as template and you can write a Java app to fill the data:

http://jxls.sourceforge.net/

You can load you template file like any other XLS. And then make the changes you want to the specific cells and write it out into another file.

Some sample code:

Load file

InputStream inputStream = new FileInputStream ("D:\\book_original.xls");
            POIFSFileSystem fileSystem = new POIFSFileSystem (inputStream);

            HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);

do stuff

HSSFSheet         sheet1    = workBook.getSheetAt (0);
            Iterator<Row> rows     = sheet1.rowIterator ();

while (rows.hasNext ())
{
Row row = rows.next ();

// do stuff
if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
    System.out.println ("Row No.: " + row.getRowNum ()+ " " + row.getCell(0).getNumericCellValue());
HSSFCell cell = row.createCell(0);
cell.setCellValue("100"); 

}

Write the output to a file

 FileOutputStream fileOut1 = new FileOutputStream("D:\\book_modified.xls");
            workBook.write(fileOut1);
            fileOut1.close();

You may also take a look at Xylophone. This is Java library built on top of Apache POI. It uses spreadsheet templates in XLS(X) format and consumes data in XML format.