当前位置 : 主页 > 网络编程 > PHP >

phpexcel导出兼容26*26列

来源:互联网 收集:自由互联 发布时间:2021-06-28
gistfile1.txt PHPExcel = new \PHPExcel(); } /** * Excel读入转PHPArray * @param string $filename [description] * @return [type] [description] */ public function excel2array($filename = '') { // $cacheMethod = \PHPExcel_CachedObjectStorage
gistfile1.txt
 PHPExcel = new \PHPExcel();
    }

    /**
     * Excel读入转PHPArray
     * @param  string $filename [description]
     * @return [type]           [description]
     */
    public function excel2array($filename = '')
    {
        // $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        // $cacheSettings = array('memoryCacheSize' => '16MB');

        // $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        // $cacheSettings = array('memoryCacheSize' => '16MB');

        // \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $PHPExcel_IOFactory = \PHPExcel_IOFactory::load($filename); //将execl文件对象化
        return $PHPExcel_IOFactory->getActiveSheet()->toArray(null, true, true, true); //转化成数组
    }

    public function index()
    {


        $data = M('devices')->where("addCode=''")->select();
        $this->exportExcel($data, array());
    }


    /**
     * 导出Excel
     * @param $expTableData array 每一行数据的集合
     * @param $expCellName array 数据的列的名字
     * @param $expTitle string 要保存文件的名字
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public function exportExcel($expTableData, $expCellName = array(), $expTitle = '')
    {
        /* 表名补充 */
        $expTitle || $expTitle = date('Y-m-d H:i:s', time());
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);

        /* 列名补充 */
        $expCellName || array_keys(current($expTableData));

        /* 记录转换参数 */
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);

        /* 根据列的数量生成对应列名 */
        $cellName = array();
        for ($i = 1; $i <= $cellNum; ++$i) {
            $cellName[] = $this->num26tochar($i);
        }

        /* 第一行输出导出时间,不要请自行注释.。修改时请注意偏移值的修改 */
        $this->PHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1'); // 合并单元格
        $this->PHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  Export time:' . date('Y-m-d H:i:s'));

        /* 第二行输出字段列名 */
        for ($i = 0; $i < $cellNum; ++$i) {
            $this->PHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i]);
        }

        /* 第三行开始输出数据 */
        $keys = array_keys($expTableData);

        for ($i = 0; $i < $dataNum; ++$i) {
            for ($j = 0; $j < $cellNum; ++$j) {
                $row = $expTableData[$keys[$i]];
                $row_keys = array_keys($row);
                $this->PHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $row[$row_keys[$j]]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header('Content-Disposition:attachment;filename="' . $expTitle . '.xls"'); // attachment新窗口打印inline本窗口打印
        $Writer = \PHPExcel_IOFactory::createWriter($this->PHPExcel, 'Excel5');
        $Writer->save('php://output');
        exit;
    }

    /**
     * 生成Excel对应列,兼容到26^2
     * @param $num
     * @return string
     */
    public function num26tochar($num = 0)
    {
        $num || ++$num;
        $quotient = intval($num / 27);

        /* A-Z */
        if ($quotient === 0) {
            return chr($num + 64);
        }

        $remainder = $num % 27;

        /* AA-ZZ */
        return $this->num26tochar($quotient) . $this->num26tochar($remainder);
    }

    /**
     *php导出excel去掉html及换行标签
     */
    public function getText($data)
    {
        $data = preg_replace("/<\/br>/i", "\n", $data);
        return preg_replace("/<[^>]+>/i", '', $data);
    }
}
网友评论