inserting sql table rows to excel file

Topics: Developer Forum, Project Management Forum, User Forum
Apr 30, 2013 at 7:54 AM

i have 150 rows and 40 columns in a sql table..i am displaying the entire table in a web,what i want to do is create a link on that web page that will take the entire table and insert it in an excel file(dosn't matters if it creates a new excel file,or modifies sum exisiting one) i can do it manually by using(PHPExcel library)," objPHPExcel->setCellValue('C5', $v) "...but i would have to write this like 40 times(change '$v' variable in every statment) nd its inside a loop that will run 150 times..hence i dont wanna do it this way..

now i wanted to know if i can insert the table,row by row in the excel when i insert a row,it will insert the entire cells of d row..that way it will be pretty i wanted to know if there any specific commands for doing this..

if not,wat other alternatives do i have of doing this..all i want to do is to export the entire sql table to an excel file using php..
Apr 30, 2013 at 9:16 AM
Use the fromArray() method
Apr 30, 2013 at 9:51 AM
can u give an example...i tried looking in PHPExcel developer documentation..but thn dats not enuf..
Apr 30, 2013 at 10:02 AM
$dataArray = array(
$objPHPExcel->getActiveSheet()->fromArray($dataArray, NULL, 'A'.$row++);
$mysqli->real_query("SELECT id FROM test ORDER BY id ASC");
$res = $mysqli->use_result();

$r = 0;
while ($row = $res->fetch_assoc()) {
    $objPHPExcel->getActiveSheet()->fromArray($row, NULL, 'A'.$r++);
Apr 30, 2013 at 6:32 PM
here is what i am using..problem is its showing every value(value coming from database) twice in the excel sheet...and secondly some of the values are 0 in database,for all those values its shows an empty cell in excel...

while($rows = sqlsrv_fetch_array( $tsql ) )

    $objPHPExcel->getActiveSheet()->fromArray($rows, NULL, 'A'.$rowCNT);

Apr 30, 2013 at 9:22 PM
For fetching the results from your database, use
while($rows =sqlsrv_fetch_array($tsql, SQLSRV_FETCH_ASSOC))
while($rows =sqlsrv_fetch_array($tsql, SQLSRV_FETCH_NUMERIC))
otherwise sqlsrv_fetch_array() defaults to returning both associative and enumerated keys.

Check how the database is set to handle NULL returns; but using
$objPHPExcel->getActiveSheet()->fromArray($rows, 0, 'A'.$rowCNT);
should set all NULL values from the database result to 0 value in PHPExcel