공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday

티스토리 뷰

■ 백엔드 ■/PHP

PhpSpreadsheet

serpiko 2018. 7. 26. 14:26
         PhpSpreadsheet


      Software requirements

    PHP version 5.6 or newer

PHP extension php_zip enabled

PHP extension php_xml enabled

PHP extension php_gd2 enabled (if not compiled in)



   내가 ubuntu에 설치한 패키지


sudo apt-get install php7.0-xml

sudo apt-get install php7.0-gd

sudo apt-get install php7.0-intl

sudo apt-get install php7.0-xsl

sudo apt-get install php7.0-zip 

sudo apt-get install php7.0-mbstring 


   phpSpreadsheet 설치

composer require phpoffice/phpspreadsheet


   php 공식 홈

https://phpspreadsheet.readthedocs.io/en/develop/



   샘플로 작성한 코드


html, js)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<a class="btn btn-success excel-btn m-l-10">엑셀다운로드</a>
<script>
    
    function GetParam( $obj, $encodeBool ){
    
        var ver = "2017.03.06  허정진";
 
        $encodeBool = $encodeBool || false;
 
        var str = "";
        for (var key in $obj){
            if( str != "" ){
                str += "&";
            }
            
            /*
            if( $encodeBool )str += key + "=" + encodeURI(encodeURIComponent( $obj[key] )); // decodeURIComponent
            else str += key + "=" +  $obj[key];
            */
            
            
            if( $encodeBool )str += key + "=" + escape( $obj[key] ); // decodeURIComponent
            else str += key + "=" +  $obj[key];
            
        }
        
        return str;
    }
 
 
    $(".excel-btn").click(function(e){
    
        var rqObj = {};
        rqObj.client_id = $("#client_list").val();
        rqObj.bill_name = $("#billing_name").val().replace(/&/g, "%amp");
        rqObj.date = $(".datepicker").val();
        rqObj.isExcel = 1;
        rqObj.debug = 0;
        var param = GetParam( rqObj );
        
        $(e.currentTarget).attr({
            href :'/api/BillingModel/getBillingData/'+_user_idx+"/"+_permission+"/"+_ip+"/"+_company_idx+"?"+param
        });
    });
 
</script>
cs


php)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
 
class BillingModel extends Model
{
    
    public function getBillingExcel(){
        $fileName = "excel";
        // 1. Create new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        // 2. Set document properties
        $spreadsheet->getProperties()->setCreator('Atlasnetworks')
            ->setLastModifiedBy('Atlasnetworks')
            ->setTitle('Office 2007 XLSX Document')
            ->setSubject('Office 2007 XLSX Document')
            ->setDescription('document for Office 2007 XLSX, generated using Billing Data.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Billing Data file');
        // 3. Add some data
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B2', 'world!')
            ->setCellValue('C1', 'Hello')
            ->setCellValue('D2', 'world!');
        // 4. serpiko, UTF-8
        $spreadsheet->setActiveSheetIndex(0)
            ->setCellValue('A4', 'serpiko')
            ->setCellValue('A5', '안녕하세요')
            ->setCellValue('A6', 'http://serpiko.tistory.com');
        // 5. Rename worksheet
        $spreadsheet->getActiveSheet()->setTitle('sheet_01');
        // 6. Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $spreadsheet->setActiveSheetIndex(0);
        ////////////////////////////////////////////////////////////////////////////////////////////////
        self::exportXlsx( $spreadsheet, $fileName.'_'.date("Ymd_His") );
    }
    
    static function exportXlsx($spreadsheet, $fileName="excel.xlsx"){
 
        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
 
        // If you're serving to IE over SSL, then the following may be needed
        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'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
 
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }    
}
?>
cs


   결과


