Adding rows to an excel spreadsheet using apache poi

In a recent project for a client I was tasked with modifying an existing excel spreadsheet to add data from a query. Being familiar with Java and ColdFusion I assumed this would be a pretty trivial exercise. Read the existing file, get the sheet and then write the data, but I ran into an issue where adding rows using shiftRows didn’t make them writable and/or visible to apache poi. I realized I needed to literally add the rows and the columns to the excel spreadsheet to be able to change the values. Not a big deal code-wise and also really fast to complete, but frustrating to figure out.

currentCharterTemplate = 'existingWorkbook.xlsx';
currentFilePath = getDirectoryFromPath(getCurrentTemplatePath());
javaFile = createObject('java', 'java.io.File').init(currentFilePath & currentCharterTemplate);
excelFile = createObject('java', 'java.io.FileInputStream').init(javaFile);
xssfWorkbook = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFWorkbook').init(excelFile);

summarySheet = xssfWorkbook.getSheetAt(0);
totalColumns = 12;
rowsToAdd = query.recordCount;
//add enough rows/columns to the spreadsheet to handle the record count of the query and the sort fields
for (rows = 1; rows <= rowsToAdd; rows++) {
  summarySheet.createRow(rows);
  theCurrentRow = summarySheet.getRow(rows);
  for (columns = 0; columns <= totalColumns; columns++) {
    theCurrentRow.createCell(columns);
    theCurrentRow.getCell(columns);
  }
}

ColdFusion and Apache POI modifying an existing file

I was tasked with creating an excel spreadsheet that mimics a template from a vendor and after a couple hours of struggling I realized it was fruitless and it’d be easier to modify the existing file. The existing file had macros that were ran to validate certain things and all I was adding was names, date of birth, weight and gender.

The following is the gist of what I ended up doing. This is 100% using the poi built into ColdFusion and does not require extra jars or adding anything to the classpath.

<cfscript>
  currentTemplate = 'filename.xlsx';
  currentFilePath = getDirectoryFromPath(getCurrentTemplatePath());
  javaFile = createObject('java', 'java.io.File').init(currentFilePath & currentTemplate);
  excelFile = createObject('java', 'java.io.FileInputStream').init(javaFile);
  xssfWorkbook = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFWorkbook').init(excelFile);

  // get the first sheet index is 0 based in Java
  sheet1 = xssfWorkbook.getSheetAt(0);
  rowIterator = sheet1.iterator();
  while (rowIterator.hasNext()) {
    currentRow = rowIterator.next();

    // row 8 is where we want to start writing the names
    if (currentRow.getRowNum() > 7) {
      cellIterator = currentRow.iterator();

      while (cellIterator.hasNext()) {
        //1 - last name, 2 first name, 3 middle, 4 DOB, 5 Gender, 6 Weight
        currentCell = cellIterator.next();
        //when in doubt of methods dump out the whole java object
        //writeDump(currentCell); abort;
        currentCellNumber = currentCell.getColumnIndex();
        currentQueryRow = currentRow.getRowNum() - 7;
        // writing an empty row made the macro think I was adding a user
        if (len(trim(getTravelers['lastName'][currentQueryRow]))) {
          if (currentCellNumber == 1) {
            currentCell.setCellValue(getTravelers['lastName'][currentQueryRow]);
          }
          if (currentCellNumber == 2) {
            currentCell.setCellValue(getTravelers['firstName'][currentQueryRow]);
          }
          if (currentCellNumber == 3) {
            currentCell.setCellValue(getTravelers['middle'][currentQueryRow]);
          }
          if (currentCellNumber == 4) {
            currentCell.setCellValue(dateFormat(getTravelers['dob'][currentQueryRow], 'mm/dd/yyyy'));
          }
          if (currentCellNumber == 5) {
            currentCell.setCellValue(left(getTravelers['gender'][currentQueryRow], 1));
          }
          if (currentCellNumber == 6) {
            currentCell.setCellValue(getTravelers['weight'][currentQueryRow]);
          }
        }
      }
    }
  }

  // need to force formulas to be recalculated once the file is written
  formulaEvaluator = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator');
  formulaEvaluator.evaluateAllFormulaCells(xssfWorkbook);
  // and once the workbook is opened
  xssfWorkbook.setForceFormulaRecalculation(true);

  // close the file input stream
  excelFile.close();
  // create a new excel file
  newFileName = 'roster.xlsx';
  newFile = createObject('java', 'java.io.File').init(currentFilePath & newFileName);
  newExcelFile = createObject('java', 'java.io.FileOutputStream').init(newFile);
  xssfWorkbook.write(newExcelFile);
  newExcelFile.close();
</cfscript>

Multiple insert statement without counter

I often have a need to write an INSERT statement that adds multiple rows to a database. Obviously you can run any ┬ánumber of inserts separately, but that’s innefficient, so I loop over a list with a counter to know when to end. Recently I learned that the counter is unnecessary and you can create a fake SELECT statement to avoid needing a counter

<cfquery>
INSERT INTO Attendees (Schedule_ID, Attendee_ID, Meeting_ID, User_ID)
<cfloop list="#variables[eventlocation]#" index="i">
  SELECT #Schedule_ID#, #i#, #Meeting_ID#, 1
  UNION ALL
</cfloop>
<!--- returns no rows --->
SELECT 0, 0, 0, 0
WHERE 1 = 0
</cfquery>

For the sake of brevity I’ve avoided omitted the cfqueryparam’s that should always be used in queries.