■ 백엔드 ■/PHP2018.07.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 


   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.  


Posted by SAP (Study And Programming) by serpiko

댓글을 달아 주세요