However when i open the report in excel these cells come out as general or text formatting and not a Number cell format? This then seems to effect a sum column at the bottom which does not seem to calculate the totals and comes up as 0.0. When i type in a value manually into these cells then the sum field automatically seems to generate the totals… Does anyone have any ideas…any help appreciated.
Lucee doesn’t support spreadsheet functions out of the box so you must be using the community spreadsheet extension. Unfortunately its SpreadsheetSetCellValue() function doesn’t support the datatype argument.
You just need to copy the library to where your app can call it. No installation required, and you can use it alongside the cfspreadsheet extension if you have a lot of existing code you don’t want to update (although the syntax is quite similar).
Hi @Julian_Halliwell , thanks for the reply. I can see that you created this library? Thats great.
I was wondering if you could help me further…I’m facing this issue when trying to create a workbook with multipe sheets
Basically i’m looping a query that brings back a number of years. then for each year i have specific data which I then want to add to the current sheet in the loop like so:
local.YOAs = local.query.setSQL(local.sql).execute().getResult();
spreadsheet = New spreadsheetLibrary.Spreadsheet();
workbook = spreadsheet.new();
// loop the years
for(local.yoa IN local.YOAs){
spreadsheet.createSheet( workbook,local.yoa.yoa );
data = QueryNew( “First,Last”, “VarChar, VarChar”, [ [ “Susi”, “Sorglos” ], [ “Frumpo”, “McNugget” ] ] );
spreadsheet.addRows( workbook, data );
}
spreadsheet.write( workbook, “#expandPath(‘new-example2.xls’)#”);
Basically i get a workbook with the first sheet called sheet1 and not a name of the year ive specified. I get the other sheets created with the year ok but none contain the data? The first sheet seems to contain all the data… Can you pls see if anything wrong in the code? Many Thx
That’s because when you create a new workbook it contains a default sheet called “Sheet1”. Your loop is then adding more sheets named as each year, but the default sheet is still there.
The simplest solution is probably to just delete the default sheet before you start your loop over the years, so just add this line immediately after you create the workbook:
But as you’ve found, adding data like that in the loop will put it all in the first sheet, because it remains the “active” sheet. To add the data to each sheet, you need to make each newly created sheet “active” first. Do this with setActiveSheet() before you add the row data.
// loop the years
for(local.yoa IN local.YOAs){
spreadsheet.createSheet( workbook,local.yoa.yoa );
spreadsheet.setActiveSheet( workbook, local.yoa.yoa );
data = QueryNew( “First,Last”, “VarChar, VarChar”, [ [ “Susi”, “Sorglos” ], [ “Frumpo”, “McNugget” ] ] );
spreadsheet.addRows( workbook, data );
}
Ah, it’s the row that you are referencing in your setRowHeight() call which doesn’t exist. As you’re specifying row 1 that would suggest the currently active sheet doesn’t contain any data.
Bascilly I’m trying to add a logo image to the first row after setting it to a height of 60. I would have thought I can have an empty sheet and just increase the first row height without needing to add any data? Or do i need to add some data first to the sheet and then can increase the row height?
Even if i switch around the addimage first and then the setrowheight i still get the same error?
Incidentally, another issue I can see is that the endRow and endColumn anchors for the image you are adding are the same as the startRow and startColumn values. This means your image won’t appear. The end values aren’t inclusive, so set them to one above the start values if you are adding the image to one cell.
