Phpspreadsheet (PHP Excel)

Phpspreadsheet: (phpexcel is deprecated):

https://github.com/PHPOffice/PhpSpreadsheet

Don’t use heading as data key:

/config/excel.php: heading => false;

Setting a cell value by column and row

// Set cell A5 with a string value
$spreadsheet->getActiveSheet()->setCellValueByColumnAndRow(1, 5, 'PhpSpreadsheet');

Retrieving a cell value by column and row

// Get the value from cell B5
$cellValue = $spreadsheet->getActiveSheet()->getCellByColumnAndRow(2, 5)->getValue();

Reference:

Column Configs for import and export

    'item_code_table_export' => [
        ['product_id', 'Product ID','產品ID', true, 'int',15],
        ['product_name','Product Name','品名', true, 'string',30],
        ['model_number','Art No.', 'Topeak料號', true, 'string',15],
        ['item_no','Giant Art No.', 'Giant料號', true, 'string',15],
        ['safety_inventory','Below Minimum Stock', '庫存低水位', true, 'int',20],
        ['inventory','Default Warehouse' ,'庫存', true, 'int',20],
        ['list_price','List Price' ,'原價', true, 'int',20],
        ['price','Price' ,'售價', true, 'int',20],
        ['updated_at','Default Warehouse-Last Updated On' ,'庫存最後更新時間', true, 'string',30],
    ],

Export as Excel

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

public function export(Request $req){
	$this->check_pms($this->pms_key . 'view');

	$data_type = $req->input('data_type');
	$product_type = $req->input('product_type');

	//search category
	$category = $req->input('category');
	$selected = [];
	if($category != false){
		$sel_prods = ProductCategoryM::where('category_id', $category)
		->get();
		foreach ($sel_prods as $key => $item) {
			$selected[] = $item->product_id;
		}
	}

	$options = [
		'data_type' => $data_type, 
		'product_type' => $product_type, 
		'selected' => $selected,
	];
	$rows = ItemCodeM::export_items($options);
	//logg($rows);

	$fields = Config::get('admin.item_code_table_export');
	$nowDate = Carbon::now()->toDateString();
	$file_name = 'Topeak Warehouse-Inventory-'.$nowDate.'.xlsx';
	$spreadsheet = new Spreadsheet();
	$sheet = $spreadsheet->getActiveSheet();

	//Write Chinese headers
	$row_index = 1;
	$col_index = 1;
	foreach ($fields as $key => $field) {
		$sheet->setCellValueByColumnAndRow($col_index, $row_index,$field[2]);
		$col_index++;
	}

	//Write English headers
	$row_index = 2;
	$col_index = 1;
	foreach ($fields as $key => $field) {
		$sheet->setCellValueByColumnAndRow($col_index, $row_index,$field[1]);
		$col_index++;

		//設定寬度
		$chr = to_alphabet($key);
		$sheet->getColumnDimension($chr)->setWidth($field[5]);
	}


	//Write data
	$row_index++;
	foreach ($rows as $key => $item) {
		$col_index = 1;
		foreach ($fields as $key2 => $field) {
			$chr = to_alphabet($key2);
			$coordinate = $chr . $row_index;

			$value = val($item, $field[0]);

			//指定cell資料格式
			$data_type = val($field, 4, 'string');
			if($data_type == 'int'){
				$sheet->setCellValue($coordinate, $value);
			}elseif($data_type == 'date'){
				$sheet->setCellValue($coordinate, $value);
			 
				$sheet->getStyle($coordinate)
					->getNumberFormat()
					->setFormatCode(
						\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME
					);
			}else{
				$sheet->setCellValueExplicit(
					$coordinate,
					$value,
					\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING
				);
			}

			$col_index++;
		}

		$row_index++;
	}

	// Redirect output to a client’s web browser (Xlsx)
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="' . $file_name . '"');
	header('Cache-Control: max-age=0');
	// If you're serving to IE 9, then the following may be needed
	header('Cache-Control: max-age=1');

	// If you're serving to IE over SSL, then the following may be needed
	header('Expires: Mon, 26 Jul 2019 05:00:00 GMT'); // Date in the past
	header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
	header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header('Pragma: public'); // HTTP/1.0

	$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
	$writer->save('php://output');
}

