Using POI for posting data to Excel xlsx spreadsheet file

[Bill Melendez]

I wrote a program using the java POI to Excel that places product name and quantity to the spreadsheet. The spreadsheet has 15 rows with possible entries. After the POI posts the admin data at the top of the spreadsheet, the POI works on the first 4 lines without any issues, but then doesn’t post the other product names and quantities once it encounters a blank row entry.

The spreadsheet has 15 rows with name, price, quantity. I need to be able to place the product name and quantity anywhere on any of the 15 rows. For example, the first 4 rows (PRO1 – row 3, PRO4 – row 6, and PRO10 -row 69) get posted correctly to the spreadsheet. After rows 12 thru 15 are posted correctly, there are several blank rows (which have basically the same coding as the other selections but have empty variables for posting to the excel file). After the blank rows, the following rows get more product names and quantities. That’s where I have a problem. Those consecutive rows that do have product names and quantities for posting are not posted. So I end up with the product section only having the first 4 products in the spreadsheet. The rest of the products are not posted to the file.  

I checked, using print statements after each cell entry coding, for the variables (PRO1-PRO6 AND PRO1X THRU PRO66X) and they have the correct product name and quantity from the database. I then substituted a string within quotations instead of each variable and that also posted correctly to the spreadsheet.  

However, when I go back to using the variables only (after PRO5X entries) no posting occurs. I know my database Result statement does receive the product name and quantity correctly from the database. It correctly assigns the database data to the variables (PRO1, etc.). The variables do have data content correctly in the method (doDATA1() ) from accessing the database and in the method (set2EXCEL3() )for posting these variables to the Excel file. I can System print all the variables within the set2EXCEL3() method.

Please let me know what I’m doing incorrectly. Attached is a jpg shot of the spreadsheet rows and the missing data.

public void set2EXCEL3() throws FileNotFoundException, IOException, NullPointerException,SQLException, InstantiationException{
 
         int row=15;
         for (int i = 0; i < row; i++) {
             //START ADMIN SECTION
             
             //Set Project Name to Excel File
            If   (PRO1.length()>0){
                	XSSFRow row1 = sheet.getRow(3);
                	cell = row1.getCell(3);
                	style1 = cell.getCellStyle();
               	 style1.setFont(font2);
                	cell.setCellStyle(style1);
                	cell.setCellValue(PRO1);
            }
             
             // Set Invoicing Entity to Excel File
            If  (PRO2.length()>0){
                	XSSFRow row2 = sheet.getRow(4);
                	cell = row2.getCell(3);
               	 style2 = cell.getCellStyle();
                	style2.setFont(font1);
                	cell.setCellStyle(style2);
                	cell.setCellValue(PRO2);
            }
             
              //START MATERIAL SECTION ENTRIES TO EXCEL FILE
           
             //tubes entries Line 13 of Spreadsheet (line 12 in POI)
             If (PRO1X.length()>0){
                	XSSFRow row10 = sheet.getRow(12);
                	cell = row10.getCell(4);
                    style2 = cell.getCellStyle();
                	style2.setFont(font3);
                	cell.setCellStyle(style2);
                	cell.setCellValue(PRO1X);
             
                	XSSFRow row11 = sheet.getRow(12);
                	cell = row11.getCell(6);
                	style2 = cell.getCellStyle();
                	style2.setFont(font3);
                	cell.setCellStyle(style2);
                	cell.setCellValue(PRO1XQ);
                 	
            }
             
             //couplings entries line 14 of Spreadsheet (line 13 of POI)  
            If (PRO2X.length()>0){
                	XSSFRow row12 = sheet.getRow(13);
               	 cell = row12.getCell(4);
                	style2 = cell.getCellStyle();
                	style2.setFont(font3);
                	cell.setCellStyle(style2);
                	cell.setCellValue(PRO2X);
             
                	XSSFRow row13 = sheet.getRow(13);
                	cell = row13.getCell(6);
                	style2 = cell.getCellStyle();
                	style2.setFont(font3);
                	cell.setCellStyle(style2);
                	cell.setCellValue(PRO2XQ);
                 	
            }
             
            
             
             //Space One entries line 17 of Spreadsheet (line 16 of POI)
             
              If  (PRO5X.length()>0){
                     XSSFRow row18 = sheet.getRow(16);
                    cell = row18.getCell(4);
                
                    style2= cell.getCellStyle();
                    style2.setFont(font3);
                    cell.setCellStyle(style2);
                    cell.setCellValue(PRO5X);
                 
                     XSSFRow row19 = sheet.getRow(16);
                    cell = row19.getCell(6);
                    style2= cell.getCellStyle();
                    style2.setFont(font3);
                    cell.setCellStyle(style2);
                    cell.setCellValue(PRO5XQ);
                     System.out.println(PRO5X);
                  } 
             
                         
             
            // Data Collector entries line 27 of spreadsheet (line 26 of POI)
               If  (PRO15X.length()>0){
                 XSSFRow row38 = sheet.getRow(26);
                cell = row38.getCell(4);
                style2= cell.getCellStyle();
                style2.setFont(font3);
                cell.setCellStyle(style2);
                cell.setCellValue(PRO15X);
             
                XSSFRow row39 = sheet.getRow(26);
                cell = row39.getCell(6);
                cell.getCellStyle();
                style3.setFont(font3);
                cell.setCellStyle(style3);
                cell.setCellValue(PRO15XQ);
                System.out.println(PRO15X);
            }
         }
    
        try (FileOutputStream fos = new FileOutputStream(new File(filename2))) {
            workbook.write(fos);
        }
		System.out.println("Done");
      }
 }

[Carey Brown]

Not enough information. Seeing as how you're not using the loop variable 'i' anywhere you'd just be writing the same data to the same cells 15 times.

[Paul Clapham]

Carey Brown wrote:Not enough information.

I read through your post a few hours ago. I read it several times, trying to match the text to the code, and failed to find a match. The text mentioned some variables which weren't used in the code as if they were significant, for example.

So I decided to leave it alone and see what other people came up with. Looks like they've got nothing too.

So help us out. For example: is there code in the method you posted which should be putting non-blank data into cells, but it isn't doing that?

[Bill Melendez]

Paul:

To start, I use Netbeans 8.2 IDE to develop the software. I do so because I use tons of input screens that interact with several Derby Databases --basically, a CRM type program, and I'm more comfortable with the GUI developer in Netbeans.

The variables are PRO1 to PRO10 (ADMIN Section on top of Spreadsheet); PRO1X to PRO15X (Product section below the Admin). I use a Derby DB which places data into these variables.

Then these variables are assigned to a row/cell location. Unfortunately, the amount of code I can post is limited to only a certain length so some of the codes were omitted. But the basic block used for placing into the spreadsheet is as follows. I use the same code for every row/cell entry. This is a DRAFT software program that I will clean up and improve once I have all the parts working. The problem is that the below code works (PRO1 to PRO10 and PRO1X to PRO5X) fine until it gets to PRO6X to PRO15X. The variables are PRO1X = Product Name, PRO1XQ = Product Quantity. These are posted to the spreadsheet (the Excel Spreadsheet has lookup tables for Costs, Markup, and Margin). I have tried everything I could think of but the problem is still there. Normally the code should place the database data from each variable to the location I specified in the code --and it does up to PRO5X. This program is a java "learning" program for me that I am doing to expedite and automate some of my own workloads. I don't do java professionally --just amateur stuff.

You can substitute any strings for the variables--doesn't matter since we're just placing strings to cell locations. I can also send you the entire java file. Email: [e-mail].

//tubes entries Line 13 of Spreadsheet (line 12 in POI)
            if (PRO1X.length()>0){
               XSSFRow row10 = sheet.getRow(12);
               cell = row10.getCell(4);
               style2 = cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO1X);
           
XSSFRow row11 = sheet.getRow(12);
               cell = row11.getCell(6);
               style2 = cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO1XQ);
               
           }

[Paul Clapham]

Bill Melendez wrote:The problem is that the below code works (PRO1 to PRO10 and PRO1X to PRO5X) fine until it gets to PRO6X to PRO15X. The variables are PRO1X = Product Name, PRO1XQ = Product Quantity.

So as far as I can tell, you've posted the code which works but not the code which doesn't work.

Also, the code which you have posted seems to repeat the same code over and over again with minor changes. This is a situation where you ought to use a method which contains the repeated code and just call the method repeatedly.

[Bill Melendez]

