|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpExcelWrapper; |
|
4
|
|
|
|
|
5
|
|
|
use \PHPExcel_IOFactory; |
|
6
|
|
|
use \PHPExcel_Cell; |
|
7
|
|
|
use \PHPExcel_Worksheet_Drawing; |
|
8
|
|
|
use \PHPExcel_Style_Border; |
|
9
|
|
|
use \PHPExcel_Style_Alignment; |
|
10
|
|
|
use \PHPExcel_Style_Fill; |
|
11
|
|
|
|
|
12
|
|
|
/** |
|
13
|
|
|
* PhpExcelWrapper |
|
14
|
|
|
* PHPExcelを記載しやすくするためのラッパー |
|
15
|
|
|
*/ |
|
16
|
|
|
class PhpExcelWrapper |
|
17
|
|
|
{ |
|
18
|
|
|
private $__phpexcel; |
|
19
|
|
|
private $__sheet = []; |
|
20
|
|
|
private $__deleteSheetList = []; |
|
21
|
|
|
private static $__borderType = [ |
|
22
|
|
|
'none' => PHPExcel_Style_Border::BORDER_NONE, |
|
23
|
|
|
'thin' => PHPExcel_Style_Border::BORDER_THIN, |
|
24
|
|
|
'medium' => PHPExcel_Style_Border::BORDER_MEDIUM, |
|
25
|
|
|
'dashed' => PHPExcel_Style_Border::BORDER_DASHED, |
|
26
|
|
|
'dotted' => PHPExcel_Style_Border::BORDER_DOTTED, |
|
27
|
|
|
'thick' => PHPExcel_Style_Border::BORDER_THICK, |
|
28
|
|
|
'double' => PHPExcel_Style_Border::BORDER_DOUBLE, |
|
29
|
|
|
'hair' => PHPExcel_Style_Border::BORDER_HAIR, |
|
30
|
|
|
'mediumdashed' => PHPExcel_Style_Border::BORDER_MEDIUMDASHED, |
|
31
|
|
|
'dashdot' => PHPExcel_Style_Border::BORDER_DASHDOT, |
|
32
|
|
|
'mediumdashdot' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT, |
|
33
|
|
|
'dashdotdot' => PHPExcel_Style_Border::BORDER_DASHDOTDOT, |
|
34
|
|
|
'mediumdashdotdot' => PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT, |
|
35
|
|
|
'slantdashdot' => PHPExcel_Style_Border::BORDER_SLANTDASHDOT, |
|
36
|
|
|
]; |
|
37
|
|
|
|
|
38
|
|
|
private static $__alignHolizonalType = [ |
|
39
|
|
|
'general' => PHPExcel_Style_Alignment::HORIZONTAL_GENERAL, |
|
40
|
|
|
'center' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, |
|
41
|
|
|
'left' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT, |
|
42
|
|
|
'right' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT, |
|
43
|
|
|
'justify' => PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY, |
|
44
|
|
|
'countinuous' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS, |
|
45
|
|
|
]; |
|
46
|
|
|
|
|
47
|
|
|
private static $__alignVerticalType = [ |
|
48
|
|
|
'bottom' => PHPExcel_Style_Alignment::VERTICAL_BOTTOM, |
|
49
|
|
|
'center' => PHPExcel_Style_Alignment::VERTICAL_CENTER, |
|
50
|
|
|
'justify' => PHPExcel_Style_Alignment::VERTICAL_JUSTIFY, |
|
51
|
|
|
'top' => PHPExcel_Style_Alignment::VERTICAL_TOP, |
|
52
|
|
|
]; |
|
53
|
|
|
|
|
54
|
|
|
private static $__fillType = [ |
|
55
|
|
|
'linear' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR, |
|
56
|
|
|
'path' => PHPExcel_Style_Fill::FILL_GRADIENT_PATH, |
|
57
|
|
|
'none' => PHPExcel_Style_Fill::FILL_NONE, |
|
58
|
|
|
'darkdown' => PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN, |
|
59
|
|
|
'darkgray' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY, |
|
60
|
|
|
'darkgrid' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID, |
|
61
|
|
|
'darkhorizontal' => PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL, |
|
62
|
|
|
'darktrellis' => PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS, |
|
63
|
|
|
'darkup' => PHPExcel_Style_Fill::FILL_PATTERN_DARKUP, |
|
64
|
|
|
'darkvertical' => PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL, |
|
65
|
|
|
'gray0625' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625, |
|
66
|
|
|
'gray125' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY125, |
|
67
|
|
|
'lightdown' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN, |
|
68
|
|
|
'lightgray' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY, |
|
69
|
|
|
'lightgrid' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID, |
|
70
|
|
|
'lighthorizontal' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL, |
|
71
|
|
|
'lighttrellis' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS, |
|
72
|
|
|
'lightup' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP, |
|
73
|
|
|
'lightvertical' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL, |
|
74
|
|
|
'mediumgray' => PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY, |
|
75
|
|
|
'solid' => PHPExcel_Style_Fill::FILL_SOLID, |
|
76
|
|
|
]; |
|
77
|
|
|
|
|
78
|
|
|
/** |
|
79
|
|
|
* __construct |
|
80
|
|
|
* |
|
81
|
|
|
* @param string $template テンプレートファイルのパス |
|
82
|
|
|
* @author hagiwara |
|
83
|
|
|
*/ |
|
84
|
|
|
public function __construct($template = null, $type = 'Excel2007') |
|
85
|
|
|
{ |
|
86
|
|
|
if ($template === null) { |
|
87
|
|
|
//テンプレート無し |
|
88
|
|
|
$this->__phpexcel = new \PHPExcel(); |
|
89
|
|
|
} else { |
|
90
|
|
|
//テンプレートの読み込み |
|
91
|
|
|
$reader = PHPExcel_IOFactory::createReader($type); |
|
92
|
|
|
$this->__phpexcel = $reader->load($template); |
|
93
|
|
|
} |
|
94
|
|
|
} |
|
95
|
|
|
|
|
96
|
|
|
/** |
|
97
|
|
|
* setVal |
|
98
|
|
|
* 値のセット |
|
99
|
|
|
* @param string $value 値 |
|
100
|
|
|
* @param integer $col 行 |
|
101
|
|
|
* @param integer $row 列 |
|
102
|
|
|
* @param integer $sheetNo シート番号 |
|
103
|
|
|
* @param integer $refCol 参照セル行 |
|
104
|
|
|
* @param integer $refRow 参照セル列 |
|
105
|
|
|
* @param integer $refSheet 参照シート |
|
106
|
|
|
* @author hagiwara |
|
107
|
|
|
*/ |
|
108
|
|
|
public function setVal($value, $col, $row, $sheetNo = 0, $refCol = null, $refRow = null, $refSheet = 0) |
|
109
|
|
|
{ |
|
110
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
111
|
|
|
//値のセット |
|
112
|
|
|
$this->getSheet($sheetNo)->setCellValue($cellInfo, $value); |
|
113
|
|
|
|
|
114
|
|
|
//参照セルの指定がある場合には書式をコピーする |
|
115
|
|
|
if (!is_null($refCol) && !is_null($refRow)) { |
|
116
|
|
|
$this->styleCopy($col, $row, $sheetNo, $refCol, $refRow, $refSheet); |
|
117
|
|
|
} |
|
118
|
|
|
} |
|
119
|
|
|
|
|
120
|
|
|
/** |
|
121
|
|
|
* setImage |
|
122
|
|
|
* 画像のセット |
|
123
|
|
|
* @param string $img 画像のファイルパス |
|
124
|
|
|
* @param integer $col 行 |
|
125
|
|
|
* @param integer $row 列 |
|
126
|
|
|
* @param integer $sheetNo シート番号 |
|
127
|
|
|
* @param integer $height 画像の縦幅 |
|
128
|
|
|
* @param integer $width 画像の横幅 |
|
129
|
|
|
* @param boolean $proportial 縦横比を維持するか |
|
130
|
|
|
* @param integer $offsetx セルから何ピクセルずらすか(X軸) |
|
131
|
|
|
* @param integer $offsety セルから何ピクセルずらすか(Y軸) |
|
132
|
|
|
* @author hagiwara |
|
133
|
|
|
*/ |
|
134
|
|
|
public function setImage($img, $col, $row, $sheetNo = 0, $height = null, $width = null, $proportial = false, $offsetx = null, $offsety = null) |
|
135
|
|
|
{ |
|
136
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
137
|
|
|
|
|
138
|
|
|
$objDrawing = new PHPExcel_Worksheet_Drawing();//画像用のオプジェクト作成 |
|
139
|
|
|
$objDrawing->setPath($img);//貼り付ける画像のパスを指定 |
|
140
|
|
|
$objDrawing->setCoordinates($cellInfo);//位置 |
|
141
|
|
|
if (!is_null($height)) { |
|
142
|
|
|
$objDrawing->setHeight($height);//画像の高さを指定 |
|
143
|
|
|
} |
|
144
|
|
|
if (!is_null($width)) { |
|
145
|
|
|
$objDrawing->setWidth($width);//画像の高さを指定 |
|
146
|
|
|
} |
|
147
|
|
|
if (!is_null($proportial)) { |
|
148
|
|
|
$objDrawing->setResizeProportional($proportial);//縦横比の変更なし |
|
149
|
|
|
} |
|
150
|
|
|
if (!is_null($offsetx)) { |
|
151
|
|
|
$objDrawing->setOffsetX($offsetx);//指定した位置からどれだけ横方向にずらすか。 |
|
152
|
|
|
} |
|
153
|
|
|
if (!is_null($offsety)) { |
|
154
|
|
|
$objDrawing->setOffsetY($offsety);//指定した位置からどれだけ縦方向にずらすか。 |
|
155
|
|
|
} |
|
156
|
|
|
$objDrawing->setWorksheet($this->getSheet($sheetNo)); |
|
157
|
|
|
} |
|
158
|
|
|
|
|
159
|
|
|
/** |
|
160
|
|
|
* cellMerge |
|
161
|
|
|
* セルのマージ |
|
162
|
|
|
* @param integer $col1 行 |
|
163
|
|
|
* @param integer $row1 列 |
|
164
|
|
|
* @param integer $col2 行 |
|
165
|
|
|
* @param integer $row2 列 |
|
166
|
|
|
* @param integer $sheetNo シート番号 |
|
167
|
|
|
* @author hagiwara |
|
168
|
|
|
*/ |
|
169
|
|
|
public function cellMerge($col1, $row1, $col2, $row2, $sheetNo) |
|
170
|
|
|
{ |
|
171
|
|
|
$cell1Info = $this->cellInfo($col1, $row1); |
|
172
|
|
|
$cell2Info = $this->cellInfo($col2, $row2); |
|
173
|
|
|
|
|
174
|
|
|
$this->getSheet($sheetNo)->mergeCells($cell1Info . ':' . $cell2Info); |
|
175
|
|
|
} |
|
176
|
|
|
|
|
177
|
|
|
|
|
178
|
|
|
/** |
|
179
|
|
|
* styleCopy |
|
180
|
|
|
* セルの書式コピー |
|
181
|
|
|
* @param integer $col 行 |
|
182
|
|
|
* @param integer $row 列 |
|
183
|
|
|
* @param integer $sheetNo シート番号 |
|
184
|
|
|
* @param integer $refCol 参照セル行 |
|
185
|
|
|
* @param integer $refRow 参照セル列 |
|
186
|
|
|
* @param integer $refSheet 参照シート |
|
187
|
|
|
* @author hagiwara |
|
188
|
|
|
*/ |
|
189
|
|
|
public function styleCopy($col, $row, $sheetNo, $refCol, $refRow, $refSheet) |
|
190
|
|
|
{ |
|
191
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
192
|
|
|
$refCellInfo = $this->cellInfo($refCol, $refRow); |
|
193
|
|
|
$style = $this->getSheet($refSheet)->getStyle($refCellInfo); |
|
194
|
|
|
|
|
195
|
|
|
$this->getSheet($sheetNo)->duplicateStyle($style, $cellInfo); |
|
196
|
|
|
} |
|
197
|
|
|
|
|
198
|
|
|
/** |
|
199
|
|
|
* setStyle |
|
200
|
|
|
* 書式のセット(まとめて) |
|
201
|
|
|
* @param integer $col 行 |
|
202
|
|
|
* @param integer $row 列 |
|
203
|
|
|
* @param integer $sheetNo シート番号 |
|
204
|
|
|
* @param array $style スタイル情報 |
|
205
|
|
|
* @author hagiwara |
|
206
|
|
|
*/ |
|
207
|
|
|
public function setStyle($col, $row, $sheetNo, $style) |
|
208
|
|
|
{ |
|
209
|
|
|
$default_style = [ |
|
210
|
|
|
'font' => null, |
|
211
|
|
|
'underline' => null, |
|
212
|
|
|
'bold' => null, |
|
213
|
|
|
'italic' => null, |
|
214
|
|
|
'strikethrough' => null, |
|
215
|
|
|
'color' => null, |
|
216
|
|
|
'size' => null, |
|
217
|
|
|
'alignh' => null, |
|
218
|
|
|
'alignv' => null, |
|
219
|
|
|
'bgcolor' => null, |
|
220
|
|
|
'bgpattern' => null, |
|
221
|
|
|
]; |
|
222
|
|
|
$style = array_merge($default_style, $style); |
|
223
|
|
|
$this->setFontName($col, $row, $sheetNo, $style['font']); |
|
224
|
|
|
$this->setUnderline($col, $row, $sheetNo, $style['underline']); |
|
225
|
|
|
$this->setFontBold($col, $row, $sheetNo, $style['bold']); |
|
226
|
|
|
$this->setItalic($col, $row, $sheetNo, $style['italic']); |
|
227
|
|
|
$this->setStrikethrough($col, $row, $sheetNo, $style['strikethrough']); |
|
228
|
|
|
$this->setColor($col, $row, $sheetNo, $style['color']); |
|
229
|
|
|
$this->setSize($col, $row, $sheetNo, $style['size']); |
|
230
|
|
|
$this->setAlignHolizonal($col, $row, $sheetNo, $style['alignh']); |
|
231
|
|
|
$this->setAlignVertical($col, $row, $sheetNo, $style['alignv']); |
|
232
|
|
|
$this->setBackgroundColor($col, $row, $sheetNo, $style['bgcolor'], $style['bgpattern']); |
|
233
|
|
|
} |
|
234
|
|
|
|
|
235
|
|
|
/** |
|
236
|
|
|
* setFontName |
|
237
|
|
|
* フォントのセット |
|
238
|
|
|
* @param integer $col 行 |
|
239
|
|
|
* @param integer $row 列 |
|
240
|
|
|
* @param integer $sheetNo シート番号 |
|
241
|
|
|
* @param string|null $fontName フォント名 |
|
242
|
|
|
* @author hagiwara |
|
243
|
|
|
*/ |
|
244
|
|
View Code Duplication |
public function setFontName($col, $row, $sheetNo, $fontName) |
|
|
|
|
|
|
245
|
|
|
{ |
|
246
|
|
|
if (is_null($fontName)) { |
|
247
|
|
|
return; |
|
248
|
|
|
} |
|
249
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
|
|
|
|
|
250
|
|
|
$this->getFont($col, $row, $sheetNo)->setName($fontName); |
|
251
|
|
|
} |
|
252
|
|
|
|
|
253
|
|
|
/** |
|
254
|
|
|
* setUnderline |
|
255
|
|
|
* 下線のセット |
|
256
|
|
|
* @param integer $col 行 |
|
257
|
|
|
* @param integer $row 列 |
|
258
|
|
|
* @param integer $sheetNo シート番号 |
|
259
|
|
|
* @param boolean|null $underline 下線を引くか |
|
260
|
|
|
* @author hagiwara |
|
261
|
|
|
*/ |
|
262
|
|
|
public function setUnderline($col, $row, $sheetNo, $underline) |
|
263
|
|
|
{ |
|
264
|
|
|
if (is_null($underline)) { |
|
265
|
|
|
return; |
|
266
|
|
|
} |
|
267
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
|
|
|
|
|
268
|
|
|
$this->getFont($col, $row, $sheetNo)->setUnderline($underline); |
|
269
|
|
|
} |
|
270
|
|
|
|
|
271
|
|
|
/** |
|
272
|
|
|
* setFontBold |
|
273
|
|
|
* 太字のセット |
|
274
|
|
|
* @param integer $col 行 |
|
275
|
|
|
* @param integer $row 列 |
|
276
|
|
|
* @param integer $sheetNo シート番号 |
|
277
|
|
|
* @param boolean|null $bold 太字を引くか |
|
278
|
|
|
* @author hagiwara |
|
279
|
|
|
*/ |
|
280
|
|
View Code Duplication |
public function setFontBold($col, $row, $sheetNo, $bold) |
|
|
|
|
|
|
281
|
|
|
{ |
|
282
|
|
|
if (is_null($bold)) { |
|
283
|
|
|
return; |
|
284
|
|
|
} |
|
285
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
|
|
|
|
|
286
|
|
|
$this->getFont($col, $row, $sheetNo)->setBold($bold); |
|
287
|
|
|
} |
|
288
|
|
|
|
|
289
|
|
|
/** |
|
290
|
|
|
* setItalic |
|
291
|
|
|
* イタリックのセット |
|
292
|
|
|
* @param integer $col 行 |
|
293
|
|
|
* @param integer $row 列 |
|
294
|
|
|
* @param integer $sheetNo シート番号 |
|
295
|
|
|
* @param boolean|null $italic イタリックにするか |
|
296
|
|
|
* @author hagiwara |
|
297
|
|
|
*/ |
|
298
|
|
|
public function setItalic($col, $row, $sheetNo, $italic) |
|
299
|
|
|
{ |
|
300
|
|
|
if (is_null($italic)) { |
|
301
|
|
|
return; |
|
302
|
|
|
} |
|
303
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
|
|
|
|
|
304
|
|
|
$this->getFont($col, $row, $sheetNo)->setItalic($italic); |
|
305
|
|
|
} |
|
306
|
|
|
|
|
307
|
|
|
/** |
|
308
|
|
|
* setStrikethrough |
|
309
|
|
|
* 打ち消し線のセット |
|
310
|
|
|
* @param integer $col 行 |
|
311
|
|
|
* @param integer $row 列 |
|
312
|
|
|
* @param integer $sheetNo シート番号 |
|
313
|
|
|
* @param boolean|null $strikethrough 打ち消し線をつけるか |
|
314
|
|
|
* @author hagiwara |
|
315
|
|
|
*/ |
|
316
|
|
|
public function setStrikethrough($col, $row, $sheetNo, $strikethrough) |
|
317
|
|
|
{ |
|
318
|
|
|
if (is_null($strikethrough)) { |
|
319
|
|
|
return; |
|
320
|
|
|
} |
|
321
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
|
|
|
|
|
322
|
|
|
$this->getFont($col, $row, $sheetNo)->setStrikethrough($strikethrough); |
|
323
|
|
|
} |
|
324
|
|
|
|
|
325
|
|
|
/** |
|
326
|
|
|
* setColor |
|
327
|
|
|
* 文字の色 |
|
328
|
|
|
* @param integer $col 行 |
|
329
|
|
|
* @param integer $row 列 |
|
330
|
|
|
* @param integer $sheetNo シート番号 |
|
331
|
|
|
* @param string|null $color 色(ARGB) |
|
332
|
|
|
* @author hagiwara |
|
333
|
|
|
*/ |
|
334
|
|
|
public function setColor($col, $row, $sheetNo, $color) |
|
335
|
|
|
{ |
|
336
|
|
|
if (is_null($color)) { |
|
337
|
|
|
return; |
|
338
|
|
|
} |
|
339
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
|
|
|
|
|
340
|
|
|
$this->getFont($col, $row, $sheetNo)->getColor()->setARGB($color); |
|
341
|
|
|
} |
|
342
|
|
|
|
|
343
|
|
|
/** |
|
344
|
|
|
* setSize |
|
345
|
|
|
* 文字サイズ |
|
346
|
|
|
* @param integer $col 行 |
|
347
|
|
|
* @param integer $row 列 |
|
348
|
|
|
* @param integer $sheetNo シート番号 |
|
349
|
|
|
* @param integer|null $size |
|
350
|
|
|
* @author hagiwara |
|
351
|
|
|
*/ |
|
352
|
|
View Code Duplication |
public function setSize($col, $row, $sheetNo, $size) |
|
|
|
|
|
|
353
|
|
|
{ |
|
354
|
|
|
if (is_null($size)) { |
|
355
|
|
|
return; |
|
356
|
|
|
} |
|
357
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
|
|
|
|
|
358
|
|
|
$this->getFont($col, $row, $sheetNo)->setSize($size); |
|
359
|
|
|
} |
|
360
|
|
|
|
|
361
|
|
|
private function getFont($col, $row, $sheetNo) |
|
362
|
|
|
{ |
|
363
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
364
|
|
|
return $this->getSheet($sheetNo)->getStyle($cellInfo)->getFont(); |
|
365
|
|
|
} |
|
366
|
|
|
|
|
367
|
|
|
/** |
|
368
|
|
|
* setAlignHolizonal |
|
369
|
|
|
* 水平方向のalign |
|
370
|
|
|
* @param integer $col 行 |
|
371
|
|
|
* @param integer $row 列 |
|
372
|
|
|
* @param integer $sheetNo シート番号 |
|
373
|
|
|
* @param string|null $type |
|
374
|
|
|
* typeはgetAlignHolizonalType参照 |
|
375
|
|
|
* @author hagiwara |
|
376
|
|
|
*/ |
|
377
|
|
View Code Duplication |
public function setAlignHolizonal($col, $row, $sheetNo, $type) |
|
|
|
|
|
|
378
|
|
|
{ |
|
379
|
|
|
if (is_null($type)) { |
|
380
|
|
|
return; |
|
381
|
|
|
} |
|
382
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
383
|
|
|
$this->getSheet($sheetNo)->getStyle($cellInfo)->getAlignment()->setHorizontal($this->getAlignHolizonalType($type)); |
|
384
|
|
|
} |
|
385
|
|
|
|
|
386
|
|
|
/** |
|
387
|
|
|
* setAlignVertical |
|
388
|
|
|
* 垂直方法のalign |
|
389
|
|
|
* @param integer $col 行 |
|
390
|
|
|
* @param integer $row 列 |
|
391
|
|
|
* @param integer $sheetNo シート番号 |
|
392
|
|
|
* @param string|null $type |
|
393
|
|
|
* typeはgetAlignVerticalType参照 |
|
394
|
|
|
* @author hagiwara |
|
395
|
|
|
*/ |
|
396
|
|
View Code Duplication |
public function setAlignVertical($col, $row, $sheetNo, $type) |
|
|
|
|
|
|
397
|
|
|
{ |
|
398
|
|
|
if (is_null($type)) { |
|
399
|
|
|
return; |
|
400
|
|
|
} |
|
401
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
402
|
|
|
$this->getSheet($sheetNo)->getStyle($cellInfo)->getAlignment()->setVertical($this->getAlignVerticalType($type)); |
|
403
|
|
|
} |
|
404
|
|
|
|
|
405
|
|
|
/** |
|
406
|
|
|
* setBorder |
|
407
|
|
|
* 罫線の設定 |
|
408
|
|
|
* @param integer $col 行 |
|
409
|
|
|
* @param integer $row 列 |
|
410
|
|
|
* @param integer $sheetNo シート番号 |
|
411
|
|
|
* @param array $border |
|
412
|
|
|
* borderの内部はgetBorderType参照 |
|
413
|
|
|
* @author hagiwara |
|
414
|
|
|
*/ |
|
415
|
|
|
public function setBorder($col, $row, $sheetNo, $border) |
|
416
|
|
|
{ |
|
417
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
418
|
|
|
$default_border = [ |
|
419
|
|
|
'left' => null, |
|
420
|
|
|
'right' => null, |
|
421
|
|
|
'top' => null, |
|
422
|
|
|
'bottom' => null, |
|
423
|
|
|
'diagonal' => null, |
|
424
|
|
|
'all_borders' => null, |
|
425
|
|
|
'outline' => null, |
|
426
|
|
|
'inside' => null, |
|
427
|
|
|
'vertical' => null, |
|
428
|
|
|
'horizontal' => null, |
|
429
|
|
|
]; |
|
430
|
|
|
$border = array_merge($default_border, $border); |
|
431
|
|
|
foreach ($border as $border_position => $border_setting) { |
|
432
|
|
|
if (!is_null($border_setting)) { |
|
433
|
|
|
$borderInfo = $this->getSheet($sheetNo)->getStyle($cellInfo)->getBorders()->{'get' . $this->camelize($border_position)}(); |
|
434
|
|
|
if (array_key_exists('type', $border_setting)) { |
|
435
|
|
|
$borderInfo->setBorderStyle($this->getBorderType($border_setting['type'])); |
|
436
|
|
|
} |
|
437
|
|
|
if (array_key_exists('color', $border_setting)) { |
|
438
|
|
|
$borderInfo->getColor()->setARGB($border_setting['color']); |
|
439
|
|
|
} |
|
440
|
|
|
} |
|
441
|
|
|
} |
|
442
|
|
|
} |
|
443
|
|
|
|
|
444
|
|
|
/** |
|
445
|
|
|
* setBackgroundColor |
|
446
|
|
|
* 背景色の設定 |
|
447
|
|
|
* @param integer $col 行 |
|
448
|
|
|
* @param integer $row 列 |
|
449
|
|
|
* @param integer $sheetNo シート番号 |
|
450
|
|
|
* @param string $color 色 |
|
451
|
|
|
* @param string $fillType 塗りつぶし方(デフォルトsolid) |
|
452
|
|
|
* fillTypeの内部はgetFillType参照 |
|
453
|
|
|
* @author hagiwara |
|
454
|
|
|
*/ |
|
455
|
|
|
public function setBackgroundColor($col, $row, $sheetNo, $color, $fillType = 'solid') |
|
456
|
|
|
{ |
|
457
|
|
|
$cellInfo = $this->cellInfo($col, $row); |
|
458
|
|
|
|
|
459
|
|
|
$this->getSheet($sheetNo)->getStyle($cellInfo)->getFill()->setFillType($this->getFillType($fillType))->getStartColor()->setARGB($color); |
|
460
|
|
|
} |
|
461
|
|
|
|
|
462
|
|
|
/** |
|
463
|
|
|
* getBorderType |
|
464
|
|
|
* 罫線の種類の設定 |
|
465
|
|
|
* @param string $type |
|
466
|
|
|
* @author hagiwara |
|
467
|
|
|
*/ |
|
468
|
|
|
private function getBorderType($type) |
|
469
|
|
|
{ |
|
470
|
|
|
$type_list = self::$__borderType; |
|
471
|
|
|
if (array_key_exists($type, $type_list)) { |
|
472
|
|
|
return $type_list[$type]; |
|
473
|
|
|
} |
|
474
|
|
|
return PHPExcel_Style_Border::BORDER_NONE; |
|
475
|
|
|
} |
|
476
|
|
|
|
|
477
|
|
|
/** |
|
478
|
|
|
* getAlignHolizonalType |
|
479
|
|
|
* 水平方向のAlignの設定 |
|
480
|
|
|
* @param string $type |
|
481
|
|
|
* @author hagiwara |
|
482
|
|
|
*/ |
|
483
|
|
|
private function getAlignHolizonalType($type) |
|
484
|
|
|
{ |
|
485
|
|
|
$type_list = self::$__alignHolizonalType; |
|
486
|
|
|
if (array_key_exists($type, $type_list)) { |
|
487
|
|
|
return $type_list[$type]; |
|
488
|
|
|
} |
|
489
|
|
|
return PHPExcel_Style_Alignment::HORIZONTAL_GENERAL; |
|
490
|
|
|
} |
|
491
|
|
|
|
|
492
|
|
|
/** |
|
493
|
|
|
* getAlignVerticalType |
|
494
|
|
|
* 垂直方向のAlignの設定 |
|
495
|
|
|
* @param string $type |
|
496
|
|
|
* @author hagiwara |
|
497
|
|
|
*/ |
|
498
|
|
|
private function getAlignVerticalType($type) |
|
499
|
|
|
{ |
|
500
|
|
|
$type_list = self::$__alignVerticalType; |
|
501
|
|
|
if (array_key_exists($type, $type_list)) { |
|
502
|
|
|
return $type_list[$type]; |
|
503
|
|
|
} |
|
504
|
|
|
return null; |
|
505
|
|
|
} |
|
506
|
|
|
|
|
507
|
|
|
/** |
|
508
|
|
|
* getFillType |
|
509
|
|
|
* 塗りつぶしの設定 |
|
510
|
|
|
* @param string $type |
|
511
|
|
|
* @author hagiwara |
|
512
|
|
|
*/ |
|
513
|
|
|
private function getFillType($type) |
|
514
|
|
|
{ |
|
515
|
|
|
$type_list = self::$__fillType; |
|
516
|
|
|
if (array_key_exists($type, $type_list)) { |
|
517
|
|
|
return $type_list[$type]; |
|
518
|
|
|
} |
|
519
|
|
|
return PHPExcel_Style_Fill::FILL_SOLID; |
|
520
|
|
|
} |
|
521
|
|
|
|
|
522
|
|
|
/** |
|
523
|
|
|
* createSheet |
|
524
|
|
|
* シートの作成 |
|
525
|
|
|
* @param string $name |
|
526
|
|
|
* @author hagiwara |
|
527
|
|
|
*/ |
|
528
|
|
|
public function createSheet($name = null) |
|
529
|
|
|
{ |
|
530
|
|
|
//シートの新規作成 |
|
531
|
|
|
$newSheet = $this->__phpexcel->createSheet(); |
|
532
|
|
|
$sheetNo = $this->__phpexcel->getIndex($newSheet); |
|
533
|
|
|
$this->__sheet[$sheetNo] = $newSheet; |
|
534
|
|
|
if (!is_null($name)) { |
|
535
|
|
|
$this->renameSheet($sheetNo, $name); |
|
536
|
|
|
} |
|
537
|
|
|
} |
|
538
|
|
|
|
|
539
|
|
|
/** |
|
540
|
|
|
* deleteSheet |
|
541
|
|
|
* シートの削除 |
|
542
|
|
|
* @param integer $sheetNo |
|
543
|
|
|
* @author hagiwara |
|
544
|
|
|
*/ |
|
545
|
|
|
public function deleteSheet($sheetNo) |
|
546
|
|
|
{ |
|
547
|
|
|
//シートの削除は一番最後に行う |
|
548
|
|
|
$this->__deleteSheetList[] = $sheetNo; |
|
549
|
|
|
} |
|
550
|
|
|
|
|
551
|
|
|
/** |
|
552
|
|
|
* copySheet |
|
553
|
|
|
* シートのコピー |
|
554
|
|
|
* @param integer $sheetNo |
|
555
|
|
|
* @param integer $position |
|
556
|
|
|
* @param string $name |
|
557
|
|
|
* @author hagiwara |
|
558
|
|
|
*/ |
|
559
|
|
|
public function copySheet($sheetNo, $position = null, $name = null) |
|
560
|
|
|
{ |
|
561
|
|
|
$base = $this->getSheet($sheetNo)->copy(); |
|
562
|
|
|
if ($name === null) { |
|
563
|
|
|
$name = uniqid(); |
|
564
|
|
|
} |
|
565
|
|
|
$base->setTitle($name); |
|
566
|
|
|
|
|
567
|
|
|
// $positionが null(省略時含む)の場合は最後尾に追加される |
|
568
|
|
|
$this->__phpexcel->addSheet($base, $position); |
|
569
|
|
|
} |
|
570
|
|
|
|
|
571
|
|
|
/** |
|
572
|
|
|
* renameSheet |
|
573
|
|
|
* シート名の変更 |
|
574
|
|
|
* @param integer $sheetNo |
|
575
|
|
|
* @param string $name |
|
576
|
|
|
* @author hagiwara |
|
577
|
|
|
*/ |
|
578
|
|
|
public function renameSheet($sheetNo, $name) |
|
579
|
|
|
{ |
|
580
|
|
|
$this->__sheet[$sheetNo]->setTitle($name); |
|
581
|
|
|
} |
|
582
|
|
|
|
|
583
|
|
|
/** |
|
584
|
|
|
* write |
|
585
|
|
|
* xlsxファイルの書き込み |
|
586
|
|
|
* @param string $file 書き込み先のファイルパス |
|
587
|
|
|
* @author hagiwara |
|
588
|
|
|
*/ |
|
589
|
|
|
public function write($file, $type = 'Excel2007') |
|
590
|
|
|
{ |
|
591
|
|
|
//書き込み前に削除シートを削除する |
|
592
|
|
|
foreach ($this->__deleteSheetList as $deleteSheet) { |
|
593
|
|
|
$this->__phpexcel->removeSheetByIndex($deleteSheet); |
|
594
|
|
|
} |
|
595
|
|
|
$writer = PHPExcel_IOFactory::createWriter($this->__phpexcel, $type); |
|
596
|
|
|
$writer->save($file); |
|
597
|
|
|
} |
|
598
|
|
|
|
|
599
|
|
|
/** |
|
600
|
|
|
* getReader |
|
601
|
|
|
* readerを返す(※直接PHPExcelの関数を実行できるように) |
|
602
|
|
|
* @author hagiwara |
|
603
|
|
|
*/ |
|
604
|
|
|
public function getReader() |
|
605
|
|
|
{ |
|
606
|
|
|
return $this->__phpexcel; |
|
607
|
|
|
} |
|
608
|
|
|
|
|
609
|
|
|
/** |
|
610
|
|
|
* getSheet |
|
611
|
|
|
* シート情報の読み込み |
|
612
|
|
|
* @param integer $sheetNo シート番号 |
|
613
|
|
|
* @author hagiwara |
|
614
|
|
|
* @return null|\PHPExcel_Worksheet |
|
615
|
|
|
*/ |
|
616
|
|
|
private function getSheet($sheetNo) |
|
617
|
|
|
{ |
|
618
|
|
|
if (!array_key_exists($sheetNo, $this->__sheet)) { |
|
619
|
|
|
$this->__sheet[$sheetNo] = $this->__phpexcel->setActiveSheetIndex(0); |
|
620
|
|
|
} |
|
621
|
|
|
return $this->__sheet[$sheetNo]; |
|
622
|
|
|
} |
|
623
|
|
|
|
|
624
|
|
|
/** |
|
625
|
|
|
* cellInfo |
|
626
|
|
|
* R1C1参照をA1参照に変換して返す |
|
627
|
|
|
* @param integer $col 行 |
|
628
|
|
|
* @param integer $row 列 |
|
629
|
|
|
* @author hagiwara |
|
630
|
|
|
*/ |
|
631
|
|
|
private function cellInfo($col, $row) |
|
632
|
|
|
{ |
|
633
|
|
|
$stringCol = PHPExcel_Cell::stringFromColumnIndex($col); |
|
634
|
|
|
return $stringCol . $row; |
|
635
|
|
|
} |
|
636
|
|
|
|
|
637
|
|
|
/** |
|
638
|
|
|
* cellInfo |
|
639
|
|
|
* http://qiita.com/Hiraku/items/036080976884fad1e450 |
|
640
|
|
|
* @param string $str |
|
641
|
|
|
*/ |
|
642
|
|
|
private function camelize($str) |
|
643
|
|
|
{ |
|
644
|
|
|
$str = ucwords($str, '_'); |
|
645
|
|
|
return str_replace('_', '', $str); |
|
646
|
|
|
} |
|
647
|
|
|
} |
|
648
|
|
|
|
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.