PHPExcel auto size column width

Question

I m trying to auto size the columns of my sheet   I m writing the file and in the end I try to resize all of my columns      Add some data  objPHPExcel- gt setActiveSheetIndex 0              - gt setCellValue  B1    test1111111111111111111111               - gt setCellValue  C1    test1111111111111               - gt setCellValue  D1    test1111111               - gt setCellValue  E1    test11111               - gt setCellValue  F1    test1               - gt setCellValue  G1    test1     foreach  objPHPExcel- gt getActiveSheet  - gt getColumnDimension   as  col         col- gt setAutoSize true      objPHPExcel- gt getActiveSheet  - gt calculateColumnWidths      The above code doesn t work  Doesn t change the column size to fit the text  UPDATE The writer I m using  objWriter   PHPExcel IOFactory  createWriter  objPHPExcel   Excel5

User · Answer

foreach range  B   G   as  columnID         objPHPExcel- gt getActiveSheet  - gt getColumnDimension  columnID - gt setAutoSize true

User · Answer

In case somebody was looking for this   The resolution below also works on PHPSpreadsheet  their new version of PHPExcel      assuming  spreadsheet is instance of PhpOffice PhpSpreadsheet Spreadsheet    assuming  worksheet    spreadsheet- gt getActiveSheet    foreach range  A   worksheet- gt getHighestColumn    as  column         spreadsheet- gt getColumnDimension  column - gt setAutoSize true          Note  getHighestColumn   can be replaced with getHighestDataColumn   or the last actual column     What these methods do   getHighestColumn  row   null  - Get highest worksheet column   getHighestDataColumn  row   null  - Get highest worksheet column that contains data   getHighestRow  column   null  - Get highest worksheet row  getHighestDataRow  column   null  -  Get highest worksheet row that contains data

User · Answer

If a column is set to AutoSize  PHPExcel attempts to calculate the column width based on the calculated value of the column  so on the result of any formulae   and any additional characters added by format masks such as thousand separators    By default  this is an estimated width  a more accurate calculation method is available  based on using GD  which can also handle font style features such as bold and italic  but this is a much bigger overhead  so it is turned off by default  You can enable the more accurate calculation using  PHPExcel Shared Font  setAutoSizeMethod PHPExcel Shared Font  AUTOSIZE METHOD EXACT     However  autosize doesn t apply to all Writer formats    for example CSV  You don t mention what writer you re using   But you also need to identify the columns to set dimensions   foreach range  B   G   as  columnID         objPHPExcel- gt getActiveSheet  - gt getColumnDimension  columnID          - gt setAutoSize true        objPHPExcel- gt getActiveSheet  - gt getColumnDimension   expects a column ID    objPHPExcel- gt getActiveSheet  - gt getColumnDimensions   will return an array of all the defined column dimension records  but unless a column dimension record has been explicitly created  perhaps by loading a template  or by manually calling getColumnDimension    then it won t exist  memory saving

User · Answer

If you need to do that on multiple sheets  and multiple columns in each sheet  here is how you can iterate through all of them      Auto size columns for each worksheet foreach   objPHPExcel- gt getWorksheetIterator   as  worksheet          objPHPExcel- gt setActiveSheetIndex  objPHPExcel- gt getIndex  worksheet          sheet    objPHPExcel- gt getActiveSheet         cellIterator    sheet- gt getRowIterator  - gt current  - gt getCellIterator         cellIterator- gt setIterateOnlyExistingCells true            var PHPExcel Cell  cell        foreach   cellIterator as  cell             sheet- gt getColumnDimension  cell- gt getColumn   - gt setAutoSize true

User · Answer

All of these answers kinda suck    do not use range   it wont work beyond column Z  Simply use   sheet    spreadsheet- gt getActiveSheet    foreach   sheet- gt getColumnIterator   as  column         sheet- gt getColumnDimension  column- gt getColumnIndex   - gt setAutoSize true      Do this after you have written your data so that the column iterator knows how many columns to iterate over

User · Answer

col    A   while true        tempCol    col         objPHPExcel- gt getActiveSheet  - gt getColumnDimension  tempCol - gt setAutoSize true       if  tempCol     objPHPExcel- gt getActiveSheet  - gt getHighestDataColumn             break

User · Answer

Auto-size columns for all worksheets foreach   objPHPExcel- gt getWorksheetIterator   as  worksheet        foreach   worksheet- gt getColumnIterator   as  column             worksheet             - gt getColumnDimension  column- gt getColumnIndex                - gt setAutoSize true

User · Answer

