PHPExcel modify existing .xlsx file with multiple worksheet

Question


require_once '../Classes/PHPExcel/IOFactory.php';


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

$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
$excel2 = $excel2->load('dentkey.xlsx');
$excel2->setLoadAllSheets();
$excel2->setActiveSheetIndex(0);
$excel2->getActiveSheet()->setCellValue('C6', '4')           

->setCellValue('C7', '5')         

  ->setCellValue('C8', '6')       

    ->setCellValue('C9', '7');
$excel2->setActiveSheetIndex(1);
$excel2->getActiveSheet()->setCellValue('A7', '4')

->setCellValue('C7', '5');


$objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel2007');
$objWriter->save('dentkey1.xlsx');

here, i am able to load (.xlsx) file and i also able to modify (dentkey.xlsx). But After generating new .xlsx file (dentkey1.xlsx)..all worksheets data which was in (dentkey.xlsx) is not loading and also i cannot set value for 2nd worksheet.

you can fine

$excel2->setActiveSheetIndex(1);
$excel2->getActiveSheet()->setCellValue('A7', '4')

->setCellValue('C7', '5');

is not setting value 5 to 'C7' column of generated (dentkey.xlsx) file

please help me...

thanks a lot in advance

is it possible to write to an excel in append mode ? scenario : 1) create new excel object and insert data 2) write object to an existing excel file by adding a new worksheet so if file has worksheet : "Sheet 1" , "Sheet 2" , "Sheet 3" the write will create "Sheet 4" but doesn't delete the previously written sheets — Denish, Sep 29 '11 at 10:26

score 58 · Accepted Answer · edited Dec 26 '18 at 17:22

58

This code seems working, Tested in local Environment WampServer Version 2.1

Download latest version of PHPExcel from here PHPExcel 1.7.6

Working Code.

<?php
error_reporting(E_ALL);
date_default_timezone_set('Europe/London');
require_once '../Classes/PHPExcel/IOFactory.php';
require_once '../Classes/PHPExcel.php';

$excel2 = PHPExcel_IOFactory::createReader('Excel2007');
$excel2 = $excel2->load('nTest.xlsx'); // Empty Sheet
$excel2->setActiveSheetIndex(0);
$excel2->getActiveSheet()->setCellValue('C6', '4')
    ->setCellValue('C7', '5')
    ->setCellValue('C8', '6')       
    ->setCellValue('C9', '7');

$excel2->setActiveSheetIndex(1);
$excel2->getActiveSheet()->setCellValue('A7', '4')
    ->setCellValue('C7', '5');
$objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel2007');
$objWriter->save('Nimit New.xlsx');
?>

Remove this line from your code: $excel2->setLoadAllSheets();.

Hope this will help.

edited Dec 26 '18 at 17:22

mgoubert

313
1
5
16

answered Oct 05 '11 at 06:35

Nimit Dudani

4,840
3
31
46

Thanks for reply...hi nDudani, just let you know my xlsx file(dentket.xlsx) contains macro and function (calculation), by using your code the new file (dentkey1.xlsx) is generated, but not loding with existing function or macros, I hope you understand my Question..... – Denish Oct 05 '11 at 09:44
can you upload dentket.xlsx somewhere? so I can test in real scenario – Nimit Dudani Oct 05 '11 at 10:19
yes ofcourse, do you have any idea form where (after uploading this xlsx file) you and I both can access this file..or can you just give me your emai-address i will send you immediately...Thanks a lot in advance. – Denish Oct 05 '11 at 10:27
hi nDudani, i created account in Rapidshare, Here is the link "https://rapidshare.com/files/1690632927/dentkey.xlsx" hope you understand how urgent it is for me...heartly thanking to you...waiting for your reply... – Denish Oct 05 '11 at 10:36
I don't have access of this site, you can mail me – Nimit Dudani Oct 05 '11 at 10:41
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/4023/discussion-between-ndudani-and-denish) – Nimit Dudani Oct 05 '11 at 12:20
1

Macros and User-Defined Functions are not supported in PHPExcel; and the library discards any unsupported features of a workbook when it is loaded, so they don't exist in the PHPExcel object at all... when saving, PHPExcel only saves those elements of the workbook that exist in the PHPExcel object. – Mark Baker Oct 05 '11 at 12:41
Thanks Mark, Your comment is useful for me, But can you give me other option if you are aware of this probleam.!?...Or it is not possible to do with any other library..!!! – Denish Oct 07 '11 at 04:25
@Denish, so what is final conclusion? – Nimit Dudani Oct 11 '11 at 05:25
1

hi nDudani, In PHPExce/cell.php line no 288, i just commented the exception code line, the conclusion is that my xlsx file will load with existing data but without reference withing multisheets, and calculations is also remain same...Thanks to all for help me – Denish Oct 11 '11 at 07:06
how about if the xlsx file is not empty? – perodriguezl May 27 '16 at 15:35

PHPExcel modify existing .xlsx file with multiple worksheet

1 Answers1

Linked