• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1483
  • Last Modified:

How to Build an Excel-like ColdFusion Web App

Hello,

I'm guessing if this is possible, someone's already explained the how-to's in another question's solution or on another website.  If anyone knows where I can find my solution, a link would be greatly appreciated!

So...  here's the problem:

For the sake of batch data entry into one of our coporate databases, I've built an Excel spreadsheet that uses VBA macros to allow the user to customize the layout of the spreadsheet, validate their input, and upload their validated information as a CSV file to an ftp site.  (From here the data is pulled into our database through a series of automated processes.)  This functions perfectly, however, it is huge and more importantly, it's very very very slow, especially the validation process!  Validating entries in 150000+ cells is not a pretty sight!

So, my desired solution is to build an Excel-like application on the web to take the place of the forementioned Excel spreadsheet.  I'd like to use ColdFusion and JavaScript to build the online, Excel-like layout, validate input, and create the CSV file to send to the ftp site.  One major requirement of this is that users must be able to copy data from an Excel spreadsheet and paste it into the online spreadsheet.

So, am I just wishful thinking, or could this be possible?  Anyone see instructions on how to do this on this or any other site?

Thanks!
0
vadnick
Asked:
vadnick
  • 5
  • 4
1 Solution
 
cyberdevil67Commented:
Hi vadnick,


 you know you can publish an exel spreadsheet using cfcontent to the browser?

Cheers!
0
 
mrichmonCommented:
I do not think this would be better.  I bet it would  be worse - by a lot - in terms of speed.  You are much better off with the process you have.

There are many disadvantages to using a web substitute especially if the web substitute is just going to create a csv to FTP.

Also you will NOT be able to easily copy and paste from an excel spreadsheet to your online app.
0
 
vadnickAuthor Commented:
At the moment, the data validation in the Excel spreadsheet can easily take over an hour, during which time, the user's CPU is being almost completely monopolized.  mrichmon, you may be right about the web being slower.  Maybe under the circumstances, though, it may still be worth a try to see what happens.  I've never encountered as slow processing speed with JavaScript or ColdFusion as I have with Excel's VBA.  (In fact, the slowness of VBA has sort-of given me the impression that VBA was never really meant to be pushed to the limits that I'm pushing it to.  But it's very possible that I'm just not programming very effeciently...)

Also, forget what I said about the online spreadsheet creating a csv file and sending it to an FTP site.  That wouldn't make sense.  The online version would instead dump the validated data directly into an Oracle database.

What about this:  What if I used cfcontent or cffile to pull the information from an Excel spreadsheet to the web, as cyberdevil67 suggests?  I could pull the data into tightly-knit textboxes, to give the visual illusion of a spreadsheet, while still allowing the user to edit "cell" contents as necessary.  Then request the user to label each of the columns, so I know what they're trying to enter.  (This way they can sumbit data from a spreadsheet formatted in any way.)  Based on the column headings, I can validate the data in each column to make sure that it is in the correct format/matches valid entry options/etc.  Finally, I could insert the data into the Oracle database.  Also, as an added bonus, before inserting the data, I could check for duplicate records, which is something I can't do currently in Excel.  

Does this undertaking sound worthwhile, or would anyone/everyone out there recommend that I stick with the sluggish Excel spreadsheet?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
mrichmonCommented:
I don't mean to insult you at all but, without seeing the exact validation you are doing, I would tend to agree with this statement you made: "it's very possible that I'm just not programming very effeciently..."

However, I would agree with you that VBA can be clunky and cumbersome to code in.

Also it is possible to check for duplicate records in Excel.

We had some people in our accounting department that every fiscal quarter or so took a dump of accounts from a customized program and a dump of accounts from an Oracle database into Excel.  The people in accounting then took about 2 weeks to manaully go through the spreadsheets doing validation and comparisons of the data.  One of teh things they were looking for was duplicate records.

I had a student working for my department about 2 years ago.  We asked him to write a Macro in Excel using VBA to automate most of this process.  He had never written Excel Macros before nor in VBA.

In about a month he had completed a Macro that reduced the workload for the accounting people to a few hours from a few weeks.  The Macro took about 20 min to a half hour to run I think.  The macro color coded spreadsheets and created new ones - including a spreadsheet that only contained duplicate records.

Now this was an awesome student, but still, it shows that it is possible.
0
 
mrichmonCommented:
A few things you would have to watch out for is

1) You would need to get the value not the formula of the cell when exporting to web.
2) Watch out for screnn space "real-estate".  There are a lot of special scrolling/timesaving features in Excel for mass data entry that do not exist on the web.
3) It will look different per browser - hopefully you can standardize on one
4) You would want some login method fro the web to ensure only the correct people are entering data, and have to worry about timeouts
5) You would need to build in a save mechanism for partial data.

But these may be worth looking into if you still want to go forward:
http://www.ozgrid.com/Services/Excel-HTML-Java.htm
http://www.blueshoes.org/en/javascript/spreadsheet/
http://www.activewidgets.com/


0
 
