How to display data from Excel

Last update:
Created :
Written by Thomas Speekenbrink

Overview:

With InfoPanels, you are also able to show data from an Excel file in, for instance, a visually pleasing pie chart.
Make sure Office is installed server-side or install ACE: http://www.microsoft.com/en-us/download/details.aspx?id=13255

How to:

Below are two examples, how to link from an Excel file and display them as list or pie chart.

When you create the widget, select one of the Plug-in's below :

The plug-in "Data: SQL View based chart", is to display the data in a pie chart.

The plug-in "Data: SQL View Table", is to display the data in a list.

Complete the widget and click Save.

In the widget parameters, select the Connection type "OleDb".

The connection string looks like this for xlsx files:

  • Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Country.xlsx; Extended Properties="Excel 12.0 Xml;HDR=YES";

Please note that the file location should be accessible from the web server. The examble above, shows the file located on the C-Drive on the web server.

  • The "View" or "table name" is the worksheet name in the format [Sheet1$]. Ex. A worksheet with the name Sheet1 should be written [Sheet1$]

For the Plug-in "SQL View based chart", you need to tell the InfoPanels which column it needs to use for the count.

Fill out the "Group by Column", with the name of the column you want counted and displayed. In the example below, the cell A1 is equal to "Name" and the cell B1 is equal to "Country".

The result for the pie chart:

The result for the list view: