How to import data from an Excel Spreadsheet dynamically

In this tutorial we will show how to import data from an Excel file dynamically, so when the Excel file changes the linked table in your site gets updated automatically. This is very useful when a lot of updates occurs and you want to simply update the external Excel file, instead of recreating the tables in your site.

Note: In addition to importing data dynamically, Tabulizer allows you to import data from an Excel file statically, meaning that the resulting table is a regular HTML table that you can edit afterwards with your Joomla/Wordpress editor like any other table. In another tutorial we discuss how to do that.

Step 1. Create the data source

The first step is to create the data source that will link the external Excel file with the resulting table. In order to do that we need to:

  • Specify the area that contains the data we want to import
  • Select the ruleset we want to apply
  • Choose some optional importation options

Step 2. Insert the data source into the article

Once the data source has been created you insert it into the article. The actual table data will appear only in the front-end when the table is rendered.

Available importation options

So far we have used only the required importation parameters but there a few more option that we need to discuss.

Server Side (Beta)

The is a beta feature, but still very useful and practically indispensable when the size of the imported table is very large. When this feature is enabled, the table will not be loaded all at once. Only the table rows that are visible will be loaded, while the rest of the rows will be fetched from the server via AJAX calls only when it's needed. Using this approach, the loading time of a page that contains a really large table (say, 50000 rows) will be reduced to a few seconds.

If you choose to use this features there are some restrictions, including:

  1. The used ruleset must contain a pagination rule.
  2. The data source cache must be enabled

Data source cache

Another way to improve loading time is to enable the data source cache.


  • The cache time is in minutes, e.g. the value 1440 means 1440 minutes or 24 hours.
  • If the external data source (i.e. Excel file) is modified before the data source expires you need to manually clean the cache by clicking on the relevant link.

Remove Empty

This is a very useful when the number of imported rows or column may change in the future. For instance, if you want to import from an Excel file a phone list that currently has 10 entries but you expect in the near future to have up to 20 entries, you can choose to set the selection area to include the cells are will contain the future data but right now are empty.

Read Font, Color, Images and Hyperlinks

You can check any of this options if you want to preserve the font attributes, font and background colors, images and hyperlinks. Specifically for the font and the color options, there are a few sub-options that are accessible via the User Preferences panel. For instance, if you check the "Read Color" importation option, only the font color will be preserved, unless the background color is checked in the Excel Reading Preferences section of the User Preferences panel.

If you choose to preserve the background color, then the following CSS statement need to be added to the ruleset's CSS file:

span.xcell { display: block; padding: 3px; margin: -3px; }

The padding/margin value must be the same as the padding value of the table cell (i.e. table tr td.tabcol { padding: 3px; }).

Location of the Excel file

The location of the Excel file can be relative to the root path of your site, or an absolute path. The import thing to remember is that:

  • The file must be on the same server as the Joomla/Wordpress site. In other words, it cannot be on a remote server.
  • If you update the Excel file locally, you need to upload the updated copy to the site server.

The file uploading can be done with an FTP client, but you can also try to use the Data Manager which will load the files into the "Data" directory templates/tabulizer/data. This is very helpful, because all files that reside in the data folder can be accessed by the data source without the need to specify any other path, as shown in the example below.

You may also noticed that the Data Manager provides some extra information about the Excel file, like when it was modified last time and which data source is using it. There is also a link to download it, so if you choose to use the Data Manager you can completely avoid using an FTP client or an additional file manager. Having said that, an FTP client is still a better solution for files or a large size or for the transfer of multiple files.

Video Guide

The following video tutorial summarizes the minimum steps for creating a data source of type Excel and how to update the resulting table with the modified version of the source Excel file.