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



   샘플로 작성한 코드


<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

public function getExcel( $params )
    {
        
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('A1', 'Hello World !');
        

        //header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        //header('Content-Disposition: attachment;filename="export.xlsx"');
        //header('Cache-Control: max-age=0');

        $writer = new Xlsx($spreadsheet);
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        self::SaveViaTempFile($writer);

    }

    static function SaveViaTempFile($objWriter){
        $filePath = sys_get_temp_dir() . "/" . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="export.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
        //$objWriter->save($filePath);
        //readfile($filePath);
        unlink($filePath);
    }

?>



   예전 버전 코드


  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

댓글을 달아 주세요