PHPexcel：多sheet上传和下载，phpexcelsheet上传-php手册-php.cn

PHPexcel：多sheet上传和下载，phpexcelsheet上传

excel表格上传和下载，断断续续写了很久，赶紧记下来万一以后忘记就亏大了= =

数据库有三张表：

上传一张表格，每个sheet对应一个if_table_n,if_user_table记录上传信息，if_column_map记录每个if_table_n的列名与数据库列名对应，if_system_config记录表格数目。

<span>  1 <span>public function uploadFile() {
<span>  2 
<span>  3 <span>        if(!empty($_FILES)) {
<span>  4 <span>            $upload = new \Think\Upload();// 实例化上传类
<span>  5 <span>            $upload->maxSize   =     1000000000  ;// 设置附件上传大小
<span>  6 <span>            $upload->rootPath  =     './Uploads/'; // 设置附件上传根目录
<span>  7 <span>            // $upload->savePath  =     $filePath.'/'; // 设置附件上传（子）目录
<span>  8 <span>            $info   =   $upload->upload();
<span>  9 
<span> 10 <span>            if($info === false) {// 上传错误提示错误信息
<span> 11 <span>                $this->error("上传错误：".$upload->getError(),"http://192.168.151.175/basicinfo",3);
<span> 12 <span>            }
<span> 13 
<span> 14 <span>            //判断文件是否为excel格式
<span> 15 <span>            $fileName = $info["file"]['name'];
<span> 16 
<span> 17 
<span> 18 <span>            $fileType = substr($fileName,strrpos($fileName, '.') + 1);
<span> 19 
<span> 20 <span>            if(strtolower($fileType) !== "xls" && strtolower($fileType) !== "xlsx") {
<span> 21 <span>               $this->error("文件格式错误！");
<span> 22 <span>            }
<span> 23 
<span> 24 <span>            //文件名
<span> 25 <span>            $filen=substr($fileName,0,strrpos($fileName, '.'));
<span> 26 
<span> 27 <span>            //判断引入何种格式的phpexcel 对应两种版本的excel
<span> 28 <span>            import("Org.Util.PHPExcel");
<span> 29 
<span> 30 <span>            $PHPExcel = new \PHPExcel();
<span> 31 
<span> 32 <span>            if($fileType === "xlsx") {
<span> 33 <span>                //如果excel文件后缀名为.xlsx，导入类
<span> 34 <span>                import("Org.Util.PHPExcel.Reader.Excel2007");
<span> 35 <span>                $PHPReader=new \PHPExcel_Reader_Excel2007();
<span> 36 <span>            }
<span> 37 <span>            else {
<span> 38 <span>                import("Org.Util.PHPExcel.Reader.Excel5"); 
<span> 39 <span>                $PHPReader=new \PHPExcel_Reader_Excel5();
<span> 40 <span>            }
<span> 41 
<span> 42             
<span> 43 <span>            $PHPExcel=$PHPReader->load(SITE_PATH."Uploads/".$info["file"]["savepath"].$info["file"]['savename']);            
<span> 44 <span>            // 确定当前excel文件的数量
<span> 45 <span>            $res = D('IfSystemConfig')->getValueByKey('table_count');
<span> 46 
<span> 47 <span>            //获取工作表个数
<span> 48 <span>            $sheetCount = $PHPExcel->getSheetCount();
<span> 49 
<span> 50 <span>            //获取sheet的名字
<span> 51 <span>            $sheetname = $PHPExcel->getSheetNames();
<span> 52 
<span> 53 <span>            // 当前表数量字段,加上工作表的数量
<span> 54 <span>            $result = D('IfSystemConfig')
<span> 55 <span>            ->setValueByKey("table_count",intval($res[0]['value'])+ $sheetCount);
<span> 56 
<span> 57 <span>            if($result === false) {
<span> 58 <span>                $this->error("数据上传失败！");
<span> 59 <span>            }
<span> 60 
<span> 61 <span>            $unique_name_id = $res[0]['value'];
<span> 62 
<span> 63             for($s = 0;$s<span><<span>$sheetCount<span>;$s++) 
<span> 64 <span>            {
<span> 65 <span>                /**
<span> 66 <span>                * 保存表的信息
<span> 67 <span>                * @access public
<span> 68 <span>                * @param string $tablename 表名
<span> 69 <span>                * @param string $filename 文件名（全路径）
<span> 70 <span>                * @return null
<span> 71 <span>                */
<span> 72 <span>                $PHPExcel<span>=$PHPReader-<span>><span>load($filename);
<span> 73 
<span> 74 <span>                //选择工作表
<span> 75 <span>                $currentSheet = $PHPExcel->getSheet($sheetnum);
<span> 76     
<span> 77 <span>                //获取总列数
<span> 78 <span>                $allColumn=$currentSheet->getHighestColumn();
<span> 79 
<span> 80 <span>                //获取总行数
<span> 81 <span>                $allRow=$currentSheet->getHighestRow();
<span> 82 
<span> 83 <span>                //获取整张表，写入二维数组arr中 arr[行][列]
<span> 84                 for($currentRow=1;$currentRow<span><<span>=$allRow<span>;$currentRow++){
<span> 85 <span>                    //从哪列开始，A表示第一列
<span> 86 <span>                    for($currentColumn<span>='A';$currentColumn<=$allColumn;$currentColumn++){
<span> 87                         <span>//数据坐标
<span> 88 <span>                        $address<span>=$currentColumn.$currentRow;
<span> 89                         <span>$cvalue <span>= $currentSheet-<span>><span>getCell($address)->getValue();
<span> 90 
<span> 91 <span>                        //读取到的数据，保存到数组$arr中
<span> 92 <span>                        $arr[$currentRow][$currentColumn]=$cvalue;
<span> 93 <span>                    }
<span> 94 <span>                }
<span> 95 
<span> 96 <span>                // 表、列、代表含义的映射  
<span> 97 <span>                // 列位自定义  
<span> 98 <span>                //field_0 为自增形id
<span> 99 <span>                $j = 1;
<span>100 <span>                $data['map_table'] = $tablename;
<span>101 <span>                $data['col_name'] = "field_".'0';
<span>102 <span>                $data['col_meaning'] = "";
<span>103 
<span>104 <span>                //从field_1 .... field_n,对应excel列名
<span>105 <span>                $res = D('IfColumnMap')->saveData($data);
<span>106                 foreach ($arr[1] as $key => $value) {    
107                     $data['col_name'] = "field_".$j;
108                     $data['col_meaning'] = $arr[1][$key];
109                     $res = D('IfColumnMap')->saveData($data);
110                     $j++;
111                 } 
112 
113                 // 查找每个字段数据的最大长度
114                 // 用来确定每个字段的长度
115                 $t = 0;
116                 foreach ($arr[2] as $key => $value) {
117                     $ml = 0;
118                     for($i = 2;$i <= count($arr);$i++) {
119                         if(strlen($arr[$i][$key]) > $ml) {
120                             $ml =  strlen($arr[$i][$key]);
121                         }
122                     }   
123                     $maxLenght[$t] = $ml; 
124                     $t++;
125                 }
126             
127                 // 如果长度大于256，就将字段类型设置为text类型
128                 for($i = 0;$i < count($maxLenght); $i++) {
129 
130                     if($maxLenght[$i] > 256) {
131                     $type[$i] = "text";
132                     }
133                     else {
134                         $type[$i] = "varchar(".($maxLenght[$i]+15).")";
135                     }   
136                 }
137 
138                 //建立if_table_n的sql语句 
139                 //utf-8编码 default charset=utf8
140                 //自增类型 int primary key not  null  auto_increment
141                 $sqlString = "CREATE TABLE ".$tablename." ( ";
142                 $sqlString .= "field_0"." "."int primary key not  null  auto_increment,";
143                 $sqlString .= "field_1"." ".$type[0];
144                 for($i = 1;$i < count($maxLenght);$i++) {
145                     $sqlString .= ","."field_".($i+1)." ".$type[$i];
146                 }
147                 $sqlString .= ") default charset=utf8";
148 
149                 // 数据表创建
150                 $Model = new \Think\Model(); // 实例化一个model对象 没有对应任何数据表
151                 $Model->execute($sqlString);
152                    
153                 // 为新建的数据表if_table_n添加数据 
154                 for($i = 2;$i <= count($arr);$i++) {
155                     $k = 1;
156                     foreach ($arr[$i] as $key => $value) {
157                         $info['field_'.$k] = $arr[$i][$key];
158                         $k++;
159                     }
160                     M($tablename)->add($info);
161                 }
162                 
163                 // 插入 用户、表 数据之间的关系
164                 //if_user_table
165                 $data = array(
166                     'userid' => session('if_userid'),
167                     'unique_name' => 'if_table_'.$unique_name_id,
168                     'file_name' => $filen,
169                     'save_name' => $info["file"]['savename'],
170                     'save_path' => $info["file"]["savepath"],
171                     'submit_time' => date("Y-m-d h:i:s"),
172                     'tag' => 1,
173                     'file_id' => $res[0]['value'],
174                     'sheet' => $s,
175                     'sheetname' => $sheetname[$s]
176                 );
177 
178                 $result = D('IfUserTable')->saveData($data);
179  
180                 if($result === false) {
181                     $this->error("数据上传失败！");
182                 }
183                 $unique_name_id++;
184             }
185 
186             $this->success("上传成功！",__APP__."/Home/Index/index");
187           
188         }</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

ログイン後にコピー

下载此表格：

还有一些未用到的设置：

设置单元格宽度

<span>$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);</span>

