How to add a new sheet into an existing xls file

Question

I need to add a new sheet with different methods and headers within the same workbook. I'm able to add the new sheet but how do add the separate methods and headers for the second sheet? Right now both sheets are duplicate copies. Basically How would I add different data to both sheets. Any help would be appreciated and I always accept the answers and also up vote.

public class ExcelWriter {

Logger log = Logger.getLogger(ExcelWriter.class.getName());

private HSSFWorkbook excel;

public ExcelWriter() {
    excel = new HSSFWorkbook();
}

public HSSFWorkbook getWorkbook() {
    return excel;   
}

public void writeExcelFile(String filename, String[] columns, Object[][] data, HSSFCellStyle[] styles, 
        HSSFCellStyle columnsStyle, String[] header, String[] footer) throws IOException {

    FileOutputStream out = new FileOutputStream(filename);

    HSSFSheet sheet = excel.createSheet("Daily Screening");
    HSSFSheet sheet1 = excel.createSheet("Parcel Return");  

    int numHeaderRows = header.length; 
    createHeader(sheet,header,columns.length, 0);
    createHeader(sheet1,header,columns.length, 0);

    createColumnHeaderRow(sheet,columns,numHeaderRows,columnsStyle);
    createColumnHeaderRow(sheet1,columns,numHeaderRows,columnsStyle);
    int rowCtr = numHeaderRows;
    for( int i = 0; i < data.length; i++) {         

        if (i > data.length -2) 
            ++rowCtr;
        else 
            rowCtr = rowCtr + 2;

        createRow(sheet, data[i], rowCtr, styles);
    }
    int rowCtr1 = numHeaderRows;
    for( int i = 0; i < data.length; i++) {         

        if (i > data.length -2) 
            ++rowCtr1;
        else 
            rowCtr1 = rowCtr1 + 2;

        createRow(sheet1, data[i], rowCtr1, styles);
    }

    int totalRows = rowCtr1 + 1;
    createHeader(sheet1,footer,columns.length, totalRows);

    excel.write(out);
    out.close();
}

private void createHeader(HSSFSheet sheet1, String[] header, int columns, int rowNum) {     

    for( int i = 0; i < header.length ; i++ ) {
        HSSFRow row = sheet1.createRow(i + rowNum);
        HSSFCell cell = row.createCell((short) 0);
        String text = header[i];
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(text);            
        HSSFCellStyle style = excel.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont arialBoldFont = excel.createFont();
        arialBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        arialBoldFont.setFontName("Arial");
        style.setFont(arialBoldFont);

        if (!isEmpty(header[i]) && rowNum < 1) {
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        }
        cell.setCellStyle(style);           
        sheet1.addMergedRegion( new Region(i+rowNum,(short)0,i+rowNum,(short)(columns-1)) );
    }
}   


private HSSFRow createColumnHeaderRow(HSSFSheet sheet, Object[] values, int rowNum, HSSFCellStyle style) {

    HSSFCellStyle[] styles = new HSSFCellStyle[values.length];

    for( int i = 0; i < values.length; i++ ) {
        styles[i] = style;
    }

    return createRow(sheet,values,rowNum,styles);
}

private HSSFRow createRow(HSSFSheet sheet1, Object[] values, int rowNum, HSSFCellStyle[] styles) {

    HSSFRow row = sheet1.createRow(rowNum);

    for( int i = 0; i < values.length; i++ ) {
        HSSFCell cell = row.createCell((short) i);
        cell.setCellStyle(styles[i]);

        try{
            Object o = values[i];

            if( o instanceof String ) { 
                String text = String.valueOf(o);
                cell.setCellValue(text);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            }
            else if (o instanceof Double) {
                Double d = (Double) o;                  
                cell.setCellValue(d.doubleValue());
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);                   
            }
            else if (o instanceof Integer) {
                Integer in = (Integer)o;                    
                cell.setCellValue(in.intValue());                   
            }
            else if (o instanceof Long) {
                Long l = (Long)o;                   
                cell.setCellValue(l.longValue());
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);                   
            }
            else if( o != null ) {
                String text = String.valueOf(o);
                cell.setCellValue(text);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            }
        }
        catch(Exception e) {
            log.error(e.getMessage());
        }
    }

    return row;
}

public boolean isEmpty(String str) {

    if(str.equals(null) || str.equals("")) 
        return true;
    else
        return false;
}

}

Report Generator Class

