Delphi for PHPでExcel帳票を作ろう

<?php
require_once("vcl/vcl.inc.php");
require_once("phpExcel/Classes/PHPExcel.php");
require_once("phpExcel/Classes/PHPExcel/IOFactory.php");
//Includes
use_unit("classes.inc.php");

//Class definition
class PHPExcelSimpleReport extends Component
{
   /*
   * ファイル名・シートｐ名には、以下の文字は使えない
   * 不等号 (< >)、アスタリスク (*)、疑問符 (?)、二重引用符 (")、縦線またはパイプ (|)、
   * コロン (:)、スラッシュ (/)、または角かっこ ([])
   * Columns array 各列の定義
   */

  //パラメータ指定用定数
  const C_EXCEL_REPORT_COL_FIELDNAME    = 'Excel_col_fieldname';//カラムのフィールド名
  const C_EXCEL_REPORT_COL_TITLE        = 'Excel_col_title';    //カラムのタイトル
  const C_EXCEL_REPORT_COL_WIDTH        = 'Excel_col_width';    //カラムの幅
  const C_EXCEL_REPORT_COL_NUMFORMAT    = 'Excel_col_numformat';//表示形式
  const C_EXCEL_REPORT_COL_ALIGNMENT    = 'Excel_col_alignment';//セルのAlignment

  //ページ設定用定数
  const C_EXCEL_PAPERSIZE_A4  = 'A4';
  const C_EXCEL_PAPERSIZE_A3  = 'A3';

  //スタイル関連定数
  const C_EXCEL_NUMFORMAT_NUMBER_00       = PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00;
  const C_EXCEL_NUMFORMAT_COMMA_SEPARATED  = '#,##0';

  const C_EXCEL_STYLE_ALIGNMENT_H_RIGHT   = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
  const C_EXCEL_STYLE_ALIGNMENT_H_LEFT    = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
  const C_EXCEL_STYLE_ALIGNMENT_H_CENTER  = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;

  const C_EXCEL_STYLE_DEFAULT_COL_WIDTH = 32;

  //Writerのタイプ
  const C_EXCEL_WRITER_EXCEL5    = 'Excel5';
  const C_EXCEL_WRITER_EXCEL2007 = 'Excel2007';

  //レポート用の既定値
  const C_TITLE_ROW_START = 4;        //レポート本体の開始行

  //各プロパティ用の内部フィールド
  protected $_filename = "";
  protected $_sheetname = "";
  protected $_orientation = "";
  protected $_papersize = 0;
  protected $_dataset = null;
  protected $_report_title = "";
  protected $_creator = "";
  protected $_columns = null;
  protected $_titlerowcolor= "";
  protected $_writertype="";

  function __construct($aowner = null)
  {
    parent::__construct($aowner);

    $this->_titlerowcolor = $this->defaultTitleRowColor();
    $this->_orientation = $this->defaultOrientation();
    $this->_papersize = $this->defaultPaperSize();
    $this->_writertype = $this->defaultWriterType();
  }

  public function ExportExcel()
  {
    //PHPExcelオブジェクトの生成
    $xl = new PHPExcel();

    //シートの設定
    $xl->setActiveSheetIndex(0);
    $ws = $xl->getActiveSheet();
    $ws->getDefaultStyle()->getFont()->setName('ＭＳ Ｐゴシック');
    $ws->getDefaultStyle()->getFont()->setSize(11);

    $ws->setTitle($this->_sheetname);
    if($this->_orientation)
      $ws->getPageSetup()->setOrientation($this->_orientation);

    if($this->_papersize)
      $ws->getPageSetup()->setPaperSize($this->_papersize);

    //レポートタイトルを設定
    $ws->setCellValueByColumnAndRow(0, 1, $this->_report_title);
    $style = $ws->getStyleByColumnAndRow(0, 1);
    $style->getFont()->setSize(12);
    $style->getFont()->setBold(true);

    $columns = $this->_columns;

    //作成日時を設定
    $ws->setCellValueByColumnAndRow(count($this->_columns)-1, 1, '作成日:'.date('Y/m/d h:n:s'));

    //作成者を設定
    global $DM_Main;
    $ws->setCellValueByColumnAndRow(count($this->_columns)-1, 2, '作成者:'.$this->_creator);

    //タイトル行を設定
    $TitleRowColor = new PHPExcel_Style_Color();
    $TitleRowColor->setRGB($this->_titlerowcolor);

    $row = self::C_TITLE_ROW_START;

    //Columnsが未設定の場合は、DataSetから条件を生成する
    if($this->_columns==null)
    {
      $this->_columns=array();
      $fields = $this->_dataset->Fields;
      $keys=array_keys($fields);
      for($col=0;$col<count($keys);$col++)
      {
        $array1 = array(self::C_EXCEL_REPORT_COL_FIELDNAME, self::C_EXCEL_REPORT_COL_TITLE, self::C_EXCEL_REPORT_COL_WIDTH);
        $array2 = array($keys[$col], $keys[$col], self::C_EXCEL_STYLE_DEFAULT_COL_WIDTH);
        array_push($this->_columns, array_combine($array1, $array2));
      }
    }

    //タイトル行を処理
    for($col=0; $col<count($this->_columns); $col++)
    {
      $ws->setCellValueByColumnAndRow($col, $row, $this->_columns[$col][self::C_EXCEL_REPORT_COL_TITLE]);
      $style = $ws->getStyleByColumnAndRow($col, $row);
      $style->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $style->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $style->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
      $style->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
      $style->getFill()->setStartColor($TitleRowColor);
      $ws->getColumnDimensionByColumn($col)->setWidth($this->_columns[$col][self::C_EXCEL_REPORT_COL_WIDTH]);
    }

    //DataSetが無ければ以下は未処理
    if($this->_dataset!=null)
    {
       //データを出力
      $this->_dataset->first();
      while ($this->_dataset->EOF==false)
      {
        $row++;
        for($col=0; $col<count($this->_columns);$col++)
        {
          $ws->setCellValueByColumnAndRow($col, $row, $this->_dataset->Fields[$this->_columns[$col][self::C_EXCEL_REPORT_COL_FIELDNAME]]);
          $style = $ws->getStyleByColumnAndRow($col, $row);
          $style->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $style->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $style->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
          $style->getAlignment()->setWrapText(true);
          if($this->_columns[$col][self::C_EXCEL_REPORT_COL_NUMFORMAT])
          {
            $style->getNumberFormat()->setFormatCode($this->_columns[$col][self::C_EXCEL_REPORT_COL_NUMFORMAT]);
          }
          if($this->_columns[$col][self::C_EXCEL_REPORT_COL_ALIGNMENT])
          {
            $style->getAlignment()->setHorizontal($this->_columns[$col][self::C_EXCEL_REPORT_COL_ALIGNMENT]);
          }
        }
        $this->_dataset->next();
      }
    }


    //Excel形式で保存
    $writer = PHPExcel_IOFactory::createWriter($xl, $this->_writertype);

    // redirect output to client browser
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: no-cache, must-revalidate, max-age=0, post-check=0, pre-check=0"); // HTTP/1.1
    header("Pragma: no-cache"); // HTTP/1.0
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Content-Type: application/vnd.ms-excel");
    header('Content-Disposition: attachment;filename="'.$this->_filename);
    header("Content-Transfer-Encoding: binary");

    $writer->save('php://output');
  }


  //filename
  public function setFilename($pValue)
  {
    if($pValue)
    {
      $this->_filename = $pValue;
    }
  }
  public function getFilename()
  {
    return $this->_filename;
  }
  public function defaultFilename()
  {
    return 'phpexcel';
  }

  //Sheetname
  public function setSheetname($pValue)
  {
    if($pValue)
    {
      $this->_sheetname = $pValue;
    }
  }
  public function getSheetname()
  {
    return $this->_sheetname;
  }
  public function defaultSheetname()
  {
    return 'sheet1';
  }

  //Orientation
  public function setOrientation($pValue)
  {
    switch($pValue)
    {
      case PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT:
        break;
      case PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE:
        break;
      default:
        $pValue = $this->defaultOrientation();
    }
    $this->_orientation = $pValue;
  }

  public function getOrientation()
  {
    return $this->_orientation;
  }
  public function defaultOrientation()
  {
    return PHPExcel_Worksheet_PageSetup::ORIENTATION_DEFAULT;
  }

  //Papersize
  public function setPaperSize($pValue)
  {
    switch($pValue)
    {
      case self::C_EXCEL_PAPERSIZE_A4:
        $pValue = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4;
        break;
      case serlf::C_EXCEL_PAPERSIZE_A3:
        $pValue = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A3;
        break;
      default:
        $pValue = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4;
      }
    $this->_papersize = $pValue;
  }
  public function getPaperSize()
  {
    $pValue = $this->_papersize;
    switch($pValue)
    {
      case PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4:
        $pValue = self::C_EXCEL_PAPERSIZE_A4;
        break;
      case PHPExcel_Worksheet_PageSetup::PAPERSIZE_A3:
        $pValue = self::C_EXCEL_PAPERSIZE_A3;
        break;
      default:
        $pValue = $this->defaultPaperSize();
    }
    return $pValue;
  }
  public function defaultPaperSize()
  {
    return PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4;
  }

  //DataSet
  public function setDataSet($pValue)
  {
    $this->_dataset = $this->fixupProperty($pValue);
  }
  public function getDataSet()
  {
    return $this->_dataset;
  }
  public function defaultDataSet()
  {
    return null;
  }

  //Report_Title
  public function setReport_Title($pValue)
  {
    $this->_report_title = $pValue;
  }
  public function getReport_Title()
  {
    return $this->_report_title;
  }
  public function defaultReport_Title()
  {
    null;
  }

  //Creator
  public function setCreator($pValue)
  {
    $this->_creator = $pValue;
  }
  public function getCreator()
  {
    return $this->_creator;
  }
  public function defaultCreator()
  {
    return null;
  }

  //Columns
  public function setColumns($pValue = array())
  {
    if(is_array($pValue)==true)
    {
      $this->_columns=$pValue;
    }else{
      $this->_columns=array();
    }
  }
  public function getColumns()
  {
    return $this->_columns;
  }
  public function defaultColumns()
  {
    return array();
  }

  //TitleColor
  public function getTitleRowColor()
  {
    return $this->_titlerowcolor;
  }
  public function setTitleRowColor($value)
  {
    $this->_titlerowcolor=$value;
  }
  public function defaultTitleRowColor()
  {
    return 'CCFFFF';
  }

  //WriterType
  public function getWriterType()
  {
    return $this->_writertype;
  }
  public function setWriterType($pValue)
  {
    switch($pValue)
    {
      case self::C_EXCEL_WRITER_EXCEL5;
        break;
      case self::C_EXCEL_WRITER_EXCEL2007;
        break;
      default:
        $pValue=$this->defaultWriterType();
    }
    $this->_writertype=$pValue;
  }
  public function defaultWriterType()
  {
    return self::C_EXCEL_WRITER_EXCEL5;
  }

}

?>