Beispiel #1
0
 public function actionExp()
 {
     $msg = $this->msgcode();
     $smonth = Yii::app()->getRequest()->getParam("smonth", date("Ym"));
     //时间
     $pmonth = Yii::app()->getRequest()->getParam("pmonth", date("Ym"));
     //时间
     $mcol_type = Yii::app()->getRequest()->getParam("mcol_type", 0);
     //类型
     $col = AppBsColi::model()->findByPk($smonth);
     if (empty($col)) {
         $msg['msg'] = sprintf("开始时间%s 该月数据为空,请重新选择开始时间", $smonth);
         echo $msg['msg'];
     } else {
         $criteria = new CDbCriteria();
         $criteria->addCondition("month>={$smonth} AND month<={$pmonth}");
         $allList = AppBsCol::model()->findAll($criteria);
         $head = json_decode($col->desc, true);
         $ms = isset($head[$mcol_type]) ? $head[$mcol_type] : array();
         header('Content-Type: application/vnd.ms-excel');
         header('Content-Disposition: attachment;filename="COL.csv"');
         header('Cache-Control: max-age=0');
         $fp = fopen('php://output', 'a');
         // 输出Excel列名信息
         $head1 = array("月份", "AM", "DM", "餐厅编号", "餐厅名");
         foreach ($ms as $k => $val) {
             if ($k === "min") {
                 continue;
             }
             if ($k === "max") {
                 continue;
             }
             array_push($head1, $val);
         }
         foreach ($head1 as $i => $v) {
             // CSV的Excel支持GBK编码,一定要转换,否则乱码
             $head1[$i] = iconv('utf-8', 'gbk', $v);
         }
         // 将数据通过fputcsv写到文件句柄
         fputcsv($fp, $head1);
         // 计数器
         $cnt = 0;
         // 每隔$limit行,刷新一下输出buffer,不要太大,也不要太小
         $limit = 100000;
         foreach ($allList as $value) {
             $cnt++;
             if ($limit == $cnt) {
                 //刷新一下输出buffer,防止由于数据过多造成问题
                 ob_flush();
                 flush();
                 $cnt = 0;
             }
             $mkl = explode("|!|", $value->desc);
             if (isset($ms['min'])) {
                 $arrr = array($value->month, $value->am, $value->dm, $value->ct_id, $value->ct_name);
                 if (empty($ms['max'])) {
                     $mkl = array_merge($arrr, array_slice($mkl, $ms['min']));
                 } else {
                     $mkl = array_merge($arrr, array_slice($mkl, $ms['min'], $ms['max']));
                 }
             }
             $row = $mkl;
             foreach ($row as $i => $v) {
                 // CSV的Excel支持GBK编码,一定要转换,否则乱码
                 $row[$i] = iconv('utf-8', 'gbk', $v);
             }
             fputcsv($fp, $row);
         }
     }
 }
Beispiel #2
0
 /**
  * 将csv文件保存到数据库中
  *
  * @param string $loadPath csv文件的路径
  * @return int 成功条数
  */
 public function storeCsv($loadPath, $month)
 {
     $connection = Yii::app()->db;
     $sql = sprintf("INSERT INTO %s(`month`,`am`,`dm`,`ct_id`,`desc`,`ct_name`) VALUES", $this->tableName());
     //构造SQL
     $file_handle = fopen($loadPath, "r");
     $tp = fgetcsv($file_handle);
     $i = 0;
     $colAll = array();
     foreach ($tp as $k => $val) {
         if ($k !== 0 && trim($val) != "") {
             $colAll[$i] = $k;
             $i++;
         }
     }
     $tp1 = fgetcsv($file_handle);
     $sendArr = $this->getArr($tp1, $colAll);
     $tp2 = fgetcsv($file_handle);
     $sendArr2 = $this->getArr($tp2, $colAll);
     foreach ($sendArr2 as $k => $val) {
         foreach ($val as $k1 => $v1) {
             if ($sendArr[$k][$k1] == "") {
                 $sendArr[$k][$k1] = isset($sendArr[$k][$k1 - 1]) ? $sendArr[$k][$k1 - 1] : $sendArr[$k][$k1];
             }
             $sendArr2[$k][$k1] = $sendArr[$k][$k1] . $v1;
         }
         if ($k == 0) {
             $sendArr2[$k]['min'] = 4;
             $sendArr2[$k]['max'] = $colAll[0] - 4;
         } elseif ($k == 6) {
             $sendArr2[$k]['min'] = $colAll[5];
             $sendArr2[$k]['max'] = "";
         } else {
             $n = $k - 1;
             $sendArr2[$k]['min'] = $colAll[$n];
             $sendArr2[$k]['max'] = $colAll[$k] - $colAll[$n];
         }
     }
     $dec = json_encode($sendArr2);
     $model = AppBsColi::model()->findByPk($month);
     if (empty($model)) {
         $model = new AppBsColi();
         $model->month = $month;
         $model->type = 0;
     }
     $model->desc = $dec;
     $model->save();
     $str = "";
     while (!feof($file_handle)) {
         $arr = fgetcsv($file_handle);
         if (empty($arr[0])) {
             break;
         }
         foreach ($arr as $k => $val) {
             $arr[$k] = trim(iconv("GBK", "UTF-8//IGNORE", $val));
         }
         $desca = implode("|!|", $arr);
         if (isset($arr[3]) && !empty($arr[0])) {
             $str .= sprintf("('%s','%s','%s','%s','%s','%s'),", $month, $arr[0], $arr[1], $arr[2], $desca, $arr[3]);
         }
     }
     fclose($file_handle);
     if (empty($str)) {
         return "更新数据失败";
     } else {
         $this->deleteAll("month=:mh", array(":mh" => $month));
         $sql .= rtrim($str, ",");
         $sqlCom = $connection->createCommand($sql)->execute();
         return "添加数据成功";
     }
 }