General function that takes an array of structs representing data to go on the file along with column/row/cell styling.

createSpreadSheet.cfm

/*
Example argument structure
worksheets = [
  {
      name: [sheet_name],
      data: [query],
      includeColumnNames: [true|false],
      columnFormat: [struct],
      columnCellStyle: [array_of_structs]
	}
]
*/

/**
 * @hint Creates a spreadsheet object of the data passed in.
 * @worksheets.hint An array of structs that represent the worksheet name, data, and column details.
 * @xmlFormat.hint Determines if the workbook format should be XLSX or XLS. Defaults to true (XLSX).
 */
public string function createSpreadsheet(
  required array worksheets,
  boolean xmlFormat = true
) {
  // Reduce each worksheet collection into a single workbook
  return arguments.worksheets.reduce((workbook, worksheet, wsIndex) => {
    // Sheet names have a max length of 31, default if too long
    var sheetName = (arguments.worksheet.name.len() <= 31)
      ? arguments.worksheet.name
      : "Sheet #wsIndex#";

    // Set workbook worksheet name if first page
    if (arguments.wsIndex == 1) {
      arguments.workbook.getWorkBook().setSheetName(0, sheetName);
    }
    // Otherwise create the next worksheet
    else {
      arguments.workbook.createSheet(sheetName);
    }

    // Set which worksheet is currently being worked on
    arguments.workbook.setActiveSheet(sheetName);
    // Get the worksheet object for further formatting (index begins at 0)
    var currentWorksheet = arguments.workBook.getWorkBook().getSheetAt(arguments.wsIndex - 1);

    // Get query columns going on the worksheet
    // Excludes an config/styling columns (e.g. X_BackgroundColor)
    // Note: The result is not a true CF array, so member functions cannot be used on it
    var columns = arrayFilter(
      arguments.worksheet.data.getColumnNames(),
      (column) => !arguments.column.findNoCase("X_")
    );

    // Column header
    var rowPadding = 0;
    if (arguments.worksheet.includeColumnNames) {
      // Increase row position by 1 to account for header
      rowPadding++;
      // Add and format columns to worksheet
      columns.each((column, colIndex) => {
        workbook.addColumn(arguments.column, 1, arguments.colIndex, true, "STRING");
      });
      arguments.workBook.formatRow(arguments.worksheet.columnFormat, 1);
    }

    // Write row data to worksheet
    for (var row in arguments.worksheet.data) {
      // Current row going on sheet (data row + column row)
      var currentDataRow = arguments.worksheet.data.currentRow + rowPadding;

      // Get row formats, if defined (color, font, etc.)
      // Format Options: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetformatcell.html
      var format = (row?.X_Format?.len() > 0) ? deserializeJSON(row.X_Format) : {};

      // Write cells and apply any formatting
      columns.each((column, colIndex) => {
        // Write the current column row cell to the workbook
        var columnRowCell = row[arguments.column];
        workbook.setCellValue(columnRowCell, currentDataRow, arguments.colIndex, "STRING");

        // Get current column cell style info
        var cellStyle = worksheet.columnCellStyle[arguments.colIndex];

        // Merge column cell format with row format
        // Note: Overwrites any formats already specified on the row
        if (cellStyle.format.count() > 0) {
          format.append(cellStyle.format, true);
        }
        // Apply formatting to the current column row cell
        workbook.formatCell(format, currentDataRow, arguments.colIndex);

        // Apply formula to the current column row cell
        if (cellStyle.formula.len() > 0) {
          workbook.setCellFormula(cellStyle.formula, currentDataRow, arguments.colIndex);
        }
      });
    }

    return arguments.workbook;
  }, spreadsheetNew(arguments.xmlFormat));
}