PhpSpreadsheet – Tips

$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');

배경색 지정하기

phpspreadsheet 설치하기

# 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 

설치완료

Let’s Encrypt (Certbot)으로 SSL 인증서 발급

Let’s Encrypt

Let’s Encrypt 에서 발급하는 90일짜리 무료 인증서입니다. 3개월에 한번씩 인증을 해야하지만 자동업데이트를 이용하면 쉽게 사용할 수 있습니다.

Certbot 설치

$ snap install --classic certbot

우분투 18.04 기준 snap로 certbot를 설치합니다.

SSL 인증서 발급 받기

// 아파치
$ certbot --apache

// nginx
$ certbot --nginx

certbot 명령을 이용해서 SSL인증서를 발급받습니다. 옵션을 이용해서 웹서버를 지정합니다.

Plugins selected: Authenticator apache, Installer apache
 Enter email address (used for urgent renewal and security notices)
  (Enter 'c' to cancel):

이메일주소를 입력합니다. 인증서 만료전에 이메일로 알려줍니다.

Please read the Terms of Service at
 https://letsencrypt.org/documents/LE-SA-v1.2-November-15-2017.pdf. You must
 agree in order to register with the ACME server. Do you agree?
 
 (Y)es/(N)o: Y
 
 Would you be willing, once your first certificate is successfully issued, to
 share your email address with the Electronic Frontier Foundation, a founding
 partner of the Let's Encrypt project and the non-profit organization that
 develops Certbot? We'd like to send you email about our work encrypting the web,
 EFF news, campaigns, and ways to support digital freedom.
 
 (Y)es/(N)o: Y
 Account registered.

이용약관에 동의합니다.

Which names would you like to activate HTTPS for?
 
 1: jongwan.com
 2: www.jongwan.com
 
 Select the appropriate numbers separated by commas and/or spaces, or leave input
 blank to select all options shown (Enter 'c' to cancel):

인증서를 발급받을 도메인을 선택합니다. 공백으로 엔터를 누르면 모두 발급합니다. jongwan.com, www.jongwan.com을 모두 받으므로 그냥 엔터를 입력합니다.

Requesting a certificate for jongwan.com and www.jongwan.com
 Performing the following challenges:
 http-01 challenge for jongwan
 http-01 challenge for www.jongwan
 Waiting for verification…
 Cleaning up challenges
 Created an SSL vhost at /etc/apache2/sites-available/jongwan_com-le-ssl.conf
 Deploying Certificate to VirtualHost /etc/apache2/sites-available/jongwan_com-le-ssl.conf
 Enabling available site: /etc/apache2/sites-available/jongwan_com-le-ssl.conf
 Deploying Certificate to VirtualHost /etc/apache2/sites-available/jongwan_com-le-ssl.conf
 Redirecting vhost in /etc/apache2/sites-enabled/jongwan_com.conf to ssl vhost in /etc/apache2/sites-available/jongwan_com-le-ssl.conf
 
 Congratulations! You have successfully enabled https://jongwan.com and
 https://www.jongwan.com
 
 Subscribe to the EFF mailing list (email: me@jongwan.com).
 IMPORTANT NOTES:
 Congratulations! Your certificate and chain have been saved at:
 /etc/letsencrypt/live/jongwan.com/fullchain.pem
 Your key file has been saved at:
 /etc/letsencrypt/live/jongwan.com/privkey.pem
 Your certificate will expire on 2021-04-15. To obtain a new or
 tweaked version of this certificate in the future, simply run
 certbot again with the "certonly" option. To non-interactively
 renew all of your certificates, run "certbot renew"
 If you like Certbot, please consider supporting our work by:
 Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
 Donating to EFF:                    https://eff.org/donate-le 

인증서를 발급중입니다.

인증서 자동갱신하기

$ certbot renew

renew 옵션을 이용해서 인증서를 자동갱신할 수 있습니다.
–dry-run 옵션을 추가하면 발급 테스트도 가능합니다.

Saving debug log to /var/log/letsencrypt/letsencrypt.log
 
 Processing /etc/letsencrypt/renewal/jongwan.com.conf
 
 Cert not yet due for renewal
 
 The following certificates are not due for renewal yet:
   /etc/letsencrypt/live/jongwan.com/fullchain.pem expires on 2021-04-15 (skipped)
 No renewals were attempted. 

실행해보면 아래처럼 나옵니다. 방금 인증서를 받았기 때문에 갱신이 되지는 않고 메시지만 출력됩니다.

