Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > PHPexcel：多sheet上传和下载，phpexcelsheet上传_PHP教程

PHPexcel：多sheet上传和下载，phpexcelsheet上传_PHP教程

WBOY
Release： 2016-07-12 08:58:24
Original
2000 people have browsed it

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>
Copy after login

下载此表格：

还有一些未用到的设置：

设置单元格宽度

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

设置单元格高度

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

合并单元格

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

拆分单元格

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

设置保护cell,保护工作表

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

设置格式

<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>
Copy after login

设置加粗

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

设置垂直居中

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

设置字号

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

设置边框

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

设置边框颜色

复制代码 
<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>
Copy after login
复制代码

插入图像

复制代码 
<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>
Copy after login
复制代码

设置单元格背景色

<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>
Copy after login
<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>
Copy after login

www.bkjia.comtruehttp://www.bkjia.com/PHPjc/1104062.htmlTechArticlePHPexcel：多sheet上传和下载，phpexcelsheet上传 excel表格上传和下载，断断续续写了很久，赶紧记下来万一以后忘记就亏大了= = 数据库有三张表...
Related labels：
phpexcel upload download and
source：php.cn
Previous article：IRQ全称为Interrupt Request_PHP教程 Next article：Properties and methods of DOM and XMLHttpRequest objects, domxmlhttprequest_PHP tutorial
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
function_exists() cannot determine the custom function Function test () {return true;} if (function_exists ('test')) {echo "test is function...
From 2024-04-29 11:01:01
0
3
2183
How to display the mobile version of Google Chrome Hello teacher, how can I change Google Chrome into a mobile version?
From 2024-04-23 00:22:19
0
11
2330
The child window operates the parent window, but the output does not respond. The first two sentences are executable, but the last sentence cannot be implemented.
From 2024-04-19 15:37:47
0
1
1960
There is no output in the parent window document.onclick = function(){ window.opener.document.write('I am the output of the child ...
From 2024-04-18 23:52:34
0
1
1845
Where is the courseware about CSS mind mapping? Courseware
From 2024-04-16 10:10:18
0
0
1896
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template