Spreadsheet, CSV files, Excel formulas, with a web interface
Budget: $750 – $1,500 USD
Key Skills: PHP, MySQL, importing CSV files, strong Excel knowledge and using PHP/MySQL to replicate some Excel calculations.
*** Brief project description: ***
- I have a spreadsheet involving 10 worksheets (simple inputs only, just numbers and some Yes/No dropdowns), and 1 'Calculation' worksheet that makes all the calculations based on those worksheets.
- After exporting my spreadsheet to .csv files, there would be a PHP file that can import those .csv files and make the same calculations as the calculation worksheet.
- There would also be a web interface that replicates the inputs of the 10 worksheets.
- The key thing is that once we import the .csv files and all the formulas, the web interface allows us to create numerous users/profiles and each of those users
has the ability to customize their own inputs (simulating the 10 worksheets), and is given their own custom result of the calculations.
The calculations are made the same way as the master 'Calculation' worksheet, but customized to each individual user based on their custom inputs.
- Below I will give a description of the project that is in broad terms.
I have a more detailed project plan and mockups of what the web interface would look like.
*** Here's how it would work: ***
MySQL Tables 1 & 2
files: import_calculations.php, calculations_no_formulas.csv, calculations_with_formulas.csv
When we execute import_cc_calculations.php, three tables are populated using the info from the .csv files.
Table 1: 'products' - 3 columns
For many of the rows in this table we insert Yes or No based on whether or not there is an X or a number in the .csv file
Table 2: 'calculations_import' - 3 columns
Into this table we import our actual calculations from the Calculations worksheet, along with the associated cell values.
Table 3: 'calculations_made' - 6 columns
Into this table we import .csv files that represent our 10 input worksheets.
The values get imported to user: default and profile: default
We import the values into columns that look like this:
User, Profile, Worksheet, Variable_Name, Cell, Variable_Value
- In this table we also need to create the Calculations Worksheet for...
user: default
profile: default
To do this we need to use the 'calculations_import' table, and we need to actually make the calculations based on what is shown in the Variable_Value
*** Creating a new user or profile ***
- When we create a new user or profile, we would copy the 'default / default' user profile from 'calculations_made'.
This starts the user off with our defaults, and they can change the values of the 10 worksheets using the interface.
For example, let's say we create a user named: Bob , with the profile name of: Whatever
We would copy all of our values where User = default and Profile = default from 'calculations_made'
to User = Bob and Profile = Whatever
*** Using the web interface ***
- Now let's say User Bob is using the web interface, with his profile named Whatever
Remember, the web interface is designed to simulate the 10 input worksheets of the master spreadsheet.
We associate the input fields of the web interface with the appropriate fields in the 'calculations_made' table.
where user = Bob
and profile name = Whatever
If he updates the value, it gets automatically saved to the associated Variable_Value field for his user/profile.
If he clicks on Results, we use the values from 'calculations_import' and 'calculations_made' to make our calculations for Worksheet = Calculations for this user / profile.
CONTINUED - Please see the attached file for the rest of the project description
*** Brief project description: ***
- I have a spreadsheet involving 10 worksheets (simple inputs only, just numbers and some Yes/No dropdowns), and 1 'Calculation' worksheet that makes all the calculations based on those worksheets.
- After exporting my spreadsheet to .csv files, there would be a PHP file that can import those .csv files and make the same calculations as the calculation worksheet.
- There would also be a web interface that replicates the inputs of the 10 worksheets.
- The key thing is that once we import the .csv files and all the formulas, the web interface allows us to create numerous users/profiles and each of those users
has the ability to customize their own inputs (simulating the 10 worksheets), and is given their own custom result of the calculations.
The calculations are made the same way as the master 'Calculation' worksheet, but customized to each individual user based on their custom inputs.
- Below I will give a description of the project that is in broad terms.
I have a more detailed project plan and mockups of what the web interface would look like.
*** Here's how it would work: ***
MySQL Tables 1 & 2
files: import_calculations.php, calculations_no_formulas.csv, calculations_with_formulas.csv
When we execute import_cc_calculations.php, three tables are populated using the info from the .csv files.
Table 1: 'products' - 3 columns
For many of the rows in this table we insert Yes or No based on whether or not there is an X or a number in the .csv file
Table 2: 'calculations_import' - 3 columns
Into this table we import our actual calculations from the Calculations worksheet, along with the associated cell values.
Table 3: 'calculations_made' - 6 columns
Into this table we import .csv files that represent our 10 input worksheets.
The values get imported to user: default and profile: default
We import the values into columns that look like this:
User, Profile, Worksheet, Variable_Name, Cell, Variable_Value
- In this table we also need to create the Calculations Worksheet for...
user: default
profile: default
To do this we need to use the 'calculations_import' table, and we need to actually make the calculations based on what is shown in the Variable_Value
*** Creating a new user or profile ***
- When we create a new user or profile, we would copy the 'default / default' user profile from 'calculations_made'.
This starts the user off with our defaults, and they can change the values of the 10 worksheets using the interface.
For example, let's say we create a user named: Bob , with the profile name of: Whatever
We would copy all of our values where User = default and Profile = default from 'calculations_made'
to User = Bob and Profile = Whatever
*** Using the web interface ***
- Now let's say User Bob is using the web interface, with his profile named Whatever
Remember, the web interface is designed to simulate the 10 input worksheets of the master spreadsheet.
We associate the input fields of the web interface with the appropriate fields in the 'calculations_made' table.
where user = Bob
and profile name = Whatever
If he updates the value, it gets automatically saved to the associated Variable_Value field for his user/profile.
If he clicks on Results, we use the values from 'calculations_import' and 'calculations_made' to make our calculations for Worksheet = Calculations for this user / profile.
CONTINUED - Please see the attached file for the rest of the project description