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.

  currentTemplate = 'filename.xlsx';
  currentFilePath = getDirectoryFromPath(getCurrentTemplatePath());
  javaFile = createObject('java', '').init(currentFilePath & currentTemplate);
  excelFile = createObject('java', '').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 =;

    // 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 =;
        //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) {
          if (currentCellNumber == 2) {
          if (currentCellNumber == 3) {
          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) {

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

  // close the file input stream
  // create a new excel file
  newFileName = 'roster.xlsx';
  newFile = createObject('java', '').init(currentFilePath & newFileName);
  newExcelFile = createObject('java', '').init(newFile);

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

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

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