php 实现Excel表格导入导出

导入：

1.将Excel表格转换成csv格式的，比较方便。

2.用getCsv方法将数据导出来，遍历添加

代码仅供参考，getCsv方法可以直接复用

$tmp_file = $_FILES ['file'] ['tmp_name'];

$music_singer     = $this->getCsv($tmp_file);
$new_music_singer = array_values(array_unique($music_singer));

$voiceSingerDtObj  = new VoiceSinger_Dt_Mod();

$failNum   = 0;
$succNum   = 0;
$total     = count($new_music_singer);
if($new_music_singer)
{
    foreach($new_music_singer as $name)
    {
        $row   = $voiceSingerDtObj->getVoiceSingerByName($name);
        if(empty($row))
        {
            $res   = $voiceSingerDtObj->addVoiceSinger($name, 0);

            if(! $res)
            {
                $failNum ++;
                continue;

            }
            else
            {
                $succNum ++;
            }

        }

    }
}


utilLib::msgSuccRedirect('导入完成，共'.$total.'条，成功'.$succNum.'条，失败'.$failNum.'条');

 

public static function getCsv($csvFileName, $line = 0, $offset = 0)
{


    $handle = fopen($csvFileName, 'r');
    if (!$handle)
    {
        return '文件打开失败';
    }

    $i   = 0;
    $arr = array();
    while ($data = fgetcsv($handle))
    {
        if ($i < $offset && $offset)
        {
            $i++;
            continue;
        }

        //大于读取行数则退出
        if ($i > $line && $line)
        {
            break;
        }
        $i++;
        foreach ($data as $key => &$value)
        {
            $encode = mb_detect_encoding($value, array('ASCII', 'UTF-8', "GB2312", "GBK", 'BIG5'));


            if ($encode != 'UTF-8')
            {
                $value = @iconv($encode, "utf-8//IGNORE", $value);//转化编码
            }


        }
        $arr[] = $data;

    }
    return $arr;
}

 

导出

$csv_header = ['时间', '  充值金额(元)', '发币数'];
$dataArr[]  = $csv_header;

for ($i = $sbtm; $i <= $setm; $i = $i + 86400)
{
    $y           = $i + 86400;
    $data        = $payOrderObj->getSumMoneyByTm($i, $y, 0, $s_ptype);
    $rmb         = isset($data['rmb']) ? ($data['rmb'] + 0) / 100 : 0;
    $coin        = isset($data['coin']) ? $data['coin'] : 0;
    $row['time'] = date("Y-m-d", $i);
    $row['rmb']  = $rmb;
    $row['coin'] = $coin;
    $dataArr[]   = $row;

    $totalRmb  += $rmb;
    $totalCoin += $coin;
}

$csv_tail  = ['总计', $totalRmb, $totalCoin];
$dataArr[] = $csv_tail;
$string    = "";
foreach ($dataArr as $key => $value)
{
    $string .= implode(",", $value) . "\n";
}

header("Content-type:text/csv");
header("Content-Disposition:attachment;filename=充值对账单[{$billdate}].csv");
header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
header('Expires:0');
header('Pragma:public');
echo chr(0xEF) . chr(0xBB) . chr(0xBF) . $string;
exit();

示例：