How to set cell padding in PHPExcel?

Question

Does anyone know how it's possible to set cell padding in PHPExcel ? Searched for 30mins and still can't find a proper solution. There is no useful documentation on this.

score 29 · Accepted Answer · answered Jul 28 '13 at 15:06

29

Maybe

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setIndent(1);

answered Jul 28 '13 at 15:06

mr.frog

426
4
7

Excellent, man! For everybody with the same problem: In PHPExcel, they call it `indentation`, not padding or margin. – Sliq Jul 28 '13 at 16:45
How to do this in LaravelExcel? – Rápli András Oct 20 '15 at 15:57
2

as of the time of writing this comment, still works in PHPSpreadsheet. – Gellie Ann Nov 17 '20 at 02:20
Could not find this under PHPSpreadsheet Docs so thank you for posting this code. Confirmed working under PHP version 7.3.28 as of this date. – Neil_Tinkerer Jul 24 '21 at 13:41

score 5 · Answer 2 · answered Dec 16 '15 at 02:23

Just to add, please note that this will only work if the horizontal alignment is set to left or right.

Should precede with this (if it has not already been done)

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

Cheers

Mark Baker · Answer 3 · 2012-09-20T18:16:07.677

0

Cell Padding is an HTML concept, and there is nothing like it in Excel. How would you set cell padding in MS Excel itself?

Probably the best you can manage is setting the cell height and width.

EDIT

I've been googling this, and even Microsoft say that there is no equivalent of cell padding in Excel. The best option seems to be using cell alignment indents (combined with column width) for horizontal padding; and row height and vertical alignment for vertical padding. These can be set in PHPExcel.

edited Sep 20 '12 at 18:16

answered Sep 20 '12 at 12:47

Mark Baker

209,507
32
346
385

5

Nope, cell padding (or "inner margin" or "content margin" etc.) is a general concept in layouts, software and documents. MS Excel, OpenOffice and LibreOffice can do this. I'm talking about the popular PHP library ["PHPExcel"](http://phpexcel.codeplex.com/) here, so this has nothing to do with HTML – Sliq Sep 20 '12 at 13:26
1

I'm trying to identify how to set an inner or content margin in MS Excel without success, nor can I find either of those terms related to spreadsheet cells (rather than page print layout, or embedded tables) in the ECMA or BIFF documentation for xlsx or xls files. I'm somewhat familiar with PHPExcel: if I'm not familiar with elements like cellpadding, then it's unlikely to be in PHPExcel... but if you can point me to the relevant parts of the documentation, I can look at implementing it. – Mark Baker Sep 20 '12 at 15:19
Ok, I'll have a look on how this is called, but IT'S THERE, i've been working with Excel for nearly 10years and done this several times. To make clear what i mean: the amount of pixels/pt from the content to the border of the cell. – Sliq Sep 20 '12 at 16:33
Lovely, downvoted for being honest. My apologies to Panique for not being as familiar with PHPExcel as he'd like me to be.... especially as he never did get back with what cell padding actually meant to him. I'm going to stop trying to answer his questions, because he seems to downvote on principle, because I can't relate his terminology to that used by Microsoft. – Mark Baker Jul 28 '13 at 17:14
Hey, just to clarify: downvoting is useful to different useful answers from wrong ones, this is nothing personal. In this case, the answer was simply wrong (beacuse you were talking about Excel, we were talking about PHPExcel, which are totally different things). And SO does not allow to correct a downvote "unless the anwser is edited". Sorry! – Sliq Oct 17 '13 at 12:02
Gee, I really do love being downvoted, when my edit of Sep 20 '12 at 18:16 predates the accepted and upvoted answer that says exactly the same thing by 10 months.... I'm beginning to get the feeling that people hate honesty here – Mark Baker Dec 10 '14 at 21:33
Can you edit your answer, then I can remove the downvote (SO doesn't allow to take back a downvote for weird reasons btw). Or delete the answer (that's wrong) to get back the point :) – Sliq Dec 11 '14 at 01:33
Well the edit that I'd originally posted on 20-Sep-2012 did say to use indents, which wasn't wrong..... and which is exactly what the upvoted and accepted answer says to do..... so I rather object to being told I'm wrong, especially when I've cited Microsofts own documentation on the subject as well – Mark Baker Dec 11 '14 at 10:20

Nafi Shahriyar · Answer 4 · 2023-04-13T07:55:17.497

I think what you need is to expand a column. I have found a solution. You can set column dimension to auto size. Here is an example

use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new Spreadsheet();
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

I have used this approach in my laravle 9 project.

Edit: Found a way to do it dynamically

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$columnIndex = 'A';

foreach ($sheet->getColumnIterator($columnIndex) as $column) {
    $columnIndex = $column->getColumnIndex();
    $maxWidth = 0;
        foreach ($column->getCellIterator() as $cell) {
            $text = $cell->getValue();
            $width = mb_strlen($text, 'UTF-8');
            $maxWidth = max($maxWidth, $width);
        }
        $spreadsheet->getActiveSheet()->getColumnDimension($columnIndex)->setWidth($maxWidth+1);
 }

How to set cell padding in PHPExcel?

4 Answers4