Working with Excel... and badly formatted excel sheets

3 views
Skip to first unread message

Chad Stinner

unread,
Sep 10, 2018, 6:39:40 PM9/10/18
to tac...@googlegroups.com
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

unread,
Sep 10, 2018, 7:41:25 PM9/10/18
to tac...@googlegroups.com
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.


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.

Message has been deleted

Denard Springle

unread,
Sep 10, 2018, 10:49:17 PM9/10/18
to TACFUG
Ah, nvm, that only works with already read spreadsheet objects not paths. :/

Chad Stinner

unread,
Sep 11, 2018, 8:51:19 AM9/11/18
to tac...@googlegroups.com
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

unread,
Sep 11, 2018, 10:12:43 AM9/11/18
to tac...@googlegroups.com
What about VBA triggered by the onSave event

Chad Stinner

unread,
Sep 11, 2018, 10:15:51 AM9/11/18
to tac...@googlegroups.com
I'm not sure what you mean?

Gerry Gurevich

unread,
Sep 11, 2018, 10:20:04 AM9/11/18
to tac...@googlegroups.com
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

unread,
Sep 11, 2018, 10:21:17 AM9/11/18
to tac...@googlegroups.com
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

unread,
Sep 11, 2018, 10:49:43 AM9/11/18
to tac...@googlegroups.com
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

unread,
Sep 11, 2018, 11:54:01 AM9/11/18
to tac...@googlegroups.com
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.
Reply all
Reply to author
Forward
0 new messages