PHPExcel - creating multiple sheets by iteration

Question

I m trying to create multiple sheets by iteration in phpexcel    i 0   while   i  lt  10        Add new sheet  objWorkSheet    objPHPExcel- gt createSheet         Attach the newly-cloned sheet to the  objPHPExcel workbook  objPHPExcel- gt addSheet  objWorkSheet       Add some data  objPHPExcel- gt setActiveSheetIndex  i     sheet    objPHPExcel- gt getActiveSheet      sheet- gt setCellValue  A1    Hello   i          - gt setCellValue  B2    world            - gt setCellValue  C1    Hello           - gt setCellValue  D2    world         Rename sheet  sheet- gt setTitle  i     i        Unfortunately this doesn t work  I only get some sheets of this iteration filled with data and renamed and about the half are empty   So this is the result  sheet titles    0  2  4  6  8  9  and 5 empty sheets  I can t figure out why only even numbered  and sheet 9  are correct in the result

User · Answer

Complementing the coment of  Mark Baker  Do as follow      titles   array  title 1    title 2     sheet   0  foreach  array as  value       if  sheet  gt  0            objPHPExcel- gt createSheet             sheet    objPHPExcel- gt setActiveSheetIndex  sheet            sheet- gt setTitle   value              Do you want something more here      else           objPHPExcel- gt setActiveSheetIndex 0 - gt setTitle   value               sheet          This worked for me  And hope it works for those who need

User · Answer

When you first instantiate the  objPHPExcel  it already has a single sheet  sheet 0   you re then adding a new sheet  which will become sheet 1   but setting active sheet to sheet  i  when  i is 0     so you re renaming and populating the original worksheet created when you instantiated  objPHPExcel rather than the one you ve just added    this is your title  0    You re also using the createSheet   method  which both creates a new worksheet and adds it to the workbook    but you re also adding it again yourself which is effectively adding the sheet in two position   So first iteration  you already have sheet0  add a new sheet at both indexes 1 and 2  and edit title sheet 0  Second iteration  you add a new sheet at both indexes 3 and 4  and edit title sheet 1  but because you have the same sheet at indexes 1 and 2 this effectively writes to the sheet at index 2  Third iteration  you add a new sheet at indexes 5 and 6  and edit title sheet 2  overwriting your earlier editing titleing of sheet 1 which acted against sheet 2 instead     and so on

User · Answer

You can write different sheets as follows   objPHPExcel   new PHPExcel     objPHPExcel- gt getProperties  - gt setCreator  creater     objPHPExcel- gt getProperties  - gt setLastModifiedBy  Middle field     objPHPExcel- gt getProperties  - gt setSubject  Subject     objWorkSheet    objPHPExcel- gt createSheet     work sheet count 3   number of sheets you want to create  work sheet 0  while  work sheet lt   work sheet count         if  work sheet  0             objWorkSheet- gt setTitle  Worksheet work sheet              objPHPExcel- gt setActiveSheetIndex  work sheet - gt setCellValue  A1    SR No  In sheet 1  - gt getStyle  A1  - gt getFont  - gt setBold true             objPHPExcel- gt setActiveSheetIndex  work sheet - gt setCellValueByColumnAndRow  col     row     i      setting value by column and row indexes if needed             if  work sheet  1             objWorkSheet- gt setTitle  Worksheet work sheet              objPHPExcel- gt setActiveSheetIndex  work sheet - gt setCellValue  A1    SR No  In sheet 2  - gt getStyle  A1  - gt getFont  - gt setBold true             objPHPExcel- gt setActiveSheetIndex  work sheet - gt setCellValueByColumnAndRow  col     row     i      setting value by column and row indexes if needed             if  work sheet  2             objWorkSheet    objPHPExcel- gt createSheet  work sheet count             objWorkSheet- gt setTitle  Worksheet work sheet              objPHPExcel- gt setActiveSheetIndex  work sheet - gt setCellValue  A1    SR No  In sheet 3  - gt getStyle  A1  - gt getFont  - gt setBold true             objPHPExcel- gt setActiveSheetIndex  work sheet - gt setCellValueByColumnAndRow  col     row     i      setting value by column and row indexes if needed              work sheet        filename  file-name    xls     save our workbook as this file name header  Content-Type  application vnd ms-excel      mime type header  Content-Disposition  attachment filename     filename         tell browser what s the file name header  Cache-Control  max-age 0      no cach   objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel5     objWriter- gt save  php   output

User · Answer

In case you haven t come to a conclusion    I took Henrique s answer and gave a better logic solution  This is completely compatible with PHPSpreadSheet in case someone is using PHPSpreadSheet or PHPExcel    spreadOrPhpExcel   new SpreadSheet       or new PHPExcel    print in sheet  spreadOrPhpExcel    function print in sheet  spread         sheet   0       foreach  getData   as  report   gt   value               If number of sheet is 0 then no new worksheets are created         if   sheet  gt  0                 spread- gt createSheet                         Index for the worksheet is setted and a title is assigned          wSheet    spread- gt setActiveSheetIndex  sheet - gt setTitle  report              Printing data          wSheet- gt setCellValue  A1    Hello World                Index number is incremented for the next worksheet          sheet               return  spread

User · Answer

You dont need call addSheet   method  After creating sheet  it already add to excel  Here i fixed some codes         First sheet      sheet    objPHPExcel- gt getActiveSheet           Start adding next sheets      i 0      while   i  lt  10              Add new sheet        objWorkSheet    objPHPExcel- gt createSheet  i     Setting index when creating          Write cells        objWorkSheet- gt setCellValue  A1    Hello   i                     - gt setCellValue  B2    world                       - gt setCellValue  C1    Hello                      - gt setCellValue  D2    world               Rename sheet        objWorkSheet- gt setTitle   i            i

[php] PHPExcel - creating multiple sheets by iteration

Examples related to php

Examples related to phpexcel