Jongwan.com

$spreadsheet = new Spreadsheet();
$spreadsheet->getProperties()->setCreator('jongwan')
     ->setTitle('')
     ->setSubject('')
     ->setDescription('');

PhpSpreadsheet 초기화하기

$styleArray = [
     'borders' => [
         'allBorders' => [
             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
         ],
         'outline' => [
             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK
         ]
     ]
 ];
 $spreadsheet->getActiveSheet()->getStyle('A1:D10')->applyFromArray($styleArray);

외곽선과 내부선을 스타일 지정하는 방법

// 숫자형 셀스타일
$spreadsheet->getActiveSheet()->setCellValueExplicit('A1', '내용', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC);

// 문자형 셀스타일
$spreadsheet->getActiveSheet()->setCellValueExplicit('B1', '내용', \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

// 지정없이 입력
$spreadsheet->getActiveSheet()->setCellValue('C1', '내용');

셀에 데이터타입을 지정하기

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="filename.xls"');
header('Cache-Control: no-cache');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT+9'); // always modified
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');

파일로 다운로드하기

// 첫번째 시트 선택 (0번부터 시작)
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->setTitle('sheet name');

// 시트 생성하기
$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->setTitle("상세내역");

시트 생성, 선택, 이름지정하기

$spreadsheet->getActiveSheet()->mergeCells('A1:D2');

셀합치기(merge)

$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_BOTTOM);


$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

셀내용 정렬하기

$spreadsheet->getActiveSheet()->getStyle('A1:D5')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setRGB('dddddd');

배경색 지정하기

# apt install composer
# composer require phpoffice/phpspreadsheet

컴포져로 phpspreadsheet 설치하기

Do not run Composer as root/super user! See https://getcomposer.org/root for details
 Warning from https://packagist.org: You are using an outdated version of Composer. Composer 2.0 is now available and you should upgrade. See https://getcomposer.org/2
 Using version ^1.16 for phpoffice/phpspreadsheet
 ./composer.json has been created
 Loading composer repositories with package information
 Warning from https://packagist.org: You are using an outdated version of Composer. Composer 2.0 is now available and you should upgrade. See https://getcomposer.org/2
 Updating dependencies (including require-dev)
 Your requirements could not be resolved to an installable set of packages.
 Problem 4
     - Installation request for phpoffice/phpspreadsheet ^1.16 -> satisfiable by phpoffice/phpspreadsheet[1.16.0].
     - phpoffice/phpspreadsheet 1.16.0 requires ext-dom * -> the requested PHP extension dom is missing from your system.
     - phpoffice/phpspreadsheet 1.16.0 requires ext-gd * -> the requested PHP extension gd is missing from your system. 
 To enable extensions, verify that they are enabled in your .ini files:
     - phpoffice/phpspreadsheet 1.16.0 requires ext-mbstring * -> the requested PHP extension mbstring is missing from your system. 
     - phpoffice/phpspreadsheet 1.16.0 requires ext-zip * -> the requested PHP extension zip is missing from your system. 
     - /etc/php/7.2/cli/php.ini
     - /etc/php/7.2/cli/conf.d/10-mysqlnd.ini
     - /etc/php/7.2/cli/conf.d/10-opcache.ini
     - /etc/php/7.2/cli/conf.d/10-pdo.ini
     - /etc/php/7.2/cli/conf.d/20-calendar.ini
     - /etc/php/7.2/cli/conf.d/20-ctype.ini
     - /etc/php/7.2/cli/conf.d/20-exif.ini
     - /etc/php/7.2/cli/conf.d/20-fileinfo.ini
     - /etc/php/7.2/cli/conf.d/20-ftp.ini
     - /etc/php/7.2/cli/conf.d/20-gettext.ini
     - /etc/php/7.2/cli/conf.d/20-iconv.ini
     - /etc/php/7.2/cli/conf.d/20-json.ini
     - /etc/php/7.2/cli/conf.d/20-mysqli.ini
     - /etc/php/7.2/cli/conf.d/20-pdo_mysql.ini
     - /etc/php/7.2/cli/conf.d/20-phar.ini
     - /etc/php/7.2/cli/conf.d/20-posix.ini
     - /etc/php/7.2/cli/conf.d/20-readline.ini
     - /etc/php/7.2/cli/conf.d/20-shmop.ini
     - /etc/php/7.2/cli/conf.d/20-sockets.ini
     - /etc/php/7.2/cli/conf.d/20-sysvmsg.ini
     - /etc/php/7.2/cli/conf.d/20-sysvsem.ini
     - /etc/php/7.2/cli/conf.d/20-sysvshm.ini
     - /etc/php/7.2/cli/conf.d/20-tokenizer.ini
   You can also run php --ini inside terminal to see which files are used by PHP in CLI mode.
 Installation failed, deleting ./composer.json.

# apt install -y php-xml php-gd php-mbstring php-zip

오류메시지를 확인하고 설치되지 않은 패키지를 설치한다.

Do not run Composer as root/super user! See https://getcomposer.org/root for details
 Warning from https://packagist.org: You are using an outdated version of Composer. Composer 2.0 is now available and you should upgrade. See https://getcomposer.org/2
 Using version ^1.16 for phpoffice/phpspreadsheet
 ./composer.json has been created
 Loading composer repositories with package information
 Warning from https://packagist.org: You are using an outdated version of Composer. Composer 2.0 is now available and you should upgrade. See https://getcomposer.org/2
 Updating dependencies (including require-dev)
 Package operations: 11 installs, 0 updates, 0 removals
 Installing ezyang/htmlpurifier (v4.13.0): Downloading (100%)
 Installing psr/http-message (1.0.1): Downloading (100%)
 Installing psr/http-factory (1.0.1): Downloading (100%)
 Installing psr/http-client (1.0.1): Downloading (100%)
 Installing psr/simple-cache (1.0.1): Downloading (100%)
 Installing markbaker/matrix (2.1.1): Downloading (100%)
 Installing markbaker/complex (2.0.0): Downloading (100%)
 Installing myclabs/php-enum (1.7.7): Downloading (100%)
 Installing symfony/polyfill-mbstring (v1.22.0): Downloading (100%)
 Installing maennchen/zipstream-php (2.1.0): Downloading (100%)
 Installing phpoffice/phpspreadsheet (1.16.0): Downloading (100%)
 phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
 phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer (doesn't yet support PHP8))
 phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer (doesn't yet support PHP8))
 phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
 Writing lock file
 Generating autoload files 

설치완료