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;