Here's the code that doesn't work. It is the same as the previous code except for the variable and the row/cell locations:

                //Space Two entries line 18 of Spreadsheet (line 17 of POI)
               If  (PRO6X.length()>0){
                XSSFRow row20 = sheet.getRow(17);
              cell = row20.getCell(4);
                   style2= cell.getCellStyle();
                style2.setFont(font3);
                cell.setCellStyle(style2);
                cell.setCellValue(PRO6X);
           
                XSSFRow row21 = sheet.getRow(17);
                cell = row21.getCell(6);
                style2= cell.getCellStyle();
                style2.setFont(font3);
                cell.setCellStyle(style2);
                cell.setCellValue(PRO6XQ);
               
               }
         
           
            //Counters entries line 23 of the spreadsheet (line 22 of POI)
           
          If  (PRO11X.length()>0){
               XSSFRow row30 = sheet.getRow(22);
               cell = row30.getCell(4);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO11X);
           
               XSSFRow row31 = sheet.getRow(22);
               cell = row31.getCell(6);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO11XQ);
               
           }
           
            //Transmitter entries line 24 of spreadsheet (line 23 of POI)
           
             If  (PRO12X.length()>0){
               XSSFRow row32 = sheet.getRow(23);
               cell = row32.getCell(4);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO12X);
           
               XSSFRow row33 = sheet.getRow(23);
               cell = row33.getCell(6);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO12XQ);
               
           }
           
           //Repeater entries line 25 of spreadsheet (line 24 of POI)
            If  (PRO13X.length()>0){
               XSSFRow row34= sheet.getRow(24);
               cell = row34.getCell(4);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO13X);
           
               XSSFRow row35 = sheet.getRow(24);
               cell = row35.getCell(6);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO13XQ);
               
           }
           
           //Receivers entries line 26 of spreadsheet (line 25 of POI)
             If  (PRO14X.length()>0){
               XSSFRow row36 = sheet.getRow(25);
               cell = row36.getCell(4);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO14X);
               
               XSSFRow row37 = sheet.getRow(25);
               cell = row37.getCell(6);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO14XQ);
           }
           
           // Data Collector entries line 27 of spreadsheet (line 26 of POI)
              If  (PRO15X.length()>0){
                XSSFRow row38 = sheet.getRow(26);
               cell = row38.getCell(4);
               style2= cell.getCellStyle();
               style2.setFont(font3);
               cell.setCellStyle(style2);
               cell.setCellValue(PRO15X);
           
               XSSFRow row39 = sheet.getRow(26);
               cell = row39.getCell(6);
               cell.getCellStyle();
               style3.setFont(font3);
               cell.setCellStyle(style3);
               cell.setCellValue(PRO15XQ);
               System.out.println(PRO15X);
           }
        }
   
       try (FileOutputStream fos = new FileOutputStream(new File(filename2))) {
           workbook.write(fos);
       }
System.out.println("Done");
     }
}

[Bill Melendez]

As you probably guessed, I'm not an expert on JAVA, and not sure how I would use a method that handles the repeated codes. The only changes are the variables and the row/cell locations. Any suggestion would be greatly appreciated.

[Paul Clapham]

Old code (for example):

      XSSFRow row34= sheet.getRow(24);
      cell = row34.getCell(4);
      style2= cell.getCellStyle();
      style2.setFont(font3);
      cell.setCellStyle(style2);
      cell.setCellValue(PRO13X);

You have many many variations on that code. Just write it once:

   private void fillInCellData(int rowNumber, int cellNumber, FontThingy font, String contents) {
      XSSFRow row= sheet.getRow(rowNumber);
      XSSFCell cell = row.getCell(cellNumber);
      XSSFCellStyle style= cell.getCellStyle();
      style.setFont(font);
      cell.setCellStyle(style);
      cell.setCellValue(contents);
   }

And call it like this:

   if (PRO13X.length() > 0 {
      fillInCellData(24, 4, font3, PRO13X);
      fillInCellData(24, 6, font3, PRO13XQ);
   }

I've guessed at some of the datatypes and made some of them up, because I don't know what they actually are, but you should get the idea.

[Bill Melendez]

Paul:

Thank you so much. You solve the problem and I do appreciate your help. Hope your Holiday Season is full of Christmas joy.

[Bill Melendez]

Paul:  What is a FontThingy?

[Paul Clapham]

Paul Clapham wrote:I've guessed at some of the datatypes and made some of them up, because I don't know what they actually are, but you should get the idea.

[Bill Melendez]

Paul:  I did! I was able to create a font method for it. Again thanks.

[Paul Clapham]

Right, I didn't know whether your "font3" variable was of type Font or int or whatever, but I didn't feel like researching the POI API to see what was supposed to go into the setFont() method. So I thought I'd just let you figure it out... looks like it worked, then.