最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

PHP使用PHPExcel导出Excel

XAMPP教程 中文小张 719浏览 0评论

首先使用composer安装PHPExcel

composer require phpoffice/phpexcel

封装的常用二维数组导出为Excel表格。

/*** 导出为Excel* @param array   $data                    要导出的数据(二维数组)* @param string  $fileName                导出的文件名* @param array   $tilte                   标题列* @param int     $startRow                开始的行,默认是第一行为标题,第二行开始正文* @throws \PHPExcel_Exception* @throws \PHPExcel_Reader_Exception* @throws \PHPExcel_Writer_Exception*/public function exportExcel($data, $fileName, $tilte = array(), $down = true, $dir, $startRow = 2){    $objPHPExcel = new \PHPExcel();    $title = title?:array_keys($data[0]);//如果标题字段没有设置,则取数组key为标题    //设置横列编号    $header_arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'Y', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AY', 'AW', 'AX');    //设置表格标题,默认第一行    foreach ($tilte as $k => $v) {        //$objPHPExcel->setActiveSheetIndex(0) 设置要操作的工作表,0代表第一个工作表,下同        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($header_arr[$k] . '1', $v);    }    foreach ($data as $row) {        //二维数组遍历,存数据,每一次遍历为一行        foreach ($indexKey as $key => $value) {            //行数据存储            $row_val = $row[$value] ? $row[$value] : '暂无!';//处理空数据            $objPHPExcel->getActiveSheet()->getStyle($header_arr[$key] . $startRow)->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置格式为文本            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($header_arr[$key] . $startRow, $row_val . ' ');//设置单元格数据            //注意,部分int形数据较长,如身份证等会出现格式问题,使用在末尾添加空格来解决        }        $startRow++;    }    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');    if ($down) {        //判断为直接下载        header('pragma:public');        header("Content-Disposition:attachmeng;filename=" . $fileName . ".xls");//设置导出保存的文件名        $objWriter->save('php://output');//直接在浏览器输出        exit;    }    $filenamedir = $dir . parseurl($fileName) . '.xlsx';//设置原生路径和文件名    //保存到指定路径    $fileName = iconv('utf-8', 'gb2312', $fileName);//转码    //判断路径是否存在    if (!file_exists($dir)) {        mkdir($dir);    }    $objWriter->save($dir . $fileName . '.xlsx');//保存到特定目录    return $filenamedir;   //返回保存的文件路径}

下面整理了一些常用方法的介绍

设置excel的属性://设置创建人$objPHPExcel->getProperties()->setCreator("Jack Ma");//设置最后修改人$objPHPExcel->getProperties()->setLastModifiedBy("Pony");//设置标题$objPHPExcel->getProperties()->settitle("I am Title");//设置题目$objPHPExcel->getProperties()->setSubject("hello world");//设置描述$objPHPExcel->getProperties()->setDescription("I'm a description.");//设置关键字$objPHPExcel->getProperties()->setKeywords("PHP is the best language");//设置种类$objPHPExcel->getProperties()->setCategory("PHP");//设置当前的sheet$objPHPExcel->setActiveSheetIndex(0);//设置sheet的name$objPHPExcel->getActiveSheet()->settitle('world');//设置单元格的值$objPHPExcel->getActiveSheet()->setCellValue('A2', 'hello world!');//合并单元格,A18-E22单元格所选区域$objPHPExcel->getActiveSheet()->mergeCells('B1:C10');//分离单元格$objPHPExcel->getActiveSheet()->unmergeCells('B1:C10');//冻结窗口$objPHPExcel->getActiveSheet()->freezePane('A2');//保护cell$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); //首先设置为true $objPHPExcel->getActiveSheet()->protectCells('B1:C10', 'PHPExcel');//设置格式$objPHPExcel->getActiveSheet()->getStyle('A2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置格式为文本//设置宽width// Set column widths$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(16);// 设置单元格高度// 所有单元格默认高度$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);// 第一行的默认高度$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);//设置填充颜色$objPHPExcel->getActiveSheet()->getstyle('A1')->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getstyle('A1')->getFill()->getStartColor()->setARGB('FF808080');$objPHPExcel->getActiveSheet()->getstyle('B1')->getFill()->setFillType(PHPExcel_style_Fill::FILL_SOLID);$objPHPExcel->getActiveSheet()->getstyle('B1')->getFill()->getStartColor()->setARGB('FF808080');//设置font$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setName('Candara');$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setSize(20);$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->setUnderline(PHPExcel_style_Font::UNDERLINE_SINGLE);$objPHPExcel->getActiveSheet()->getstyle('B1')->getFont()->getColor()->setARGB(PHPExcel_style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getstyle('E1')->getFont()->getColor()->setARGB(PHPExcel_style_Color::COLOR_WHITE);$objPHPExcel->getActiveSheet()->getstyle('D13')->getFont()->setBold(true);$objPHPExcel->getActiveSheet()->getstyle('E13')->getFont()->setBold(true);//设置align$objPHPExcel->getActiveSheet()->getstyle('D11')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getstyle('D12')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getstyle('D13')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_RIGHT);$objPHPExcel->getActiveSheet()->getstyle('A18')->getAlignment()->setHorizontal(PHPExcel_style_Alignment::HORIZONTAL_JUSTIFY);//垂直居中$objPHPExcel->getActiveSheet()->getstyle('A18')->getAlignment()->setVertical(PHPExcel_style_Alignment::VERTICAL_CENTER);//设置column的border$objPHPExcel->getActiveSheet()->getstyle('A4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getstyle('B4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getstyle('C4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getstyle('D4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);$objPHPExcel->getActiveSheet()->getstyle('E4')->getBorders()->getTop()->setBorderstyle(PHPExcel_style_Border::BORDER_THIN);//设置border的color$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getstyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getstyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');$objPHPExcel->getActiveSheet()->getstyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');
//添加加图片$objDrawing = new \PHPExcel_Worksheet_Drawing();//设置图片路径 切记:只能是本地图片$objDrawing->setPath($img_val);//设置图片高度$objDrawing->setWidth(200);$img_height[] = $objDrawing->getHeight();//设置图片要插入的单元格$objDrawing->setCoordinates($img_k[$j].$i);//设置图片所在单元格的格式$objDrawing->setOffsetX(10);$objDrawing->setOffsetY(10);$objDrawing->setRotation(0);$objDrawing->getShadow()->setVisible(true);$objDrawing->getShadow()->setDirection(50);$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

转载请注明:XAMPP中文组官网 » PHP使用PHPExcel导出Excel

您必须 登录 才能发表评论!