Update Excel and export:

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

    public function download_upload_template(Request $req){
        $file_name = 'upload_template.xlsx';
        $tpl = storage_path('app/upload_template.xlsx');
        $excel = IOFactory::load($tpl);
        
        //Choose second sheet
        $sheet = $excel->setActiveSheetIndex(1);

        //Write sources
        $source_data = SourceM::orderBy('id')->get();
        $row_index = 2;
        foreach ($source_data as $key => $item) {
            $col_index = 1;
            $sheet->setCellValueByColumnAndRow($col_index, $row_index, $item->id);
            $sheet->setCellValueByColumnAndRow($col_index+1, $row_index, $item->name);
            $row_index++;
        }

        $excel->setActiveSheetIndex(0);

        // Redirect output to a client’s web browser (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $file_name . '"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
         
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 2019 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
         
        $writer = IOFactory::createWriter($excel, 'Xlsx');
        $writer->save('php://output');
    }

Import:

    public function import_handler(Request $req){
        $file = $req->file('file');
        //logg($file->getClientOriginalName());
        //$this->excel_handler($target_file);

        $file_name = 'import.xlsx';
        $dest = storage_path('app');
        $file->move($dest, $file_name);
        //$path = $file->store('app/public');
        //logg($dest);

        $spreadsheet = IOFactory::load($dest . '/' . $file_name);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        //dd($sheetData);

        $fields = Config::get('admin.product_import');
        //logg($fields);

        $success = 0;
        $failed = 0;
        foreach ($sheetData as $row_index => $row) {
            if($row_index == 1) continue;
            //logg($row);
            $data = [];
            $row_errs = [];
            foreach ($fields as $key => $field) {
                $value = val($row, $key);
                //logg($value);

                if($value == false){
                    if($field[2] == true){
                        $row_errs[] = $field[1] . ' 為必填';
                        break;
                    }else{
                        continue;
                    }
                }

                //If value is date, the value will be converted to Carbon.
                if($field[3] == 'date' && $value != false){
                    $value = Carbon::parse($value)->toDateString();
                    //logg($value);
                }

                if($field[0] == 'iccid'){
                    if($value == false){
                        $row_errs[] = $field[1] . '為必填';
                    }else{
                        $current_iccid = ProductM::item_by_iccid($value);
                        if($current_iccid != false){
                            $row_errs[] = $value . '已經存在';
                        }
                    }
                }

                if($field[0] == 'enabled_end_buff'){
                    //Get index of enabled_end
                    if($data['enabled_end'] != false && $value != false && $value > 0){
                        $d = Carbon::parse($data['enabled_end']);
                        $data['enabled_end'] = $d->subDays($value)->toDateString();
                    }
                }

                $data[$field[0]] = $value;
            }
            //logg($data);

            if($row_errs == false){
                $result = ProductM::new_item($data);
                if($result == false){
                    echo '第' . ($row_index) . '列: Failed. 新增資料失敗<br/>';
                    $failed++;
                }else{
                    echo '第' . ($row_index) . '列: Success.<br/>';
                    $success++;
                }
            }else{
                echo '第' . ($row_index) . '列: Failed. ' . implode(';', $row_errs) . '<br/>';
                $failed++;
            }
        }

        echo '完成, 成功:' . $success . '筆, 失敗: ' . $failed;
    }

Form Example (modal)

<div class="modal fade" tabindex="-1" role="dialog" id="md_import">
  <div class="modal-dialog" role="document">
    <div class="modal-content">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        <h4 class="modal-title">Import Dealers</h4>
      </div>
      <div class="modal-body">
        <form name="gform" id="gform" action="/admin/{{$controller}}/import" class="modal-form" method="POST" enctype="multipart/form-data">

          <div class="form-group">
            <label for="name" class="col-lg-2 control-label">File</label>
            <div class="col-lg-10 filled">
              <input type="file" name="file">
            </div>
          </div>

          <input type="hidden" name="country" value="">
          {{ csrf_field() }}
        </form>
      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-primary bt_submit">Submit</button>
        <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
      </div>
    </div><!-- /.modal-content -->
  </div><!-- /.modal-dialog -->
</div><!-- /.modal -->

Javascript Example

var import_app = function(){
    var $c = null;
    var $form = null;

    function init(){
        $c = $('#md_import');
        $form = $c.find('form');

        $c.find('.bt_submit').on('click', function(e){
            e.preventDefault();
            $form.submit();
        });
    }

    function show(country_code){
        clear();
        $c.modal();
        set_value('country', country_code, $c);
    }

    function clear(){
        set_value('country', '', $c);
    }

    return {
        init: init, 
        show: show
    }
}();

Cell Style

            $sheet->setCellValueByColumnAndRow($col_index, $row_index, $value);
            $cell = $sheet->getCellByColumnAndRow($col_index, $row_index);
            $cell->getStyle()->applyFromArray(
                [
                    'font' => [
                        'bold' => true,
                        'color' => ['rgb' => 'ff5630'],
                        'size' => 15, 
                    ],
                ]
            );

https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/08-Recipes.md#styles