How To Close an Excel File?

[Dan_Bracuk]

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?

[-__cfSearching__-]

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==-

[Dan_Bracuk]

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.

[Adam Cameron.]

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

(just a guess).

--

Adam

[-__cfSearching__-]

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

[Dan_Bracuk]

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.

[-__cfSearching__-]

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==-

[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 😉

--

Adam

[-__cfSearching__-]

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

[Adam Cameron.]

The half that are about you...

[-__cfSearching__-]

The half that are about you...

You read my mind .. or my last post