Rumah  >  Artikel  >  pembangunan bahagian belakang  >  phpExcel---自定义函数

phpExcel---自定义函数

WBOY
WBOYasal
2016-08-08 09:24:431525semak imbas

<code>require_once <span>'../class/c_mysql.class.php'</span>;
$db = <span>new</span> c_mysql();
$sql = <span>"select group_name,group_email,member_name,member_email,member_department from email_group;"</span>;
$db->connect();
$db->query($sql);
$db->close();
<span>while</span>($row = $db->fetch_row()){
    $data[] = $row;
}   
$title = array(<span>'群组名称'</span>,<span>'群组邮箱'</span>,<span>'成员姓名'</span>,<span>'成员邮箱'</span>,<span>'成员所在部门'</span>);

<span>/*****************函数调用*********************/</span><span>function</span> exportXlsx($title,$content){
    require_once <span>'../PHPExcel/PHPExcel.php'</span>;
    require_once <span>'../PHPExcel/PHPExcel/Writer/Excel2007.php'</span>;
    <span>//</span>创建一个excel
    $objPHPExcel = <span>new</span> PHPExcel();
    <span>//</span>保存excel—<span>2007</span>格式
    $objWriter = <span>new</span> PHPExcel_Writer_Excel2007($objPHPExcel);
    <span>//</span>向excel中插入数据
    $letter = range(<span>"A"</span>,<span>"Z"</span>);
    $row = <span>2</span>;<span>//</span>从第二行开始插入数据,预留出第一行插入列名
    foreach($content as $line){     
        $i = <span>0</span>;
        foreach($line as $cell){
            $objPHPExcel->getActiveSheet<span><span>()</span>-></span>setCellValue($letter[$i] . $row, $cell);
            $i++;
        }
        $row++;
    }

    <span>//</span>将标题输入到excel中
    foreach($title as $key<span> =></span> $cell){
        $objPHPExcel->getActiveSheet<span><span>()</span>-></span>setCellValue($letter[$key] . <span>'1'</span>, $cell);
    }
    <span>//</span>设置宽width
    $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getColumnDimension<span><span>(<span>'A'</span>)</span>-></span>setWidth(<span>40</span>);
    $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getColumnDimension<span><span>(<span>'B'</span>)</span>-></span>setWidth(<span>30</span>);
    $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getColumnDimension<span><span>(<span>'C'</span>)</span>-></span>setWidth(<span>35</span>);
    $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getColumnDimension<span><span>(<span>'D'</span>)</span>-></span>setWidth(<span>35</span>);
    $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getColumnDimension<span><span>(<span>'E'</span>)</span>-></span>setWidth(<span>100</span>);

    <span>//</span>第一行居中,其他行默认
    foreach($title as $key<span> =></span> $cell){
        $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getStyle<span><span>($letter[$key] . <span>'1'</span>)</span>-></span>getAlignment<span><span>()</span>-></span>setHorizontal(<span>PHPExcel_Style_Alignment</span>::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getStyle<span><span>($letter[$key] . <span>'1'</span>)</span>-></span>getFill<span><span>()</span>-></span>setFillType(<span>PHPExcel_Style_Fill</span>::FILL_SOLID);        
        $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getStyle<span><span>($letter[$key] . <span>'1'</span>)</span>-></span>getFill<span><span>()</span>-></span>getStartColor<span><span>()</span>-></span>setARGB(<span>'FFC3C3C3'</span>);    
        <span>//</span>设置border的color
        $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getStyle<span><span>($letter[$key] . <span>'1'</span>)</span>-></span>getBorders<span><span>()</span>-></span>getAllBorders<span><span>()</span>-></span>setBorderStyle(<span>PHPExcel_Style_Border</span>::BORDER_THIN);
        $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getStyle<span><span>($letter[$key] . <span>'1'</span>)</span>-></span>getBorders<span><span>()</span>-></span>getAllBorders<span><span>()</span>-></span>getColor<span><span>()</span>-></span>setARGB(<span>'FF000000'</span>);
    }
    <span>//</span>给其他行填充颜色
    $line_count = count($content) + <span>2</span>;
    foreach($title as $key<span> =></span> $cell){
        <span>//</span>设置border的color
        <span>for</span>($i=<span>2</span>;$igetActiveSheet<span><span>()</span>-></span>getStyle<span><span>($letter[$key] . $i)</span>-></span>getBorders<span><span>()</span>-></span>getAllBorders<span><span>()</span>-></span>setBorderStyle(<span>PHPExcel_Style_Border</span>::BORDER_THIN);
            $objPHPExcel->getActiveSheet<span><span>()</span>-></span>getStyle<span><span>($letter[$key] . $i)</span>-></span>getBorders<span><span>()</span>-></span>getAllBorders<span><span>()</span>-></span>getColor<span><span>()</span>-></span>setARGB(<span>'FF000000'</span>);
        }       
    }
    header(<span>"Pragma: public"</span>);
    header(<span>"Expires: 0"</span>);
    header(<span>"Cache-Control:must-revalidate, post-check=0, pre-check=0"</span>);
    header(<span>"Content-Type:application/force-download"</span>);
    header(<span>"Content-Type:application/vnd.ms-execl"</span>);
    header(<span>"Content-Type:application/octet-stream"</span>);
    header(<span>"Content-Type:application/download"</span>);
    $filename = <span>'群组信息.xlsx'</span>;
    $filename = iconv(<span>'UTF-8'</span>,<span>'GB2312'</span>,$filename);
    header(<span>"Content-Disposition:attachment;filename=$filename"</span>);
    header(<span>"Content-Transfer-Encoding:binary"</span>);
    $objWriter->save(<span>'php://output'</span>);
}
</code>

以上就介绍了phpExcel---自定义函数,包括了方面的内容,希望对PHP教程有兴趣的朋友有所帮助。

Kenyataan:
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn