Data from database and custom cells

Topics: Developer Forum
May 6, 2013 at 9:44 AM
Edited May 6, 2013 at 9:45 AM
I want import data from mysql to excel via phpexcel, but in rows I want change some cells...
example
name | surname | number | and insert formulas

my code
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
// connection with the database
include "../../mikanprom/inc/mysql.php";
// require the PHPExcel file
require_once '../Classes/PHPExcel.php';
require_once 'functions.php'; //my functions for data
// simple query
$query = "SELECT jmbg,ime FROM dobavljaci";
if ($result = mysql_query($query) or die(mysql_error())) {
// Create a new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet(0)->setTitle('List of Cities');
$objPHPExcel->setActiveSheetIndex(0);
// Loop through the result set
$rowNumber = 1;
while ($row = mysql_fetch_row($result)) {
$col = 'D'; //start from column
$ime = $row['ime']; //this is data from mysql
$jmbg = $row['jmbg']; //this is data from mysql
$grad = 'anything else'; //example my excel formulas
$rows = array("$ime","$jmbg","$grad"); //this I create custom array
foreach($rows as $cell) {
$objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$cell);
$col++;
}
$rowNumber++;
}
// Save as an Excel BIFF (xls) file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="myFile.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
}
When I start script I get error
Notice: Undefined index: ime in C:\xamp\xampp\htdocs\mljekara\phpexcel\Tests\mojtest2.php on line 28
Notice: Undefined index: jmbg in C:\xamp\xampp\htdocs\mljekara\phpexcel\Tests\mojtest2.php on line 29
This error is repeated as many records in the database have

Can anybody help me, I do not know to solve this problem.

Best regards,
Tesic
May 6, 2013 at 11:19 AM
Edited May 6, 2013 at 5:27 PM
The number (28,29) do not correspond to the posted code.
$row = mysql_fetch_row($result)
[..]
$ime = $row['ime']; //this is data from mysql
$jmbg = $row['jmbg']; //this is data from mysql
Mysql_fetch_row return a numerical array, no chance to have a 'ime' or a 'jmbg' key.
Try with mysql_fetch_assoc...
Troubleshooting : use print_r on your array, you quickly see what is in it.

PS :
$rows = array("$ime","$jmbg","$grad"); // why using " ? lost of time in a loop.
$rows = array($ime,$jmbg,$grad); // same result, or you can use $row['ime'], $row['jmbg']
Edit : A little quick on this, you got a string... but i prefer using setCellValueExplicit.
$objPHPExcel->getActiveSheet(0)->setCellValue($col.$rowNumber,$cell)// no parameter for getActiveSheet (only a sheet active)
May 6, 2013 at 12:20 PM
Excellent, now working. Thanks LWol.

In the meantime, I have few problems when I load excel template.
For example duplicating my sheets. In template I have 3 sheets, but my output file has 8 sheets.

In my template I have one hidden sheet, if the real problem? The same happens when Assign a sheet that is visible.

also
when I open my create file I get a message in excel
"Excel found unreadable content in 'MYFILE.XLSX.' Do you want to recover the contents of this workbooks? If you trust the source of this workbook, click Yes."
May 6, 2013 at 12:50 PM
The last point: A common problem (you will find many topics of discussion dealing with it), there is something that can be found in the Excel file that should not be there. Opens the file (not modified by Excel) using a text editor and look if you do not see an error message (as your "notices") in the file or other messages. It may also be more difficult to locate characters: a simple space, return to the line, the UTF-8 marker. If your data are not in UTF-8, then turn them (utf8_encode)

For a copy of sheets, you can find an example in the documentation Chapter 4.4.2.
A hidden attribute must not interfere, but Excel does not love if all the sheets are equipped with.
May 6, 2013 at 3:16 PM
I want disable copy sheets... when I start script, output file has 8 sheets...in my templates I have 3 sheet, and I want 3 sheet in my output file...
May 6, 2013 at 5:45 PM
Amazing... A call to getSheetCount after loading returns what?
May 6, 2013 at 9:23 PM
when I call getSheetCount I get result 10
May 7, 2013 at 11:16 AM
If this value is displayed immediately after loading, one would tend to think that the original workbook contains ten sheets. If this is really not the case... Something disrupts the reader, this will require an analysis of this file.