Add data to an existing excel

Topics: Developer Forum, User Forum
Oct 9, 2008 at 3:29 PM

I have an html form and I want to collect all data in an excel sheet.
So the 1st time, a user fills in the fields and presses Submit -> the excel file is created like in your examples from Tests.
The 2nd, 3rd time a user fills  in the fields, I check if the file already exists and if it does, I calculate the higestRow and want to add the new data on the (higestRow+1) row.
I've tried the below code, but it doesn't work.

if (file_exists($nume_fis)) {
    echo "The file $nume_fis exists";
 //write in the already existing file
 echo date('H:i:s') . " Load from Excel2007 file\n";
 $objReader = PHPExcel_IOFactory::createReader('Excel2007');
 $objPHPExcel = $objReader->load($nume_fis);
 
$objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();
//echo "maxRows=".$highestRow;

$objPHPExcel->getActiveSheet()->setCellValue($highestRow+1, $my_date);
$objPHPExcel->getActiveSheet()->setCellValue($highestRow+1, $mancare);
$objPHPExcel->getActiveSheet()->setCellValue($highestRow+1, $rating);
$objPHPExcel->getActiveSheet()->setCellValue($highestRow+1, $comentariu);

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($nume_fis);

} else {
    echo "The file $nume_fis does not exist";
 //creating the new file
// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Test result file");

// Create a first sheet
echo date('H:i:s') . " Add data\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', "Data");
$objPHPExcel->getActiveSheet()->setCellValue('B1', "Fel mancare");
$objPHPExcel->getActiveSheet()->setCellValue('C1', "Rating");
$objPHPExcel->getActiveSheet()->setCellValue('D1', "Comentariu");

$objPHPExcel->getActiveSheet()->setCellValue('A2', $my_date);
$objPHPExcel->getActiveSheet()->setCellValue('B2', $mancare);
$objPHPExcel->getActiveSheet()->setCellValue('C2', $rating);
$objPHPExcel->getActiveSheet()->setCellValue('D2', $comentariu);

......
I've received:
Fatal error: Uncaught exception 'Exception' with message 'Column string index can not be empty.' in C:\Win16App\wamp\www\PHPExcel\Classes\PHPExcel\Cell.php:500 Stack trace: #0 C:\Win16App\wamp\www\PHPExcel\Classes\PHPExcel\Worksheet.php(635): PHPExcel_Cell::columnIndexFromString('') #1 C:\Win16App\wamp\www\PHPExcel\Classes\PHPExcel\Worksheet.php(432): PHPExcel_Worksheet->getHighestColumn() #2 C:\Win16App\wamp\www\PHPExcel\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(212): PHPExcel_Worksheet->calculateWorksheetDimension() #3 C:\Win16App\wamp\www\PHPExcel\Classes\PHPExcel\Writer\Excel2007\Worksheet.php(104): PHPExcel_Writer_Excel2007_Worksheet->_writeDimension(Object(PHPExcel_Shared_XMLWriter), Object(PHPExcel_Worksheet)) #4 C:\Win16App\wamp\www\PHPExcel\Classes\PHPExcel\Writer\Excel2007.php(312): PHPExcel_Writer_Excel2007_Worksheet->writeWorksheet(Object(PHPExcel_Worksheet), Array) #5 C:\Win16App\wamp\www\feedback\process.php(78): PHPExcel_Writer_Excel2007->save('feedback_6-10-2...') #6 {main} thrown in C:\Win16App\wamp\www\PHPExcel\Classes\PHPExcel\Cell.php on line 500

Is it possible to write data in an already created excel file?
Can you please advise me how to do that?
Thank you very much.
Laura

Developer
Oct 9, 2008 at 4:39 PM
The error seems to be here:

$objPHPExcel->getActiveSheet()->setCellValue($highestRow+1, $my_date);

First parameter $highestRow+1 is not a complete cell reference, but just an integer.

Assuming you want to set value in column A, use any of these alternatives:

$objPHPExcel->getActiveSheet()->setCellValue('A' . ($highestRow+1), $my_date);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $highestRow+1, $my_date);

Oct 13, 2008 at 10:12 AM
txs! it worked:)