How To Close an Excel File?

Question

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__- · Answer

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded