Mimicking Excel

Mimicking Excel

Old forum URL: forums.lhotka.net/forums/t/5912.aspx


TheSquirrelKing posted on Monday, December 01, 2008

Hello everyone,

So I've got this new project that I'm working on and I'm having some issues with its design. Basically we're converting our users from having an unholy number of inter-linked Excel spreadsheets to having one single database. In order for our users to still be able to work with their data, we're creating an application to allow them to view and work with the "cells" in a similar manner as they were able to with Excel.

As such, we've determined that Cell will be our main BO, and it will have to contain some identifying properties, as well as a Value and a Formula. When we parse this formula to determine its value, it can potentially rely on the Value of other Cells. This has the result of turning our model into a directed acyclic graph of Cells (we will have to check for circularity when modifying Formulas). To represent this, every Cell will have to contain a collection of Dependents and a collection of Contributors. The Contributors collection will consist of the Cells that "this" Cell refers to in its Formula, while the Dependents are the Cells that refer to "this" Cell in their Formulas and need to be updated if the Value of "this" Cell changes.

I had intended to have a Cells table in my database along with an Assignment table to list the Dependent-Contributor relationships, but I'm not entirely sure about the choice of types for the business objects.

Cell: Switchable
        Contributors: Read-Only Collection
        Dependents: Child Collection

Anyone have any poignant insights they'd care to share? Something about the implementation just doesn't sit right with me, and I could really use some guidance from the great CSLA hive mind.

Many thanks,
TSK

dlambert replied on Monday, December 01, 2008

I think it's likely that you'll still end up with something "unholy", though for completely different reasons.  Re-implementing Excel seems unfathomably daunting, and I'm not entirely certain how you'd be in a better place with respect to managing this data if you do get this up and running.

Is there any reason you couldn't keep Excel as a front end (or even use something like http://www.spreadsheetgear.com), and supply the data via business objects that you write in CSLA?

Sorry for the pessimism - if I'm missing something here, please disregard.

Jack replied on Monday, December 01, 2008

I would agree - if your looking for something a little more excel like as a frontend the Infragistics grid integrates with a calcManager control to provide a lot of that type of functionality.  But re-inventing Excel sounds like a horrible nightmare.

 

 

TheSquirrelKing replied on Tuesday, December 02, 2008

Don't apologise for the pessimism, you're not the only one feeling it. That said, we're not exactly re-inventing Excel. The aim of the project is to consolidate hundreds of cross-linked Excel files into one single database (like a data warehouse, but more dynamic and immediately functional). We'll still be using Excel (via the interop) to evaluate the Formulas for each Cell, though obviously we will now need to resolve the Value of each Cell involved in that Formula before feeding it in.

Since there is an existing prototype app (it is pretty rough and not OO) that has qualified as proof-of-concept, I have little say in how to approach the problem, I'm just trying to determine the best way of implementing the business objects.

Right now I am thinking along the lines of:

Cell: Switchable Object
   Dependents: Read-Only List of child Cells
   Contributors: Read-Only List of CellContributors
      CellContributor: Read-Only (basically a read-only version of Cell)

I think I'm getting close to something that feels a bit better, but something still feels not-quite-right about this. I wish I could suggest taking an entirely different approach to the entire problem, or at least explain the situation in more depth (that could take a while...), but if anyone has any ideas along this course of action, I'm open to suggestions. Staring at this for so long has probably skewed my perspective rather severely...

Thanks again,
TSK

Copyright (c) Marimer LLC