Explore PHPSpreadsheet cell setup

PHPSpreadsheet provides a rich API that allows you to set up lots of cell and document properties, including styles, images, dates, functions, etc. You can use it in any Excel spreadsheet you want.

When debugging your setup, make sure that the correct files are introduced and instantiated.

use PhpOfficePhpSpreadsheetSpreadsheet;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet(); 

The font

The first line sets the A7 through B7 cells to bold, Arial, 10-point font; Line 2 sets the B1 cell to bold.

$spreadsheet->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial')
    ->setSize(10);;
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); 

color

Set the text color to red.

$spreadsheet->getActiveSheet()->getStyle('A4')
    ->getFont()->getColor()->setARGB(PhpOfficePhpSpreadsheetStyleColor::COLOR_RED); 

The picture

You can load images into Excel.

$drawing = new PhpOfficePhpSpreadsheetWorksheetDrawing();
$drawing->setName('Logo');
$drawing->setDescription('Logo');
$drawing->setPath('./images/officelogo.jpg');
$drawing->setHeight(36); 

Column width

Set the width of column A to 30 characters.

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30); 

If you want to automatically calculate column widths, you can do this:

$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true); 

Set the default column width to 12.

$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12); 

Line height

Set the height of row 10 to 100pt.

$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100); 

Sets the default row height.

$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15); 

alignment

Set the A1 cell to be horizontally centered.

$styleArray = [
    'alignment' => [
        'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER,
    ],
];
$worksheet->getStyle('A1')->applyFromArray($styleArray); 

merge

Merge A18 through E22 into a single cell.

$spreadsheet->getActiveSheet()->mergeCells('A18:E22'); 

Break up

Split the merged cells.

$spreadsheet->getActiveSheet()->unmergeCells('A18:E22'); 

A border

Add a red border between B2 and G8.

$styleArray = [
    'borders' => [
        'outline' => [
            'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_THICK,
            'color' => ['argb' => 'FFFF0000'],
        ],
    ],
];
$worksheet->getStyle('B2:G8')->applyFromArray($styleArray); 

Sheet title

Sets the current sheet title.

$spreadsheet->getActiveSheet()->setTitle('Hello'); 

Date/time

Set the date format.

$spreadsheet->getActiveSheet()
    ->setCellValue('D1', '2018-06-15');

$spreadsheet->getActiveSheet()->getStyle('D1')
    ->getNumberFormat()
    ->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_DATE_YYYYMMDD2); 

A newline

Use n to wrap a cell, equivalent to (Alt +”Enter”).

$spreadsheet->getActiveSheet()->getCell('A4')->setValue("hellonworld");
$spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true); 

hyperlinks

Set the cell to hyperlink form.

$spreadsheet->getActiveSheet()->setCellValue('E6', 'www.helloweba.net');
$spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://www.helloweba.net'); 

Using the function

Calculate the SUM of cells between B5 and C5 using SUM. The same applies to other functions: MAX, MIN, AVERAGE.

$spreadsheet->getActiveSheet()
    ->setCellValue('B7', '=SUM(B5:C5)'); 

Set document properties

You can set Excel document properties.

$Spreadsheet ->getProperties() ->setCreator(" HelloWeba ") // Author ->setLastModifiedBy("Yuegg") // Last Modifier ->setTitle("Office" 2007 XLSX Test Document") // Title -> SetSubject ("Office 2007 XLSX Test Document") // Subtitle -> SetDescription ("Test Document for" Office 2007 XLSX, Generated using PHP classes.") and set keyword ("office 2007 openxml PHP "); / / classification

PHPSpreadshee also offers CSV, PDF, HTML and XML file processing interfaces in addition to Excel.

Use more set document please refer to website: https://phpspreadsheet.readthedocs.io/en/stable/.