Page 1 of 1
Ned Help in writing to an existing excel file
Posted: Wed Apr 29, 2009 2:54 am
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!
Re: Ned Help in writing to an existing excel file
Posted: Wed Apr 29, 2009 3:02 am
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.
Re: Ned Help in writing to an existing excel file
Posted: Wed Apr 29, 2009 3:21 am
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
Re: Ned Help in writing to an existing excel file
Posted: Wed Apr 29, 2009 7:29 am
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);
Re: Need Help in writing to an existing excel file
Posted: Thu Apr 30, 2009 9:46 am
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.
Re: Need Help in writing to an existing excel file
Posted: Thu Apr 30, 2009 10:50 am
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
Re: Need Help in writing to an existing excel file
Posted: Fri May 01, 2009 12:53 am
by shishi
hi sir. i simply am appending data at the end of the sheet but the formats/styles are really lost. pls advise...
Re: Need Help in writing to an existing excel file
Posted: Fri May 01, 2009 2:36 am
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