PHPExcelの使い方・エクセル操作方法のまとめ
あまり分かりやすくまとまっているサイトがなかったのと、英語を読みたくないので、最近使用した部分について自分用にまとめておきます。
使用していない部分は随時追加していきます。
最低限の基本的な使い方はPHPでエクセル（xlsx&xls）を扱う方法を読んでください。
ライブラリの読み込み
|
1 2 3 |
//PHPExcelファイルの読み込み include_once ( dirname(__FILE__) . '/Classes/PHPExcel.php'); include_once ( dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php'); |
この文章を入れないと、エクセル操作をすることができません。
ちなみに上記の例は、エクセル操作をするphpファイルと同じ階層にclassesフォルダが設置されている場合のものになります。
エクセルファイルの新規作成
|
1 2 |
//PHPExcelの新規オブジェクト作成 $excel = new PHPExcel(); |
テンプレートを読み込んだりせず、エクセルファイルを新規作成する場合は、このコードを使用します。
既存のエクセルファイルの読み込み
|
1 2 3 4 5 6 7 |
// Excel97-2003形式(xls)テンプレートの読み込み $reader = PHPExcel_IOFactory::createReader('Excel5'); $excel = $reader->load(テンプレートファイルパス); // Excel2007形式(xlsx)テンプレートの読み込み $reader = PHPExcel_IOFactory::createReader('Excel2007'); $excel = $reader->load(テンプレートファイルパス); |
例えば、テンプレートファイルがエクセル操作を行うPHPファイルと同じ階層にあるなら、下記のように入力する。
|
1 |
$excel = $reader->load( dirname(__FILE__) . '/template.xls' ); |
エクセルファイルの出力
|
1 2 3 4 5 6 7 |
// Excel97-2003形式(xls)で出力する $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5'); $writer->save(ファイルパス); // Excel2007形式(xlsx)で出力する $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007'); $writer->save(ファイルパス); |
サーバーの実行ファイルと同じ階層に出力させたいなら、下記のような感じで入力する。
|
1 |
$writer->save(dirname(__FILE__) . '/output.xls'); |
ちなみにこの出力方法だと、ブラウザから自動的にダウンロードされないことがあるので、下記コードも併せて記載した方がいいかもしれません。
|
1 2 3 4 5 6 7 |
$file_path = dirname(__FILE__) . 'output.xls';ダウンロードさせるファイルのパス $file_name = output.xls;//ダウンロードさせるファイル名 header("Content-Type: application/octet-stream");//ダウンロードの指示 header("Content-Disposition: attachment; filename=$file_name");//ダウンロードするファイル名 header("Content-Length:".filesize($file_path));//ダウンロードするファイルのサイズ ob_end_clean();//ファイル破損エラー防止 readfile(dirname(__FILE__) . '/./' . $file_path);//ダウンロード |
シート
シートの設定方法
|
1 2 3 4 |
// シートの設定 $excel->setActiveSheetIndex(0);//何番目のシートに有効にするか $sheet = $excel->getActiveSheet();//有効になっているシートを取得 $sheet = setTitle('シート名');//シート名を設定します。 |
エクセルファイルを新規作成したときは、
|
1 |
$excel->setActiveSheetIndex(0); |
上記の0の部分が1や2などになっているとエラーが出ます。
テンプレートを読み込んでいる場合、対応しているシートがあればエラーは発生しません。
つまり、存在しないシートを有効化しようとするとエラーが出るということですね、当たり前ですが(笑)
2番目のシートがあれば1、3番目のシートがあれば2と入力してシートを有効化することができます。
また、エクセルファイルを新規作成した場合のシート名のデフォルトはWorksheetになります。
シートのその他
|
1 |
$excel->getSheetCount();//シートの数を返す。 |
|
1 |
$excel->getSheetNames();//シートタブの順序に応じてブック内のシートリストを返す。 |
|
1 |
$excel->getSheet();//シートタブの順番でシートにアクセス。1番目のシートは"0"から |
|
1 |
$excel->getSheetByName()//シートタブの名前でシートにアクセス。 |
|
1 |
$excel->createSheet();//シートを新規追加する |
|
1 2 3 |
//My Dataというワークシート作成し、シートタブの一番目に挿入 $myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, 'My Data'); $objPHPExcel->addSheet($myWorkSheet, 0); |
addSheetの第2引数が何もなければ、シートの最後に追加されます。
|
1 2 3 4 5 6 7 8 |
//同一ブック内でシートを複製して追加 $objClonedWorksheet = clone $objPHPExcel->getSheetByName('sheet1'); $objClonedWorksheet->setTitle('コピーsheet1') $objPHPExcel->addSheet($objClonedWorksheet); //別ブックのシートを複製して追加 $objClonedWorksheet = clone $objPHPExcel1->getSheetByName('sheet1'); $objPHPExcel->addExternalSheet($objClonedWorksheet); |
シート名が重複しないように注意が必要です。
|
1 2 3 |
//シートの削除 $sheetIndex = $excel->getIndex($objPHPExcel-> getSheetByName('Worksheet 1'));//指定したシートの順序を取得 $excel->removeSheetByIndex($sheetIndex);//指定したシートの順序からシートを削除 |
シート名では削除できません。何番目のシートか取得して削除する必要があります。
値の入力
|
1 2 3 4 5 |
//セル名で指定する場合 $sheet->setCellValue('セル名' , '設定したい値'); //数値で指定する場合 $sheet->setCellValueByColumnAndRow(列 , 行, '設定したい値' ); |
値の設定には、setCellValueを使用します。
値の設定はセル名か、列座標と行座標を指定して設定することができます。
例えば、セルのA1に「こんにちは」と設定したい場合は、下記のようになります。
|
1 2 3 4 5 |
//セル名で指定する場合 $sheet->setCellValue('A1' , 'こんにちは'); //数値で指定する場合 $sheet->setCellValueByColumnAndRow(0 , 1, 'こんにちは' ); |
列番号と行番号で指定する場合は、行は1から、列は0から始まることに注意しておく必要があります。
画像の貼り付け
|
1 2 3 4 5 6 7 8 |
$objDrawing = new PHPExcel_Worksheet_Drawing();//画像用のオプジェクト作成 $objDrawing->setPath('ファイルのパス');//貼り付ける画像のパスを指定 $objDrawing->setHeight(数値);//画像の高さを指定 $objDrawing->setWidth(数値);//画像の高さを指定 $objDrawing->setResizeProportional(false);//縦横比の変更なし $objDrawing->setCoordinates('セル名');//位置 $objDrawing->setOffsetX(数値);//指定した位置からどれだけ横方向にずらすか。 $objDrawing->setWorksheet($sheet); |
画像の貼り付けは、画像用のオブジェクトを作成して、オブジェクトに情報を追加して、シートに貼り付けます。
他にも様々なプロパティが用意されていますが、貼り付けに必要そうなものだけを記載しています。
数値を指定するところの単位ですが、エクセル2007形式で出力する場合はピクセルになっているような気がするんですが、97~2003形式だとピクセルじゃないような気がします。
それと、ドキュメントや色んなサイトを見ましたが、画像の列番号と行番号による位置の指定方法は分かりませんでした。
セルの結合
|
1 2 3 4 5 |
//セルで範囲指定 $sheet->mergeCells('開始セル:終了セル'); //列番号と行番号で範囲指定 $sheet->mergeCellsByColumnAndRow(開始セル列座標,開始セル行座標,終了セル列座標,終了セル行座標); |
セルの結合は、mergeCellsを使用します。
セルの結合の場合は、ByColumnAndRowで範囲指定が可能でした。
例えば、セルA1～A10までを結合したい場合、下記のように書きます。
|
1 2 3 4 5 |
//セルで範囲指定 $sheet->mergeCells('A1:A10'); //列番号と行番号で範囲指定 $sheet->mergeCellsByColumnAndRow(0,1,9,1); |
セルの書式設定
|
1 2 |
$sheet->getStyle('セル名')->各種設定;//単一セル指定 $sheet->getStyle('セル名:セル名')->各種設定;//セル範囲指定 |
書式に関してはこの文章から始まります。
getstyleもByColumnAndRowを使用すれば座標でセルを指定することが可能です。
|
1 |
$sheet->getStyleByColumnAndRow(列,行)->各種設定; |
ただし、値の入力のように座標での範囲指定はできませんでした。
座標指定で範囲を指定する場合は、座標をセル名に置き換えてくれるstringFromColumnIndexメソッドを使用する必要があります。
|
1 2 3 |
$cell_range = PHPExcel_Cell::stringFromColumnIndex(開始セルの列座標) . '開始セルの行座標:' .PHPExcel_Cell::stringFromColumnIndex(終了セルの列座標) . '終了セルの行座標'; $sheet->getStyle($cell_range)->各種設定; |
フォントスタイル
|
1 |
$sheet->getStyle('セル名')->getFont()->フォントスタイルの設定; |
フォントのスタイルを設定する場合は、getStyleの後に上記のようにgetFontメソッドを繋げます。
設定できるスタイルは下記の通りになります。
|
1 2 3 4 5 6 7 |
getFont()->setName('使用フォント名');//フォント名 getFont()->setBold(true/false);//太文字 getFont()->setItalic(true/false);//斜字 getFont()->setUnderline(true/false);//下線 getFont()->setStrikethrough(true/false);//打消し線 getFont()->getColor()->setARGB('ARGB値'or PHPExcel_Style_Color::COLOR_色名);//フォントカラー getFont()->getSize(数値);//フォントサイズ |
フォントカラーの設定におけるARGB値は透明度、赤、緑、青の値を16進数で表して連結した値です。
例えば、赤の場合は、
FFFF0000
という値になります。
色名に関しては、以下の表を参考にしてください。
|
色名 |
色 |
ARGB値 |
|---|---|---|
|
COLOR_BLACK |
黒 |
FF000000 |
|
COLOR_WHITE |
白 |
FFFFFFFF |
|
COLOR_RED |
赤 |
FFFF0000 |
|
COLOR_DARKRED |
ワインレッド |
FF800000 |
|
COLOR_BLUE |
青 |
FF0000FF |
|
COLOR_DARKBLUE |
藍色 |
FF000080 |
|
COLOR_GREEN |
緑 |
FF00FF00 |
|
COLOR_DARKGREEN |
深緑 |
FF008000 |
|
COLOR_YELLOW |
黄 |
FFFFFF00 |
|
COLOR_DARKYELLOW |
濃い黄 |
FF808000 |
また、setARGBのほかにsetRGBを使用することでRGBカラーを使用することもできます。
setRGBの場合は、ARGB値の透明度を除外した、赤、緑、青の値を16進数を表して連結した値で色を指定します。
例えば、赤の場合だと、
FF0000
という値になります。
RGBと16進数カラーコードの相互変換ツールのページで色を16進数のRGB値に変換できます。
参考にしてみてください。
罫線
|
1 |
$sheet->getStyle('セル名')->getBorders()->罫線の設定; |
罫線を引く場合は、getBordersを使用します。
|
1 2 3 4 5 6 7 8 9 10 |
getBorders()->getLeft();//セルの左側にボーダー getBorders()->getRight();//セルの右側にボーダー getBorders()->getTop();//セルの上部にボーダー getBorders()->getBottom();//セルの下部にボーダー getBorders()->getDiagonal();//セルに斜線 getBorders()->getAllBorders();//範囲内のセルに全区切りにボーダー getBorders()->getOutline();//範囲内のセルの一番外側にボーダー getBorders()->getInside();//範囲内のセルの一番外側以外にボーダー getBorders()->getVertical();//範囲内のセルに縦線ボーダーのみ getBorders()->getHorizontal();//範囲内のセルに横線ボーダーのみ |
次に罫線を引きたい場所を指定します。
|
1 |
getBorders()->getLeft()->setBorderStyle(); |
罫線を引く場所を指定した後、さらにsetBorderStyleを指定することで罫線のスタイルを指定できます。
引数に罫線スタイルの定数を指定して使用します。
例えば、通常の罫線を引きたい場合は、
|
1 |
getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN); |
という風に記載します。
用意されているボーダーの定数は、
|
ボーダースタイル名 |
線 |
|---|---|
|
PHPExcel_Style_Border::BORDER_NONE |
罫線なし |
|
PHPExcel_Style_Border::BORDER_THIN |
細めの線 |
|
PHPExcel_Style_Border::BORDER_MEDIUM |
少し太い線 |
|
PHPExcel_Style_Border::BORDER_DASHED |
破線 |
|
PHPExcel_Style_Border::BORDER_DOTTED |
点線 |
|
PHPExcel_Style_Border::BORDER_THICK |
太線 |
|
PHPExcel_Style_Border::BORDER_DOUBLE |
二重線 |
|
PHPExcel_Style_Border::BORDER_HAIR |
凄く細かくて細い点線 |
|
PHPExcel_Style_Border::BORDER_MEDIUMDASHED |
太めの破線 |
|
PHPExcel_Style_Border::BORDER_DASHDOT |
細めの一点鎖線 |
|
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT |
太めの一点鎖線 |
|
PHPExcel_Style_Border::BORDER_DASHDOTDOT |
細めの二点鎖線 |
|
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT |
太めの二点鎖線 |
|
PHPExcel_Style_Border::BORDER_SLANTDASHDOT |
一点鎖線(線の切り口が斜め) |
|
1 |
getBorders()->getLeft()->getColor()->setARGB('ARGB値'or PHPExcel_Style_Color::COLOR_色名); |
ボーダーの色の設定にはgetColorを使用し、その後setARGBかsetRGBを使用して色を指定します。
色の指定方法に関しては、フォントスタイルのところを参照してください。
文字配置
|
1 |
$sheet->getStyle('セル名')->getAlignment()->文字揃え; |
文字配置を設定する時はgetAlignmentを使用します。
|
1 |
$sheet->getStyle('セル名')->setHorizontal();//文字の横位置揃え |
横位置の配置を設定する場合は、setHorizontalを繋げます。
引数に下記の定数を指定します。
|
横位置の配置スタイル名 |
配置 |
|---|---|
|
PHPExcel_Style_Alignment::HORIZONTAL_GENERAL |
標準 |
|
PHPExcel_Style_Alignment::HORIZONTAL_LEFT |
左詰め（インデント） |
|
PHPExcel_Style_Alignment::HORIZONTAL_RIGHT |
右詰め（インデント） |
|
PHPExcel_Style_Alignment::HORIZONTAL_CENTER |
中央揃え |
|
PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS |
選択範囲内で中央 |
|
HORIZONTAL_JUSTIFY |
両端揃え |
|
HORIZONTAL_FILL |
繰り返し |
|
HORIZONTAL_DISTRIBUTED |
均等割り付け（インデント） |
|
1 |
$sheet->getStyle('セル名')->setVertical();//文字の縦位置揃え |
縦位置の配置を設定する場合は、setVerticalを繋げます。
引数に下記の定数を指定します。
|
縦位置の配置スタイル名 |
配置 |
|---|---|
|
PHPExcel_Style_Alignment::VERTICAL_BOTTOM |
下詰め |
|
PHPExcel_Style_Alignment::VERTICAL_TOP |
上詰め |
|
PHPExcel_Style_Alignment::VERTICAL_CENTER |
中央詰め |
|
PHPExcel_Style_Alignment::VERTICAL_JUSTIFY |
両端揃え |
|
PHPExcel_Style_Alignment::VERTICAL_DISTRIBUTED |
均等割り付け |
背景色
|
1 |
$sheet->getStyle('セル名')->getFill(); |
背景色を設定する場合getFillを使用します。
|
1 |
getFill()->setFillType()->getStartColor()->setARGB(); |
getFillTypeで背景色の塗り方を指定した後にgetStartColorメソッドのserARGBで色を指定します。
色の指定方法はフォントスタイルのところを参考にしてください。
背景色の塗り方の定数は下記を参照にしてください。
|
背景色のパターン名 |
|
|---|---|
|
PHPExcel_Style_Fill::FILL_NONE |
上から順にパターンに対応 |
|
PHPExcel_Style_Fill::FILL_SOLID |
|
|
PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR |
|
|
PHPExcel_Style_Fill::FILL_GRADIENT_PATH |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_DARKUP |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625 |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_GRAY125 |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL |
|
|
PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY |
|
セルの幅・高さ
|
1 2 |
$objSheet->getColumnDimension('セル名')->setWidth(数値);//セルの幅 $objSheet->getRowDimension('行数')->setRowHeight(数値);//セルの高さ |
自動改行
|
1 |
$sheet->getStyle('セル名')->getAlignment()->setWrapText(true);//セルの自動改行 |
最後に
ドキュメントやファイルを直接見てみたり、Google先生に確認しながら今回PHPExcelを使用しましたが、オートフィルタやリッチテキスト、計算式、エクセルファイル以外の出力、ワークシートの初期設定などなど網羅できていない部分が沢山あります。
今後徐々に追記出来たらと思います。
参考になれば幸いです。
ではでは。
コメントを残す