Created
May 15, 2022 06:35
-
-
Save tonyjunkes/49a3975984e003b31508418e66a15433 to your computer and use it in GitHub Desktop.
General function that takes an array of structs representing data to go on the file along with column/row/cell styling.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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)); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment