PHPexcel：多sheet上传和下载，phpexcelsheet上传
- WBOYOriginal
- 2016-06-13 08:45:341581browse
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>
Statement：
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
Previous article：二层（链路层）数据包发送过程分析Next article：封装自己的DB类（PHP），封装db类php