php 导出数据至csv

最新推荐文章于 2024-09-12 02:08:55 发布

原创最新推荐文章于 2024-09-12 02:08:55 发布 · 207 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#php #csv #excel

本文介绍如何使用PHP实现数据导出到CSV文件的功能，适用于生成可被Excel读取的文件格式。示例代码展示了关键步骤。

我导出的文件格式为.csv，下面是我做的导出产品数据的功能，上代码。

<button type="button" class="button" id="export"><span><span>导出</span></span></button>

<script type="text/javascript"><!--
    $(function(){
        $(document).keydown(function(event){
            if(event.keyCode==13){
                filter();
            }
        });

        $("#export").click(function(){
            var selected = "";
            var checkSelected = $("input:checkbox[name='selected[]']");
            for(var i=0; i<checkSelected.length; i++){
                if(checkSelected[i].checked){
                    if(checkSelected[i].value != ''){
                        selected += ("," + checkSelected[i].value);
                    }
                }
            }
            selected = selected.slice(1);
            var url = 'http://localhost/easyManager/manager/product.php?action=export';
            if(selected){
                url += '&' + "selected=" + selected;
            }
            if( confirm("确定要导出该数据吗？")) setLocation(url);
        })
    });

function setLocation(url)
{
   window.location.href = url;
}

</script>

上php代码：

<?php

$action = isset($_GET['action'])?$_GET['action']:'';

