Excel导入导出

composer require phpoffice/phpspreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
    导出
**/
function get_excel($uploadfile)
{
    $inputFileType = IOFactory::identify($uploadfile); //传入Excel路径
    $excelReader = IOFactory::createReader($inputFileType); //Xlsx
    $PHPExcel = $excelReader->load($uploadfile); // 载入excel文件
    $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
    $sheetdata = $sheet->toArray();//返回数组
    var_dump($sheetdata);exit;
}



/**
 *  导入
 * $article=Db("article")->field("id,title,FROM_UNIXTIME(create_time,'%Y/%m/%d')")->select();
 * $title=array("编号","标题","添加时间");
 * $filename = 'download.xlsx';
 * download($filename,$title,$article);
 */
public function download($filename, $title, $data)
{
    try {
        if (!is_array($title) || count($title) <= 0) {
            return false;
        }
        $spreadsheet = new Spreadsheet();
        foreach ($title as $key => $one) {//设置标题栏目
            $spreadsheet->setActiveSheetIndex(0)->setCellValueByColumnAndRow($key + 1, 1, $one);//列，行，值
        }

        foreach ($data as $key => $one) {//设置内容
            $i = 1;
            foreach ($one as $ckey => $cone) {
                $spreadsheet->setActiveSheetIndex(0)->setCellValueByColumnAndRow($i, $key + 2, $cone);//列，行，值
                $i++;
            }
        }
        $writer = new Xlsx($spreadsheet);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename="' . $filename . '"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');//禁止缓存
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    } catch (\Exception $e) {
        $this->error = $e;
        return 0;
    }
}

作者