setCellValue and preserve the existing formatting in the cell

[dlineg4]

My use case is I have an existing excel file I'm using as a template and I'm populating the excel file with data from a database query. The first row in the excel file has a background color of Yellow and has bold formatting. When I call:

spreadsheet.setCellValue(
          workbook=workbook,
          value="My Header Cell",
          row=1,
          column=1
      );

the formatting that already exists in Cell 1:1 is removed and I wind up having to reformat the row at the end of my population process.

Is there a way to preserve the existing cell formatting that I'm not aware of?

Thanks!

[sebgmc]

Hi dlineg4,

What I usually do in that case is I use the AddRow function. So I loop over the data per row and add one row at a time with data, filling all the cells at once. This preserves the formatting of the preloaded spreadsheet.

The way this works is you build an array of data per row and then add the array at once per row like this:

spreadsheet.addRow(workbook=spreadsheetObj, data=spreadsheetRowData);

Hope this helps.

Sebastiaan

[sebgmc]

Here's an example of filling a preloaded spreadsheet using setCellValue which preserves the pre-exisiting formatting, it only replaces the values in the cell:

/* subtitle for the report */
/* row 4 */
var cellVal = spreadsheet.getCellValue(workbook=spreadsheetObj, row=4, column=1);
cellVal = Replace(cellVal, "<NaamVestiging>", spreadsheetObjData.NaamVestiging, "one");
cellVal = Replace(cellVal, "<qMonth>", spreadsheetObjData.qMonth, "one");
cellVal = Replace(cellVal, "<qYear>", spreadsheetObjData.qYear, "one");
spreadsheet.setCellValue(workbook=spreadsheetObj, value=cellVal, row=4, column=1);

I've done this on Lucee Spreadsheet version < 2.13 and > 2.13 and the latest version of Spreadsheet-CFML.

[cfsimplicity]

@dlineg4 setCellValue() should only overwrite the value in the cell not the style. I can't seem to replicate what you are seeing with a simple test:

templatePath = "c:/temp/template.xlsx"
testPath = "c:/temp/test.xlsx"
spreadsheet = New spreadsheet.Spreadsheet()
//create a template with a formatted cell
template = spreadsheet.newXlsx()
spreadsheet.setCellValue( template, "header", 1, 1 )
spreadsheet.formatCell( template, { bold: true, fgcolor: "YELLOW" }, 1, 1 )
spreadsheet.write( template, templatePath, true )
// read the template, replace the value in the cell and save as a separate file
wb = spreadsheet.read( templatePath )
spreadsheet.setCellValue( wb, "test", 1, 1 )
spreadsheet.write( wb, testPath, true )

Can you reduce what you are doing to a simple test case that reproduces the issue?

Are you by any chance using Streaming XLSX anywhere in the process?

[dlineg4]

Thanks all for the feedback. I did spend some time trying to get a handle on this. My template excel files first row is formatted yellow and bold (it's done by clicking the row and applying formatting in excel).

I think the issue has to do with Row formatting not being preserved vs Cell Formatting. In my template file if I explicitly set a cell format, when I call setCellValue the formatting is preserved. If I call setCellValue on a cell that was formatted using row format, then the formatting is removed.

In my example here, my template.xls file has the first row formatted using Yellow and Bold. I explicitly formatted Cell 2 with Blue and Bold. When I run the code below Cell 1:1 is not formatted and Cell 1:2 preserves the formatting.

var sourceFilePath = 'C:\temp\template.xls';
var outputFilePath = 'C:\temp\test.xls';

/* I'm using spreadsheet as a coldbox module */
// spreadsheet = New spreadsheet.Spreadsheet()

var workbook = spreadsheet.read(src=sourceFilePath);

spreadsheet.setCellValue(
        workbook=workbook,
        value="TESTING 1",
        row=1,
        column=1
    );

spreadsheet.setCellValue(
        workbook=workbook,
        value="TESTING 2",
        row=1,
        column=2
    );

spreadsheet.write(
        workbook=workbook, 
        filePath=outputFilePath,
        overwrite=true
    );   

I'm attaching the files I used. I would have created the template using the spreadsheet library, but for some reason I couldn't get 'formatRow()' working.

template.xls test.xls

[cfsimplicity]

Thanks for the further details. I can replicate the issue and as you say it must be something to do with the way Excel (and Libre Office which I use) handles formatting manually applied to an entire empty row.

I think POI (the underlying java library) doesn't see that formatting because there are no values in the row. It certainly only allows formatting to be applied to cells which contain values (which may be "blank" values), which is presumably why you say you couldn't get formatRow() working to create your template with the library.

It should work though if you add blank values when creating the template, like so:

//create a template with a formatted header row
template = spreadsheet.newXls()
blankValues = []
//Lucee only, use cfloop with from, to and index in ACF
// adjust to how many formatted cells you need
loop times=100{
  blankValues.Append( "" )
}
spreadsheet.addRow( template, blankValues )
spreadsheet.formatRow( template, { bold: true, fgcolor: "YELLOW" }, 1 )
spreadsheet.write( template, templatePath, true )

[cfsimplicity]

Researching a bit further, it looks like it is possible to apply "whole-row cell styling" with POI using row.setRowStyle(). I've tested it out but the effect is exactly the same as when you do it manually: i.e. setting a value on any cell in the row ignores the preset row style.

There isn't much documentation around it, but it seems fairly useless.

Formatting really needs to be done cell by cell on cells with values, which is what the library format functions all do, and hence the suggested workaround.

[dlineg4]

Thanks for the help. It's unfortunate the behavior isn't what I would expect but I can deal with this limitation.

[cfsimplicity]

Agreed, the way POI works with "whole-row cell styling" isn't what you'd expect.

I've found you can also apply my suggestion "manually" to the template if that's how you prefer to set things up. So instead of selecting the entire first row and changing the background formatting, you can select the range of cells you want to format, e.g. A1-Z1 and set the format using Excel.

Then when you set the values with the library the style will be preserved: the reason again being that the style has been set at the individual cell level rather than the row level.