switch ($action) {

case 'export';
    $error = false;

    if( isset($_GET['selected'])&& !empty($_GET['selected'])){
        $selected = explode(',',$_GET['selected']);
    } else  {
        $error = true;
        $message_stack->add_session('product', '未选中所需导出的数据。');
    }

    if($error == true){
        redirect(href_link(FILENAME_PRODUCT));
    } else {
        $fileName = 'product' .time(). '.csv';
        header("Content-Type: text/csv");
        header('Content-Disposition: attachment; filename="' . $fileName);
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');

        // 打开PHP文件句柄，php://output 表示直接输出到浏览器
        $fp = fopen('php://output', 'a');
        $header = array("id", "name","description",'google product category','product_type', "link", "image link","condition","availability","price", "sale price", "gtin","brand","color", "size", "material");

        foreach ($header as $e => $f) {
            // CSV的Excel支持GBK编码，需转换，否则乱码
            $header[$e] = iconv('utf-8', 'gbk', $f);
        }
        fputcsv($fp, $header);

        $productList = array();
        foreach ($selected as $ke => $v) {
            unset($productList);
            $sql = "SELECT sku,name,description,filter_12,filter_13, url, image,filter_14,filter_15,price,specials_price,filter_16,filter_17,filter_18,filter_19,filter_20
                    FROM " . TABLE_PRODUCT . "
                    WHERE product_id = ".$v;
            $productList[0] = $db->Execute($sql);
            preg_match_all('/(\d+)\.(\d+)/is',$currencies->display_price($productList[0]->fields['price']),$productPrice);
            $productList[0]->fields['filter_14']   = 'new';
            $productList[0]->fields['filter_15']   = "in stock";
            $productList[0]->fields['url']         = HTTP_SERVER . '/' . FILENAME_INDEX . '?main_page=product&pID=' . $v;
            $productList[0]->fields['image']       = HTTP_SERVER . '/' . $productList[0]->fields['image'];
            $productList[0]->fields['price']       = $productPrice[0][0]." ".$currencies->get_code();
            $productList[0]->fields['specials_price'] = number_format($productList[0]->fields['specials_price'],2)." ".$currencies->get_code();
            $sqlQuery         = "SELECT product_id,product_option_id,product_option_value_id, price FROM ".TABLE_PRODUCT_ATTRIBUTE . " WHERE product_id = ". $v;
            $productAttrValue = $db->Execute($sqlQuery);
            $productAttr      = array();
            $optionValueArray = array();
            $product_re       = array();
            $productTmp       = array();
            $optionArray      = array();

            while (!$productAttrValue->EOF) {
                $productAttr[] = array(
                        'product_option_id'       => $productAttrValue->fields['product_option_id'],
                        'product_option_value_id' => $productAttrValue->fields['product_option_value_id'],
                        'price'                   => $productAttrValue->fields['price']
                );
                $productAttrValue->MoveNext();
            }

            foreach($productAttr as $key => $val){
                //select product option
                $optionValueSql     = "SELECT product_option_id,product_option_value_id,name FROM ".TABLE_PRODUCT_OPTION_VALUE ." WHERE product_option_value_id = ".$val['product_option_value_id'];
                $optionValueResult  = $db->Execute($optionValueSql);
                $optionValueArray[] = array(
                    'product_option_id'       => $optionValueResult->fields['product_option_id'],
                    'product_option_value_id' => $optionValueResult->fields['product_option_value_id'],
                    'name'                    =>  $optionValueResult->fields['name']
                );

                $optionSql     = "SELECT product_option_id,name FROM " . TABLE_PRODUCT_OPTION  . " WHERE product_option_id = " . $val['product_option_id'];
                $optionResult  = $db->Execute($optionSql);
                $optionArray[] = array(
                    'product_option_id' => $optionResult->fields['product_option_id'],
                    'name'              => $optionResult->fields['name']
                );

                $productOption[$key] = array(
                    'product_value_id' => $val['product_option_value_id'],
                    'attribute'        => $optionArray[$key]['name'],
                    'optionValue'      => $optionValueArray[$key]['name'],
                    'price'            => $val['price']
                );

                $productTmp[$val['product_option_id']][] = array(
                    'product_value_id' => $val['product_option_value_id']
                );
            }
            //combination
            $rows = array();

            $rows = getCombination($productTmp);
            //Combinatorial Calculation
            $productCom = array();

            foreach($rows as $option => $item){
                $productCom[] = combinatorialCalculation($item,$productOption);
            }

            //printf
            if($productCom){
                $size = $color = $brand = $material = "";
                foreach($productCom as $key => $value){
                    $price = array_pop($value);
                    foreach($value as $k => $v) {
                        switch ($v) {
                            case 'size':
                                $size = $value[$k + 1];
                                break;
                            case 'color':
                                $color = $value[$k + 1];
                                break;
                            case 'material':
                                $material = $value[$k + 1];
                                break;
                            case 'brand':
                                $brand = $value[$k + 1];
                                break;
                        }
                    }

                    $product_price = ($price + $productPrice[0][0])." ".$currencies->get_code();
                    $product_re[] = array(
                        'id'             => $productList[0]->fields['sku'],
                        'name'           => $productList[0]->fields['name'],
                        'description'    => $productList[0]->fields['description'],
                        'google product category'=> '',
                        'product_type'   => '',
                        'link'           => $productList[0]->fields['url'],
                        'image link'     => $productList[0]->fields['image'],
                        'condition'      => 'new',
                        'availability'   => "in stock",
                        'price'          => $product_price,
                        'sale price'     => $productList[0]->fields['specials_price'],
                        'gtin'           => '',
                        'brand'          => $brand,
                        'color'          => $color,
                        'size'           => $size,
                        'material'       => $material
                    );
                }
            }
            //要导出的数据
            $row = $productList[0]->fields;

            foreach ($row as $kv => $va) {
                $row[$kv] = iconv('utf-8', 'gbk', $va);
            }
            fputcsv($fp, $row);

            if($product_re) {
                for ($ij = 0; $ij < count($product_re); $ij++) {
                    $product_row = $product_re[$ij];
                    foreach ($product_row as $it => $item) {
                        $product_row[$it] = iconv('utf-8', 'gbk', $item);
                    }
                    fputcsv($fp, $product_row);
                }
            }
        }

        fclose($fp);
        $message_stack->add_session('product', '数据导出成功！', 'success');
        exit;
    }
    break;

}

function getCombination($product_arr){
    $rows = array();
    foreach($product_arr as $option => $items){
        if(count($rows) > 0){
            $clone = $rows;
            $rows = array();
            foreach($items as $item){
                $tmp = $clone;
                foreach($tmp as $index => $val){
                    $val[$option] = $item['product_value_id'];
                    $tmp[$index] = $val;
                }
                $rows = array_merge($rows, $tmp);
            }
        }else{
            //记录第一列
            foreach($items as $item){
                $rows[][$option] = $item['product_value_id'];
            }
        }
    }
    return $rows;
}

function combinatorialCalculation($item, $product){
    $combination = array();
    $price = 0.0;
    $array = array('size','color','material','brand');
    foreach($item as $value){
        foreach($product as $k => $v){
            if($value == $v['product_value_id'] && in_array(strtolower($v['attribute']),$array)){
                array_push($combination,strtolower($v['attribute']));
                $price += $v['price'];
                array_push($combination,$v['optionValue']);
                break;
            }
        }
    }
    array_push($combination,$price);
    return $combination;
}

红色部分为导出关键代码