Riddick Riddick - 2 months ago 5
PHP Question

Write in a Excel file with PHPExcel

Well, I would like to write in an Excel file using a PHP script.
Actually, when an user submit the form, we get the mail and the current date in order to put it in the Excel.

The mail must be in one column, and the date too.

So this is my form (a classic form):

<form action="mail.php" method="POST">
<label for="name">Nom*<br />
<input id="name" type="text" name="Nom" />
</label><br /><br />

<label for="mail">Mail*<br />
<input id="mail" type="email" name="Email" />
</label><br /><br />

<label for="tel">Téléphone*<br />
<input id="tel" type="text" name="Téléphone" />
</label><br /><br />

<label for="comment">Commentaire*<br />
<textarea id="comment" type="text" name="Commentaires"> </textarea>
</label><br /><br />

<input type="submit" value="Envoyer" />
</form>


And my mail.php, so this is here that I use PHPExcel classes :

<?php
/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
include './Classes/PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
include './Classes/PHPExcel/IOFactory.php';

/* Set the variables */
$mail = $_POST['Email'];

// Line number
$ligne = 1;

// Excel object
$fichier = new PHPExcel();
$fichier->getProperties()->setCreator("Don't know");
//Set the size
$fichier->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$fichier->getActiveSheet()->getColumnDimension('B')->setWidth(30);

$fichier->setActiveSheetIndex(0);
$feuille = $fichier->getActiveSheet();

function writeExcel($objExcel, $sheet, $email){
$sheet->SetCellValue('A'.$ligne, $email);
$sheet->SetCellValue('B'.$ligne, date("Y-m-d H:i"));
$objWriter = new PHPExcel_Writer_Excel2007($objExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$ligne++;
}

writeExcel($fichier, $feuille, $mail);
?>


I think it's the line number is a problem, I don't know how to organize my code.
Each time a user submit the form, we put his mail (cell A1) and the current date (cell B1), and the next mail must be at A2 cell, etc ...

I don't know if you know what I mean ^^

EDIT
With the comments I modified my code ! But it's the same ^^

<?php
/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
include './Classes/PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
include './Classes/PHPExcel/IOFactory.php';

/* Set the variable */
$mail = $_POST['Email'];

// Load file if it doesn't exists
if (file_exists("mail.xlsx"))
{
$fichier = PHPExcel_IOFactory::load("mail.xlsx");
}
else
{
$fichier = new PHPExcel();
}
// Line number
$ligne = $fichier->getActiveSheet()->getHighestRow() + 1;

// Excel object
$fichier->getProperties()->setCreator("Don't know");

//Set the size
$fichier->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$fichier->getActiveSheet()->getColumnDimension('B')->setWidth(30);

$fichier->setActiveSheetIndex(0);
$feuille = $fichier->getActiveSheet();

// Line number
global $ligne = $fichier->getActiveSheet()->getHighestRow() + 1;

function writeExcel($objExcel, $sheet, $email){
$sheet->SetCellValue('A'.global $ligne, $email);
$sheet->SetCellValue('B'.global $ligne, date("Y-m-d H:i"));
$objWriter = new PHPExcel_Writer_Excel2007($objExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
global $ligne++;
}

writeExcel($fichier, $feuille, $mail);
?>


LAST EDIT (I HOPE)
Ok it's working :D

This is my code :

<?php
/** Error reporting */
error_reporting(E_ALL);

/** PHPExcel */
include './Classes/PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
include './Classes/PHPExcel/IOFactory.php';

/* Set the variable */
$mail = $_POST['Email'];

// Load file if it doesn't exists
if (file_exists('mail.xlsx'))
{
$objExcel = PHPExcel_IOFactory::load('mail.xlsx');
}
else
{
$objExcel = new PHPExcel();
}

$objExcel->getProperties()->setCreator("Don't know");

//Set the size
$objExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);

$objExcel->setActiveSheetIndex(0);
$sheet = $objExcel->getActiveSheet();

// Line number
$line = $objExcel->getActiveSheet()->getHighestRow() + 1;

/*
This function write in the Excel file.
Note that the variables are passed by reference.
*/
function writeExcel(&$objExcel, &$sheet, $email, &$line){
$sheet->SetCellValue('A'.$line, $email);
$sheet->SetCellValue('B'.$line, date("Y-m-d H:i"));
$objWriter = new PHPExcel_Writer_Excel2007($objExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
}

writeExcel($objExcel, $sheet, $mail, $line);
?>

Answer

First of all you need to learn about variable scope in PHP. As noted in the comments, this is where your main problem lies.

You also need to read up exactly how you use the global keyword, as you clearly have misunderstood this. Well... I'd really recommend not using it at all instead, as it is generally considered a bad thing!
I recommend that you use the technique called pass by reference instead, which is the answer to your question for the first comment.

Lastly, please, please, write your code in English. Not only for our sake, the people trying to help you, but also for your own (and employers) sake later on. Seeing variable and function names in a language you don't understand seriously hampers ones ability to understand the code, and thus increases the probability of errors occurring. Always assume that other people will need to look at, and understand, your code!

Comments