:: Forum >> Version 1 >>

how to import data from Mysql Database & export to excel

More information on this topic is available in the documentation section: /grid.howto/.

Hi,

I wanted to know that how can i import data from a mysql database using php into the grid.

i want the grid to show something like the basic ver.
e.g : http://www.activewidgets.com/examples/grid/v2basic.htm

i also want to know that if it is possible to export the grid to an excel file and word file.

can anyone plz guide me on this , if there are working code examples please do give me a LINK.




Abhishek Ranjan (abhishek349@gmail.com)
Thursday, March 30, 2006
Complete PHP/MySQL example for AW 2.0 is available here -

http://www.activewidgets.com/grid.examples/php-mysql.html

Version 1.0.2 contains similar exampe in /examples/php/ folder.

Client-side export to excel or word is not supported in the current version.
Alex (ActiveWidgets)
Thursday, March 30, 2006
True, client-side export to excel is not supported, but you can use an ActiveXObject to do it. I found an example in the forum for V1 of AW and modified it to my own needs.
I created an "Excel Export" button that when clicked opens up Excel and writes the visible GRID to the spreadsheet.

I'm still refining it, but if you'd like to see a working example, let me know. Send me an email - dafweg69@yahoo.com and I can post a hidden link that I can email you.

Basically, the function for the button looks like this:

function ToExcel(){ 
  if (
window.ActiveXObject){ 
    var 
xlApp = new ActiveXObject("Excel.Application");  
    var 
xlBook xlApp.Workbooks.Add(); 
    
xlBook.worksheets("Sheet1").activate
    var 
XlSheet xlBook.activeSheet
    
xlApp.visible true;  
    
// Set Excel Column Headers and formatting
    
for (var P=0obj.getColumnIndices().lengthP++) {
      
XlSheet.cells(1,P+1).font.bold=true;
      
XlSheet.cells(1,P+1).value obj.getHeaderText(obj.getColumnIndices()[P]);
    }
    
//Populate Rows from Grid
    
for (var 0obj.getRowIndices().lengthP++){ 
      for (var 
0<= obj.getColumnIndices().lengthQ++){ 
        
XlSheet.cells(P+3,Q+1).value obj.getCellText  (obj.getColumnIndices()[Q],obj.getRowIndices()[P]);
      } 
    } 
    
XlSheet.columns.autofit;
    
XlSheet.Name="Generation Active Queues"
  } 
}
//end function 
 
