CF10 and Spreadsheets changed from CF9 !!

[Ken R Caldwell]

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

[Anit_Kumar]

Hi Ken,

Can you share a sample code with us?

Regards,

Anit Kumar

[Ken R Caldwell]

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

[Anit_Kumar · Accepted solution]

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

[Ken R Caldwell]

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.