public class SummaryReportGenerator extends ReportGenerator {

Logger log = Logger.getLogger(SummaryReportGenerator.class.getName());

public SummaryReportGenerator(String reportDir, String filename) {
    super( reportDir + filename + ".xls", reportDir + filename + ".pdf");       
}

public String[] getColumnNames() {
    String[] columnNames = {"ISC\nCode", "Total\nParcels", "Total\nParcel Hit\n Count",
            "Filter Hit\n%", "Unanalyzed\nCount", "Unanalyzed\n%",
            "Name\nMatch\nCount", "Name\nMatch\n%", "Pended\nCount",
            "Pended\n%", "E 1 Sanction\nCountries\nCount", "E 1 Sanction\nCountries\n%", "Greater\nthat\n$2500\nCount", "Greater\nthat\n$2500\n%",
            "YTD\nTotal Hit\nCount", "YTD\nLong Term\nPending", "YTD\nLong Term\n%"};
    return columnNames;
}

public HSSFCellStyle getColumnsStyle(HSSFWorkbook wrkbk) {
    HSSFCellStyle style = wrkbk.createCellStyle();
    style.setWrapText(true);        
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont timesBoldFont = wrkbk.createFont();
    timesBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    timesBoldFont.setFontName("Times New Roman");
    style.setFont(timesBoldFont);
    return style;
}

public Object[][] getData(Map map) {    
    int rows = map.size();// +  1 + // 1 blank row   1;  // 1 row for the grand total;      
    int cols = getColumnNames().length;

    Object[][] data = new Object[rows][cols];       
    int row = 0;
    for (int i=0; i < map.size(); i++ ){

        try{
            SummaryBean bean = (SummaryBean)map.get(new Integer(i));

            data[row][0] = bean.getIscCode();
            data[row][1] = new Long(bean.getTotalParcelCtr());
            data[row][2] = new Integer(bean.getTotalFilterHitCtr());
            data[row][3] = bean.getFilterHitPrctg();
            data[row][4] = new Integer(bean.getPendedHitCtr());
            data[row][5] = bean.getPendedHitPrctg();
            data[row][6] = new Integer(bean.getTrueHitCtr());
            data[row][7] = new Integer(bean.getRetiredHitCtr());                
            data[row][8] = new Integer(bean.getSanctCntryCtr());
            data[row][9] = new Integer(bean.getC25Ctr());
            data[row][10] = new Integer(bean.getCnmCtr());
            data[row][11] = new Integer(bean.getCndCtr());              
            data[row][12] = new Integer(bean.getCnlCtr());
            data[row][13] = new Integer(bean.getCneCtr());
            data[row][14] = new Integer(bean.getVndCtr());
            data[row][15] = new Integer(bean.getCilCtr());
            data[row][16] = new Integer(bean.getHndCtr());
            data[row][17] = new Integer(bean.getCnrCtr());
            ++row;
        }
        catch(Exception e) {                    
            log.error(e.getMessage());
        }           
    }   

    return data;
}

public String[] getHeader(String startDate, String endDate) {
    Date today = new Date();
    String reportDateFormat = Utils.formatDateTime(today, "MM/dd/yyyyHH.mm.ss");

    String nowStr = Utils.now(reportDateFormat);
    String[] header = {"","EXCS Daily Screening Summary Report ","",
              "for transactions processed for the calendar date range", 
              "from " + startDate  + " to " + endDate,
              "Report created on " + nowStr.substring(0,10)+ " at " 
              +  nowStr.substring(10)};
    return header;
}

public HSSFCellStyle[] getStyles(HSSFWorkbook wrkbk) {
    int columnSize = getColumnNames().length;
    HSSFCellStyle[] styles = new HSSFCellStyle[columnSize];

    HSSFDataFormat format = wrkbk.createDataFormat();
    for (int i=0; i < columnSize; i++){
        styles[i] = wrkbk.createCellStyle();
        if (i == 0){
            styles[i].setAlignment(HSSFCellStyle.ALIGN_LEFT);
        }else{
            styles[i].setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        }
        if (i == 1 || i == 2){
            styles[i].setDataFormat(format.getFormat("#,###,##0"));
        }
        HSSFFont timesFont = wrkbk.createFont();            
        timesFont.setFontName("Times New Roman");
        styles[i].setFont(timesFont);
    }
    return styles;
}

public String[] getFooter() {

    String[] header = {"","Parcel Return Reason Code Reference","",
                  "DPM = Sender and/or recipient matches denied party",
                  "HND = Humanitarian exception not declared",
                  "CNM = Content not mailable under export laws",
                  "VND = Value of content not declared",
                  "CNR = Customer non-response",
                  "C25 = Content Value greater than $2500",
                  "CIL = Invalid license",
                  "C30 =  More than one parcel in a calendar month",
                  "CNL = Content description not legible",
                  "CNE = Address on mailpiece not in English",
                  "RFN = Requires full sender and addressee names",
                  "DGS = Dangerous goods",
                  "R29 = RE-used 2976 or 2976A",
                  "ANE = PS Form 2976 or 2976A not in English",
                  "ICF = Incorrect Customs Declaration Form used",
                  "DPR = Declaration of purpose required",
                  "ITN = Internal Transaction Number (ITN), Export Exception/Exclusion Legend (ELL), or Proof of Filing Citation (PFC) is required",
                  "OTH = Other","",};
    return header;
}

}

Answer 1

what you need is

    HSSFSheet sheet = parentworkbookname.createSheet("Sample sheet2");