to read and write sheets one by one from templates

Topics: Developer Forum, Project Management Forum, User Forum
May 27, 2010 at 7:27 AM



      I am new to to phpexcel, i couldn't understand how to read a sheet and write datas to one sheet at a time. When ever i try to do this  i am getting either Undefined offset eror or call method setTitle to a non member functio.

I have also posted some discussions before for which i haven't got reply.  Please help as i am new to this library and technology, i couldn't cope up.

I have 84 sheets with sheets name starting from sheet1 to sheet84.  But when i try to rename the sheets with various names like


    $objPHPExcel = PHPExcel_IOFactory::load("../excel_templates/q4full.xls");
    foreach($countryArray as $ck =>$cv) {


sometumes the last sheet i.e., sheet82 changes.


Please help me and try to come out of this problem



May 27, 2010 at 9:08 AM
$objPHPExcel = new PHPExcel();
foreach($countryArray as $ck =>$cv) {
    $sheet = $objPHPExcel->createSheet($ck);
    $sheet->setCellValue('A1', $cv.' '.$ck);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
May 27, 2010 at 11:04 AM

Thanks Mark,


                       But i just want to have it from a template say 10 sheets for 10 countries and i don't know how to migrate from one sheet to another sheet t write data in each sheet. 

                       That is my problem. I know this is bit silly but i am trying for  more than 2 days

                       Please try to provide the solution



May 27, 2010 at 11:05 PM

where $index is the sheet index



where $title is the sheet name

May 28, 2010 at 8:03 AM

  Thanks Mark,

                        With your suggestion to me and by seeing your previous posts to other developers i tried to manage some what and  could now  work with templates. But the problem now my template sheet consists of six sheets

from which i am trying to generate 86 sheets .  The first sheet wwill be cloned 82 times and the rest were copied once . But i could make this  only for 85 sheets after which i a getting this message

<b>Fatal error</b>:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 3 bytes) in <b>D:\projects\VRM\pfizerCRS\pfizer_crash_report\PHPExcel\Classes\PHPExcel\Worksheet.php</b> 
on line
<b>2485</b><br />

but my ini_set level is 1024M

For your convenience i have given some code part

Can u help me


    * File : dummy.php
    * created for cloning the xls worksheets dynamically and to fetch records
    * */    
     // include the configuartion file
   //Declaration of variables
    $i = 0;
    $countrySheet = "";
    $tcoSheet = "";
    $tcoAnnual = "";
    $CO2Sheet = "";
    $safetySheet = "";
    $imburseSheet = "";
    $sheet = "";
    $sheetTCOQuarter = "";
    $sheetTCOAnnual = "";
    $sheetSafety = "";
    $sheetCO2 = "";
    $sheetImburse = "";
        $objPHPExcel = PHPExcel_IOFactory::load("../excel_templates/report.xls");
        //Get the copy of all the sheets in the template.
        $countrySheet = $objPHPExcel->getSheetByName('Sheet1')->copy();
        $tcoSheet = $objPHPExcel->getSheetByName('Sheet2')->copy();
        $tcoAnnual = $objPHPExcel->getSheetByName('Sheet3')->copy();
        $CO2Sheet = $objPHPExcel->getSheetByName('Sheet4')->copy();
        $safetySheet = $objPHPExcel->getSheetByName('Sheet5')->copy();
        $imburseSheet = $objPHPExcel->getSheetByName('Sheet6')->copy();
        //disconnect all existing sheets

    // looping with different quarters and countries
    foreach($countryArray as $ck =>$cv) {
        //Sheets are cloned here and 82 tabs are created and one for each country
        $sheet = "";
        $sheet = clone $countrySheet;
    }    //unset the copied sheet which has been created for countries
        // Here the sheet is cloned for Tco quarterly report
        $sheetTCOQuarter = clone $tcoSheet;
        $sheetTCOQuarter->setTitle("TCO Report quarterly");
        //Here the sheet is cloned for Tco Annual report
        $sheetTCOAnnual = clone $tcoAnnual;
        $sheetTCOAnnual->setTitle("TCO Annual Report");
        //Here the sheet is cloned for CO2 report
        $sheetCO2 = clone $CO2Sheet;
        $sheetCO2->setTitle("CO2 Report");
        //Here the sheet is cloned for Safety report
        $sheetSafety = clone $safetySheet;
        $sheetSafety->setTitle("Safety Report");
        //Here the sheet is cloned for Reimbursement report
        $sheetImburse = clone $imburseSheet;
        $sheetImburse->setTitle("Re-Imbursement Report");