thinkphp--导入导出xls文件
阅读原文时间:2023年07月10日阅读:1

/**
* 数组转xls格式的excel文件
* @param array $data 需要生成excel文件的数组
* @param string $filename 生成的excel文件名
* 示例数据:
$data = array(
array(NULL, 2010, 2011, 2012),
array('Q1', 12, 15, 21),
array('Q2', 56, 73, 86),
array('Q3', 52, 61, 69),
array('Q4', 30, 32, 0),
);
*/
function create_xls($data,$filename = 'simple.xls'){
ini_set('max_execution_time','0');
Vendor('PHPExcel.PHPExcel');
$filename = str_replace('.xls','',$filename) . '.xls';
$phpexcel = new PHPExcel();
$phpexcel->getProperties()
->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$phpexcel->getActiveSheet()->fromArray($data);
$phpexcel->getActiveSheet()->setTitle('Sheet1');
$phpexcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=$filename");
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');//Date in the past
header ('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');//always modified
header ('Cache-Control: cache, must-revalidate');//HTTP/1.1
header ('Pragma: public');//HTTP/1.0
$objwriter = PHPExcel_IOFactory::createWriter($phpexcel,'Excel5');
$objwriter->save('php://output');
exit;
}

导出的使用:

 //导出  
 public function create\_xls() {  
     $industry\_list = M('welfare')->field('welfare\_name')->select();  
     $data = array();  
     array\_push($data, array('福利名称'));  
     foreach ($industry\_list as $key => $value) {  
         array\_push($data, array($value\['welfare\_name'\]));  
     }  
     create\_xls($data, '福利');  
 }

导入:

//导入  
public function upload\_xls() {  
    $upload = new \\Think\\Upload();  
    $upload->maxSize = 3145728;  
    $upload->exts = array('xls', 'xlsx');  
    $upload->saveExt = 'xls';  
    $upload->rootPath = './Application/Upload/excel/';  
    $upload->autoSub = false;  
    $result = $upload->uploadOne($\_FILES\['xls\_file'\]);  
    if (!$result) {  
        $this->error($upload->getError(), U('WelFare/welfare\_list'));  
    }

    $data = import\_excel($upload->rootPath . $result\['savepath'\] . $result\['savename'\]);  
    delDirAndFile($upload->rootPath . $result\['savepath'\]);

    $list = array();  
    unset($data\[1\]);  
    $i = 0;

    foreach ($data as $k => $v) {  
        $param = array();  
        $param\['welfare\_name'\] = $v\[0\];  
        $info = M('welfare')->where($param)->find();  
        if ($info)  
            continue;

        $list\[$i\]\['welfare\_name'\] = $v\[0\];  
        $i++;  
    }

    if ($i) {  
        M('welfare')->addAll($list);  
        $this->success('成功插入了' . $i . '条数据', U('WelFare/welfare\_list'));  
    } else {  
        $this->error('插入了0条数据', U('WelFare/welfare\_list'));  
    }  
}

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器

你可能感兴趣的文章