PHP使用PHPExcel导出，导入数据总结

导出excel:

        header("Content-type: text/html; charset=utf-8");
        include_once dirname(dirname(__FILE__)).'/Classes/PHPExcel.php';
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        
        //设置标题
        $objPHPExcel->getActiveSheet()->setTitle($filename);

        //设置表头
        $key1 = 1;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$key1, 'ID')
            ->setCellValue('B'.$key1, '关键词')
            ->setCellValue('C'.$key1, '歌曲HASH')
            ->setCellValue('D'.$key1, '歌曲名')
            ->setCellValue('E'.$key1, '排序')
            ->setCellValue('F'.$key1, '开始日期')
            ->setCellValue('G'.$key1, '结束日期')
            ->setCellValue('H'.$key1, '操作人');

        //设置样式：
        $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true); //多个单元格
        // $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->getColor()->setARGB('FFFF0000'); //设置颜色
        // $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true); //单个单元格
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);  //列宽必须单个设置
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);

        //写入内容
        foreach($datalist as $key =>$value){
            $key1=$key+2;
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$key1, $value['id'])
                ->setCellValue('B'.$key1, $value['keyword'])
                ->setCellValue('C'.$key1, $value['hash'])
                ->setCellValue('D'.$key1, $value['filename'])
                ->setCellValue('E'.$key1, $value['weight'])
                ->setCellValue('F'.$key1, $value['startdate'])
                ->setCellValue('G'.$key1, $value['enddate'])
                ->setCellValue('H'.$key1, $value['editor']);
        }
        // $objPHPExcel->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'. $filename .'.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

从excel导入：

        header("Content-type: text/html; charset=utf-8");
        $addFile=$_FILES['excel'];
        $excelFileName =$addFile['tmp_name'];
        $location = KG_DOMAIN . '/diysort/index';
        if ($addFile['type'] != 'application/vnd.ms-excel' && $addFile['type'] != 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
            echo '<script type="text/javascript">alert("上传格式错误");window.location.href="' . $location .'";</script>';
            //header("Location: " . KG_DOMAIN . '/diysort/index');
            exit;
        }
        include_once dirname(dirname(__FILE__)).'/Classes/PHPExcel/IOFactory.php';
    
        $objPHPExcel = PHPExcel_IOFactory::load($excelFileName);
        $objWorksheet = $objPHPExcel->getSheet(0);
        $highestRow    = $objWorksheet->getHighestRow();          // 取得总行数
        $highestColumn = $objWorksheet->getHighestColumn();    // 取得总列数

        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString ( $highestColumn );  
        $excelData = array ();  
        for($row = 1; $row <= $highestRow; $row++) {  
            for($col = 0; $col < $highestColumnIndex; $col++) {  
                $excelData[$row-1][] = $objWorksheet->getCellByColumnAndRow( $col, $row )->getValue();  
            }  
        }  
        //数据入库
        $diysortmodule = new DiysortModule();
        $rowins = 0;
        foreach ($excelData as $key => $value) {
            if (empty($value) || trim($value[0]) == '关键词' || trim($value[0] == 'ID')) {
                continue;
            }
            $keyword = trim(addslashes($value[0]));
            $hash = trim(addslashes($value[1]));
            $filename = trim(addslashes($value[2]));
            $weight = intval($value[3]);
            $startdate = trim(addslashes($value[4]));
            $enddate = trim(addslashes($value[5]));
            $adddate = date('Y-m-d H:i:s',time());
            $input_data = array(
                'keyword'    => $keyword,
                'startdate'  => $startdate,
                'enddate'    => $enddate,
                'hash'       => $hash,
                'filename'   => $filename,
                'weight'     => $weight,
                'adddate'    => $adddate,
                'editor'     => $this->editor, 
            );
            if (!empty($hash) && !empty($filename) && is_numeric($weight)) {
                $newid = $diysortmodule->addData($input_data);
                if ($newid > 0) {
                    $rowins ++;
                }
            }
        }
        echo '<script type="text/javascript">alert("共上传成功'. $rowins.' 条记录");window.location.href="' . $location .'";</script>';
        exit;