Ever wondered...
How can I display an EXCEL Spreadsheet in DotNetNuke?
I have been asked this question several times in the last few weeks and have seen the same question posted in the forums too. The answer is rather straight forward and the work is mostly done by Microsoft. But there is always a catch. In most causes, it's view only! Also, the syntax to extract the data is a special version of T-SQL, so there are some limits.
First, lets look at the connection string required to access the the spreadsheet. The standard format is something like: (See www.connectionstrings.com for more detail.)
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\MyExcel.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
The provider is Mircosoft's JET engine that in part of the ACCESS family. It does understand SpreadSheets prior to EXCEL 2007. So if you are using EXCEL 2007, you will need to save the file as a true XLS file. The Extended properties HDR=YES indicates that the first row contains the column names.
To access the data, you can use SQL - but it has given syntax. For example, use
SELECT * FROM [sheet1$]
where the source of the select is the EXCEL worksheet name followed by a "$" and wrapped in "[" "]" brackets.
So if you have a spreadsheet called SCORES.XLS with the default sheet names and the first sheet like
| PlayDate |
Player |
Course |
Par |
Score |
| 01-JUL-2009 |
Paul |
The Dells |
72 |
80 |
| 01-JUL-2009 |
Bob |
The Dells |
72 |
79 |
| 02-JUL-2009 |
Paul |
SummerHill |
70 |
78 |
| 02-JUL-2009 |
Bob |
SummerHill |
70 |
79 |
Then the connection string would be something like the following - the location of the file is arbitrary:
Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\DOTNETNUKE\PORTALS\0\SCORES.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
and the select would be
Select * from [Sheet1$]
with a result like
| PlayDate |
Player |
Course |
Par |
Score |
| 01-JUL-2009 |
Paul |
The Dells |
72 |
80 |
| 01-JUL-2009 |
Bob |
The Dells |
72 |
79 |
| 02-JUL-2009 |
Paul |
SummerHill |
70 |
78 |
| 02-JUL-2009 |
Bob |
SummerHill |
70 |
79 |
There are several different modules that can generate a display like this:
- Reports Modules - the core module can display the data
- SQLGrid from DNNStuff (www.dnnstuff.com)
- SQLGridSelectedView from this site (www.tressleworks.ca) can display the data and allow you to filter the data in various ways. You can see an example of EXCEL spreadsheet data display here
Hope this helps - if you have questions -- let me know
Paul.