PhpSpreadsheet导入导出excel

​
简便方法：https://learnku.com/articles/59344

安装：

composer require phpoffice/phpspreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ReaderXlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;


/**
 * 导出excel表
 * $data：要导出excel表的数据，接受一个二维数组
 * $fileName：excel表的表名
 * $title：excel表的表头，接受一个一维数组
 * $sheetName：excel表的sheet名
 */
function exportExcel($fileName='', $title=[], $data=[],$sheetName=null,$type = null)
{
//    $data = array(0 => array(0 => 1, 1 => 'alex1', 2 => 1,),
//        1 => array(0 => 2, 1 => 'alex2', 2 => 2,),
//        2 => array(0 => 3, 1 => 'alex3', 2 => 1,),
//        3 => array(0 => 4, 1 => 'alex4', 2 => 2,),
//        4 => array(0 => 5, 1 => 'alex5', 2 => 1,),
//        5 => array(0 => 6, 1 => 'alex6', 2 => 2,));
//
//    $title = ['id', 'name', 'sex'];
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    //设置工作表标题名称
    if(isset($sheetName) && !empty($sheetName)){
        $sheet->setTitle($sheetName);
    }else{
        $sheet->setTitle(date('Y-m-d',time()));
    }
    // $sheet->getDefaultColumnDimension()->setWidth(40);
    $iRow=0;
    //设置单元格内容
    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);     
        $iRow =$iRow +1;
        $colum=index2ColName($iRow);
        $sheet->getColumnDimension($colum)->setAutoSize(true);
        //设置单元格宽度  与setAutoSize 是冲突的
        //$sheet->getDefaultColumnDimension()->setWidth(20);
        //$sheet->getColumnDimension('B')->setWidth(50);
        $sheet->getStyle($colum.'1')->getFont()->setBold(true);
    }
    
    $row = 2; // 从第二行开始
    foreach ($data as $item) {
        $column = 1;
        foreach ($item as $value) {
            // 单元格内容写入
            $sheet->setCellValueByColumnAndRow($column, $row, $value);
            $column++;
        }
        $row++;
    }
    if(isset($type)){
        # 保存为xlsx
        $fileName = $fileName.'.Xlsx';
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $filePath = ROOT_PATH.'uploads/excel/'.$fileName;
        $writer->save($filePath);
        return $filePath;
        die;
    }else{
        # 浏览器下载
        $fileName = $fileName.'.Xlsx';
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        ob_end_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        //删除清空
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

}

//根据列数转Excel的列名；
function index2ColName($columnNumber)
{
    $dividend = $columnNumber;
    $columnName = '';
    while ($dividend > 0) {
        $modulo = ($dividend - 1) % 26;
        $columnName = chr(65 + $modulo) . $columnName;
        $dividend = (int)(($dividend - $modulo) / 26);
    }
    return $columnName;
}

//导入excel表
function importExcel($file='',$sheetName = 0){
    // 有Xls和Xlsx格式两种
    $objReader = IOFactory::createReader('Xls');
    if (!$objReader->canRead($file)) {
        $objReader = IOFactory::createReader('Xlsx');
    }
    $objPHPExcel = $objReader->load($file);  //$filename可以是上传的表格，或者是指定的表格
    $sheet = $objPHPExcel->getSheet($sheetName);   //excel中的第一张sheet
    $highestRow = $sheet->getHighestRow();       // 取得总行数
    $highestColumn = $sheet->getHighestColumn();   // 取得总列数
    //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
    for ($currentRow = 1; $currentRow <= $highestRow; $currentRow++) {
        //从哪列开始，A表示第一列
        for ($currentColumn = 'A'; $currentColumn <= $highestColumn; $currentColumn++) {
            //数据坐标
            $address = $currentColumn . $currentRow;
            //读取到的数据，保存到数组$data中
            $cell = $sheet->getCell($address);

            // 获取原始值
            $cellValue = $cell->getValue();

            if ($cellValue instanceof PHPExcel_RichText) {
                $cellValue = $cellValue->__toString();
            }

            // 检查是否为日期格式
            if (\PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
                $dateTime = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cellValue);
                // 格式化为字符串（根据需要调整格式）
                $cellValue = $dateTime->format('Y-m-d H:i:s');
            }
            $data[$currentRow - 1][$currentColumn] = $cell;
            //  print_r($cell);
        }
    }

    return $data;

}
/**
 * Created by PhpStorm.
 * function: imports
 * Description:导入excell
 */
function imports()
{
    header("Content-Type:text/html;charset = utf-8");

    $upload = new \Think\Upload();// 实例化上传类
    $upload->maxSize = 3145728;// 设置附件上传大小
    $upload->exts = array('xls', 'xlsx');// 设置附件上传类
    $upload->rootPath  = '../uploads/excel'; // 设置附件上传目录
    // 上传文件
    $info = $upload->uploadOne($_FILES['excelData']);
    $filename = $upload->rootPath . $info['savepath'] . $info['savename'];
    $exts = $info['ext'];
    if (!$info) {// 上传错误提示错误信息
        $this->error($upload->getError());
    } else {// 上传成功
        $this->data_import($filename, $exts,3);
    }

}

//上传excel
function import($request)
{
    //解决后缀获取不对的问题
    $file = $request->file('excel');
    $ext = $file->getClientOriginalExtension();
    $fileName = uniqid('', true) . '.' . $ext;
    $path = $file->storeAs('excel', $fileName, 'local');
    return storage_path('app') . "/" . $path;
}

​

另一种导出方式

/**
 * 数组转xls格式的excel文件
 * @param $data
 * @param $title
 * 示例数据
 * @throws PHPExcel_Exception
 * @throws PHPExcel_Reader_Exception
 * @throws PHPExcel_Writer_Exception
 */
function export_excel($data=array(),$title=array(),$filename='报表')
{
    //处理中文文件名
    ob_end_clean();
    Header('content-Type:application/vnd.ms-excel;charset=utf-8');
    header("Content-Disposition:attachment;filename=export_data.xls");
    //处理中文文件名
    $ua = $_SERVER["HTTP_USER_AGENT"];
    $encoded_filename = urlencode($filename);
    $encoded_filename = str_replace("+", "%20", $encoded_filename);
    if (preg_match("/MSIE/", $ua) || preg_match("/LCTE/", $ua) || $ua == 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko') {
        header('Content-Disposition: attachment; filename="' . $encoded_filename . '.xls"');
    }else {
        header('Content-Disposition: attachment; filename="' . $filename . '.xls"');
    }
    header ( "Content-type:application/vnd.ms-excel" );
    $html = "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>";
    $html.="<html xmlns='http://www.w3.org/1999/xhtml'>";
    $html.="<meta http-equiv='Content-type' content='text/html;charset=UTF-8' /><head><title>".$filename."</title>";
    $html.="<style>td{text-align:center;font-size:12px;font-family:Arial, Helvetica, sans-serif;border:#1C7A80 1px solid;color:#152122;";
    $html.="width:auto;}table,tr{border-style:none;}.title{background:#C60;color:#FFFFFF;font-weight:bold;}</style>";
    $html.="</head><body><table width='100%' border='1'><tr>";
    foreach($title as $k=>$v){
        $html .= " <td class='title' style='text-align:center;'>".$v."</td>";
    }
    $html .= "</tr>";
    foreach ($data as $key =>$value) {
        $html .= "<tr>";
        foreach($value as $aa){
            $html .= "<td>".$aa."</td>";
        }
        $html .= "</tr>";
    }
    $html .= "</table></body></html>";
    echo $html;
    exit;
}