[카테고리:] Works

$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 

설치완료

어드민 콘솔 접속 주소

https://www.google.com/recaptcha/admin

HTML 폼작성하기

<!-- recaptcha element -->
<script src="https://www.google.com/recaptcha/api.js?onload=onloadCallback&render=explicit" async defer></script>
<input type="hidden" id="recaptcha_response" name="recaptcha_response" value="" />
<div id="recaptcha_area"></div>

<script type="text/javascript">
var verify_value = "";
var onloadCallback = function() {
	grecaptcha.render('recaptcha_area', {
		'sitekey' : '{어드민 콘솔에서 발급받은 사이트키}',
		'callback' : function(response) {
			if (response != "") {
				verify_value = response;
			}
		},
		'theme' : 'light'
	});
};
</script>
<!-- //recaptcha element -->

<script>
// 폼서브밋시
$("#recaptcha_response").val(verify_value);
</script>

FORM DATA 처리 (php)

<?php
// recaptcha 확인
include_once 'Snoopy.class.php';
$snoopy = new Snoopy;
$data = array(
	"secret" => '어드민 콘솔에서 발급받은 시크릿키',
	"response" => $recaptcha_response
);
$snoopy->submit('https://www.google.com/recaptcha/api/siteverify', $data);
$response = json_decode($snoopy->results);
if (!$response->success) {
	// fail
	exit;
} else {
	// success
}

Snoopy.class.php

https://github.com/endroy/Snoopy