Copy link to clipboard
Copied
Hi All,
Has Coldufsion 10 changed how the interopablity between Coldfusion and ms office work?
I had some code that worked perfectly in CF9.
It read an xls or xlsx file and deleted some sheets that where not required and then saved the xls or xlsx
But now in CF10 with all xlsx files I get a null pointer error.
It would appear that when using SpreadSheetRead this always opens the file in xls mode.
I have tried numerous variation to find a solution.
I also tried to create a new spreadsheet with SpreadsheetNew this was created in xlsx mode.
While I could update cells and save as xlsx I could not read a sheet from an existing file and add it to this one.
Is there anyone from Adobe that can shed some light on this??
Regards
Ken Caldwell
Hi Ken,
I checked the code on CF10 and it works at my end. The update level is /C:/ColdFusion10/cfusion/lib/updates/chf10000011.jar
Regards,
Anit Kumar
Copy link to clipboard
Copied
Hi Ken,
Can you share a sample code with us?
Regards,
Anit Kumar
Copy link to clipboard
Copied
Hi Anit,
Thanks for the reply.
I sure can
<cfscript>
sheetsToKeep = '1'; // sheet number to keep
theFile = thePath & "\myXLSX.xlsx"; // path to the xlsx file
spreadsheet = SpreadSheetRead(theFile); // read the spreadsheet
sheetsToDelete = ""; // var to hold the sheet that need to be deleted
// loop through the sheets in the spreadsheet
for(x=1; x<=spreadsheet.SummaryInfo.Sheets; x++){
if(listContains(sheetsToKeep, x) eq 0){
// build a list of sheets to delete
sheetsToDelete = listAppend(sheetsToDelete, listgetAt(spreadsheet.SummaryInfo.SheetNames, x));
}
}
// loop through the sheets to delete and delete them
for(s=1; s<=listLen(sheetsToDelete); s++){
SpreadsheetSetActiveSheet (spreadsheet, listGetAt(sheetsToDelete, s));
spreadsheetRemoveSheet(spreadsheet, listGetAt(sheetsToDelete, s));
}
//save the spreadsheet
SpreadSheetWrite(spreadsheet, theFile, "yes");
</cfscript>
The above code works in CF9, but I get the follwoing error in CF10
An exception occurred while using action=write
java.lang.NullPointerException
I should also state that if I save the xlsx as xls in ms excel and then use the same code it works.
I have also tried the read and save and removed all the code inbetween and still get the error.
Regards
Ken Caldwell
Copy link to clipboard
Copied
Hi Ken,
I checked the code on CF10 and it works at my end. The update level is /C:/ColdFusion10/cfusion/lib/updates/chf10000011.jar
Regards,
Anit Kumar
Copy link to clipboard
Copied
Hi Anit,
Thanks for the reply.
It turned out that we have a person who is maintaining these xlsx files and using copy and paste from somewhere else.
what happened is that some of the fields ended up with hidden characters in them and this is what was causing the errors.
Thanks for looking into this.