PHPexcel：多sheet上传和下载，phpexcelsheet上传
- WBOY原創
- 2016-06-13 08:45:341624瀏覽
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>
陳述：
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn