Working with Excel... and badly formatted excel sheets

[Chad Stinner]

Greetings all!

I'd love to pick your brain on something. I've got an example excel sheet that has multiple tabs, and the first tab has a blank column and duplicate column names. ColdFusion SpreadsheetRead() function wigs out on this and pegs the server processor.

I've found no way to identify issues with a spreadsheet prior to upload other than file size. Does anyone have experience with this kind of issue and what they did to resolve it? I'm not against using third party solutions here.

The perfect world scenario here would be to identify potential issues with formatting prior to coldfusion trying to read in the entire thing. So far I'm limited to filesize. 

For example:

- Does this excel sheet have the columns in this list?

- Does this excel sheet have more than 1 tab

The problem is... I can't currently get that information with SpreadsheetRead() which breaks right now.

--

Chad Stinner
(919) 530-0054

[Jim Priest]

CF version?  Are you currently all patched up?  You could try just using Apache POI directly - I'm sure that's probably what Adobe is using under the hood.

https://poi.apache.org/

Jim

--
Upcoming events: http://www.meetup.com/Triangle-Area-ColdFusion-Users-Group/
Follow us on Twitter: @tacfug
---
You received this message because you are subscribed to the Google Groups "TACFUG" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tacfug+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Denard Springle]

Ah, nvm, that only works with already read spreadsheet objects not paths. :/

[Chad Stinner]

Adobe is definitely patched up. The issue is that the spreadsheets that are uploaded are often "edited" where the person who's doing it "hides" tabs, columns, rows etc.. My goal is to identify when they do that and issue a reply "Fix this".

Unfortunately the info doesn't tell you how many tabs. I had hope when I saw the "Sheets" column, but no luck.

--

Upcoming events: http://www.meetup.com/Triangle-Area-ColdFusion-Users-Group/
Follow us on Twitter: @tacfug
---
You received this message because you are subscribed to the Google Groups "TACFUG" group.

To unsubscribe from this group and stop receiving emails from it, send an email to tacfug+un...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Gerry Gurevich]

What about VBA triggered by the onSave event

[Chad Stinner]

I'm not sure what you mean?

[Gerry Gurevich]

Visual Basic for Applications. You can add a module to your spreadsheets (add it in via the default template if that's an option) and do all your checking directly in the spreadsheet instead of relying on ColdFusion.

I confess that I didn't  read all the details of your initial request, but if you have initial control over the spreadsheets, then you can make the user fix stuff before uploading by writing code in the application directly.  Obviously not a solution for every scenario.

[Chad Stinner]

ah, see I can't even read the spreadsheet due to some bug with SpreadsheetRead(). SpreadsheetInfo() returns some basic information but nothing I need to validate against. 

[Dan Pendergrass]

If it were me, I'd upload the file to a server and validate the file contents using cs or vb .net code via an HTTP request and return a JSON struct with status codes and validation messages. That way if the data uploaded is invalid, you can notify your end user of the issue (output the validation message passed back from .net), or continue the data parse with coldfusion.

No, I don't know how to write the cs/vb code to do that, nor do I know if it will run into similar issues as you've experienced, but I have a feeling that a Microsoft-code-based server-side validation is probably more feasible on an Excel document.

Regards,

Dan Pendergrass Solutions Engineer VAIRKKO p: 888-290-0671  m: 919-278-7645 w: www.vairkko.com  e: d...@vairkko.com

---

Confidentiality Notice: This e-mail communication and any attachments is considered to be confidential and privileged information for the use of the designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify VAIRKKO Technologies, LLC immediately by replying to this message and deleting it from your computer.

[Chad Stinner]

Thanks Dan! We may have found a solution using Google Sheets Import. It seems to import fine so we're exploring their api. My biggest concern would be maintaining that import cs/vb/.net codebase. If the Google Sheets Import works out, I'll let everyone know.

Thanks again for all of your help!

Chad.