ThinkPHPExcel 导入导出

// 数字转字母
    function getLetter($num) {
        $str = "$num";
        $num = intval($num);
        if ($num <= 26){
            $ret = chr(ord('A') + intval($str) - 1);
        } else {
            $first_str = chr(ord('A') + intval(floor($num / 26)) - 1);
            $second_str = chr(ord('A') + intval($num % 26) - 1);
            if ($num % 26 == 0){
                $first_str = chr(ord('A') + intval(floor($num / 26)) - 2);
                $second_str = chr(ord('A') + intval($num % 26) + 25);
            }
            $ret = $first_str.$second_str;
        }
        return $ret;
    }
    
    // excel 导入
    /**
     * 导入excel到数据库
     * @param string $db 数据库表名
     * @param path string 文件名(路径)
     * @return boolean
     */
    function excelImport($db, $file) {
        import("Org.Util.PHPExcel");
        $PHPExcel = new PHPExcel();
        
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($file)) {
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($file)){
                return false;
            }
        }
        
        $E = $PHPReader->load($file);
        $cur = $E->getSheet(0);  // 读取第一个表
        $end = $cur->getHighestColumn(); // 获得最大的列数
        $line = $cur->getHighestRow(); // 获得最大总行数
        // 获取数据数组
        $info = array();        
        for ($row = 1; $row <= $line; $row ++) {
            for ($column = 'A'; $column <= $end; $column ++) {                
                $val = $cur->getCellByColumnAndRow(ord($column) - 65, $row)->getValue();
                $info[$row][] = $val;
            }
        }
        
        $DB = M($db);
        $data = array();
        for ($i = 2; $i <= count($info); $i ++) {
            for ($j = 0; $j < count($info[$i]); $j ++) {
                for ($k = 0; $k < count($info[1]); $k ++) {
                    $data[$i][$info[1][$k]] = $info[$i][$k];
                }
            }
        }
        $datalist = array_values($data);
        $result = $DB->addAll($datalist);
        // echo $DB->getLastSql();exit;
        if ($result) {
            return true;
        }
        return false;
    }
    
    // 导出excel
    /**
     * 导出excel方法
     * @param array $data 需要导出的数据
     * @param array $title excel表头
     * @param string $name 导出后的文件名
     */
    function excelExport ($data, $title=null, $name=null) {
        import("Org.Util.PHPExcel");
        $PHPExcel = new PHPExcel();
        
        if(!is_null($title)){
            array_unshift($data, $title);
        }
        
        if(is_null($name)){
            $name = time();
        }
            
        foreach ($data as $k => $v) {
            for ($i = 1; $i <= count($v); $i++){
                $tr = getLetter($i).($k+1);
                if ($value == null) {
                    $value = '';
                }
                $buffer[$tr]=array_values($v)[$i-1];
                $PHPExcel->getActiveSheet()->setCellValue($tr, array_values($v)[$i-1]);
            }        
        }
        
        $PHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel'); 
        header('Content-Disposition: attachment;filename="' . $name . '.xls"'); //文件名称 
        header('Cache-Control: max-age=0');
        $result = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007');
        $result->save('php://output');        
    }

Comments : 0

有问题可在下面发表评论,当然没事也可以在下面吹吹牛皮、扯扯淡!

发表评论

*


Warning: Cannot modify header information - headers already sent by (output started at /www/wwwroot/blog/content/templates/Bitter/footer.php:40) in /www/wwwroot/blog/include/lib/view.php on line 23