php 导出方法

一种乐趣

已于 2022-12-06 11:07:05 修改

阅读量435

点赞数

分类专栏： php 文章标签： php excel 开发语言

于 2022-12-06 11:06:06 首次发布

本文链接：https://blog.csdn.net/zhazhaji/article/details/128199436

版权

php 专栏收录该内容

48 篇文章 0 订阅

订阅专栏


    /**
     * Excel导出
     *
     * @param array $title
     * @param array $data
     * @param string $filename
     * @param string $sheet_name
     */
    public function exportExcel($title = array(),$data = array(),$filename = null, $sheet_name = '')
    {
        $sheet_name = empty(!$sheet_name)?mb_convert_encoding($sheet_name,"GBK", "UTF-8"):'';
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=".$filename.".xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        echo '<html xmlns:o="urn:schemas-microsoft-com:office:office"
            xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
            <head>
            <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
            <meta http-equiv=Content-Type content="text/html; charset=GBK">
            <!--[if gte mso 9]><xml>
            <x:ExcelWorkbook>
            <x:ExcelWorksheets>
            <x:ExcelWorksheet>
            <x:Name>'.$sheet_name.'</x:Name>
            <x:WorksheetOptions>
            <x:DisplayGridlines/>
            </x:WorksheetOptions>
            </x:ExcelWorksheet>
            </x:ExcelWorksheets>
            </x:ExcelWorkbook>
            </xml><![endif]--></head><table>
            <tbody>';


        if (false !== $index = array_search('色号', $title)) {
            unset($title[$index]);
            $title = array_values($title);
        }

        // 写入表头
        if (!empty($title)){
            foreach ($title as $k => $v) {
                // 设定文本格式
                $title[$k]="<td style='vnd.ms-excel.numberformat:@'>".mb_convert_encoding($v,"GBK", "UTF-8")."</td>";
            }
            $title= "<tr>".implode("\t", $title)."</tr>";
            echo "$title\n";
        }

        // 写入数据内容
        if (!empty($data)){
            foreach($data as $key=>$val){
                unset($val['color_id']);
                foreach ($val as $ck => $cv) {
                    // 设定文本格式
                    $data[$key][$ck]="<td style='vnd.ms-excel.numberformat:@'>".mb_convert_encoding($cv,"GBK", "UTF-8")."</td>";
                }
                $data[$key]="<tr>".implode("\t", $data[$key])."</tr>\n";
            }

            echo implode("\n",$data);
        }

        echo '</tbody></table>';

        exit();
    }

    /**
     * 导出csv
     *
     * @param array $head 标题
     * @param array $data 数据
     * @param string $filename 文件名
     * @return void
     */
    function exportCsv($head, $data, $filename)
    {
        // 输出Excel文件头，可把user.csv换成你要的文件名
        header('Content-Type: application/octet-stream');
        header('Accept-Ranges:bytes');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'.csv"');
        header('Cache-Control: max-age=0');

        // 打开PHP文件句柄，php://output 表示直接输出到浏览器
        $fp = fopen('php://output', 'a');

        // 输出Excel列名信息
        foreach ($head as $i => $v) {
            // CSV的Excel支持GBK编码，一定要转换，否则乱码
            $head[$i] = iconv('utf-8', 'gbk', $v)."\t";
        }

        // 将数据通过fputcsv写到文件句柄
        fputcsv($fp, $head);

        // 计数器
        $cnt = 0;
        // 每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
        $limit = 100000;

        // 逐行取出数据，不浪费内存
        $count = count($data);
        for($t=0;$t<$count;$t++) {

            $cnt ++;
            if ($limit == $cnt) { //刷新一下输出buffer，防止由于数据过多造成问题
                ob_flush();
                flush();
                $cnt = 0;
            }
            $row = $data[$t];
            foreach ($row as $i => $v) {
                $row[$i] = iconv('utf-8', 'gbk', $v);
            }
            fputcsv($fp, $row);
        }
        fclose($fp);
        exit();
    }

    /**
     * csv文件导入
     *
     * @param string $file
     * @return [$title, $data]
     */
    public function import_csv($file = null)
    {
        $handle = fopen($file, 'r');

        $out = array();
        $n = 0;
        while ($data = fgetcsv($handle, 10000)) {
            $num = count($data);
            for ($i = 0; $i < $num; $i++) {
                $out[$n][$i] =  mb_convert_encoding(trim($data[$i]), "UTF-8", "GBK");
            }
            $n++;
        }

        // 获取表单头信息
        $title = array_filter(array_shift($out));

        // 去除整行为空的值，保留键值
        $data = array_filter($out, function($v) {
            // 去除空格
            foreach ($v as &$value) {
                $value = preg_replace("/\s/","",$value);
            }
            unset($value);
            if (!empty(array_filter($v))) {
                return true;
            }
            return false;
        });

        return [$title, $data];
    }