Excel导出数据并下载Excel表格

1.composer命令执行
composer require phpoffice/phpspreadsheet

/**
* 环境检测
*/
//var_dump(PHP_VERSION);
//var_dump(extension_loaded(‘zip’));
//var_dump(extension_loaded(‘xml’));
//var_dump(extension_loaded(‘gd’));

2.生成基本Excel

/**
 * 生成基本Excel
 */
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new Spreadsheet;
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','编号')
      ->setCellValue('B1','姓名')
      ->setCellValue('C1','年龄')
      ->setCellValue('D1','联系方式')
      ->setCellValue('E1','地址');
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
$writer->save('houdunren.xls');

3.读取Excel数据

/**
 * 读取Excel内容
 */
use PhpOffice\PhpSpreadsheet\IOFactory;
$inputFillName = 'houdunren.xls';
$spreadsheet = IOFactory::load($inputFillName);
$sheetData = $spreadsheet->getActiveSheet()->toArray();
//拿到数据做相应的处理
//print_r($sheetData);

4.批量讲数据写入Excel表格当中

/**
 * 批量讲数据写入Excel表格当中
 */

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$spreadsheet->setActiveSheetIndex(0);
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','编号')
    ->setCellValue('B1','姓名')
    ->setCellValue('C1','昵称');

$data = [
  [
      'uid'=>1,
      'username'=>'张国宝',
      'nickname'=>'PHPer',
  ],
    [
        'uid'=>2,
        'username'=>'孙伟',
        'nickname'=>'java',
    ],
    [
        'uid'=>3,
        'username'=>'刘通',
        'nickname'=>'python',
    ],
    [
        'uid'=>4,
        'username'=>'小明',
        'nickname'=>'C##',
    ],
];
$sheet->fromArray($data,null,'A2');
$write = new Xlsx($spreadsheet);
$write->save('houdunren.xls');

设置单元格列宽

$sheet->getColumnDimension('A')->setWidth('10');

设置excel字体大小

$style = new \PhpOffice\PhpSpreadsheet\Style\Style();
$style->getFont()->setSize('20');
$column = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex(1) . 1;
$sheet->duplicateStyle($style,$column);

设置excel字体、边框、填充颜色

$sheet->getStyle('B2')->applyFromArray([
    'font' => [
      'name' => 'Arial', //设置字体
      'bold' => true, //是否强调进行显示
      'italic' => false, //斜体
      'underline' => \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_DOUBLE, //下划线
      'strikethrough' => false,//设置字体删除线
      'color' => [
          'rgb' => '808080'
      ]
    ],
    'borders' => [
          'bottom' => [
              'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DASHDOT,
              'color' => [
                  'rgb' => '808080'
         ]
          ],
          'top' => [
              'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_DASHDOT,
              'color' => [
                  'rgb' => '808080'
         ]
          ]
      ],
    'fill'=>[
      'fillType'=>\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'color'=>[
            'argb'=>'FFCCFFCC',
        ]
    ],
]);