8 - Creating a calculator from a spreadsheet file (PRO) - WPC
This module will help you in creating product’s price calculator by loading and using the calculation spreadsheet. The calculator uses the same formula described in the spreadsheet to calculate prices for products (with dynamic measurements).
The process contains two major steps.
- Creating WooCommerce Product
- Creating WooPrice Calculator (from spreadsheet)
Creating WooCommerce Product
First of all, you need to create your WooCommerce Product:
- Click on "Products > Add New" on the left Wordpress sidebar
- Insert the name of the Product and set the product mode as "Simple Product": The plugin will override the WooCommerce simple product features.
- Set a price in "Regular Price". Also putting "0" is fine. This is important otherwise, WPC can't attach its features to the product page.
- Save the product by clicking on "Publish"
After you've created the WooCommerce Product and after you've created your customer input/output fields (See How to create fields) you are now ready to create the calculator.
Creating WooPrice Calculator
To create the calculator, go in the "Calculator" tab and click on "Upload your worksheet":
- Find the calculation sheet using the "Browse" function, select it and click on "Upload"
- Choose the desired sheet from the list and click on "Next"
- From this screen(shown below) the cells in the sheet can be mapped to the ones in the calculator.
- Input Cell: the fields that will be used by the visitors to insert the data
- Output Cell: holds the formulas which will show the output values. You're required to map the Price field because it's the default field. If the output spreadsheet cell contains a numeric value, a formatted numeric value will be displayed to the visitor (by following the rules of the field). If the output spreadsheet cell contains a string value, a text will be displayed to the visitor.
- Base Price: WPC will get this value from your WooCommerce Product "Regular Price"
- Error Cell: This let you validate your input calculator data by using Excel formulas or check if your Excel spreadsheet contains errors. You can map more Error Cells for the same Input Field. If the cell is not empty an error will be shown in the product calculator. "IFERROR" and "ISERROR" are supported formulas to check if a cell has an Excel error (For example #DIV/0).
- Tax Rate: WPC will get this value from your WooCommerce Taxes (WooCommerce Settings > Tax). You need to use the Tax Rate cell as part of a formula inside another cell in your Excel file, example:
- A2 is a cell with a fixed value
- B2 is a cell with a starting fixed value. This value will be then changed by WPC once you mapped the field with "Tax Rate"
- C2 is a cell with a formula =A2+(A2*B2)/100. The formula used to calculate the final price VAT included.
- SKU: Starting from version 2.3.5 on WPC it is possible to export the SKU value of the landed product on the Excel spreadsheet. This let you create different logic on different products using only one excel sheet. The SKU is located in the WooCommerce Product Page:
You can change this value any time and this change will be reflected to the Excel spreadsheet logic too.
Once all the fields have been mapped, click on "Next".
- The required information for the calculator will be displayed:
- * Name: This value is only needed to remember what the calculator is for, and to find it in the list of calculators
- Description: A more detailed description of what the calculator does
- Input fields: Show or hide the input fields you want. To show move the item to right. To hide, move the item to left. You can drag&drop the fields on the right to change fields' order.
- Output fields: Show or hide the output fields you want. To show move to the right, to hide move the item to the left. You can drag&drop the fields on the right to change fields order.
- Overwrite Quantity: You can overwrite the default quantity field of WooCommerce: WooCommerce quantity field will be hide, instead your selected field will be used as quantity field. WooCommerce Inventory Managment is also supported.
- Overwrite WooCommerce Weight: You can overwrite the default weight of the WooCommerce product by setting an output field. In this way, the weight will be calculated by using the spreadsheet formula.
- Overwrite WooCommerce Length: You can overwrite the default length of the WooCommerce product by setting an output field. In this way, the length will be calculated by using the spreadsheet formula.
- Overwrite WooCommerce Width: You can overwrite the default width of the WooCommerce product by setting an output field. In this way, the width will be calculated by using the spreadsheet formula.
- Overwrite WooCommerce Height: You can overwrite the default height of the WooCommerce product by setting an output field. In this way, the height will be calculated by using the spreadsheet formula.
- Products: The products that the calculator must be linked to; you can assign the calculator to whole categories. (Note: Starting from WPC 2.3.6 you can select the calculator directly inside from your WooCommerce Product: Go to the product you want to assign the calculator and click on the "Calculator" tab; select the calculator from the list and click on "Attach calculator").
- Force to show price on errors: It try to show the price and output fields if there are some errors (ex: Required fields not filled in).
- Hide field errors on startup: On product page startup field errors are not shown.
- Redirect to checkout if "Add to cart" is clicked: The visitor will be redirected to the checkout after adding the product to the cart if “Yes”. The visitor will not be redirected and will continue shopping if “No”.
- Empty cart: If "Yes" it empties the cart when a product has been added to the cart. It's useful for services.
- Themes: The theme used to see the calculator
To modify the mapping of the calculator, click on "Edit Mapping" in the calculator list. To edit the other information click on "Edit".
Price format and price approximation are made by WooCommerce (See WooCommerce > Settings > General tab)
- WPC allows to upload Microsoft Excel, LibreOffice, OpenOffice spreadsheet files.