10 Replies Latest reply: Jun 2, 2011 2:59 PM by -==cfSearching==- RSS

    How To Close an Excel File?

    Dan Bracuk Community Member
    Dan Bracuk Jun 1, 2011 12:56 PM

      My short term objective is to open a .xlsx file with ColdFusion, close it, and then open it with excel.  The long term goal is to edit individual cells which cfspreadsheet does not support, so I am using apache poi.

       

      Every reference I looked at said that the way to close a file is to create a FileOutputStream object and use it's close method.  Sounds simple, but,..

       

      This gets us started.

      TheFileCF = ExpandPath( "./dan.xlsx" );

      TheFileJava = CreateObject("java","java.io.File").Init(TheFileCF);

      This will open the file:

      TheWorkbook = CreateObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook").Init(CreateObject("java ","java.io.FileInputStream").Init(TheFileJava));

      This will crash, saying that TheWorkbook does not have a close method.

      TheWorkbook.Close(CreateObject("java","java.io.FileOutputStream").init(TheFileJava));

       

      This will run successfully.

      TheOutputStream= CreateObject("java","java.io.FileOutputStream").init(TheFileJava);

      TheOutputStream.Close();

      However, once you run it, when you try to open the file in Excel, you can't.  The error dialogue says that the format doesn't match the extension.  Also, further attempts to run the webpage will throw this error.  "An exception occurred while instantiating a Java object. The class must not be an interface or an abstract class. Error"

       

      That will continue until you comment out some code and do this:

      x=TheFileJava.delete();

       

      My trials and errors tell me that this is the command that affects the file.

      TheOutputStream= CreateObject("java","java.io.FileOutputStream").init(TheFileJava);

       

      So how do I close the file?

      • 2496 Views
      • Tags:
        • 1. Re: How To Close an Excel File?
          -==cfSearching==- Community Member
          -==cfSearching==- Jun 1, 2011 2:02 PM (in response to Dan Bracuk)
          This will crash, saying that TheWorkbook does not have a close method.

          TheWorkbook.Close(CreateObject("java","java.io.FileOutputStream").init (TheFileJava));

          Right, because the code is calling XSSFWorkbook.close() instead of FileOutputStream.close(). Use workbook.write() to copy the data into your outputstream first, then close() it.

           

           

                 TheOutputStream= CreateObject("java","java.io.FileOutputStream").init(TheFileJava);

                 TheWorkbook.write(TheOutputStream);

                 TheOutputStream.close();

           

          TheWorkbook = CreateObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook").Ini t(CreateObject("java","java.io.FileInputStream").Init(TheFileJava));

          Though it works, you should not need a FileInputStream. Give it a try with just the file path. The same goes for FileOutputStream.init()

           

          Message was edited by: -==cfSearching==-

          • 2. Re: How To Close an Excel File?
            Dan Bracuk Community Member
            Dan Bracuk Jun 1, 2011 3:58 PM (in response to -==cfSearching==-)

            Thank you for the reply.

             

            When I ran this:

             

            TheFileCF = ExpandPath( "./dan2.xlsx" );

            TheFileJava = CreateObject("java","java.io.File").Init(TheFileCF);

            TheWorkbook = CreateObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook"
            ).Init(
            CreateObject("java","java.io.FileInputStream").Init(TheFileJava));

            TheOutputStream= CreateObject("java","java.io.FileOutputStream").init(TheFileJava);

            TheWorkBook.Write(TheOutputStream);
            TheOutputStream.Close();

            The page ran without error.  I was able to open the file in Excel afterwards but could not save changes.  The dialogue said that the file was still in use.

             

            I'll keep slogging away.

            • 3. Re: How To Close an Excel File?
              Adam Cameron. Community Member
              Adam Cameron. Jun 1, 2011 4:03 PM (in response to Dan Bracuk)

              Is it because you've opened a FileInputStream but not closed it?

               

              (just a guess).

               

              --

              Adam

              • 4. Re: How To Close an Excel File?
                -==cfSearching==- Community Member
                -==cfSearching==- Jun 1, 2011 6:46 PM (in response to Dan Bracuk)

                I ran the code under CF9 and the resulting file was not locked. You might try a different file/name just to make sure the lock was not left over from a previous failed attempt. That has happened to me before.

                 

                You should not need to close the FileInputStream, but doing so should not hurt either. So you may as well give that a try too.

                 

                -Leigh

                • 5. Re: How To Close an Excel File?
                  Dan Bracuk Community Member
                  Dan Bracuk Jun 1, 2011 7:11 PM (in response to -==cfSearching==-)

                  First, I should have mentioned it earlier, but my quest for info led me to cfsearching's blog.  Also, I have finally finished the easy part with this code.

                   

                  TheFileCF = ExpandPath( "./dan.xlsx" );

                  TheFileJava = CreateObject("java","java.io.File").Init(TheFileCF);
                  TheInputStream = CreateObject("java","java.io.FileInputStream").init(TheFileJava);

                  TheWorkbook = CreateObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook").Init(TheInputStream);
                  TheInputStream.Close();
                  TheOutputStream= CreateObject("java","java.io.FileOutputStream").init(TheFileJava);

                   

                  TheWorkBook.Write(TheOutputStream);
                  TheOutputStream.close();

                  All answers were helpful.  All seemed to be correct, to the extent that I understand what was happening.  It seemed that once I coded myself into trouble, a 20 minute rest would time out the session and release the files.

                   

                  Thank you Adam and cfsearching.  I take back half the bad things I have said about each of you.

                  • 6. Re: How To Close an Excel File?
                    -==cfSearching==- Community Member
                    -==cfSearching==- Jun 1, 2011 7:50 PM (in response to Dan Bracuk)
                    TheInputStream.Close();

                     

                    Odd. I ran your code several times and it works perfectly without closing the inputstream.  In fact I almost never do that for inputstreams. Even POI's examples do not use it. Output streams are another story. But like I said, using it certainly will not hurt.

                     

                    Thank you Adam and cfsearching.  I take back half the bad

                    things I have said about each of you.

                     

                    As long as we are not under any similar obligations ;-)

                     

                    Message was edited by: -==cfSearching==-

                    • 7. Re: How To Close an Excel File?
                      Adam Cameron. Community Member
                      Adam Cameron. Jun 1, 2011 11:04 PM (in response to Dan Bracuk)

                      Thank you Adam and cfsearching.  I take back half the bad things I have said about each of you.

                       

                      Most of them are probably true though ;-)

                       

                      --

                      Adam

                      • 8. Re: How To Close an Excel File?
                        -==cfSearching==- Community Member
                        -==cfSearching==- Jun 2, 2011 9:53 AM (in response to Adam Cameron.)

                        Thank you Adam and cfsearching.  I take back half the

                        bad things I have said about each of you.

                         

                        Most of them are probably true though

                         

                        .. maybe half of them

                        • 9. Re: How To Close an Excel File?
                          Adam Cameron. Community Member
                          Adam Cameron. Jun 2, 2011 10:35 AM (in response to -==cfSearching==-)

                          The half that are about you...

                          • 10. Re: How To Close an Excel File?
                            -==cfSearching==- Community Member
                            -==cfSearching==- Jun 2, 2011 2:59 PM (in response to Adam Cameron.)

                            The half that are about you...

                             

                            You read my mind .. or my last post