how to import data from Mysql Database & export to excel

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)

March 30,

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)

March 30,

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=0; P < obj.getColumnIndices().length; P++) {
      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 P = 0; P < obj.getRowIndices().length; P++){ 
      for (var Q = 0; Q <= obj.getColumnIndices().length; Q++){ 
        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

March 30,

Just found the other post (a few posts under this one (lol).
Credit for the Excel script should go to Leon Van Zyl.

Carl

March 30,

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)

April 2,

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

April 13,

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


<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>

</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

April 13,

This topic is archived.

how to import data from Mysql Database & export to excel

See also:

Search

New Data Grid

Version 2