excel_20181127_163912.xlsx






   예전 버전 코드


  1. <?
  2. /**************************************************************************************************************
  3. *
  4. * header
  5. *
  6. **************************************************************************************************************/
  7. $fileName = "Excel_" . date("_Ymd_Hi") . ".xls";
  8.  
  9. /** Error reporting */
  10. error_reporting(E_PARSE | E_ERROR);
  11. ini_set('display_errors', TRUE);
  12. ini_set('display_startup_errors', TRUE);
  13.  
  14. if (PHP_SAPI == 'cli')
  15. die('This example should only be run from a Web Browser');
  16.  
  17. /** Include PHPExcel */
  18. require_once dirname(__FILE__) . '/Classes/PHPExcel.php';
  19.  
  20. // Create new PHPExcel object
  21. $objPHPExcel = new PHPExcel();
  22.  
  23. // Set document properties
  24. $objPHPExcel->getProperties()->setCreator("PrimaryNeT")
  25. ->setLastModifiedBy("PrimaryNeT")
  26. ->setTitle("Office 2007 XLSX Test Document")
  27. ->setSubject("Office 2007 XLSX Test Document")
  28. ->setDescription("Document for Export Generated By. PrimaryNeT.")
  29. ->setKeywords("office 2007 openxml php")
  30. ->setCategory("Export File");
  31.  
  32. /***************************************************************************/
  33.  
  34. $len = 5;
  35. $arrDOC_RECORD = array();
  36. $arrDOC_RECORD[0]['FieldDescr'] = "필드0";
  37. $arrDOC_RECORD[1]['FieldDescr'] = "필드1";
  38. $arrDOC_RECORD[2]['FieldDescr'] = "필드2";
  39. $arrDOC_RECORD[3]['FieldDescr'] = "필드3";
  40. $arrDOC_RECORD[4]['FieldDescr'] = "필드4";
  41.  
  42. $row = 1;
  43. for($i=0; $i<$len; $i++)
  44. {
  45. //echo $arrDOC_RECORD[$i]['FieldDescr'];
  46. //$arrDOC_RECORD[$i]['FieldName']; // No : json's Data의 Name
  47. //$arrDOC_RECORD[$i]['GridWidth'] * 10;
  48.  
  49. $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($i, $row , $arrDOC_RECORD[$i]['FieldDescr']);
  50. };
  51.  
  52. /*
  53. $len = 5;
  54. $arrDOC_RECORD2 = array();
  55. $arrDOC_RECORD2[0]['FieldName'] = "나의필드0";
  56. $arrDOC_RECORD2[1]['FieldName'] = "나의필드1";
  57. $arrDOC_RECORD2[2]['FieldName'] = "나의필드2";
  58. $arrDOC_RECORD2[3]['FieldName'] = "나의필드3";
  59. $arrDOC_RECORD2[4]['FieldName'] = "나의필드4";
  60.  
  61.  
  62. $jsonData = array( 'rows'=>array() );
  63. $arrCell = array();
  64. for($i=0; $i<$len; $i++){
  65. $FieldName = $arrDOC_RECORD2[$i]['FieldName'];
  66. $arrCell[ $i ] = $FieldName;
  67. }
  68.  
  69. $entry = array(
  70. 'id' => "아이디", // ID for JSON
  71. //'TableID' => $row['TableID'],
  72. //'CategoryID' => $row['CategoryID'],
  73. 'cell' => $arrCell
  74. );
  75.  
  76. // 3. 데이터 결합
  77. $jsonData['rows'][] = $entry;
  78. */
  79.  
  80. $jsonData = array();
  81. $jsonData['rows'][0] = array( "cell" => array( "나의필드0") );
  82. $jsonData['rows'][1] = array( "cell" => array( "나의필드1") );
  83. $jsonData['rows'][2] = array( "cell" => array( "나의필드2") );
  84. $jsonData['rows'][3] = array( "cell" => array( "나의필드3") );
  85.  
  86. $row = 2;
  87. for($i=0; $i<count($jsonData['rows']); $i++){
  88.  
  89. $col = 0;
  90.  
  91. foreach( $jsonData['rows'][$i]['cell'] as $key=>$value ){
  92. //echo '['.$key.':'.$value.']';
  93. $objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow($col, $row, $value);
  94. $col++;
  95. }
  96.  
  97. $row++;
  98. }
  99.  
  100. $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);
  101.  
  102. /***************************************************************************/
  103.  
  104. $objPHPExcel->getDefaultStyle()->getFont()->setName('맑은 고딕')
  105. ->setSize(9);
  106.  
  107. $objPHPExcel->getActiveSheet()->getStyle('A1:AZ1')->applyFromArray(
  108.  
  109. array(
  110. 'font' => array(
  111. 'bold' => true
  112. ),
  113. 'alignment' => array(
  114. 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
  115. ),
  116. 'borders' => array(
  117. 'top' => array(
  118. 'style' => PHPExcel_Style_Border::BORDER_THIN
  119. )
  120. ),
  121. 'fill' => array(
  122. 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
  123. 'rotation' => 90,
  124. 'startcolor' => array(
  125. 'argb' => 'FFA0A0A0'
  126. ),
  127. 'endcolor' => array(
  128. 'argb' => 'FFFFFFFF'
  129. )
  130. )
  131. )
  132.  
  133. );
  134.  
  135. $objPHPExcel->getActiveSheet()->setTitle('Export-List'); //시트이름
  136. $objPHPExcel->setActiveSheetIndex(0);
  137.  
  138. // Redirect output to a client’s web browser (Excel5)
  139. header("Content-Type: application/vnd.ms-excel; charset=utf-8");
  140. header("Content-Disposition: attachment; filename={$fileName}");
  141. header("Expires: 0");
  142. header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  143. header("Cache-Control: private",false);
  144.  
  145. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  146. $objWriter->save('php://output');
  147. ?>
  148.  
  149.  
  150.  


저작자표시 비영리 동일조건

'■ 백엔드 ■ > PHP' 카테고리의 다른 글

PDO SQL 인젝션을 막기위한 처리  (0) 2018.11.22
PDO Tutorial for MySQL Developers  (0) 2018.11.22
PHP5 PASSWORD HASH GENERATOR  (0) 2018.07.26
php 웹페이지 엑세스 솔루션들  (0) 2018.06.21
json 한글이 ?로 깨질때  (0) 2018.06.04
댓글