ログイン後にコピー

设置单元格高度

<span>$objPHPExcel->getActiveSheet()->getRowDimension(<span>$i)->setRowHeight(40);</span></span>

ログイン後にコピー

合并单元格

<span>$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');</span>

ログイン後にコピー

拆分单元格

<span>$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');</span>

ログイン後にコピー

设置保护cell,保护工作表

<span>$objPHPExcel->getActiveSheet()->getProtection()->setSheet(<span>true<span>); 
<span>$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');</span></span></span></span>

ログイン後にコピー

设置格式

<span>$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::<span>FORMAT_CURRENCY_EUR_SIMPLE);
<span>$objPHPExcel->getActiveSheet()->duplicateStyle( <span>$objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );</span></span></span></span>

ログイン後にコピー

设置加粗

<span>$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(<span>true);</span></span>

ログイン後にコピー

设置垂直居中

<span>$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);</span>

ログイン後にコピー

设置字号

<span>$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);</span>

ログイン後にコピー

设置边框

<span>$objPHPExcel->getActiveSheet()->getStyle('A1:I20')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); </span>

ログイン後にコピー

设置边框颜色

<span>$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300'<span>);
<span>$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300'<span>);
<span>$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'<span>);
<span>$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300'<span>);
<span>$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'<span>);
<span>$objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');</span></span></span></span></span></span></span></span></span></span></span>

