An asp.net price quoting app patterened after excel spreadsheet

[NuJoizey]

My company has a rather complex one page spreadsheet that they use for price quoting. The sheet is about 15 columns wide and 90 rows long, and it takes roughly 100 inputs to do its calculation. 100 may seem like a lot, but that is just the nature of the business. It calculates an annual price (say $250,000) and a corresponding rate (say $0.53 per) , while showing the structure of the entire quote to the user so he/she can keep tabs on how it's shaping up as they do the data entry (a desireable trait)

The issue is that all these quotes are saved in singular sheets scattered in various places within a file system, and so there is no easy way to extract the information in them for analysis. Therefore the directive is to develop functionality that handles the quoting and saves all of the quote data in a SQL server database for later reuse, reporting or other analysis. This would all be done presumably within some aspx pages to be incorporated into an existing web app.

Excel seems to handle the quote calculations and presentation quite well. To me it seems to be performing a task for which a spreadsheet was intended, but maybe I am having trouble thinking outside the excel box - because right now I don't see a better way.

What would be your approach to this problem? Do I redesign an entire UI to try and replicate what excel is already doing?

That seems impractical, I can't see how asp pages accommodating 100 texboxes for data entry would be any more usable than this excel spreadsheet.

Is there some relatviely elegant way to incorporate a re-usable version of the excel spreadsheet as part of my web app such that the data can be extracted from it and dumped into my database? I don't want it to look like a hack. Can anyone give me some research clues, approaches or working examples of something similar?

 

[ca8msm]

If the interface for inputting the data is what they want, why change it? You may be better off looking to create an application that collects and collates the data, loads it into sql server and then setting up reports based on the summarised data.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[NuJoizey]

right, that's kind of what i've been thinking. So how then would you recommend I go about doing the task of collecting and collating that data? and also having one central re-usable form to collect it from? I guess i could just have a link in my app to this specially outfitted excel spreadsheet...

 

[jmeckley]

there are 3rd party controls to make an html table like excel. teltrek and component one have them, i think. to mark's point, if it ain't broke don't fix it.

if you where to design a system for aggreating the quotes, the i would look at this this way. create default templates/scenarios where a majority of the fields are preset. give the user the option to override and plug in the remaining variables. then do the calculation.

I would also advise against "excel on the web" format, it's not what the web was meant for. and really 15 by 90 is difficult to maintain (think of the input error issues alone). I would break this out into steps and use a wizard driven "calculator" that logically parses the data into sections.

the other benefit to separating the columns into steps is you can create custom steps determined by the "type" of quote, if that applies.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ca8msm]

I'm not sure you even need to build a system to collect and collate. You could just go down the SQL Server Integration Services (SSIS) route and create a package that does the full task for you.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[NuJoizey]

i've been looking at possible third party web controls. I downloaded telerik RadControls, but I didn't see anything that gave excel-like functions. I'll look again though.

The thing about designing a wizard, is just that what I seem end up doing when I sketch it out is just breaking up a single cohesive spreadsheet of 100 cells into smaller spreadsheets of 20-30 each. Any way you cut it, it just seems I would still need some sort of gridlike entry mechanism for this to make sense.

So okay, an embedded excel-as-a-webform is not what you recommend. That leaves me with either a wizard/3rd party spreadsheet control option, or the SSIS route that Mark proposes.

But I don't really understand how I would do this via SSIS, Mark, would you mind expounding a bit upon this approach?

 

[jmeckley]

your still thinking in terms of a spreadsheet with rows and columns. think in terms of work flow.

does every row use every column?
yes, then the spreadsheet makes sense.
no, you have different logic for different rows. these can be considered different work flows, which should each have there own flow in the system.

not knowing more about the spreadsheet maybe I'm way off base. I'm having a difficult time understanding how 1500 values need to be accessible and editable simultaneously.

another option to SSIS is Rhino ETL which stands for extract, transform, load. this is a code based alternative to SSIS and DTS packages. the benefits are modularity and testability. the cost is the learning curve and working with OSS.

you will need some knowledge of the following tools to get rhino ETL (the entire rhino stack for that matter) up and running
1. svn (and tortoise svn for ease of use)
2. nant
3. nunit or mbunit
4. .net 3.5
5. sql server express

if this is all new to you, then rhino ETL may not be the way to go right now as the learning curve to learn rhino is steep

I have worked with other frameworks in the rhino stack but not ETL specifically. there is a rhino-tools forum on google groups which is very active for more help.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[NuJoizey]

guys, take a look at the actual document at:

http://www.jerseytransit.org/Docs/Page.htm

pretty much every row uses every column.

The blue cells are the ones that require human input. all of the rest calculate.

What would you do to turn this into an app?

 

[jmeckley]

Excel seems to handle the quote calculations and presentation quite well. To me it seems to be performing a task for which a spreadsheet was intended, but maybe I am having trouble thinking outside the excel box - because right now I don't see a better way.

What would be your approach to this problem? Do I redesign an entire UI to try and replicate what excel is already doing?

seeing the spreadsheet, i agree. it is presented very well. It did require me poking around the formulas to understand what was happening. this may be due to the spreadsheet being
scrubbed for public viewing.

so what do you see a new system accomplishing that the current excel method doesn't do? storing the values in a centralized location?

in that case I would design an application which spits out the excel template. after the sheet is updated, have user upload the excel spreadsheet back into the central system. parse the spreadsheet, extract values and save back into the database.


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[NuJoizey]

so what do you see a new system accomplishing that the current excel method doesn't do? storing the values in a centralized location?

yes, that's really about it. The big thing is to be able to extract the entered values, store who entered them and when, for what client in what industry. Many of these quotes are generated per week. The idea is then that we will then look at these values over time and thus be able to do stuff like identify trends in rates, or prices, compare quotes by different people, or pull an average of all rates quoted for a particular industry in 2008, etc etc etc....

in that case I would design an application which spits out the excel template.

so basically, you're saying use excel as a webform, right? -and of course it needs to be re-usable....That leads me to the question of how best to present the initial excel template to the user using asp.net. Is there some native functionality of excel I can leverage? Or do I go with a third party control that mimics an excel like UI, such as maybe:

http://www.infragistics.com/dotnet/netadvantage/aspnet/webcalcmanager.aspx#Overview

redesigning a whole new UI to capture input seems like perhaps not the best option.

 

[jmeckley]

no, use the .net framework to create an excel workbook on the fly. then have the user download the workbook to there local machine. fill out the spreadsheet. upload spreadsheet.

the web UI just acts as a middle tier to download and upload excel spreadsheets. it could also be used to manage the default values populated into the spreadsheet.

if that's the case you could create a windows service and have the user's dump the excel worksheets into a specific folder. have filesystemwatcher pickup the files and import them, negating the webpage GUI altogether. now the windows service will act as a middle tier.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ca8msm]

But I don't really understand how I would do this via SSIS, Mark, would you mind expounding a bit upon this approach?

SSIS allows you to create a workflow of various steps. You can write .NET code and interact directly with SQL Server as well as setup any ETL processes you need.

As a simplified version (and you may want to change some of these) you could:

1. Have your users fill in their worksheets and save them somewhere
2. Have your SSIS package pick up the files, read them, work out what data belongs where and then insert the data into a staging table (this is the ETL part)
3. Process the staging table to write out to your reporting database
4. Use Reporting Services or any other report tool to produce your management based reports directly from the report database

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers