CODEWITHSUNDEEP
javaexcelapache-poi
reef
reef

Reputation: 147

Cell values are not copying into the correct row when trying to set cellvalue in excel from Java (POI API)

I have 2 arrays which vary in length and would like to be able to insert the data starting at B3 and D3 going downwards, these are v8columnname and v9columnname. I also have 2 called v8tableNames and v9tableNames which are already populated. My arraylists are queried from two different databases and I plan on comparing the two rows in VBA, but I have that macro set up already. Im only stuck on getting the rows to align up perferctly as the code right now has the B column starting off at B3 but the D column starting off at the last index of the B row. Any help is greatly appreciated

ArrayList v8tableNames = new ArrayList();
ArrayList v9tableNames = new ArrayList();
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet;
XSSFRow row;

ArrayList v8columns = new ArrayList();
ArrayList v9columns = new ArrayList();
for (int i = 0; i<5; i++) {
        sheet = workbook.createSheet();
        row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Version 8");
        row = sheet.getRow(0);
        cell = row.createCell(2);
        cell.setCellValue("Version 9");
        row = sheet.getRow(0);
        cell = row.createCell(3);
        cell.setCellValue(v9tableNames.get(i).toString());

        //getv9 is a function that returns column names from v8table

        v9columns = getv9Columns(v9tableNames.get(i).toString());

        row = sheet.getRow(0);

        //set C1 to v9tablename, one value from the array
        cell = row.createCell(3);
        cell.setCellValue(v9tableNames.get(i).toString());

        //loop through all table names on v9 to see if there is a match for v8
        for (int count = 0; count<v9tableNames.size();count++) {
        //checks if there is a match in table names
            if (v9tableNames.get(i).toString().equals(v8tableNames.get(count).toString())) {
                //puts v8 table name in B1
                cell = row.createCell(1);
                cell.setCellValue(v8tableNames.get(count).toString());

                //getv8 is a function that returns table names

                v8columns = getv8Columns(v8tableNames.get(count).toString());

                for (int k = 0; k<v8columns.size() || k<v9columns.size();k++) {
                    //would like to put columnnames from v8table here starting at B3
                    row = sheet.createRow(k+2);
                    cell = row.createCell(1);
                    cell.setCellValue(v8columns.get(k).toString());
                }
            }
        }


        //put v9columns into D3, this is where it gets stuck putting the last value into D(lastindex of B)
        for (int m = 0; m<v9columns.size();m++) {
                    cell = row.createCell(3);
                    cell.setCellValue(v9columns.get(m).toString());
        }
    }
    try {
        FileOutputStream out = new FileOutputStream( 
              new File("Connect.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("Workbook.xlsx written successfully" );
    } catch(Exception e) {
        e.printStackTrace();
    }

Upvotes: 1

Views: 109

Answers (1)

Finbarr O&#39;B
Finbarr O&#39;B

Reputation: 1464

You need to update the 'row' entering the v9columns for loop:

for (int m = 0; m<v9columns.size();m++) {
    // Start at row zero and shift to row 3, then increment by m++ on each iteration
    row = sheet.createRow(0+m+2);
    cell = row.createCell(3);
    cell.setCellValue(v9columns.get(m).toString());
}

Also, I notice some redundant use of, 'row = sheet.getRow(0);' in your code, in some cases, row already points to sheet.getRow(0). It may help you to comment your code where you have row/cell create/get to indicate to yourself which column or row is being addressed.

Upvotes: 1

Related Questions