PHP 엑셀 다운로드 예제

엑셀 파일을 만들고 나서, 그 파일을 다운로드 하는 식으로 진행되어야 한다.

엑셀 파일을 만들지 않고 다운로드가 되게끔 하려면 알 수 없는 오류를 만나 고생하게 된다... 

 

엑셀 라이브러라 다운로드

PHPExcel-1.8.zip

다운로드

 

예제 소스는 다음과 같다.

<?php include 'PHPExcel-1.8/Classes/PHPExcel.php'; // 엑셀 라이브러리 import ?> 

    <?php
    //set the desired name of the excel file
    $fileName = 'excelTemp';

    //prepare the records to be added on the excel file in an array
    $excelData = array(
     0 => array('Jackson','Barbara','27','F','Florida'),
     1 => array('Kimball','Andrew','25','M','Texas'),
     2 => array('Baker','John','28','M','Arkansas'),
     3 => array('Gamble','Edward','29','M','Virginia'),
     4 => array('Anderson','Kimberly','23','F','Tennessee'),
     5 => array('Houston','Franchine','25','F','Idaho'),
     6 => array('Franklin','Howard','24','M','California'),
     7 => array('Chen','Dan','26','M','Washington'),
     8 => array('Daniel','Carolyn','27','F','North Carolina'),
     9 => array('Englert','Grant','25','M','Delaware')
    );

    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    // Set document properties
    $objPHPExcel->getProperties()->setCreator("Me")->setLastModifiedBy("Me")->setTitle("My Excel Sheet")->setSubject("My Excel Sheet")->setDescription("Excel Sheet")->setKeywords("Excel Sheet")->setCategory("Me");

    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);

    // Add column headers
    $objPHPExcel->getActiveSheet()
     ->setCellValue('A1', 'Last Name')
     ->setCellValue('B1', 'First Name')
     ->setCellValue('C1', 'Age')
     ->setCellValue('D1', 'Sex')
     ->setCellValue('E1', 'Location')
     ;

    //Put each record in a new cell
    for($i=0; $i<count($excelData); $i++){
     $ii = $i+2;
     $objPHPExcel->getActiveSheet()->setCellValue('A'.$ii, $excelData[$i][0]);
     $objPHPExcel->getActiveSheet()->setCellValue('B'.$ii, $excelData[$i][1]);
     $objPHPExcel->getActiveSheet()->setCellValue('C'.$ii, $excelData[$i][2]);
     $objPHPExcel->getActiveSheet()->setCellValue('D'.$ii, $excelData[$i][3]);
     $objPHPExcel->getActiveSheet()->setCellValue('E'.$ii, $excelData[$i][4]);
    }



    // Set worksheet title
    $objPHPExcel->getActiveSheet()->setTitle($fileName);

    //save the file to the server (Excel2007)
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('.\\' . $fileName . '.xlsx'); // 저장될 파일 위치

    ?>

    <?php

    // 파일 다운로드 구현
    function mb_basename($path) { return end(explode('/',$path)); }
    function utf2euc($str) { return iconv("UTF-8","cp949//IGNORE", $str); }
    function is_ie() {
    if(!isset($_SERVER['HTTP_USER_AGENT']))return false;
    if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== false) return true; // IE8
    if(strpos($_SERVER['HTTP_USER_AGENT'], 'Windows NT 6.1') !== false) return true; // IE11
    return false;
    }

    $filepath = './excelTemp.xlsx';
    $filesize = filesize($filepath);
    $filename = mb_basename($filepath);
    
    if( is_ie() ) $filename = utf2euc($filename);

    header("Pragma: public");
    header("Expires: 0");
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Content-Transfer-Encoding: binary");
    header("Content-Length: $filesize");

    ob_clean();
    flush();
    readfile($filepath);
    unlink('./excelTemp.xlsx') // 생성된 excel 파일 삭제
    
    ?>

 

-------------------------------------------------------------------------------------------

DB에서 데이터 가져와서 엑셀에 데이터 넣기 예제(위와 거의 동일하지만 데이터 넣는 부분 변경)

    <?php include 'PHPExcel-1.8/Classes/PHPExcel.php'; // 엑셀 라이브러리 import ?>

    <?
      $sql = "SELECT * FROM faqContent ORDER BY hit DESC";
      $rs = mysqli_query($con, $sql);
    ?>

    <?php
    //set the desired name of the excel file
    $fileName = 'excelTemp';

    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    // Set document properties
    $objPHPExcel->getProperties()->setCreator("Me")->setLastModifiedBy("Me")->setTitle("My Excel Sheet")->setSubject("My Excel Sheet")->setDescription("Excel Sheet")->setKeywords("Excel Sheet")->setCategory("Me");

    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);

    // Add column headers
    $objPHPExcel->getActiveSheet()
     ->setCellValue('A1', '번호')
     ->setCellValue('B1', '질문')
     ->setCellValue('C1', '답변')
     ->setCellValue('D1', '문의수')
    ;

    $ii = 2;

    while($info = mysqli_fetch_array($rs)){
      $objPHPExcel->getActiveSheet()->setCellValue('A'.$ii, $ii-1);
      $objPHPExcel->getActiveSheet()->setCellValue('B'.$ii, $info['title']);
      $objPHPExcel->getActiveSheet()->setCellValue('C'.$ii, $info['content']);
      $objPHPExcel->getActiveSheet()->setCellValue('D'.$ii, $info['hit']);
      $ii +=1;
    }

    // Set worksheet title
    $objPHPExcel->getActiveSheet()->setTitle($fileName);

    //save the file to the server (Excel2007)
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('.\\' . $fileName . '.xlsx'); // 저장될 파일 위치
    
    ?>

    <?php
    // 파일 다운로드 구현
    function mb_basename($path) { return end(explode('/',$path)); }
    function utf2euc($str) { return iconv("UTF-8","cp949//IGNORE", $str); }
    function is_ie() {
    if(!isset($_SERVER['HTTP_USER_AGENT']))return false;
    if(strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== false) return true; // IE8
    if(strpos($_SERVER['HTTP_USER_AGENT'], 'Windows NT 6.1') !== false) return true; // IE11
    return false;
    }

    $filepath = './excelTemp.xlsx';
    $filesize = filesize($filepath);
    $filename = mb_basename($filepath);
    if( is_ie() ) $filename = utf2euc($filename);

    header("Pragma: public");
    header("Expires: 0");
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Content-Transfer-Encoding: binary");
    header("Content-Length: $filesize");
    
    ob_clean();
    flush();
    readfile($filepath);
    unlink('./excelTemp.xlsx') // 생성된 excel 파일 삭제

    ?>

 

도움이 되셨다면 광고 한번씩 클릭 부탁드립니다 😁