当前位置: 首页 > 后端技术 > PHP

php使用phpoffice-phpspreadsheet扩展和操作excel

时间:2023-03-29 20:19:22 PHP

1:安装phpoffice/phpspreadsheetcomposer需要phpoffice/phpspreadsheet2:phpoffice/phpspreadsheetAPI接口详解phpSpreadsheet提供了丰富的API接口,可以设置很多单元格和文档的属性,包括样式、图片、日期、函数等诸多应用,总之,你想要什么样的Excel表格,PhpSpreadsheet都能做到。在使用phpoffice/phpspreadsheet的API接口之前,一定要导入正确的文件并实例化usePhpOffice\PhpSpreadsheet\Spreadsheet;//引入文件$spreadsheet=newPhpOffice\PhpSpreadsheet\Spreadsheet();//新建一个excel文档$sheet=$spreadsheet->getActiveSheet();//获取当前操作表的对象1:setfont:$sheet->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial')->setSize(10);//设置A7到B7的两个单元格为粗体,Arialfont,size10$sheet->getStyle('B1')->getFont()->setBold(true);//设置单元格B1为粗体字体2:setcolor$sheet->getStyle('A1')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);//设置单元格A1的文字颜色为红色3:设置列宽$sheet->getColumnDimension('A')->setWidth(20);//设置A列的宽度为20(字符)$sheet->getColumnDimension('B')->setAutoSize(true);//设置B列宽为自动宽度$sheet->getDefaultColumnDimension()->setWidth(12);//设置默认列宽为124:设置行高$sheet->getRowDimension('10')->setRowHeight(100);//设置第十行高度为100pt$sheet->getDefaultRowDimension()->setRowHeight(15);//设置默认行高为155:alignment$sheet->getStyle('A:D')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER)//设置垂直居中->setHorizo??ntal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)//设置水平居中->setWrapText(true);//设置自动换行6:合并单元格$sheet->mergeCells('A1:D2');//A1到D2合并为一个单元格7:将合并后的单元格拆分为$sheet->unmergeCells('A1:D2');//拆分合并的单元格8:使用applyFromArray实现单元格样式设置//样式变量$style=[//设置字体样式'font'=>['name'=>'Arial','bold'=>true,'italic'=>false,'underline'=>Font::UNDERLINE_DOUBLE,'strikethrough'=>false,'color'=>['rgb'=>'808080']],//设置边框线样式'borders'=>[//allBorders所有边框线样式//左边框线'bottom'=>['borderStyle'=>Border::BORDER_DASHDOT,'color'=>['rgb'=>'808080']],//上边框线'top'=>['borderStyle'=>Border::BORDER_DASHDOT,'color'=>['rgb'=>'808080']]],//对齐方式'alignment'=>['horizo??ntal'=>Alignment::HORIZONTAL_CENTER,'vertical'=>Alignment::VERTICAL_CENTER,'wrapText'=>true,],//是否使用前缀'quotePrefix'=>true];$sheet->getStyle('A1:D1')->applyFromArray($style);9:设置工作表标题$sheet->setTitle('Hello');//设置当前工作表标题10:设置单元格的格式$sheet->getStyle('D2')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);//设置D2单元格的格式为textFormat$sheet->getStyle('A1:D2')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);//设置A1到D2的单元格为文本格式11:Newline$sheet->getCell('A4')->setValue("hello\nworld");//在A4单元格内包裹hello和world12:Hyperlink//设置A2单元格内容为blog点击跳转https://www.wj0511.com$sheet->setCellValue('A2','blog');$sheet->getCell('A2')->getHyperlink()->setUrl('https://www.wj0511.com');13:使用函数常用的函数有:求和(SUM),最大数(MAX),最小数(MIN),平均值(AVERAGE)$sheet->setCellValue('B5','=SUM(B1:B4)');//设置B5单元格内容为B1到B4之和14:设置文档属性$spreadsheet->getProperties()->setCreator("author")//Author->setLastModifiedBy("last-作者")//LastModifier->setTitle("title")//Title->setSubject("subject")//副标题->setDescription("description")//Description->setKeywords("keywords")//关键字->setCategory("类别");//第三类:生成excel的简单实现(这里我下载使用的是Yii框架自带的下载方式)0){$str.=$this->intToChr(floor($index/26)-1);}返回$str。chr($index%26+$start);}publicfunctionactionIndex(){//头部信息$header=['name','gender','education','age','Height',];//内容$data=[['小明','男','大学','18','175'],['小红','女','本科','18','155'],['小蓝','男','专科','20','170'],['张三','男','本科','19','165'],['李四','男','专科','22','175'],['王二','男','专科','25','175'],['麻子','男','本科生','22','180'],];$header=array_values($hea顺序);$data=array_values($data);//获取列信息$column=[];//['A','B','C','D','E']foreach($headeras$k=>$item){$column[$k]=$this->intToChr($k);}//获取初始列和最终列$firstColum=$column[0];$lastColum=$column[count($column)-1];//获取初始行和最后一行$firstRow=1;$lastRow=count($data)+1;$行=1;$电子表格=新电子表格();//新建一个excel文档$sheet=$spreadsheet->getActiveSheet();//获取当前操作sheet的对象$sheet->setTitle('Title');//设置标题$sheet->getStyle("{$firstColum}:{$lastColum}")->getAlignment()->setVertical(Alignment::VERTICAL_CENTER)//设置垂直居中->setHorizo??ntal(Alignment::HORIZONTAL_CENTER)//设置水平居中->setWrapText(true);//设置自动换行//设置宽度$sheet->getDefaultColumnDimension()->setWidth(20);$headerStyle=['对齐'=>['horizo??ntal'=>Alignment::HORIZONTAL_CENTER,],'font'=>['bold'=>true,'size'=>14,],];$cellStyle=['alignment'=>['horizo??ntal'=>Alignment::HORIZONTAL_CENTER,],'borders'=>['allBorders'=>['borderStyle'=>Border::BORDER_THIN,'color'=>['argb'=>'FF000000'],]],'font'=>['size'=>10,],];//设置excel的单元格格式为文本格式$sheet->getStyle("{$firstColum}{$firstRow}:{$lastColum}{$lastRow}")->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);//设置表头信息样式$sheet->getRowDimension($row)->setRowHeight(30);//设置行高$sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($headerStyle);//设置header信息foreach($headeras$key=>$item){$sheet->setCellValue("{$column[$key]}{$row}",$item);}$row++;foreach($dataas$key=>$model){$sheet->getRowDimension($row)->setRowHeight(30);//设置行高$sheet->getStyle("{$firstColum}{$row}:{$lastColum}{$row}")->applyFromArray($cellStyle);$i=0;foreach($modelas$value){$sheet->setCellValue("{$column[$i]}{$row}",$value);$i++;}$row++;}$file='sheet'.'.xlsx';//保存地址$writer=newXlsx($spreadsheet);$writer->save($file);//生成excel文件Yii::$app->response->sendFile($file,'下载的excel名称.xlsx')->发送();}}四:读取excel文件$title=[];//excel工作表标题$info=[];//excel内容$fileName="Form.xlsx";$spreadsheet=IOFactory::load($fileName);//$我rksheet=$spreadsheet->getActiveSheet();//获取当前文件的内容$sheetAllCount=$spreadsheet->getSheetCount();//工作表总数for($index=0;$index<$sheetAllCount;$index++){//工作表标题$title[]=$spreadsheet->getSheet($index)->getTitle();}//阅读第一个工作表$whatTable=0;$sheet=$spreadsheet->getSheet($whatTable);$highest_row=$sheet->getHighestRow();//获取总行数$highest_column=$sheet->getHighestColumn();///获取列号字母abc...$highestColumnIndex=Coordinate::columnIndexFromString($highest_column);//转换为数字;对于($i=1;$i<=$highestColumnIndex;$i++){对于($j=1;$j<=$highest_row;$j++){$conent=$sheet->getCellByColumnAndRow($i,$j)->获取计算值();$信息[$j][$i]=$内容;}}var_dump($信息);参考:https://phpspreadsheet.readth...