ログイン後にコピー

插入图像

<span>$objDrawing = <span>new<span> PHPExcel_Worksheet_Drawing();
<span>/*<span>设置图片路径 切记：只能是本地图片<span>*/ 
<span>$objDrawing->setPath('图像地址'<span>);
<span>/*<span>设置图片高度<span>*/ 
<span>$objDrawing->setHeight(180);<span>//<span>照片高度
<span>$objDrawing->setWidth(150); <span>//<span>照片宽度<span>
/*<span>设置图片要插入的单元格<span>*/
<span>$objDrawing->setCoordinates('E2'<span>);
 <span>/*<span>设置图片所在单元格的格式<span>*/
<span>$objDrawing->setOffsetX(5<span>);
<span>$objDrawing->setRotation(5<span>);
<span>$objDrawing->getShadow()->setVisible(<span>true<span>);
<span>$objDrawing->getShadow()->setDirection(50<span>);
<span>$objDrawing->setWorksheet(<span>$objPHPExcel->getActiveSheet());</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

ログイン後にコピー

设置单元格背景色

<span>$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::<span>FILL_SOLID);
<span>$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getFill()->getStartColor()->setARGB('FFCAE8EA');</span></span></span>

ログイン後にコピー

<span> 1 <span>public function downloadFile() {
<span> 2 <span>        $file_id = I('file_id');
<span> 3 <span>        $tablename = D('IfUserTable')->getNameByFileid($file_id);
<span> 4 
<span> 5 <span>        import("Org.Util.PHPExcel");
<span> 6 <span>        //不清楚为什么\PHPExcel()前要加\，不加会报错，大哥也没解释清楚
<span> 7 <span>        $objPHPExcel = new \PHPExcel();
<span> 8 <span>        import("Org.Util.PHPExcel.Reader.Excel5"); 
<span> 9 
<span>10 <span>        //或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
<span>11 
<span>12 <span>        // 实例化Create new PHPExcel object    
<span>13           
<span>14 <span>        /* @func 设置文档基本属性 */    
<span>15 <span>        $objPHPExcel->getProperties()
<span>16 <span>            ->setCreator("ctos")       //设置创建人
<span>17 <span>            ->setLastModifiedBy("ctos")     //最后修改人
<span>18 <span>            ->setTitle("Office 2007 XLSX Test Document")    //标题
<span>19 <span>            ->setSubject("Office 2007 XLSX Test Document")   //备注
<span>20 <span>            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //设置描述 
<span>21 <span>            ->setKeywords("office 2007 openxml php")  //设置关键字 | 标记
<span>22 <span>            ->setCategory("Test result file");  //设置类别
<span>23 
<span>24 
<span>25         for ($i = 0; $i <span><<span> count<span>($tablename); $i++) { 
<span>26 
<span>27 <span>            $Model <span>= new <span>\Think\Model();
<span>28 
<span>29 <span>            $name <span>= $tablename[$i]['unique_name'];
<span>30             
<span>31             <span>$sqlString <span>= "select * from "<span>.$name;
<span>32 
<span>33 <span>            $column_info[$i] <span>= D('IfColumnMap')-<span>><span>getDataByTable($name);
<span>34 
<span>35 <span>            $res[$i] = $Model->query($sqlString);
<span>36 
<span>37 <span>            //首先要创建一个sheet的空间，否则都会写在同一个sheet中
<span>38 <span>            $objPHPExcel->createSheet();
<span>39 <span>            $objPHPExcel->setActiveSheetIndex($i);
<span>40 
<span>41 <span>            // 表头写入
<span>42             for($currentColumn='A',$j=1;$j<span><<span>count<span>($column_info[$i]);$currentColumn++,$j++){
<span>43           
<span>44 <span>                $colunmname <span>= $column_info[$i][$j]['col_meaning'];
<span>45                 
<span>46                 <span>$objPHPExcel-<span>><span>getActiveSheet()
<span>47 <span>                ->setCellValue($currentColumn.'1', $colunmname);
<span>48 <span>            }
<span>49    
<span>50 <span>            // 写入内容 某个内容写进An，Bn...
<span>51             for($currentRow=2,$j=0;$currentRow<span><<span>=count<span>($res[$i])+1;$currentRow++,$j++){
<span>52 
<span>53 <span>                for($currentColumn<span>='A',$k=1;$k<count($column_info[$i]);$currentColumn++,$k++){
<span>54                     <span>//设置单元格左对齐
<span>55 <span>                    $objPHPExcel-<span>><span>getActiveSheet()
<span>56 <span>                    ->getStyle($currentColumn. $currentRow)
<span>57 <span>                    ->getAlignment()
<span>58 <span>                    ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
<span>59 
<span>60 <span>                    $objPHPExcel->getActiveSheet()->setCellValue($currentColumn. $currentRow, $res[$i][$j]['field_'.($k)]);
<span>61 <span>                }
<span>62 
<span>63 <span>            } 
<span>64 <span>            //设置sheet的标题
<span>65 <span>            $objPHPExcel->getActiveSheet()->setTitle($tablename[$i]['sheetname']);
<span>66 
<span>67 <span>            ob_end_clean();  //清空缓存  
<span>68 <span>        }
<span>69 <span>        header("Pragma: public");
<span>70 
<span>71 <span>        header("Expires: 0");
<span>72 
<span>73 <span>        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
<span>74 
<span>75 <span>        header("Content-Type:application/force-download");
<span>76 
<span>77 <span>        header("Content-Type:application/vnd.ms-execl");
<span>78 
<span>79 <span>        header("Content-Type:application/octet-stream");
<span>80 
<span>81 <span>        header("Content-Type:application/download");
<span>82 <span>        //设置文件的名称
<span>83 <span>        header('Content-Disposition:attachment;filename='.$tablename['0']['file_name'].'.xls');
<span>84 
<span>85 <span>        header("Content-Transfer-Encoding:binary");
<span>86 
<span>87 <span>        //不清楚为什么\PHPExcel_IOFactory前要加\，不加会报错，大哥也没解释清楚
<span>88 <span>        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
<span>89 
<span>90 <span>        $objWriter->save('php://output');  
<span>91     }</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

ログイン後にコピー