hi,
i have a template.xls file that holds a template for a report that is generate by data from a data. The data is going to placed in a file called report.xls that at the start of the application is empty and will eventually be populated by the elements from the template.xls file plus the data from the database.
My problem is that when i copy a row from the template.xls file it is not created in the report.xls file. I can only get the woorksheets that i create to appear in the report.xls file. Can anyone help me please? I have supplied my code below.
public class SpreadsheetUtility {
private static final SpreadsheetUtility theSpreadsheetUtility = new SpreadsheetUtility();
private final String REPORT_XLS = "reports/report.xls";
private final String TEMPLATE_XLS = "C:/MyTools/eclipse/templates/template.xls";
private final String WEEKLY_SLOTS = "WeeklySlots";
private final String DAILY_SLOTS = "DailySlots";
private final String WEEKLY_ADS = "WeeklyAdverts";
private final String DAILY_ADS = "DailyAdverts";
private FileInputStream report_input;
private FileInputStream template_input;
FileOutputStream fileout;
HSSFWorkbook report_wb;
HSSFWorkbook template_wb;
HSSFSheet template_dailySlots;
HSSFSheet template_weeklySlots;
HSSFSheet template_dailyAds;
HSSFSheet template_weeklyAds;
HSSFSheet report_dailySlots;
HSSFSheet report_weeklySlots;
HSSFSheet report_dailyAds;
HSSFSheet report_weeklyAds;
// ----- Operations -----
public String createReportWb() throws IOException{
try{
// get the work sheets from the template.xls file
template_input = new FileInputStream(TEMPLATE_XLS);
POIFSFileSystem template_fs = new POIFSFileSystem(template_input);
template_wb = new HSSFWorkbook(template_fs);
template_dailySlots = template_wb.getSheet(DAILY_SLOTS);
template_weeklySlots = template_wb.getSheet(WEEKLY_SLOTS);
template_dailyAds = template_wb.getSheet(DAILY_ADS);
template_weeklyAds = template_wb.getSheet(WEEKLY_ADS);
template_input.close();
template_input = null;
// create new work sheets in the report.xls file
report_wb = new HSSFWorkbook();
FileOutputStream fileout = new FileOutputStream(REPORT_XLS);
report_wb.createSheet(WEEKLY_SLOTS);
report_wb.createSheet(DAILY_SLOTS);
report_wb.createSheet(WEEKLY_ADS);
report_wb.createSheet(DAILY_ADS);
fileout = new FileOutputStream(REPORT_XLS);
report_wb.write(fileout);
report_dailySlots = report_wb.getSheet(DAILY_SLOTS);
report_weeklySlots = report_wb.getSheet(WEEKLY_SLOTS);
report_dailyAds = report_wb.getSheet(DAILY_ADS);
report_weeklyAds = report_wb.getSheet(WEEKLY_ADS);
fileout.close();
// copy headers over from template.xls file
HSSFRow rowOld = template_dailyAds.getRow(5);
HSSFRow rowNew = report_dailyAds.createRow(5);
//copyRow(sheet, rowOld, rowNew, false);
// Copy all the cells in the row
Iterator<?> it = rowOld.cellIterator();
while(it.hasNext()) {
HSSFCell cellOld = (HSSFCell)it.next();
if ( cellOld != null ) {
HSSFCell cellNew = rowNew.createCell(cellOld.getCellNum());
cellNew.setCellStyle(cellOld.getCellStyle());
//copyCellFormulasAndHeaders(cellOld, cellNew, difference, extend, rowNew.getRowNum());
rowNew.setHeight(rowOld.getHeight());
}
}
// Copy all the merged regions in the row
for (int i=0; i<template_dailyAds.getNumMergedRegions(); i++) {
Region region = template_dailyAds.getMergedRegionAt(i);
if (region.getRowFrom() == rowOld.getRowNum() &&
region.getRowFrom() == region.getRowTo()) {
Region merged = new Region(rowNew.getRowNum(),
region.getColumnFrom(),
rowNew.getRowNum(),
region.getColumnTo());
report_dailyAds.addMergedRegion(merged);
}
}
//rowOld = sheet.getRow(y+1);
//rowNew = sheet.createRow(target + 1);
//copyRow(sheet, rowOld, rowNew, false);
//rowOld = sheet.getRow(y+2);
//rowNew = sheet.createRow(target + 2);
//copyRow(sheet, rowOld, rowNew, false);
//rowOld = sheet.getRow(limit);
//rowNew = sheet.createRow(target + 4);
//copyRow(sheet, rowOld, rowNew, false);
//FormulaeUtility.fixTotalSums(rowNew);
}
catch(FileNotFoundException fnfe){
System.out.println("Unable to locate file: " + fnfe.toString());
}
return "";
}
The setters and getters are not included.
Many thanks for your
mark
