Java to excel not writing to multiple sheets

Question

I am using Apache POI and Java to take an SQL query and format the data and put it into two separate sheets. The first sheet works fine but it never loads data into the second sheet. I've tried just using the code for the second sheet and it works as long as the first sheets code is commented out. This leads me to believe it is caused by my poor java code. Code is below

public static void createSheet1(ResultSet rs3) throws SQLException, FileNotFoundException {
    Desktop dt = Desktop.getDesktop();
    //CREATE WORKBOOK
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Run Tickets");
    int i=0;
    while (rs3.next()){
        if (i <= 0){
            XSSFRow rowhead = sheet.createRow((short)0);
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont defaultFont = workbook.createFont();
            defaultFont.setBold(true);
            defaultFont.setFontHeight(13);
            style.setFont(defaultFont);         
            rowhead.createCell((short) 0).setCellValue("ROUTE_NAME");
            rowhead.createCell((short) 1).setCellValue("STOP_NAME");
            rowhead.createCell((short) 2).setCellValue("EQUIP_NAME");
            rowhead.createCell((short) 3).setCellValue("DESTINATION");
            rowhead.createCell((short) 4).setCellValue("PURCHASER");
            rowhead.createCell((short) 5).setCellValue("Ticket DT");
            rowhead.createCell((short) 6).setCellValue("Ticket #");
            rowhead.createCell((short) 7).setCellValue("EQUIP_TYPE");
            rowhead.createCell((short) 8).setCellValue("OPEN_FEET");
            rowhead.createCell((short) 9).setCellValue("OPEN_INCH");
            rowhead.createCell((short) 10).setCellValue("OPEN_TMP");
            rowhead.createCell((short) 11).setCellValue("CLOSE_FEET");
            rowhead.createCell((short) 12).setCellValue("CLOSE_INCH");
            rowhead.createCell((short) 13).setCellValue("CLOSE_TMP");
            rowhead.createCell((short) 14).setCellValue("OBSV_GRAV");
            rowhead.createCell((short) 15).setCellValue("OBSV_TMP");
            rowhead.createCell((short) 16).setCellValue("BSW");
            rowhead.createCell((short) 17).setCellValue("CLOSE_DT");
            rowhead.createCell((short) 18).setCellValue("OPEN_VOLUME");
            rowhead.createCell((short) 19).setCellValue("CLOSE_VOLUME");
            rowhead.createCell((short) 20).setCellValue("GROSS_VOLUME");
            rowhead.createCell((short) 21).setCellValue("NET_VOLUME");
            rowhead.createCell((short) 22).setCellValue("STATION_NAME");

            for(int j =0; j<23;j++){
                rowhead.getCell(j).setCellStyle(style);

            }

        }
        else{

        XSSFRow row = sheet.createRow((short) i);
        row.createCell((short) 0).setCellValue(rs3.getString("ROUTE_NAME"));
        row.createCell((short) 1).setCellValue(rs3.getString("STOP_NAME"));
        row.createCell((short) 2).setCellValue(rs3.getString("EQUIP_NAME"));
        row.createCell((short) 3).setCellValue(rs3.getString("COMMENTS"));
        row.createCell((short) 4).setCellValue(rs3.getString("BA_NAME"));
        row.createCell((short) 5).setCellValue(rs3.getString("LQVOL_DT"));
        row.createCell((short) 6).setCellValue(rs3.getString("LQVOL_TRANS"));
        row.createCell((short) 7).setCellValue(rs3.getString("EQUIP_TYPE"));
        row.createCell((short) 8).setCellValue(rs3.getInt("OPEN_FEET"));
        row.createCell((short) 9).setCellValue(rs3.getInt("OPEN_INCH"));
        row.createCell((short) 10).setCellValue(rs3.getInt("OPEN_TMP"));
        row.createCell((short) 11).setCellValue(rs3.getInt("CLOSE_FEET"));
        row.createCell((short) 12).setCellValue(rs3.getInt("CLOSE_INCH"));
        row.createCell((short) 13).setCellValue(rs3.getInt("CLOSE_TMP"));
        row.createCell((short) 14).setCellValue(rs3.getInt("OBSV_GRAV"));
        row.createCell((short) 15).setCellValue(rs3.getInt("OBSV_TMP"));
        row.createCell((short) 16).setCellValue(rs3.getInt("BSW"));
        row.createCell((short) 17).setCellValue(rs3.getString("CLOSE_DT"));
        row.createCell((short) 18).setCellValue(rs3.getInt("OPEN_VOLUME"));
        row.createCell((short) 19).setCellValue(rs3.getInt("CLOSE_VOLUME"));
        row.createCell((short) 20).setCellValue(rs3.getInt("GROSS_VOLUME"));
        row.createCell((short) 21).setCellValue(rs3.getInt("NET_VOLUME"));
        row.createCell((short) 22).setCellValue(rs3.getString("STATION_NAME"));


        }
        ++i;

    }
    //AUTOSIZE COLUMNS
    int j =0;
    while(j<23)
    {
        sheet.autoSizeColumn(j);
        j++;
    }

    createSheet2(rs3,workbook, dt);
    outputExcel(workbook, dt);

}
public static XSSFWorkbook createSheet2(ResultSet rs3, XSSFWorkbook workbook, Desktop dt2) throws SQLException, FileNotFoundException {
    //CREATES SECOND SHEET "ALLOCATION"
    XSSFSheet sheet2 = workbook.createSheet("Allocation");
    int rowcnt = 0;
    while(rs3.next()){
        if(rowcnt == 0){
            XSSFRow rowhead2 = sheet2.createRow((short)0);
            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont defaultFont = workbook.createFont();
            defaultFont.setBold(true);
            defaultFont.setFontHeight(13);
            style.setFont(defaultFont); 
            rowhead2.createCell((short) 0).setCellValue(rs3.getString("STATION_NAME"));
        }
        rowcnt++;

    }
    return workbook;
}
public static void outputExcel(XSSFWorkbook workbook, Desktop dt) throws FileNotFoundException {
    String yemi = "M:/MonthlyEndClosing.xlsx";
    FileOutputStream fileOut = new FileOutputStream(yemi);
    try {
        workbook.write(fileOut);
        fileOut.close();
        workbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    }



    try {
        dt.open(new File("M:/MonthlyEndClosing.xlsx"));
    } catch (IOException e) {
        e.printStackTrace();
    }

}

I have tried this in just one method,separate classes and now in two methods with the same results. I appreciate any help you can give.

I can't see any logic that's supposed to be reading from the other result set and writing into the second sheet. Did you forget to write it? — Gagravarr
– Gagravarr, Commented Aug 12, 2015 at 15:18
createCell short is deprecated, use createCell int... also your code will look nicer and is shorter (ironically). Why don't you just create an array with the headers and loop through it instead of doing everything manually... changing the indexes can be annyoing. — maraca
– maraca, Commented Aug 12, 2015 at 18:46

Karbos 538 · Accepted Answer · 2015-08-12 15:27:35Z

1

Your problem is not in POI API using but in your resultSet :

while (rs3.next()){
    ...
}
//rs3.next() will now be false

answered Aug 12, 2015 at 15:27

Karbos 538

3,0752 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java to excel not writing to multiple sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related