PHP使用PHPExcel导出Excel

首先使用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("Jankin");
//设置最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy("Jankin");
//设置标题
$objPHPExcel->getProperties()->settitle("title");
//设置题目
$objPHPExcel->getProperties()->setSubject("hello world");
//设置描述
$objPHPExcel->getProperties()->setDescription(" 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());

详细方法部分内容参考了一下链接的文章：https://blog.csdn.net/chenlix/article/details/82853698

©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容