PHPexcel:多sheet上传和下载,phpexcelsheet上传
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>

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Magnet link is a link method for downloading resources, which is more convenient and efficient than traditional download methods. Magnet links allow you to download resources in a peer-to-peer manner without relying on an intermediary server. This article will introduce how to use magnet links and what to pay attention to. 1. What is a magnet link? A magnet link is a download method based on the P2P (Peer-to-Peer) protocol. Through magnet links, users can directly connect to the publisher of the resource to complete resource sharing and downloading. Compared with traditional downloading methods, magnetic

Hongguo Short Play is not only a platform for watching short plays, but also a treasure trove of rich content, including novels and other exciting content. This is undoubtedly a huge surprise for many users who love reading. However, many users still don’t know how to download and watch these novels in Hongguo Short Play. In the following, the editor of this website will provide you with detailed downloading steps. I hope it can help everyone in need. Partners. How to download and watch the Hongguo short play? The answer: [Hongguo short play] - [Audio book] - [Article] - [Download]. Specific steps: 1. First open the Hongguo Short Drama software, enter the homepage and click the [Listen to Books] button at the top of the page; 2. Then on the novel page we can see a lot of article content, here

When you log in to someone else's steam account on your computer, and that other person's account happens to have wallpaper software, steam will automatically download the wallpapers subscribed to the other person's account after switching back to your own account. Users can solve this problem by turning off steam cloud synchronization. What to do if wallpaperengine downloads other people's wallpapers after logging into another account 1. Log in to your own steam account, find cloud synchronization in settings, and turn off steam cloud synchronization. 2. Log in to someone else's Steam account you logged in before, open the Wallpaper Creative Workshop, find the subscription content, and then cancel all subscriptions. (In case you cannot find the wallpaper in the future, you can collect it first and then cancel the subscription) 3. Switch back to your own steam

Recently, many users have been asking the editor, how to download links starting with 115://? If you want to download links starting with 115://, you need to use the 115 browser. After you download the 115 browser, let's take a look at the download tutorial compiled by the editor below. Introduction to how to download links starting with 115:// 1. Log in to 115.com, download and install the 115 browser. 2. Enter: chrome://extensions/ in the 115 browser address bar, enter the extension center, search for Tampermonkey, and install the corresponding plug-in. 3. Enter in the address bar of 115 browser: Grease Monkey Script: https://greasyfork.org/en/

123 cloud disk can download many files, so how to download files specifically? Users can select the file they want to download and click to download, or right-click the file and select download. This introduction to the method of downloading files from 123 cloud disk can tell you how to download it specifically. Friends who don’t know much about it should hurry up and take a look! How to download files from 123 cloud disk 1. First open the software, click on the software that needs to be downloaded, and then there will be a download button on it. 2. Or right-click the software and you can see the download button in the list. 3. There will be a download window, select the location to download. 4. After selecting, click Download to download these files.

Now more and more people are starting to play video accounts. Video accounts are also a short video platform where they can share their daily life and make money through video accounts. Recently, I saw some friends asking why the videos from the WeChat video account were not downloaded. Yang Shuaikang went to try it, and there was indeed no download button, so he could only extract the video through other means. Today Yang Shuaikang will share with you a stupid Method, come and take a look. How to extract videos from WeChat video accounts 1. Open our computer version of WeChat and find [Video Account] on the left; 2. Find the video you want to download through search; 3. Finally, use the screen recording tool to adjust the size of the recorded video. Just record and edit it at the end. PS: 1. This method can only be recorded on the computer version, not on the mobile phone.

The superpeople game can be downloaded through the steam client. The size of this game is about 28G. It usually takes one and a half hours to download and install. Here is a specific download and installation tutorial for you! New method to apply for global closed testing 1) Search for "SUPERPEOPLE" in the Steam store (steam client download) 2) Click "Request access to SUPERPEOPLE closed testing" at the bottom of the "SUPERPEOPLE" store page 3) After clicking the request access button, The "SUPERPEOPLECBT" game can be confirmed in the Steam library 4) Click the install button in "SUPERPEOPLECBT" and download

Many users need to download files when using Quark Network Disk, but we want to save them locally, so how to set this up? Let this site introduce to users in detail how to save files downloaded from Quark Network Disk back to the local computer. How to save files downloaded from Quark network disk back to your local computer 1. Open Quark, log in to your account, and click the list icon. 2. After clicking the icon, select the network disk. 3. After entering Quark Network Disk, click My Files. 4. After entering My Files, select the file you want to download and click the three-dot icon. 5. Check the file you want to download and click Download.