Please note, the above would need to be modified a lot. I have a fully functional version at work based on the requirements of my grid. As mentioned, I can post a hidden link if you'd like to see it functional. In addition, in my project, I wrote another routine (based on another example I saw in this forum) to generate a "printable" page of what the grid is showing at any particular moment.
Carl
Thursday, March 30, 2006
Just found the other post (a few posts under this one (lol).
Credit for the Excel script should go to Leon Van Zyl.

Carl
Thursday, March 30, 2006
i think the php-mysql code doesn't work properly,
any modifications please help

i'm a newbie at coding
Abhishek Ranjan (abhishek349@gmail.com)
Sunday, April 2, 2006
Hi

I am developing a sample application using struts framework and also tried to generate the Excel using the ActiveX controls and it runs great.
But, if i change the data on the grid , the changes are not reflected to data store.In my application the user may be changing the data of multiple cells of the grid and all thode changes should be reflected on the data store/xml which he may be editing.
Can some body help me in this regard.
Vikramaditya Garg
vikramaditya.garg@fidelity.co.in
Vikramaditya Garg
Thursday, April 13, 2006
Hi,

I think the code samples here don't work, anyways,

for some trick .

excel reporting can be made even more easier , rather than creating activex objects.

i needed this script to send the data from the grid to excel sheet, but i couldn't find any proper help. and apart from using Active Widgets. i created a different script.

see what i did.

for the grid :

created a grid.css --- for the grid design and looks

grid.js ------- for the grid sorting and effects.


and in the php file designed a table using...

see the code

<?php
$link = mysql_connect("localhost", "root", "pass")
or die("failure to communicate");
mysql_select_db("pro") or die (mysql_error());

$query = "SELECT


Station Name,
Building,
Tower,
FROM
test";

$result = mysql_query($query,$link) or die(mysql_error());
$num_movie = mysql_num_rows($result);
$movie_header =<<<EOD


<!-- grid form -->
<script>
var row = new Active.Templates.Row;
row.setEvent("onmouseover", "mouseover(this, 'active-row-highlight')");
row.setEvent("onmouseout", "mouseout(this, 'active-row-highlight')");
obj.setRowTemplate(row);

document.write(obj);

</script>



<style>
<!--.sthead
{ white-space:nowrap;color: #FFFFFF; font-weight: bold; background-color: #008000 }.strowradio
{ background-color: #F2F2F2} .strow1radio
{ background-color:#ffffff}.headst
{height:19px;background-color:#EBEADB;padding-top:2px;padding-bottom:3px;font:menu;overflow:hidden;
text-overflow:ellipsis;white-space:nowrap;border-bottom: 2px solid #D6D2C2; font-size:10px }.headfocus
{height:19px;padding-top:1px;padding-bottom:1px;font:menu;background-color:#FAF9F4;
border-top: 1px solid #FFFFFF;border-bottom:3px solid #F9B119;}.numst
{border-left: 1px solid #ffffff; border-right: 1px solid #D6D2C2;border-top: 1px solid #FFFFFF;
border-bottom: 1px solid #D6D2C2;font-family:MS Sans Serif;font-size:10px;text-align:center;
background-color: #EBEADB;}.tdborderradio
{cursor:default;font:menu;overflow:hidden; text-overflow:ellipsis;
white-space:nowrap;border-right: 1px solid #dcdcdc;border-bottom:1px solid #dcdcdc;}.tdfocusradio
{ cursor:default;border-bottom: 2px solid orange;font:menu;background-color:#FDEAC8 }.tdclick
{border-left:1px solid #c0c0c0;border-right:1px solid #ffffff;height:19px;padding-top:2px;padding-bottom:3px;
font:menu;overflow:hidden; text-overflow:ellipsis;white-space:nowrap;border-bottom: 2px solid #D6D2C2;
background-color:#EBEADB;font-size:10px }.divstradio
{ scrollbar-base-color:#EBEADB;scrollbar-border-color:#d6d2c2;overflow: auto;
height:192px;width:100%;border-style:solid;border-width:1px;border-color:#C0C0C0;cursor:default}.headerrowst
{position:relative;top:expression(this.offsetParent.scrollTop);z-index: 20;}-->
</style>

<table name='radio' id=radio cols=9 cellSpacing=0 style='table-layout:fixed;border-style:solid; border-width:0px;' cellPadding=0 width="100%" border=0>
<tr>

<th align='center' onmouseup="this.className='headfocus'" onmousedown="this.className='tdclick'" onmouseover="this.className='headfocus'" onmouseout="this.className='headst'" class='headst' width='10%' style="border-right-style: solid; border-right-width: 1px">Station Name</th>
</tr>
</table>





$movie_details =<<<EOD



<tr class='strow1radio' height=20 onmouseover='javascript:active-row-highlight(this)'>

<td onkeydown='javascript:tdkeydown(this)' class='tdborderradio' onfocus="this.className='tdfocusradio';" onfocusout="this.className='tdborderradio';"><p style='margin-left: 5px; margin-right: 5px'>$Station Name</td>

-----------------------------------------

just example u will need to modify.


for the excel export.

damn simple. just try a simple trick .

load the grid in a <div> or <table>
give the div or the table an ID in my case id="grid"

now create a simple javascript function.

and place a link above the table say : to Excel

now when user clicks on to excel the action or javascrip function tell the browser to save the downloaded file as .xls

the download dialog will make it clear.


and it just a trick to fool the browser that the html file being downloaded is not html but .xls

thats it.

---------------------------
3. client side editing.


just create simple javascript for the event which tells to run the update query . on edit.

but i think this will work best using xmlhttprequest


thanks.

i think u can also search on google groups for more examples.
Abhishek Ranjan
Thursday, April 13, 2006



This topic is archived.

Back to /grid.howto/

Documentation:

Forum search