Module Imports from Excel
Component part of the DOLYWM – ECOSYSTEM project, allows you to create rows for any document from an Excel spreadsheet
- of standard Dolibarr modules (Quotes, Orders, Invoices, etc.)
- of external (by third-party) modules that require the use of rows (Advanced Warehouse Management, Advanced Shipments, etc.) (*)
By creating specific references, which define which columns to use for individual pieces of information (Code, prices, etc.), you can use any Excel spreadsheet to create rows.
The module’s functions also allow you to:
- Import Extrafields
- Use a column (or an Extrafield) to identify the product (e.g., the Part Number)
- Insert complex logic to identify the product (e.g., if column X contains the value “Y,” take the code from the first N characters of the code from column Z; otherwise, …)
- Insert complex logic to import rows
Setup
The following single setting is possible:
Other contexts: Allows you to enable the use of module functions for third-party modules as well.
Enter the list of such contexts in the field, separated by “,”
Usage
Reasons management
The logic with which the various columns of the Excel spreadsheet are used to create rows can be defined in the reason codes.
For this purpose, a new item, Reasons for Importing from Excel, is available in the “Main Panel>>Settings” menu.
The following fields are available for each reason code:
Title: This is the title associated with the reason.
Exclude first row: This allows you to ignore the first row of the Excel spreadsheet, as this is the title row.
Product column: This identifies the column containing the product code.
Description column: This identifies the column containing the row description.
Unit Price column: This identifies the column containing the unit price.
VAT column: This identifies the column containing the VAT rate.
Discount column: This identifies the column containing the discount.
Taxation column: This identifies the column containing the tax rate.
Lot colum: identifies the column containing the lot code
Finally, you can create a list of fields to:
- Import any extrafields into the rows
- Define a column (or extrafield) to be used to identify the product
The information to be used for this purpose is:
Field Name: This is the name of the field, or extrafield, to be used
Column: This is the column in the Excel spreadsheet from which to take the value
Key: This defines whether the extracted value is to be used as the key to uniquely identify the product.
If it is not the key, the extracted value will be inserted into the row for the defined field
Extrafield: This defines whether the field is a “standard” column of the row or an extrafield
Usage
For a series of objects (customer/supplier proposals, customer/supplier orders, customer/supplier invoices, third-party objects), the [Import from Excel] button appears.
When pressed, the following prompts appear:
- Select the reason to use
- The Excel file to import
The rows will then be imported into the document
Hooks (for programmers)
The following hooks are available:
- ExcelAddLine for custom line creation
- getExcelProductId to return a calculated item code (e.g., the code can be extracted from two different columns depending on a certain condition). The hook receives the reason and columns of the individual rows in the $parameters array and must return the product ID in the $productId variable.
.
(*) The module is integrated into the DoliWM Ecosystem Project, designed for complete Dolibarr warehouse management.