Ned Help in writing to an existing excel file

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shishi
Forum Newbie
Posts: 14
Joined: Wed Apr 22, 2009 12:05 am

Ned Help in writing to an existing excel file

Post by shishi »

i need to write data into an existing excel file and in a specific sheet. is this possible?

any suggestions will be greatly appreciated.

thanks in advance!
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Ned Help in writing to an existing excel file

Post by Mark Baker »

Take a look at PHPExcel

You can use it to read the existing file (Excel 2007 or BIFF8), make your modifications, then write it out again.
shishi
Forum Newbie
Posts: 14
Joined: Wed Apr 22, 2009 12:05 am

Re: Ned Help in writing to an existing excel file

Post by shishi »

hi sir. thanks for the quick reply.
here's what i will do exactly:
1. i will read an existing Excel file which contains several sheets
2. in one particular sheet i need to append/write some data
3. save the whole excel file with only the sheet (in #2) changed

i just wanna make sure if this is possible w/ PHPExcel. pls confirm
or if it's okay with you to just paste the necessary code here.
thank you
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Ned Help in writing to an existing excel file

Post by Mark Baker »

Basics of reading, modifying and rewriting an Excel (.xls) file

Code: Select all

 
error_reporting(E_ALL);
ini_set('display_errors', '1');
 
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
 
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
 
$excelFileName = '/clients/testReadWrite.xls';
if (file_exists($excelFileName)) {
    $objReader = PHPExcel_IOFactory::createReader('Excel5');
    $objPHPExcel = $objReader->load($excelFileName);
} else {
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Counter');
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    $objPHPExcel->getActiveSheet()->setCellValue('B1', 0);
}
 
$objPHPExcel->setActiveSheetIndex(0);
$countValue = $objPHPExcel->getActiveSheet()->getCell('B1')->getValue();
echo '$countValue = '.$countValue.'<br />';
$countValue++;
$objPHPExcel->getActiveSheet()->setCellValue('B1', $countValue);
 
 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($excelFileName);
 
shishi
Forum Newbie
Posts: 14
Joined: Wed Apr 22, 2009 12:05 am

Re: Need Help in writing to an existing excel file

Post by shishi »

thanks sir.
my prob now is how do i write/add data to that existing sheet without changing its originally formatted cells/rows. bold fonts, borders, etc should be maintained.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Need Help in writing to an existing excel file

Post by Mark Baker »

shishi wrote:my prob now is how do i write/add data to that existing sheet without changing its originally formatted cells/rows. bold fonts, borders, etc should be maintained.
Existing data, formatting and styles should be maintained unless you explicitly modify or delete them
shishi
Forum Newbie
Posts: 14
Joined: Wed Apr 22, 2009 12:05 am

Re: Need Help in writing to an existing excel file

Post by shishi »

hi sir. i simply am appending data at the end of the sheet but the formats/styles are really lost. pls advise...
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Need Help in writing to an existing excel file

Post by Mark Baker »

shishi wrote:hi sir. i simply am appending data at the end of the sheet but the formats/styles are really lost. pls advise...
OK, currently only cell level formatting is supported, not row or column formatting; so if you're appending new rows you need to apply styles to the cells in that row.
There is a work item to implement rwo/column level formatting, but I couldn't tell you the current status of that request without checking with the other developers.

What you should be able to do is, for each cell in the rows that you write, read the formatting of the cell above and copy it to the current cell
Post Reply