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!
Ned Help in writing to an existing excel file
Moderator: General Moderators
-
Mark Baker
- Forum Regular
- Posts: 710
- Joined: Thu Oct 30, 2008 6:24 pm
Re: Ned Help in writing to an existing excel file
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.
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
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
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
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
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.
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
Existing data, formatting and styles should be maintained unless you explicitly modify or delete themshishi 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.
Re: Need Help in writing to an existing excel file
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
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.shishi wrote:hi sir. i simply am appending data at the end of the sheet but the formats/styles are really lost. pls advise...
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