Dolibarr Reports direct to Microsoft Excel

Using my Dolibarr
1

I have a way of directly getting several reports from Dolibarr directly into MS Excel. Basically it’s an Excel Sheet that connects to the Dolibarr MySQL database and using SQL queries one can retrieve reports like Stocks, Sales, Customer Statements etc directly into Excel.

2

How about you share how it is done to help the community?

3

Ok let me explain in detail how it’s done. This will be my good deed for the day :wink:

First my environment: I’m running Windows 10 and MS Office 2016.
This can work on MS Excel 2010 and above. Also if you have Excel for MAC then also its possible but more on that later.
So for this tutorial I’m using Windows 10 and MS Excel 2016 as earlier stated. MS SQL 2016 has Power Query which earlier versions do not, so I recommend MS Excel 2016.
Note: You need some database knowledge and some SQL knowledge (recomended) for this to work.
I have installed Dolibarr on a host that supports MySQL/MariaDB.
STEPS to follow:

  1. Download the MySQL connector from this link: https://dev.mysql.com/downloads/connector/odbc/
    Make sure you are downloading the correct version for your operating system - 32 bit or 64 bit. Once you download install it in Windows.
  2. Assuming you have Dolibarr installed with the hosting provider and you have access to the control panel and the Dolibarr database, this can of course work with localhost e.g installation with DoliWamp:
    - in the hosts control panel go to Remote MySQL section (your host may be different) and then get the remote IP or URL. You need to give access to any device that connects to this database remotely. In my host I put a % to allow all remote devices to connect as in the pic below:
  3. In MS Excel 2016 go to Data -> New Query:

    then specify the Database Server and name (what was in step 2 above):

    You can insert a SQL statement or not. If you know SQL the better. There are many SQL tutorials on the web. You need to know the structure of Dolibarr database and what each table does this is well documented at: https://wiki.dolibarr.org/index.php/Category:Table_SQL. If you don’t specify SQL query then you can select the tables:

    Then click on edit where you can select which columns and even rename columns:

    After you are through you can click on close and load button to load data into Excel.

Hope this tutorial is useful. :happy:

.

1 Like
4

Hi, Arif,
Can you provide this tutorial with some example files.
Thanks.

5

I cant attach any sample files as they would have database credentials. I can however attach some pics

Dolibarr%20Excel%20Reports
Dolibarr%20Excel%20Reports1500×768 136 KB

6

A correction may be noted. For the 32 bit vs 64 bit, it’s not the OS that matters. Its the ‘application’, that determines which ODBC driver to install. So, if you’re using 32 bit Office on 64 bit Windows, you would install the 32 bit ODBC connector.

1 Like
7

Thanks for the the clarification rootlookup

8

Hi,
Your pics are broke.

9

Pics broke when new forum layout was introduced. I shall redo the pics