teratail
回答率
85.47%
質問するログイン新規登録
トップに関する質問php-excel2htmlを使用し、Excel 形式のファイルをHTMLのtableタグに変換したい

編集履歴

質問編集履歴

4

誤字

2019/04/30 07:37

投稿

soraatori
soraatori

スコア55

test CHANGED
File without changes
test CHANGED
File without changes

3

誤字

2019/04/30 07:37

投稿

soraatori
soraatori

スコア55

test CHANGED
File without changes
test CHANGED
@@ -14,7 +14,7 @@
14
14
 
15
15
  ```
16
16
 
17
- Fatal error: Uncaught Error: Class 'tomk79\excel2html\main' not found in /xxxx
17
+ Fatal error: Uncaught ArgumentCountError: Too few arguments to function tomk79\excel2html\render_html_by_excel::__construct(), 1 passed in /xxxx
18
18
 
19
19
  ```
20
20
 
@@ -32,7 +32,7 @@
32
32
 
33
33
 
34
34
 
35
- $src = (new \tomk79\excel2html\main(__DIR__.'/data/excel.xlsx'))->get_html(array(
35
+ $src = (new \tomk79\excel2html\render_html_by_excel(__DIR__.'/data/excel.xlsx'))->get_html(array(
36
36
 
37
37
  'renderer'=>'simplify'
38
38
 

2

誤字

2019/04/30 04:55

投稿

soraatori
soraatori

スコア55

test CHANGED
File without changes
test CHANGED
@@ -48,7 +48,7 @@
48
48
 
49
49
 
50
50
 
51
- ```render_html_by_ezcel.php
51
+ ```php
52
52
 
53
53
  <?php
54
54
 

1

コードの追加

2019/04/30 04:44

投稿

soraatori
soraatori

スコア55

test CHANGED
File without changes
test CHANGED
@@ -45,3 +45,505 @@
45
45
  ?>
46
46
 
47
47
  ```
48
+
49
+
50
+
51
+ ```render_html_by_ezcel.php
52
+
53
+ <?php
54
+
55
+ /**
56
+
57
+ * excel2html - render_html_by_excel.php
58
+
59
+ */
60
+
61
+ namespace tomk79\excel2html;
62
+
63
+ /**
64
+
65
+ * class render_html_by_excel
66
+
67
+ */
68
+
69
+ class render_html_by_excel{
70
+
71
+ /**
72
+
73
+ * filesystem utility
74
+
75
+ */
76
+
77
+ private $fs;
78
+
79
+ /**
80
+
81
+ * input xlsx filename
82
+
83
+ */
84
+
85
+ private $filename;
86
+
87
+ /**
88
+
89
+ * PHPExcel Object
90
+
91
+ */
92
+
93
+ private $objPHPExcel;
94
+
95
+ /**
96
+
97
+ * constructor
98
+
99
+ */
100
+
101
+ public function __construct( $fs, $filename ){
102
+
103
+ $this->fs = $fs;
104
+
105
+ $this->filename = $filename;
106
+
107
+ $this->objPHPExcel = \PHPExcel_IOFactory::load( $this->filename );
108
+
109
+ }
110
+
111
+ /**
112
+
113
+ * Excel ファイルからHTMLを描画する
114
+
115
+ */
116
+
117
+ public function render( $options ){
118
+
119
+ $skipCell = array();
120
+
121
+ $objWorksheet = $this->objPHPExcel->getActiveSheet();
122
+
123
+ $mergedCells = $objWorksheet->getMergeCells();
124
+
125
+ // var_dump($mergedCells);
126
+
127
+ // セル幅を記憶
128
+
129
+ $col_widths = array();
130
+
131
+ foreach ($objWorksheet->getRowIterator() as $rowIdx=>$row) {
132
+
133
+ $cellIterator = $row->getCellIterator();
134
+
135
+ try {
136
+
137
+ $cellIterator->setIterateOnlyExistingCells(true);
138
+
139
+ // This loops through all cells,
140
+
141
+ // even if a cell value is not set.
142
+
143
+ // By default, only cells that have a value
144
+
145
+ // set will be iterated.
146
+
147
+ } catch (\PHPExcel_Exception $e) {
148
+
149
+ }
150
+
151
+ foreach ($cellIterator as $colIdxName=>$cell) {
152
+
153
+ $colIdx = \PHPExcel_Cell::columnIndexFromString( $colIdxName );
154
+
155
+ $col_widths[$colIdx] = intval( $objWorksheet->getColumnDimension($colIdxName)->getWidth() );
156
+
157
+ }
158
+
159
+ break;
160
+
161
+ }
162
+
163
+ $col_width_sum = array_sum($col_widths);
164
+
165
+ ob_start();
166
+
167
+ $thead = '';
168
+
169
+ $tbody = '';
170
+
171
+ foreach ($objWorksheet->getRowIterator() as $rowIdx=>$row) {
172
+
173
+ // var_dump($rowIdx); //← $rowIdx は1から始まります
174
+
175
+ $tmpRow = '';
176
+
177
+ $tmpRow .= '<tr>'.PHP_EOL;
178
+
179
+ $cellIterator = $row->getCellIterator();
180
+
181
+ try {
182
+
183
+ $cellIterator->setIterateOnlyExistingCells(true);
184
+
185
+ // This loops through all cells,
186
+
187
+ // even if a cell value is not set.
188
+
189
+ // By default, only cells that have a value
190
+
191
+ // set will be iterated.
192
+
193
+ } catch (\PHPExcel_Exception $e) {
194
+
195
+ }
196
+
197
+ foreach ($cellIterator as $colIdxName=>$cell) {
198
+
199
+ $colIdx = \PHPExcel_Cell::columnIndexFromString( $colIdxName );
200
+
201
+ // var_dump($colIdx);
202
+
203
+ $rowspan = 1;
204
+
205
+ $colspan = 1;
206
+
207
+ if( @$skipCell[$colIdxName.$rowIdx] ){
208
+
209
+ continue;
210
+
211
+ }
212
+
213
+ foreach($mergedCells as $mergedCell){//連結セルの検索
214
+
215
+ if( preg_match('/^'.preg_quote($colIdxName.$rowIdx).'\:([a-zA-Z]+)([0-9]+)$/', $mergedCell, $matched) ){
216
+
217
+ $maxIdxC = \PHPExcel_Cell::columnIndexFromString( $matched[1] );
218
+
219
+ // var_dump($colIdx);
220
+
221
+ // var_dump(\PHPExcel_Cell::stringFromColumnIndex($colIdx-1));
222
+
223
+ // var_dump($maxIdxC);
224
+
225
+ $maxIdxR = intval($matched[2]);
226
+
227
+ for( $idxC=$colIdx; $idxC<=$maxIdxC; $idxC++ ){
228
+
229
+ for( $idxR=$rowIdx; $idxR<=$maxIdxR; $idxR++ ){
230
+
231
+ $skipCell[\PHPExcel_Cell::stringFromColumnIndex($idxC-1).$idxR] = \PHPExcel_Cell::stringFromColumnIndex($idxC-1).$idxR;
232
+
233
+ }
234
+
235
+ }
236
+
237
+ $colspan = $maxIdxC-$colIdx+1;
238
+
239
+ $rowspan = $maxIdxR-$rowIdx+1;
240
+
241
+ break;
242
+
243
+ }
244
+
245
+ }
246
+
247
+ // var_dump($colIdx); //← $colIdx は1から始まります
248
+
249
+ $cellTagName = 'td';
250
+
251
+ if( $rowIdx <= $options['header_row'] || $colIdx <= $options['header_col'] ){
252
+
253
+ $cellTagName = 'th';
254
+
255
+ }
256
+
257
+ $cellValue = $cell->getFormattedValue();
258
+
259
+ switch( $options['cell_renderer'] ){
260
+
261
+ case 'text':
262
+
263
+ $cellValue = htmlspecialchars($cellValue);
264
+
265
+ $cellValue = preg_replace('/\r\n|\r|\n/', '<br />', $cellValue);
266
+
267
+ break;
268
+
269
+ case 'html':
270
+
271
+ break;
272
+
273
+ case 'markdown':
274
+
275
+ $cellValue = \Michelf\MarkdownExtra::defaultTransform($cellValue);
276
+
277
+ break;
278
+
279
+ }
280
+
281
+ // var_dump( $cell->getNumberFormat() );
282
+
283
+ // セルのスタイルを調べて、CSSを生成
284
+
285
+ $styles = array();
286
+
287
+ $cellStyle = $cell->getStyle();
288
+
289
+ // print('<pre>');
290
+
291
+ // // $cellStyle->getBorders()->getOutline();
292
+
293
+ // var_dump($cellStyle->getBorders()->getLeft()->getColor()->getRGB());
294
+
295
+ // print('</pre>');
296
+
297
+ if( $options['render_cell_align'] ){
298
+
299
+ if( $cellStyle->getAlignment()->getHorizontal() != 'general' ){
300
+
301
+ // text-align は、単純化設定でも出力する
302
+
303
+ array_push( $styles, 'text-align: '.strtolower($cellStyle->getAlignment()->getHorizontal()).';' );
304
+
305
+ }
306
+
307
+ }
308
+
309
+ if( $options['render_cell_height'] ){
310
+
311
+ array_push( $styles, 'height: '.intval($objWorksheet->getRowDimension($rowIdx)->getRowHeight()).'px;' );
312
+
313
+ }
314
+
315
+ if( $options['render_cell_font'] ){
316
+
317
+ array_push( $styles, 'color: #'.strtolower($cellStyle->getFont()->getColor()->getRGB()).';' );
318
+
319
+ array_push( $styles, 'font-weight: '.($cellStyle->getFont()->getBold()?'bold':'normal').';' );
320
+
321
+ array_push( $styles, 'font-size: '.intval($cellStyle->getFont()->getsize()/12*100).'%;' );
322
+
323
+ }
324
+
325
+ if( $options['render_cell_background'] ){
326
+
327
+ array_push( $styles, 'background-color: #'.strtolower($cellStyle->getFill()->getStartColor()->getRGB()).';' );
328
+
329
+ }
330
+
331
+ if( $options['render_cell_vertical_align'] ){
332
+
333
+ $verticalAlign = strtolower($cellStyle->getAlignment()->getVertical());
334
+
335
+ array_push( $styles, 'vertical-align: '.($verticalAlign=='center'?'middle':$verticalAlign).';' );
336
+
337
+ }
338
+
339
+ if( $options['render_cell_borders'] ){
340
+
341
+ array_push( $styles, 'border-top: '.$this->get_borderstyle_by_border($cellStyle->getBorders()->getTop()).';' );
342
+
343
+ array_push( $styles, 'border-right: '.$this->get_borderstyle_by_border($cellStyle->getBorders()->getRight()).';' );
344
+
345
+ array_push( $styles, 'border-bottom: '.$this->get_borderstyle_by_border($cellStyle->getBorders()->getBottom()).';' );
346
+
347
+ array_push( $styles, 'border-left: '.$this->get_borderstyle_by_border($cellStyle->getBorders()->getLeft()).';' );
348
+
349
+ }
350
+
351
+ $tmpRow .= '<'.$cellTagName.($rowspan>1?' rowspan="'.$rowspan.'"':'').($colspan>1?' colspan="'.$colspan.'"':'').''.(count($styles)?' style="'.htmlspecialchars(implode(' ',$styles)).'"':'').'>';
352
+
353
+ $tmpRow .= $cellValue;
354
+
355
+ // $tmpRow .= $cellStyle->getFill()->getFillType();
356
+
357
+ $tmpRow .= '</'.$cellTagName.'>'.PHP_EOL;
358
+
359
+ }
360
+
361
+ $tmpRow .= '</tr>'.PHP_EOL;
362
+
363
+ if( $rowIdx <= $options['header_row'] ){
364
+
365
+ $thead .= $tmpRow;
366
+
367
+ }else{
368
+
369
+ $tbody .= $tmpRow;
370
+
371
+ }
372
+
373
+ }
374
+
375
+ // var_dump($skipCell);
376
+
377
+ if( !@$options['strip_table_tag'] ){
378
+
379
+ print '<table>'.PHP_EOL;
380
+
381
+ }
382
+
383
+ if( $options['render_cell_width'] ){
384
+
385
+ print '<colgroup>'.PHP_EOL;
386
+
387
+ foreach( $col_widths as $colIdx=>$colRow ){
388
+
389
+ print '<col style="width:'.floatval($colRow/$col_width_sum*100).'%;" />'.PHP_EOL;
390
+
391
+ }
392
+
393
+ print '</colgroup>'.PHP_EOL;
394
+
395
+ }
396
+
397
+ if( strlen($thead) ){
398
+
399
+ print '<thead>'.PHP_EOL;
400
+
401
+ print $thead;
402
+
403
+ print '</thead>'.PHP_EOL;
404
+
405
+ }
406
+
407
+ print '<tbody>'.PHP_EOL;
408
+
409
+ print $tbody;
410
+
411
+ print '</tbody>'.PHP_EOL;
412
+
413
+ if( !@$options['strip_table_tag'] ){
414
+
415
+ print '</table>'.PHP_EOL;
416
+
417
+ }
418
+
419
+ $rtn = ob_get_clean();
420
+
421
+ return $rtn;
422
+
423
+ } // render()
424
+
425
+ /**
426
+
427
+ * ボーダーオブジェクトからHTMLのborder-style名を得る
428
+
429
+ */
430
+
431
+ private function get_borderstyle_by_border( $border ){
432
+
433
+ $style = $border->getBorderStyle();
434
+
435
+ $border_width = '1px';
436
+
437
+ $border_style = 'solid';
438
+
439
+ switch( $style ){
440
+
441
+ case 'none':
442
+
443
+ $border_width = '0';
444
+
445
+ $border_style = 'none';
446
+
447
+ break;
448
+
449
+ case 'dashDot':
450
+
451
+ $border_style = 'dashed';
452
+
453
+ break;
454
+
455
+ case 'dashDotDot':
456
+
457
+ $border_style = 'dashed';
458
+
459
+ break;
460
+
461
+ case 'dashed':
462
+
463
+ $border_style = 'dashed';
464
+
465
+ break;
466
+
467
+ case 'dotted':
468
+
469
+ $border_style = 'dotted';
470
+
471
+ break;
472
+
473
+ case 'double':
474
+
475
+ $border_width = '3px';
476
+
477
+ $border_style = 'double';
478
+
479
+ break;
480
+
481
+ case 'hair':
482
+
483
+ break;
484
+
485
+ case 'medium':
486
+
487
+ $border_width = '3px';
488
+
489
+ break;
490
+
491
+ case 'mediumDashDot':
492
+
493
+ $border_width = '3px';
494
+
495
+ $border_style = 'dashed';
496
+
497
+ break;
498
+
499
+ case 'mediumDashDotDot':
500
+
501
+ $border_width = '3px';
502
+
503
+ $border_style = 'dashed';
504
+
505
+ break;
506
+
507
+ case 'mediumDashed':
508
+
509
+ $border_width = '3px';
510
+
511
+ $border_style = 'dashed';
512
+
513
+ break;
514
+
515
+ case 'slantDashDot':
516
+
517
+ $border_width = '3px';
518
+
519
+ $border_style = 'solid';
520
+
521
+ break;
522
+
523
+ case 'thick':
524
+
525
+ $border_width = '5px';
526
+
527
+ $border_style = 'solid';
528
+
529
+ break;
530
+
531
+ case 'thin':
532
+
533
+ $border_width = '1px';
534
+
535
+ $border_style = 'solid';
536
+
537
+ break;
538
+
539
+ }
540
+
541
+ $rtn = $border_width.' '.$border_style.' #'.strtolower($border->getColor()->getRGB()).'';
542
+
543
+ return $rtn;
544
+
545
+ } // get_borderstyle_by_border()
546
+
547
+ }
548
+
549
+ ```