参考文档:
http://www.cnblogs.com/windyet/articles/9711044.html
学习地址:
插件下载地址:
下载插件:
composer require phpoffice/phpspreadsheet
百度网盘下载地址:
链接：https://pan.baidu.com/s/1TyTfTUG_YjY0MQlalEw8Zg 密码：f9u4
手册地址:
https://phpspreadsheet.readthedocs.io/en/develop/
环境要求:
Software requirements
The following software is required to develop using PhpSpreadsheet:
- PHP version 5.6 or newer
- PHP extension php_zip enabled
- PHP extension php_xml enabled
- PHP extension php_gd2 enabled (if not compiled in)
检测环境:
根目录下新建index.php文件
1
可以看到环境没有问题
index.php里写入数据并保存为Excel文件:
1 setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引 0,相当于Excel里第一个sheet127 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单28 //设置单元格列宽度29 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度30 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度31 32 //设置表头33 $sheet->setCellValue('A1', '编号')34 ->setCellValue('B1','用户名')35 ->setCellValue('B1','昵称')36 ->setCellValue('B1','年龄');37 38 //要写入的数据39 $data = [40 [41 'uid' =>1,42 'username' =>'lisi',43 'nickname' =>'李四',44 'age' =>1845 ],46 [47 'uid' =>2,48 'username' =>'wangwu',49 'nickname' =>'王五',50 'age' =>1951 ],52 [53 'uid' =>3,54 'username' =>'mailiu',55 'nickname' =>'麻六',56 'age' =>2057 ]58 ];59 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据60 $writer = new Xlsx($spreadsheet);61 $writer->save('WriteData.xlsx'); //设置保存文件名称 效果:
读取Excel数据:
根目录里新建readData.php
1 getActiveSheet()->toArray(); 20 echo '';21 print_r($sheetData);
效果:
设置字体的大小:
目标:
设置B1为30号字体,B2以下的所有设置为20号字体
1 1,31 'username' =>'lisi',32 'nickname' =>'李四',33 'age' =>1834 ],35 [36 'uid' =>2,37 'username' =>'wangwu',38 'nickname' =>'王五',39 'age' =>1940 ],41 [42 'uid' =>3,43 'username' =>'mailiu',44 'nickname' =>'麻六',45 'age' =>2046 ]47 ];48 49 $spreadsheet = new Spreadsheet();50 $spreadsheet->setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引 传入一个索引 0,相当于Excel里第一个sheet151 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单52 //设置单元格列宽度53 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度54 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度55 56 //设置字体的大小57 $style = new Style();58 $style->getFont()->setSize(30);59 //设置从B2的单元格的值开始向下开始循环到最后60 $column = Coordinate::stringFromColumnIndex(2) . 1; //相当操作B161 $sheet->duplicateStyle($style,$column); //设置样式$style:字体大小,$column:要设置的单格62 63 $style = new Style();64 for($i=0;$i getFont()->setSize(20);66 $column = Coordinate::stringFromColumnIndex(2) . ($i+2);67 $sheet->duplicateStyle($style,$column);68 69 }70 71 72 //设置表头73 $sheet->setCellValue('A1', '编号')74 ->setCellValue('B1','用户名')75 ->setCellValue('B1','昵称')76 ->setCellValue('B1','年龄');77 78 //要写入的数据79 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据80 $writer = new Xlsx($spreadsheet);81 $writer->save('WriteData.xlsx'); //设置保存文件名称 效果:
设置字体/边框/填充
1 1, 31 'username' =>'lisi', 32 'nickname' =>'李四', 33 'age' =>18 34 ], 35 [ 36 'uid' =>2, 37 'username' =>'wangwu', 38 'nickname' =>'王五', 39 'age' =>19 40 ], 41 [ 42 'uid' =>3, 43 'username' =>'mailiu', 44 'nickname' =>'麻六', 45 'age' =>20 46 ] 47 ]; 48 49 $spreadsheet = new Spreadsheet(); 50 $spreadsheet->setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引 0,相当于Excel里第一个sheet1 51 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单 52 //设置单元格列宽度 53 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度 54 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度 55 56 //设置字体的大小 57 $style = new Style(); 58 $style->getFont()->setSize(30); 59 //设置从B2的单元格的值开始向下开始循环到最后 60 $column = Coordinate::stringFromColumnIndex(2) . 1; //相当操作B1 61 $sheet->duplicateStyle($style,$column); //设置样式$style:字体大小,$column:要设置的单格 62 63 64 65 $style = new Style(); 66 for($i=0;$i getFont()->setSize(20); 68 $column = Coordinate::stringFromColumnIndex(2) . ($i+2); 69 $sheet->duplicateStyle($style,$column); 70 71 } 72 73 //设置字体/边框/填充 74 $sheet->getStyle('B2:C4')->applyFromArray([ 75 'font' => [ 76 'name' => 'Arial', 77 'bold' => true, 78 'italic' => false, 79 'underline' => \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLE, 80 'strikethrough' => false, //删除线 81 'color' => [ 82 'rgb' => '808080' 83 ] 84 ], 85 'borders' => [ 86 'bottom' => [ 87 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 88 'color' => [ 89 'rgb' => '808080' 90 ] 91 ], 92 'left' => [ 93 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 94 'color' => [ 95 'rgb' => '808080' 96 ] 97 ], 98 ], 99 'fill' =>[100 'fillType'=>\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,101 'color'=>[102 'argb'=>'FFCCFFCC'103 ]104 ]105 ]);106 107 //设置表头108 $sheet->setCellValue('A1', '编号')109 ->setCellValue('B1','用户名')110 ->setCellValue('B1','昵称')111 ->setCellValue('B1','年龄');112 113 //要写入的数据114 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据115 $writer = new Xlsx($spreadsheet);116 $writer->save('WriteData.xlsx'); //设置保存文件名称 效果:
设置Excel的属性/文件里的sheet文件重命名:
效果:
sheet1改名为houdunren
从文件->属性里 看属性
生成统计数据:
代码:
效果:
读取文件:
1 getActiveSheet()->toArray();20 21 //从B2读取到D422 $sheetData= $spreadsheet->getActiveSheet()->rangeToArray('B2:D4');23 24 //从B2读取到D4,并转换数组的key下标为字母25 //$sheetData= $spreadsheet->getActiveSheet()->rangeToArray('B2:D4',null,null,null,true);26 echo '';27 print_r($sheetData);
从B2读取到D4,并转换数组的key下标为字母:
1 //从B2读取到D4,并转换数组的key下标为字母2 $sheetData= $spreadsheet->getActiveSheet()->rangeToArray('B2:D4',null,null,null,true); 
12.导出数据并下载excel表格
1 1, 31 'username' =>'lisi', 32 'nickname' =>'李四', 33 'age' =>18 34 ], 35 [ 36 'uid' =>2, 37 'username' =>'wangwu', 38 'nickname' =>'王五', 39 'age' =>19 40 ], 41 [ 42 'uid' =>3, 43 'username' =>'mailiu', 44 'nickname' =>'麻六', 45 'age' =>12345678910 46 ] 47 ]; 48 49 $spreadsheet = new Spreadsheet(); 50 $spreadsheet->setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引 51 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单 52 //设置单元格列宽度 53 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度 54 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度 55 //不以科学计数的解决方法,列宽设置的宽一些 56 $sheet->getColumnDimension('D')->setWidth(20); //设置D列宽度 57 58 //设置字体的大小 59 $style = new Style(); 60 $style->getFont()->setSize(30); 61 //设置从B2的单元格的值开始向下开始循环到最后 62 $column = Coordinate::stringFromColumnIndex(2) . 1; 63 $sheet->duplicateStyle($style,$column); //设置样式$style:字体大小,$column:要设置的单格 64 65 66 67 $style = new Style(); 68 for($i=0;$i getFont()->setSize(20); 70 $column = Coordinate::stringFromColumnIndex(2) . ($i+2); 71 $sheet->duplicateStyle($style,$column); 72 73 } 74 75 //设置字体/边框/填充 76 $sheet->getStyle('B2:C4')->applyFromArray([ 77 'font' => [ 78 'name' => 'Arial', 79 'bold' => true, 80 'italic' => false, 81 'underline' => \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLE, 82 'strikethrough' => false, //删除线 83 'color' => [ 84 'rgb' => '808080' 85 ] 86 ], 87 'borders' => [ 88 'bottom' => [ 89 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 90 'color' => [ 91 'rgb' => '808080' 92 ] 93 ], 94 'left' => [ 95 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM, 96 'color' => [ 97 'rgb' => '808080' 98 ] 99 ],100 ],101 'fill' =>[102 'fillType'=>\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,103 'color'=>[104 'argb'=>'FFCCFFCC'105 ]106 ]107 ]);108 109 //设置表头110 $sheet->setCellValue('A1', '编号')111 ->setCellValue('B1','用户名')112 ->setCellValue('C1','昵称')113 ->setCellValue('D1','年龄');114 115 //要写入的数据116 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据117 //$writer = new Xlsx($spreadsheet);118 //$writer->save('WriteData.xlsx'); //设置保存文件名称119 120 //保存并下载生成的Excel文件121 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');122 header('Content-Disposition: attachment;filename="hello.xlsx"'); //保存的文件名字123 124 $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet,'Xlsx');125 $writer->save('php://output');126 exit; 效果:
php中导出数据到excel时数字变为科学计数的解决方法_php技巧