$ crontab -e
// 매일 0시 업데이트 진행
0 0 * * * sudo certbot renew

crontab에 등록해서 자동으로 갱신하도록 합니다.

TinyMCE 에디터 적용하기

TinyMCE 홈페이지 > Quick start

https://www.tiny.cloud/docs/quick-start/

샘플코드

<textarea id="note" class="note">내용을 입력하세요</textarea>
<script src="https://cdn.tiny.cloud/1/no-api-key/tinymce/5/tinymce.min.js" referrerpolicy="origin"></script>
<script>
tinymce.init({
     selector: '#note',
     plugins: 'print preview powerpaste casechange importcss tinydrive searchreplace autolink autosave save directionality advcode visualblocks visualchars fullscreen image link media mediaembed template codesample table charmap hr pagebreak nonbreaking anchor toc insertdatetime advlist lists checklist wordcount tinymcespellchecker a11ychecker imagetools textpattern noneditable help formatpainter permanentpen pageembed charmap tinycomments mentions quickbars linkchecker emoticons advtable',
     menu: {
         tc: {
             title: 'TinyComments',
             items: 'addcomment showcomments deleteallconversations'
         }
     },
     menubar: 'file edit view insert format tools table tc help',
     toolbar: 'undo redo | bold italic underline strikethrough | fontselect fontsizeselect formatselect | alignleft aligncenter alignright alignjustify | outdent indent |  numlist bullist checklist | forecolor backcolor casechange permanentpen formatpainter removeformat | pagebreak | charmap emoticons | fullscreen  preview save print | insertfile image media pageembed template link anchor codesample | showcomments addcomment',
     autosave_ask_before_unload: true,
     toolbar_mode: 'floating',
     tinycomments_mode: 'embedded',
     tinycomments_author: 'Author name',
     height: '100%'
 });
</script>

API Key

API 키는 My Account에서 가입후 발급이 가능
발급후 도메인을 등록하고 no-api-key 부분을 교체해서 사용이 가능

google reCAPTCHA

어드민 콘솔 접속 주소

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

xcp-ng 설치후 iptables 수정

XCP-ng 접속을 아이피로 제한

보안을 위해서 XCP-ng에 접근하는 아이피를 제한한다. 80, 443 포트의 경우 XCP-ng Center로 접근시 사용하기 때문에 해당 아이피만 허용해준다.

XCP-ng Center에서 콘솔로 접속후 iptables 수정

# vi /etc/sysconfig/iptables

-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m udp -p udp --dport 694 -j ACCEPT
-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 443 -j ACCEPT

아래처럼 -s {허용할 아이피} 를 추가해서 접근을 막는다

-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m udp -p udp --dport 694 -j ACCEPT
-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m tcp -p tcp -s {허용할 아이피} --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m tcp -p tcp -s {허용할 아이피} --dport 80 -j ACCEPT
-A RH-Firewall-1-INPUT -m conntrack --ctstate NEW -m tcp -p tcp -s {허용할 아이피} --dport 443 -j ACCEPT

# service iptables restart

C# – ComboBox 데이터바인딩

C# 콤보박스에 데이터를 바인딩하기
var list = new BindingList<KeyValuePair<int, string>>();

list.Add(new KeyValuePair<string, string>(0, "Select Color"));
list.Add(new KeyValuePair<string, string>(1, "Red"));
list.Add(new KeyValuePair<string, string>(2, "Blue"));
list.Add(new KeyValuePair<string, string>(3, "White"));
list.Add(new KeyValuePair<string, string>(4, "Black"));
list.Add(new KeyValuePair<string, string>(5, "Green"));

cbColor.DataSource = list;
cbColor.ValueMember = "Key";
cbColor.DisplayMember = "Value";
cbColor.SelectedIndex = 0;
콤보박스 선택하기
cbColor.SelectedValue = 3;
cbColor.Text = "Blue";
cbColor.SelectedValue = cbColor.FindString("Red");

라즈베리파이 HDMI

라즈베리파이의 HDMI에 연결된 출력장치를 켜거나 끌경우 사용하는 명령

https://www.raspberrypi.org/documentation/raspbian/applications/tvservice.md

켜기

tvservice -p
(vcgencmd display_power 1)

끄기

tvservice -o
(vcgencmd display_power 0)

모니터링

tvservice -M

-p, -o 명령의 경우 실행하면 화면이 검은색으로 변했다가 복구된다.
때문에 화면만 껐다켜는 경우는 vcgencmd display_power를 사용해야한다.