SpreadsheetSetCellValue not setting as a number in excel

I’m generating an excel report (xlsx) that has some money columns in it. I’m generating these columns as follows:

SpreadsheetSetCellValue(local.sheetObj, local.cellValue, local.rowCount+local.headerRowCount, local.count+1, “NUMERIC”);

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.

Worse still, it seems to force everything to a string.

I’m biased, but I’d suggested checking out the Spreadsheet CFML library which does support specifying the datatype:


Thanks for the reply @Julian_Halliwell Much appreciated. I’ll try to see if I can implement this library with my Lucee app. Hope its not a pain to do!

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:

workbook = spreadsheet.new();
spreadsheet.removeSheetNumber( workbook, 1 );

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

Thanks for the help @Julian_Halliwell . Making progress…much appreciated!

Incidentally, the library supports chaining so you could declutter slightly (if you’re into that kind of thing):

for(local.yoa IN local.YOAs){
  data = QueryNew( “First,Last”, “VarChar, VarChar”, [ [ “Susi”, “Sorglos” ], [ “Frumpo”, “McNugget” ] ] );
  spreadsheet.createSheet( workbook,local.yoa.yoa )
  	.setActiveSheet( workbook, local.yoa.yoa )
  	.addRows( workbook, data )

I’m getting an interesting error when trying to use the setRowHeight function:

can’t call method [setHeightInPoints] on object, object is null

I’m implementing the function as follows:

local.spreadsheet.setRowHeight(workbook=local.workbook, row=1, height=60);

Was wondering if you had any ideas on this? @Julian_Halliwell

Thanks in advance!

That would suggest your local.spreadsheet object doesn’t exist. Where are you instantiating the library?

Sorry, it doesn’t suggest that. I can replicate it and have created a new ticket over on Github, which I’ll investigate:

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.

I’ll try and improve the exception message.

Hi @Julian_Halliwell ,

many thnaks for getting back to me. Yes I am instantiating the library ok as i can get it to create a new sheet, freeze the first row etc.

local.spreadsheet = New spreadsheetLibrary.Spreadsheet();
local.workbook = local.spreadsheet.new(xmlFormat=true);
local.spreadsheet.removeSheetNumber( workbook, 1 );
local.sheetname = “Test”;
local.spreadsheet.createSheet(local.workbook, local.sheetname );
local.spreadsheet.addFreezePane(local.workbook, 0, 7);
local.spreadsheet.setRowHeight(workbook=local.workbook, row=1, height=60);
local.spreadsheet.addImage(workbook=local.workbook, filepath=application.reportLogo, anchor=“10,10,1390,760,1,1,1,1” );

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?

No, rows don’t exist until you create them by adding data. Try adding a blank row before calling setRowHeight():

local.spreadsheet.addRow( workbook, "" );
1 Like

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.

local.spreadsheet.addImage(workbook=local.workbook, filepath=application.reportLogo, anchor="10,10,1390,760,1,1,2,2" );
1 Like

@Julian_Halliwell thanks a lot for your help Julian…it’s been absolutely great and has helped me tremendously with my work! Really appreciated!


Come across another issue. I’m trying to display dates in the following format


However they seem to come out as US format?

In my code i have the following and have set the type to DATE:

local.spreadsheet.setCellValue(local.workbook, "#dateformat((local.data[ "#local.fieldName#" ][ local.intRow ]),"dd-mm-yyyy")#", local.rowcount+local.headerRowCount, local.count+1, "DATE");

Is there a way to set the mask on setCellValue function?

Your screenshot is showing them as the international ISO8601 format, which is the default mask used by the library.

You can change the default mask when you instantiate the library.

Alternatively you can format specific cells that contain a date value using the formatting methods, for example:

spreadsheet.formatCell( workbook, { dataformat: "dd/mm/yyyy" }, 1, 1 )
1 Like