for phpspreadsheet    sheet    spreadsheet- gt getActiveSheet        spreadsheet is instance of PhpOffice PhpSpreadsheet Spreadsheet  foreach       range           1            Coordinate  columnIndexFromString  sheet- gt getHighestColumn 1         as  column          sheet           - gt getColumnDimension Coordinate  stringFromColumnIndex  column             - gt setAutoSize true

User · Answer

for   i    A    i      objPHPExcel- gt getActiveSheet  - gt getHighestColumn     i           objPHPExcel- gt getActiveSheet  - gt getColumnDimension  i - gt setAutoSize TRUE

User · Answer

For Spreedsheet   PHP 7  you must write instead of PHPExcel Cell  columnIndexFromString   PhpOffice PhpSpreadsheet Cell  columnIndexFromString  And at the loop is a mistake  there you must  lt  not work with  lt    Otherwise  he takes a column too much into the loop

User · Answer

Here a more flexible variant based on  Mark Baker post   foreach  range  A    phpExcelObject- gt getActiveSheet  - gt getHighestDataColumn    as  col             phpExcelObject- gt getActiveSheet                   - gt getColumnDimension  col                  - gt setAutoSize true            Hope this helps

User · Answer

If you try to iterate with for   col   2   col  lt    AC       col        or with foreach range  A   AC   as  col        it will work for columns from A to Z  but it fails pass the Z  Ex  iterate between  A  to  AC     In order to iterate pass  Z   you need to convert the column to integer  increment  compare  and get it as string again    MAX COL    sheet- gt getHighestDataColumn     MAX COL INDEX   PHPExcel Cell  columnIndexFromString  MAX COL       for  index 0    index  lt    MAX COL INDEX    index          col   PHPExcel Cell  stringFromColumnIndex  index           do something  like set the column width         sheet- gt getColumnDimension  col - gt setAutoSize TRUE       With this  you easy iterate pass the  Z  column and set autosize to every column

User · Answer

This code snippet will auto size all the columns that contain data in all the sheets  There is no need to use the activeSheet getter and setter      In my case this line didn t make much of a difference PHPExcel Shared Font  setAutoSizeMethod PHPExcel Shared Font  AUTOSIZE METHOD EXACT      Iterating all the sheets      var PHPExcel Worksheet  sheet    foreach   objPHPExcel- gt getAllSheets   as  sheet           Iterating through all the columns        The after Z column problem is solved by using numeric columns  thanks to the columnIndexFromString method     for   col   0   col  lt   PHPExcel Cell  columnIndexFromString  sheet- gt getHighestDataColumn      col               sheet- gt getColumnDimensionByColumn  col - gt setAutoSize true

User · Answer

you also need to identify the columns to set dimensions   foreach  range  A    phpExcelObject- gt getActiveSheet  - gt getHighestDataColumn    as  col     phpExcelObject         - gt getActiveSheet           - gt getColumnDimension  col          - gt setAutoSize true

User · Answer

This is example how to use all columns from worksheet    sheet    PHPExcel- gt getActiveSheet     cellIterator    sheet- gt getRowIterator  - gt current  - gt getCellIterator     cellIterator- gt setIterateOnlyExistingCells  true         var PHPExcel Cell  cell    foreach   cellIterator as  cell              sheet- gt getColumnDimension   cell- gt getColumn    - gt setAutoSize  true

User · Answer

Come late  but after searching everywhere  I ve created a solution that seems to be  the one    Being known that there is a column iterator on last API versions  but not knowing how to atuoadjust the column object it self  basically I ve created a loop to go from real first used column to real last used one   Here it goes     Just before saving de Excel document  you do this   PHPExcel Shared Font  setAutoSizeMethod PHPExcel Shared Font  AUTOSIZE METHOD EXACT      We get the util used space on worksheet  Change getActiveSheet to setActiveSheetIndex 0  to choose the sheet you want to autosize  Iterate thorugh em if needed    We remove all digits from this string  which cames in a form of  A1 G24     Exploding via     to get a 2 position array being 0 fisrt used column and 1  the last used column   cols   explode      trim preg replace    d  u        objPHPExcel- gt getActiveSheet  - gt calculateWorksheetDimension         col    cols 0     first util column with data  end      cols 1     last util column with data  1  to use it inside the WHILE loop  Else  is not going to use last util range column  while  col     end        objPHPExcel- gt getActiveSheet  - gt getColumnDimension  col - gt setAutoSize true         col         Saving   objWriter- gt save  php   output

[php] PHPExcel auto size column width

Examples related to php

Examples related to resize

Examples related to size

Examples related to phpexcel