My goal here is being able to identify if a file is correctly formatted or not for reading in cfspreadsheet. Technically speaking "Does this file have these X columns? (yes/no)". I don't care how this is done... but I have no other means to do it than subject it to cfspreadsheet action="read".
One of the biggest challenges when dealing with imports is getting clients to use "clean" files. I've run into several situations where they will "hide, merge, etc" columns and entire tabs. Recently a client attempted to update 100 records where a tab had been "hidden". There wasn't a lot of data and due to privacy I can't share that file, however I can confirm that something about this excel sheet would trigger incredibly high cpu usage until such point that ColdFusion would crash.
adding query="" was the trigger to cause coldfusion to use high cpu and then crash so something about trying to build it's struct was the cause.
the information provided by name did now show all the sheets in the xlsx document. Just the first visible tab
using columnnames did not work
The xlsx file was less than 200kb
The xlsx file had 2 tabs, 1 was initially hidden.
The first tab had 2+ columns that had duplicate column names
The first tab had equations in rows
After removing ALL formatting from all tabs in the file, it would still crash the server
The only way we were able to get it to import was to copy/paste the data from tab 2 into a new xlsx document. Then it worked fine.
Any suggestions on how to read if a xlsx document is formatted correctly would be great,