4 Replies Latest reply on Dec 11, 2013 4:54 PM by Ken R Caldwell

    CF10 and Spreadsheets changed from CF9 !!

    Ken R Caldwell Level 1

      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

        • 1. Re: CF10 and Spreadsheets changed from CF9 !!
          Anit_Kumar Adobe Employee

          Hi Ken,

           

          Can you share a sample code with us?

           

          Regards,

          Anit Kumar

          • 2. Re: CF10 and Spreadsheets changed from CF9 !!
            Ken R Caldwell Level 1

            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

            • 3. Re: CF10 and Spreadsheets changed from CF9 !!
              Anit_Kumar Adobe Employee

              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

              • 4. Re: CF10 and Spreadsheets changed from CF9 !!
                Ken R Caldwell Level 1

                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.