Excel Reports Using PHP And Web Query
Ever have times when you want to present data to people around your company in a spreadsheet, but you want the data to come from a live database? When you have tried to accomplish that, have you ever run into problems of getting database drivers and ODBC settings deployed to end users? If you have ever had either of these problems, then this article is for you.
Getting live data into Excel can be a pain sometimes, especially if you have a mobile workforce with laptops and no direct access to the back-end database servers from the Internet. Fortunately, there is a solution. If your users have Microsoft Office Professional, that version of Excel supports a feature called Web Query. Web Query allows Excel to pull data from a web page with HTML tables on it and then transfer the content of those tables to a spreadsheet. Beyond that, Web Query also offers the ability to set up prompts which can be sent with the HTTP request to define what data you would like to see.
Let's start simple. We will create a PHP script which returns data from a SQL database as an HTML table. Once that is done, we will make a more advanced system with user input, authentication, and easy reconfiguration/reuse.
I am not going to go into too much detail about connecting to a database with PHP. There are a myriad of articles on the Internet which explain this in detail. For our purposes, all we need to do is execute a query, fetch the results, and display the results as an HTML table (CSS block elements will NOT work with Web Query). Below is a basic script which will return the list of all available databases on a MySQL server.
So, now that we have our script, we need to set up Excel to pull from the web site and incorporate that data into an Excel spreadsheet. To make this easy to understand, lets discuss the basic format of the request that the client makes to the web server. We have all seen web addresses before, but the important part is what comes after the address. Typically, these will look like "?param1=value1¶m2=value2". These parameters are often referred to as GET variables. PHP stores these variables in the special $_GET array. So, if you have a parameter like "db=mysql", then in PHP you could access this variable with "$_GET['db']".
Now that we know how to construct URLs using GET variables, we can proceed to creating our first web query definition. These are special files for Excel and they end in an iqy extension. Below is an example of an iqy file.
The format of the iqy file is fairly simple. The first line defines what type of data source we are referring to, in this case "WEB". The second line refers to the version of web query we are using, 1 is the only valid option that I am aware of. Finally, the last line is the most interesting as it is the address where we will get the data from along with any parameters that we want to specify. Take note of the portion in square braces ("[]"). This syntax tells Excel to prompt the user for input for that parameter. You could have none, one, or one hundred parameters (though I don't suggest many more than 5 for convenience).
Now that we have created an iqy file, we could just double-click on it and it would open in Excel. That would be pretty nice to begin with, but we can do more than that. If we create a new Excel spreadsheet and embed the web query in it, we can define formatting, pivot tables, etc.. Let start simple though and just create a spreadsheet with the iqy embedded in it. Follow the steps below:
So, now that we know how to send user input to the web page, we can use that knowledge to design more complex PHP scripts which can authenticate the user based on the input, we can allow the user to specify dates, and we can allow the user to input information to filter the returned data.
Right clicking on the data area and selecting "Refresh data" will tell Excel to go out and get a fresh copy of the data from the web server. You can also create a Macro based button to perform the same task.
Now we have covered all of the basics required to make Web Query work for your own spreadsheets, but I would like to recommend some further ideas to make them extremely useful. First, you can use parameters to perform authentication, so PHP will use 2 parameters for username and password. Second, use a defined name based on the OFFSET() function in the spreadsheet to reference the dynamic data range and use that to have dynamic pivot tables or charts. Finally, you can put user and group membership logic into the PHP script to determine what data the user is allowed to see when it is downloaded.
This technique provides a fairly straight-forward way to generate reports which can be accessed by remote users. As long as your web server is running on SSL, it is secure. One caveat, the Internet code in Excel is based on Internet Explorer and therefore cannot cache encrypted pages by default; to remedy this there is configuration change which will allow this (and it is needed if you want to do this with SSL encrypted sites). Open the "Internet settings" control panel and go to the "Advanced" tab. In the list is a option "Do not save encrypted pages to disk". Unchecking this option will allow Excel to read the cached data and present it via web query.
Well, that's all for now. Have ideas on how to improve this concept or ideas which I may have missed? Leave a comment!!
Getting live data into Excel can be a pain sometimes, especially if you have a mobile workforce with laptops and no direct access to the back-end database servers from the Internet. Fortunately, there is a solution. If your users have Microsoft Office Professional, that version of Excel supports a feature called Web Query. Web Query allows Excel to pull data from a web page with HTML tables on it and then transfer the content of those tables to a spreadsheet. Beyond that, Web Query also offers the ability to set up prompts which can be sent with the HTTP request to define what data you would like to see.
Let's start simple. We will create a PHP script which returns data from a SQL database as an HTML table. Once that is done, we will make a more advanced system with user input, authentication, and easy reconfiguration/reuse.
Introduction To Web Query
I am not going to go into too much detail about connecting to a database with PHP. There are a myriad of articles on the Internet which explain this in detail. For our purposes, all we need to do is execute a query, fetch the results, and display the results as an HTML table (CSS block elements will NOT work with Web Query). Below is a basic script which will return the list of all available databases on a MySQL server.
<html>
<body>
<table>
<?
$dbPtr = mysql_connect("127.0.0.1","root","myRootPassword") ;
mysql_select_db("mysql") ;
$query = "SHOW COLUMNS FROM db" ;
$resultSet = mysql_query($query) ;
echo " <tr>\n" ;
while ($headers=mysql_fetch_array($resultSet)) {
echo " <td>\n" ;
echo " ".$headers['Field']."\n" ;
echo " </td>\n" ;
}
echo " </tr>\n" ;
mysql_free_result($resultSet) ;
$query = "SELECT * FROM db" ;
$resultSet = mysql_query($query) ;
while ($row=mysql_fetch_array($resultSet)) {
echo " <tr>\n" ;
for ($x=0;$x<count($row);$x++) {
echo " <td>\n" ;
echo " ".$row[$x]."\n" ;
echo " </td>\n" ;
}
echo " </tr>\n" ;
}
?>
</table>
</body>
</html>
<body>
<table>
<?
$dbPtr = mysql_connect("127.0.0.1","root","myRootPassword") ;
mysql_select_db("mysql") ;
$query = "SHOW COLUMNS FROM db" ;
$resultSet = mysql_query($query) ;
echo " <tr>\n" ;
while ($headers=mysql_fetch_array($resultSet)) {
echo " <td>\n" ;
echo " ".$headers['Field']."\n" ;
echo " </td>\n" ;
}
echo " </tr>\n" ;
mysql_free_result($resultSet) ;
$query = "SELECT * FROM db" ;
$resultSet = mysql_query($query) ;
while ($row=mysql_fetch_array($resultSet)) {
echo " <tr>\n" ;
for ($x=0;$x<count($row);$x++) {
echo " <td>\n" ;
echo " ".$row[$x]."\n" ;
echo " </td>\n" ;
}
echo " </tr>\n" ;
}
?>
</table>
</body>
</html>
So, now that we have our script, we need to set up Excel to pull from the web site and incorporate that data into an Excel spreadsheet. To make this easy to understand, lets discuss the basic format of the request that the client makes to the web server. We have all seen web addresses before, but the important part is what comes after the address. Typically, these will look like "?param1=value1¶m2=value2". These parameters are often referred to as GET variables. PHP stores these variables in the special $_GET array. So, if you have a parameter like "db=mysql", then in PHP you could access this variable with "$_GET['db']".
Now that we know how to construct URLs using GET variables, we can proceed to creating our first web query definition. These are special files for Excel and they end in an iqy extension. Below is an example of an iqy file.
WEB
1
http://www.google.com/search?q=["keyword","Enter A Search Term:"]
1
http://www.google.com/search?q=["keyword","Enter A Search Term:"]
The format of the iqy file is fairly simple. The first line defines what type of data source we are referring to, in this case "WEB". The second line refers to the version of web query we are using, 1 is the only valid option that I am aware of. Finally, the last line is the most interesting as it is the address where we will get the data from along with any parameters that we want to specify. Take note of the portion in square braces ("[]"). This syntax tells Excel to prompt the user for input for that parameter. You could have none, one, or one hundred parameters (though I don't suggest many more than 5 for convenience).
Now that we have created an iqy file, we could just double-click on it and it would open in Excel. That would be pretty nice to begin with, but we can do more than that. If we create a new Excel spreadsheet and embed the web query in it, we can define formatting, pivot tables, etc.. Let start simple though and just create a spreadsheet with the iqy embedded in it. Follow the steps below:
- Open Excel and create a new blank workbook
- Place your cursor on the cell which you would like to make the top-right corner of your data area
- Click on the Data menu and select "Import external data" and select "Import data"
- In the "Select data source" dialog, navigate to the iqy file we created earlier
- When prompted, select the range where you would like the data displayed and click OK
- Excel will then prompt you for any variables you may have specified input for, enter them
- The results of the data polling will now be displayed in the spreadsheet!!
So, now that we know how to send user input to the web page, we can use that knowledge to design more complex PHP scripts which can authenticate the user based on the input, we can allow the user to specify dates, and we can allow the user to input information to filter the returned data.
Right clicking on the data area and selecting "Refresh data" will tell Excel to go out and get a fresh copy of the data from the web server. You can also create a Macro based button to perform the same task.
Now we have covered all of the basics required to make Web Query work for your own spreadsheets, but I would like to recommend some further ideas to make them extremely useful. First, you can use parameters to perform authentication, so PHP will use 2 parameters for username and password. Second, use a defined name based on the OFFSET() function in the spreadsheet to reference the dynamic data range and use that to have dynamic pivot tables or charts. Finally, you can put user and group membership logic into the PHP script to determine what data the user is allowed to see when it is downloaded.
This technique provides a fairly straight-forward way to generate reports which can be accessed by remote users. As long as your web server is running on SSL, it is secure. One caveat, the Internet code in Excel is based on Internet Explorer and therefore cannot cache encrypted pages by default; to remedy this there is configuration change which will allow this (and it is needed if you want to do this with SSL encrypted sites). Open the "Internet settings" control panel and go to the "Advanced" tab. In the list is a option "Do not save encrypted pages to disk". Unchecking this option will allow Excel to read the cached data and present it via web query.
Well, that's all for now. Have ideas on how to improve this concept or ideas which I may have missed? Leave a comment!!
Comments
your post gives me the courage to ask you with help on the following:
.
<h2>how to capture the details of multiple pages in a single excel worksheet?<h2>
say, I want to capture details of few of the Asterix series (ISBNs-> 0752866281 , 0752866109 , 0752866052 , 0752888765)
I want to caputre The title, Author, Description, Price, Pages, Publisher etcetera- ALL of which are in table 19 on respective pages
And, I want all of them to pop up one below the other in the same excel worksheet (if possible) else at least all in one workbook.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
i used this for individual book
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WEB
1
http://www.a1books.co.in/searchdetail.do?itemCode=0752866109
Selection=19
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
and tried this (in VBA) for multiple, but it did not work
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub monu_post_query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.a1books.co.in/searchdetail.do?itemCode=[""ISBN"",""Enter ISBNs to search at A1Books, separated by commas.""]", _
Destination:=Range("a1"))
'Select a specific table.
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
'Import the table containing the exchange rates.
.WebTables = "19"
'Save the query with workbook.
.SaveData = True
'Adjust columns to fit the data.
.AdjustColumnWidth = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
ofcourse there was a lot of google, lot of crtl-c ctrl-v and some editing in it.. so you may find these codes funny
please accept my thanks, for your time and patience to read till here.
~sailaab~ [at] gmail [dot] com
I am gettin into excel and VBA macros a lot recently and I am also a fan of stats.
I play a game on iPhone called Kingdoms Live.
I want to pull my stats from it automatically into excel at certain intervals so I can graph my progress.
The game can be played on computer also and it is accessed by going to a link which is a PHP with the ID and password posted.
For some reason it seems to reload in a webpage but it will not in excel.
Is there A way that I can make a script to navigate to a PHP eg, http://eg.PHP?user=asd&pass=sd
and then once authenticated navigate to http://profile.PHP?tab=main
when you first log in the cookies will authenticate the session so then I will simply be able to navigate to the profile page.
From there I want to export the entire page to an excel sheet.
I already have scripts that run automatically so thats not an issue its just that I cannot get it to load page.
I made an HTML page to open 2 iframes with a javascript to reload the 2nd iframe with the profile after a few milliseconds and the first one had loaded; however it did not work.
Could you please help me?
I am happy to provide example URL if required.
your help would be very much appreciated..:)
Thanks
Are you able to share an example.
That would fix the issue.
I am not very good with PHP but there must be a way to post the data then load another page and save that as a php page to query via excel?
I have a server that I can host the PHP file on; However I lack the knowledge to create this sort of script.
Help me, Obi-Wan Kenobi. You're my only hope. [looks to the side quickly, then crouches to end the message] -Star Wars Episode IV: A New Hope