Creating reports and dashboards | Stimulsoft community forum

Hi,

I have an Excel spreadsheet with one column of data, each cell in this column containing a string of text. I would like to open this spreadsheet as a data source, and read each cell in the single column as a record.

I have tried creating both OleDB and ODBC connections, as follows:

OleDB: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Reports\Terms.xls;Extended Properties="Excel 8.0;HDR=No;IMEX=1";

ODBC: Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\Reports\Terms.xls;DefaultDir=C:\Reports;

... but neither method returns any data when I attach a data source to it (using the SQL string "SELECT * FROM [Sheet1$]").

Attempting to display the data in a data band results in a blank page, and "View Data" returns no results.

Any help in this would be greatly appreciated.

Many thanks,
Colin

The OleDb connection looks fine.

Is the Excel sheet you are trying to query actually called 'Sheet1' ?



For example, If I wanted to query the BalanceSheet I would Add a new 'Datasource based on an OleDbConnection'
and inside the query textbox type 'SELECT * FROM [BalanceSheet$]'

Yes, certainly is. Strange one isn't it?

Hello.

Could you get the data through OLE Db Connection in Visual Studio?

If yes, please send that excel file to support[at]stimulsoft.com for analysis.

Thank you.

Ah, just changed the SQL to "SELECT * FROM [Sheet1$]" (upper case "S" on "Sheet1"), and it worked ...

Actually, I think editing the SQL just caused a refresh, so one step forward in that I can now "View Data", but it will still not display in a data band (still blank) ...

Please see the report template and example of the xls file in the attachment.

Thank you.

Thanks Edward. That works fine. I can now preview the Excel data within the designer when displayed in a data band on a single page. However, it seems that the issue is tied to the VB.NET program used as a wrapper by my client (and written by them) to launch the report and display it in a preview window. When the report is run via this wrapper, it is blank ... I don't have access to the program, but if I run the report via my own VB.NET wrapper it is fine. I need to investigate further, but it may be that this is not actually a Stimulsoft issue at all.

Many thanks,
Colin

Hi

With 64 bit machine even if we change the connection string to
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Anshul\Desktop\ResultData.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

It still does not fetch anything.

SELECT * FROM [Sheet1$]

It is giving "keyword not supported" error.

Any idea to resolve this ?

Hello,

Please check that designer is also started as x64 application.

Thank you.