Fetch mysql data into existing Excel template.

Topics: Developer Forum, Project Management Forum, User Forum
Feb 26, 2013 at 5:23 AM
I'm pretty new to PHP and I've a task to carry out, where in I need to fetch data from tables present in mysql db into existing excel template.

I've my .xls stored in my localdrive, which has n number of fields in the cells, i.e.,

A1 - NAME
A2- EMAIL ADDRESS
A3- PHONE NUMBER
A4- DEPARTMENT

I need to fetch data from mysql db and data need to be loaded into the cells
B1, B2, B3, B4 for the above respective fields.

Please advise me, as I'm not much aware of PHP coding.

Thanks in advance!
Krishna
Coordinator
Feb 26, 2013 at 8:22 AM
$objPHPExcel = PHPExcel_IOFactory::load("myExistingTemplate.xls");

$col = 1; 
while($row_data = mysql_fetch_assoc($result)) {
    $row = 1;
    foreach($row_data as $value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
        $row++;
    }
    $col++;
}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('myResultFile.xls');
Feb 26, 2013 at 8:49 AM
Hi Mark,

Thanks for your reply. But I don't understand how you are mapping A1 cell to insert value in B1 Cell and similarly for other cells as well.
As I don't have much idea about PHP, please do help me with a working example if possible.

Thank you!
Krishna
Coordinator
Feb 26, 2013 at 8:54 AM
This code isn't mapping anything: your database query should be doing the mapping.... returning only the columns you want and in the order that you want them
Feb 26, 2013 at 9:07 AM
I'm just fetching the columns I need from table. But not sure how exactly to map it in queries.
Feb 26, 2013 at 9:12 AM
Here is what I'm trying to do in my code, pls. let me know what modifications I've to do.

<?php

$con = mysql_connect("localhost","root","pwd");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("world", $con);

/* Include PHPExcel /
require_once '/Classes/PHPExcel.php';


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

$result = mysql_query("SELECT Name, District FROM city where Name = 'Kabul'");
$objPHPExcel = PHPExcel_IOFactory::load("form.xls");



while($row = mysql_fetch_array($result)){
$objPHPExcel->setActiveSheetIndex(0);

$objPHPExcel->getActiveSheet()->SetCellValue('C6', $row['Name']);
$objPHPExcel->getActiveSheet()->SetCellValue('C7', $row['District']);
    }
    header('Content-type: application/ms-excel');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('Result.xls');

    exit;
?>
Feb 26, 2013 at 12:06 PM
Edited Feb 26, 2013 at 12:07 PM
(my apologies to intrude on the subject, Mark)
Perhaps easier to understand:
$row = 2; / / whereas the first line contains a header, otherwise 1
$S = $objPHPExcel->getActiveSheet();
while($ row_data = mysql_fetch_assoc($result)){
$S->setCellValueExplicit('A'.$row, $row_data['Name']);//in first loop, write the name in A2
$S->setCellValueExplicit('B'.$row, $row_data['District']);
$row++;
}
Using setCellValueExplicit to avoid stupid thing with phone number, zip code.
This method forces the type according to what is stated in the third parameter, when it is not present - as here - this will be a string.
If this is not clear, you can write it:
$S->setCellValueExplicit('A'.$row, $row_data['Name'], PHPExcel_Cell_DataType::TYPE_STRING);