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', '').init(currentFilePath & currentCharterTemplate);
excelFile = createObject('java', '').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++) {
  theCurrentRow = summarySheet.getRow(rows);
  for (columns = 0; columns <= totalColumns; 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.

  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);