How to use Excel file as datasource in a report

Not everyone knows that FastReport .Net allows you to use files of a spreadsheet editor Excel as a data source. It can be both .xls and .xlsx files.

In this article we will show how to create such a data source in the report.

First of all - about the requirements for Excel documents. Each table should be placed on a separate sheet. The first line should have the column heads that are used as the names of the table fields.

1. Create Excel document with one table:2. Create a Windows Forms project;

3. Add the Report control on the form;

4. Double click on the added control. Skip the data source window. The report designer appears;

5. In the Report Designer create a new connection to the data source. Menu Data-> Add Data Source.

The data source wizard appears:

6. Create new connection string by using the button with the same name.

7. In the window of string connection creation choose the connection type – ODBC Connection.

 Switch the radio button “Use connection string”. Select the data source using button .

8. Here, select the tab "Computer data source".

9. Select “Excel Files” and click Ok.

In the opened window select the file .xls or .xlsx, which contains the data.

Close the wizard connection string by the button OK.

The connection string is as follows:

Dsn=Excel Files;dbq=C:\Users\Gromozeka\Documents\Visual Studio 2010\Projects\ExcelDataSource\ExcelDataSource\bin\Debug\Employee.xlsx;defaultdir=C:\Users\Gromozeka\Documents\Visual Studio 2010\Projects\ExcelDataSource\ExcelDataSource\bin\Debug;driverid=1046;maxbuffersize=2048;pagetimeout=5

10. In the data source wizard click Next. В мастере создания источника данных нажимаем Next. Go to the window of data selection.

Select desired tables. In our case Employee. And click Finish.

11. The new data source was added to the Data window.

12. Now we can create the report template. Drop all the fields onto the Data band.

Run the report:

So, you can use data from Excel files to automatization your accounting statements. A data source can contain a lot of tables. It depends on the amount of sheets in the spreadsheet.  Many companies still hold their accounts in excel format. And you can use it in your reports.