mansonLiu 2018-11-02 12:06:12 340次浏览 1条评论 0 0 0

难得今天也能过着打卡拿薪的无聊日子,写点东西。 由于工作需要,甲方版本限制了我只能用1.1的,所以用最新版本的同学请自行参考修改。 类库我用 PHPExcel 处理 excel 数据。 好了直接上代码:

  1. 配置引入类库

    <?php
     'import'=>array(
         'application.models.*',
         'application.components.*',
         'application.controllers.*',
    	   'application.extensions.*',
         'application.extensions.PHPExcel.PHPExcel',	
     ),
    ?>
    
  2. excel上传部分代码

    <?php
    $form_action = $_POST['form_action'];
    if ( $form_action == 'upload' and !empty($_FILES['excelForm'])) {
     $file = $model->dataFile = CUploadedFile::getInstance($model, 'dataFile'); //开始上传,生成对象
     if ($model->validate() && $file->getType() == 'application/vnd.ms-excel') {
    
         $excelFile = $file->getTempName();
         $fileName = $file->name;
         Yii::$enableIncludePath = false;
         Yii::import('application.extensions.PHPExcel.PHPExcel', 1);   //为什么已经import了还要写这个的原因是yii的自动引入与类库自动引入有冲突
         $extension = strtolower( pathinfo($fileName, PATHINFO_EXTENSION) );
         if ($extension =='xlsx') {
             $excelReader = PHPExcel_IOFactory::createReader('Excel2007'); //excel版本
         } else if ($extension =='xls') {
             $excelReader = PHPExcel_IOFactory::createReader('Excel5');    //excel版本
         }
         $excelReader->setReadDataOnly(true);                       //这个的作用的是只读取excel有数据的行 
         $phpexcel = $excelReader->load($excelFile)->getSheet(0);         //选择第一个工作表 sheet(0)  
    
         $maxRow = $phpexcel->getHighestRow();                     //这里开始读取处理excel数据,中间我有将excel的AQ列和AR列格式化时间   
         $maxColumn = $phpexcel->getHighestColumn();
         $maxColumnNum = PHPExcel_Cell::columnIndexFromString($maxColumn);
    
         $data = array();
    
         for ($i = 1; $i < $maxRow; $i++) {                    //从第二行开始循环读取,第一行有表title
             $rowData = array();
             for ($j = 0; $j < $maxColumnNum; $j++) {
                 $pCoordinate = PHPExcel_Cell::stringFromColumnIndex($j) . '' . ($i + 1);
                 $k = PHPExcel_Cell::stringFromColumnIndex($j);
                 if ($k == 'AQ' || $k == 'AR'){
                     $date = $phpexcel->getCell($pCoordinate) -> getFormattedValue();
                     $time = PHPExcel_Shared_Date::ExcelToPHP($date);
                     if ($time>0){
                         $date = !empty($date) ? gmdate('Y-m-d H:i:s',$time) : '';
                     } else {
                         $time = strtotime($date);
                         $date = !empty($date) ? date('Y-m-d H:i:s',$time) : '';
                     }
                     $rowData[] = $date;
                 } else {
                     $rowData[] = trim($phpexcel->getCell($pCoordinate)-> getValue());
                 }
             }
             $data[] = $rowData;
             //至此每行的数据已被放入$data数据中,后续处理我就不贴,每人需求不同
    ?>
    
  3. excel 数据下载 数据下载就简单很多了,代码:
$excelData = Axxxxx::model()->getExcelData();        //拿出要下载的数据
$maxColumn = count($excelData[0]);
$maxRow    = count($excelData);

$objPHPExcel = new PHPExcel();

for ($i = 0; $i < $maxColumn; $i++) {        //循环处理数据生成excel对象
    for ($j = 0; $j < $maxRow; $j++) {
        $pCoordinate = PHPExcel_Cell::stringFromColumnIndex($i) . '' . ($j + 1);
        $pValue      = $excelData[$j][$i];
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue($pCoordinate, $pValue);
    }
}                                

$objPHPExcel->getActiveSheet()->setTitle('文件sheet名称');//设置下载文件sheet名

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="下载文件名称"'); //设置文件名称
header('Cache-Control: max-age=0');
header("Expires: 0");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');   //输出下载
您需要登录后才可以评论。登录 | 立即注册