How to use Excel file as datasource in a report

2016-07-11

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.

 

September 19, 2024

How to troubleshoot the most common issues when installing FastReport VCL

We have compiled a list of the most popular problems when installing FastReport VCL and have prepared detailed instructions on how to fix the problems that have arisen.
August 12, 2024

How to build and install the Postgres plugin in FastReport .NET

This article describes how to connect to the database using the FastReport .NET plugin for the report designer from Visual Studio via the NuGet server.
August 08, 2024

How to install FastReport .NET and its components on Windows

Step-by-step instructions for online and manual installation via the FastReport registration code.NET and its components in Windows.
Fast Reports
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314

© 1998-2024 Fast Reports Inc.