PHPExcel 셀 서식 설정, 사용자 지정 형식 설정

필요성

• 숫자 셀끼리의 연산 등 수식 적용을 위해서(NUMERIC, FORMULA)
• 회계 등에서 천 단위 콤마(number_format) 또는 날짜 형식(yyyy-mm-dd)의 표현
• 0으로 시작하는 숫자 등 '사용자가 오롯이 입력한 문자열 그대로 표현'

셀 서식 지정에는 크게 두 가지 방법이 있는데 아래에 두 번째로 정리한 방법이 더 유용했다고 본다.

 

셀 서식 설정하기

관련 클래스 파일

• lib/PHPExcel/Cell/DataType.php (상수 부분만 아래에 내용 기재)

lib/PHPExcel/Cell/DataType.php

class PHPExcel_Cell_DataType
{
    /* Data types */
    const TYPE_STRING2  = 'str';
    const TYPE_STRING   = 's';
    const TYPE_FORMULA  = 'f';
    const TYPE_NUMERIC  = 'n';
    const TYPE_BOOL     = 'b';
    const TYPE_NULL     = 'null';
    const TYPE_INLINE   = 'inlineStr';
    const TYPE_ERROR    = 'e';
    // 생략

사용 방법

$excel->getActiveSheet()->setCellValueExplicit("A1", $price, PHPExcel_Cell_DataType::TYPE_NUMERIC);
$excel->getActiveSheet()->getCell("A1")->setValue("10000");

 

위 방법은 MS Office Excel 상에서 보았을 때(아래 이미지) 셀 서식을 좌측 범주 중에서 설정하는 것이다(일반, 숫자, 통화, 회계, 날짜...). 이 방법만으로는 모든 상황에 대응하기에는 부족하다.

 

EXCEL 컨텍스트 메뉴 - 셀 서식 설정 화면

 

아래에 정리한 방법이 더해져야 실효성이 있었다.

 

셀 서식을 '사용자 지정'으로 설정하고 세부 형식 활용하기

관련 클래스 파일

• lib/PHPExcel/Style/NumberFormat.php (상수 부분만 아래에 내용 기재)

lib/PHPExcel/Style/NumberFormat.php

class PHPExcel_Style_NumberFormat extends PHPExcel_Style_Supervisor implements PHPExcel_IComparable
{
    /* Pre-defined formats */
    const FORMAT_GENERAL                 = 'General';

    const FORMAT_TEXT                    = '@';

    const FORMAT_NUMBER                  = '0';
    const FORMAT_NUMBER_00               = '0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';

    const FORMAT_PERCENTAGE              = '0%';
    const FORMAT_PERCENTAGE_00           = '0.00%';

    const FORMAT_DATE_YYYYMMDD2          = 'yyyy-mm-dd';
    const FORMAT_DATE_YYYYMMDD           = 'yy-mm-dd';
    const FORMAT_DATE_DDMMYYYY           = 'dd/mm/yy';
    const FORMAT_DATE_DMYSLASH           = 'd/m/y';
    const FORMAT_DATE_DMYMINUS           = 'd-m-y';
    const FORMAT_DATE_DMMINUS            = 'd-m';
    const FORMAT_DATE_MYMINUS            = 'm-y';
    const FORMAT_DATE_XLSX14             = 'mm-dd-yy';
    const FORMAT_DATE_XLSX15             = 'd-mmm-yy';
    const FORMAT_DATE_XLSX16             = 'd-mmm';
    const FORMAT_DATE_XLSX17             = 'mmm-yy';
    const FORMAT_DATE_XLSX22             = 'm/d/yy h:mm';
    const FORMAT_DATE_DATETIME           = 'd/m/y h:mm';
    const FORMAT_DATE_TIME1              = 'h:mm AM/PM';
    const FORMAT_DATE_TIME2              = 'h:mm:ss AM/PM';
    const FORMAT_DATE_TIME3              = 'h:mm';
    const FORMAT_DATE_TIME4              = 'h:mm:ss';
    const FORMAT_DATE_TIME5              = 'mm:ss';
    const FORMAT_DATE_TIME6              = 'h:mm:ss';
    const FORMAT_DATE_TIME7              = 'i:s.S';
    const FORMAT_DATE_TIME8              = 'h:mm:ss;@';
    const FORMAT_DATE_YYYYMMDDSLASH      = 'yy/mm/dd;@';

    const FORMAT_CURRENCY_USD_SIMPLE     = '"$"#,##0.00_-';
    const FORMAT_CURRENCY_USD            = '$#,##0_-';
    const FORMAT_CURRENCY_EUR_SIMPLE     = '[$EUR ]#,##0.00_-';
    // 생략

사용 방법

$excel->setActiveSheetIndex(0)->getStyle("A1")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$excel->getActiveSheet()->getCell("A1")->setValue("00001");

 

위에서는 사용자 지정 서식 중 세부 형식을 @로 설정하면 입력한 그대로 출력되는 것을 이용했다.

(셀 서식을 텍스트로 지정한 것과 동일한 효과)