Create Excel reports by programming from templates

Question

I'm using "Apache POI" to generate Excel report. I've a well designed Excel template. I want to create a report by filling 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?

Answer 1

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();

Answer 2

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.