vadnickAuthor Commented:
Thanks for your advice.  No offense taken on tentatively confirming my comment on not programming efficiently in Excel.  I know there can be at least a few improvements, because I really had to fly through designing that spreadsheet and didn't have time to consider different ways to optimize the code.  Until I had this "great" idea to put the spreadsheet on the web, my plan was to redesign the Excel spreadsheet as soon as I had the time.  I just figured that even redesigned, Excel wouldn't work as fast as the web, but it's good to know that this is probably not true.  

Perhaps a bit more description on what I'm designing would be helpful:  Our company needed a means of allowing an average computer user to batch enter data into our database of customers.  Our company deals with about 10 different types of customers.  Customer Type A has different attributes than customer Type B than customer Type C, etc.  The Excel workbook I put together uses multiple sheets to allow the user to select what type of customer they want to enter information for, and then what information about that customer they want to enter.  Each sheet contains the attributes for the different customer types.  Once the user selects which type of customer they're entering information for and what attributes they want to enter about that customer, the VBA code populates the column headings of a blank sheet in the workbook with the selected attribute headings.  This is so that the user only has to deal with columns that pertain to the information they have to enter.  This is also because we could not simply list every attribute of every customer type because Excel does not have enough available columns.  Once this columns headings of this sheet are populated, the user can enter data one row at a time, or more commonly, they can copy the data from another spreadsheet.  The nice thing about populating cells one-at-a-time is that the sheet is coded so that cells in attribute columns that have certain valid values contain drop-down menus, allowing only those values.  Once all the data is submitted, the user clicks a sumbit button, which begins a validation process.  The validation process checks columns where only certain values are valid to make sure that only those values are present.  It checks to make sure the correct format is used where a number or date is required.  It checks to make sure required fields are completed.  In all cases, when a failure occurs, the code prompts the user to select or enter a valid value, and then it moves on.  When this process is complete, the code reformats all of the inputted data into another sheet which is saved as a csv file and uploaded to an ftp site, all behind-the-scenes.  

So, it's quite a process, and really bogs down Excel.  Adding a check for duplicates may be possible, but most users probably wouldn't want to invest an addition 30 minutes or more to the CPU time.  

You've been very helpful, mrichmon!  I'm going to go ahead an award the points to you.  Feel free to send any more advice/thoughts you might have.  Particularly, you wouldn't happen to know what software (if not Excel) other companies use for batch data entry, would you?  Even our IBM consultants seem to be stumped on that one.  It just seems unbelievable that this cumbersome manipulation of Excel is the only way its done (aside from hiring a team of data entry clerks).

Thanks!!
0
 
mrichmonCommented:
Well I don't think the duplicate search would add 30 min - that was the time of the whole entire process which did much more than seach for duplicates.

So if they are batch entering customer data I assume it is already pre-sorted by customer type?

Is it manually entered?  If so then a standard web form might work rather than an excel clone in web.

If not then you may be able to have the raw data uploaded and validated - again without having to use an excel clone program.

In these cases the web may be more efficient....  I guess the more details you provide the more insight I can offer.
0
 
vadnickAuthor Commented:
Thanks for your continuing help.

We are working on a web form to use whenever possible when data is entered manually, one record at a time.  But, sometimes this can't be done or the source of the data won't do this for us.  For example, our company might participate in an industry show that collects data on its visitors through surveys.  The vendor running the survey might choose not to submit their results through our web form, because they'd have to do the same for every participating company.  So instead, they just submit a spreadsheet of data, which we currently would copy-and-paste into the Excel spreadsheet that I've designed.

To answer your first question, most of the time the data is presorted by customer type.  But there are occasions when the data is mixed.  Here's a little more detail on the setup:  Our data is managed with SAP.  Basically, there are two types of records - organizations and individuals.  For each record, there's a standard set of attributes, including first name, last name, address, phone, etc.  Then to each record you can attach additional attribute sets.  The attribute sets generally contain 20 or more attributes.  There's always one attribute set per customer type, so, for example, we have an attribute set for distributors and an attribute set for retailers.  It'd be highly unusual, but not impossible that an individual or organization might have attributes of both distributors and retailers.  

So anyway, going back to my example of the industry show, the list of data we receive from the show could contain a list of 2000 attending retailers, their contact information, including what company they're from, and some specific retailer information, like how many stores they own and what's their gross annual sales.  If we received this list, we'd use my Excel spreadsheet, and set it up to show the basic contact data columns and the columns from the retail attribute set for number of stores owned and gross annual sales.  Then, we'd copy and paste the data from the trade show spreadsheet into my spreadsheet.  Finally, my spreadsheet would validate and upload the data.

Does this help, or do you have any other questions?  Thanks!
0
 
mrichmonCommented:
Well if it can be sorted by customer type for the normal customers (I think you would have to manually handle any that crossed types if that is so unusual), then you could devise a cold fusion page that could take the raw data and maybe have the user indicate the customer type.  Then a simple upload into CF and CF could do all the validation.

I don't know if this would be more effective than what you are planning.
0
 
vadnickAuthor Commented:
That's a good suggestion too.  I'll probably try experimenting with a couple different methods to see if anything works better or has greater benefit to our needs.  Thanks again for all your help!  
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now