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

Rumah

php教程

php手册

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 13, 2016 am 08:45 AM

phpexcelmuat naikmuat turundan

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>

Kenyataan

Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Alat AI Hot

Undresser.AI Undress

Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover

Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool

Gambar buka pakaian secara percuma

Clothoff.io

Penyingkiran pakaian AI

AI Hentai Generator

Menjana ai hentai secara percuma.

Tunjukkan Lagi

Artikel Panas

R.E.P.O. Kristal tenaga dijelaskan dan apa yang mereka lakukan (kristal kuning)

3 minggu yang laluBy尊渡假赌尊渡假赌尊渡假赌

R.E.P.O. Tetapan grafik terbaik

3 minggu yang laluBy尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows: Penyelesaian Riddle Seashell

2 minggu yang laluByDDD

R.E.P.O. Cara Memperbaiki Audio Jika anda tidak dapat mendengar sesiapa

3 minggu yang laluBy尊渡假赌尊渡假赌尊渡假赌

WWE 2K25: Cara Membuka Segala -galanya Di Myrise

4 minggu yang laluBy尊渡假赌尊渡假赌尊渡假赌

Tunjukkan Lagi

Alat panas

MantisBT

Mantis ialah alat pengesan kecacatan berasaskan web yang mudah digunakan yang direka untuk membantu dalam pengesanan kecacatan produk. Ia memerlukan PHP, MySQL dan pelayan web. Lihat perkhidmatan demo dan pengehosan kami.

MinGW - GNU Minimalis untuk Windows

Projek ini dalam proses untuk dipindahkan ke osdn.net/projects/mingw, anda boleh terus mengikuti kami di sana. MinGW: Port Windows asli bagi GNU Compiler Collection (GCC), perpustakaan import yang boleh diedarkan secara bebas dan fail pengepala untuk membina aplikasi Windows asli termasuk sambungan kepada masa jalan MSVC untuk menyokong fungsi C99. Semua perisian MinGW boleh dijalankan pada platform Windows 64-bit.