thinkphp--导入导出xls文件

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

导出的使用：

 1     //导出
 2     public function create_xls() {
 3         $industry_list = M('welfare')->field('welfare_name')->select();
 4         $data = array();
 5         array_push($data, array('福利名称'));
 6         foreach ($industry_list as $key => $value) {
 7             array_push($data, array($value['welfare_name']));
 8         }
 9         create_xls($data, '福利');
10     }

导入：

    //导入
    public function upload_xls() {
        $upload = new ThinkUpload();
        $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'));
        }
    }