PHPexcel：多sheet上传和下载

excel表格上传和下载，断断续续写了很久，赶紧记下来万一以后忘记就亏大了= =

数据库有三张表：

上传一张表格，每个sheet对应一个if_table_n,if_user_table记录上传信息，if_column_map记录每个if_table_n的列名与数据库列名对应，if_system_config记录表格数目。

 

  1 public function uploadFile() {
  2 
  3         if(!empty($_FILES)) {
 4  $upload = new \Think\Upload();// 实例化上传类  5  $upload->maxSize = 1000000000 ;// 设置附件上传大小  6  $upload->rootPath = './Uploads/'; // 设置附件上传根目录  7  // $upload->savePath = $filePath.'/'; // 设置附件上传（子）目录  8  $info = $upload->upload();  9  10  if($info === false) {// 上传错误提示错误信息  11  $this->error("上传错误：".$upload->getError(),"http://192.168.151.175/basicinfo",3);  12  }  13  14  //判断文件是否为excel格式  15  $fileName = $info["file"]['name'];  16  17  18  $fileType = substr($fileName,strrpos($fileName, '.') + 1);  19  20  if(strtolower($fileType) !== "xls" && strtolower($fileType) !== "xlsx") {  21  $this->error("文件格式错误！");  22  }  23  24  //文件名  25  $filen=substr($fileName,0,strrpos($fileName, '.'));  26  27  //判断引入何种格式的phpexcel 对应两种版本的excel  28  import("Org.Util.PHPExcel");  29  30  $PHPExcel = new \PHPExcel();  31  32  if($fileType === "xlsx") {  33  //如果excel文件后缀名为.xlsx，导入类  34  import("Org.Util.PHPExcel.Reader.Excel2007");  35  $PHPReader=new \PHPExcel_Reader_Excel2007();  36  }  37  else {  38  import("Org.Util.PHPExcel.Reader.Excel5");  39  $PHPReader=new \PHPExcel_Reader_Excel5();  40  }  41  42  43  $PHPExcel=$PHPReader->load(SITE_PATH."Uploads/".$info["file"]["savepath"].$info["file"]['savename']);  44  // 确定当前excel文件的数量  45  $res = D('IfSystemConfig')->getValueByKey('table_count');  46  47  //获取工作表个数  48  $sheetCount = $PHPExcel->getSheetCount();  49  50  //获取sheet的名字  51  $sheetname = $PHPExcel->getSheetNames();  52  53  // 当前表数量字段,加上工作表的数量  54  $result = D('IfSystemConfig')  55  ->setValueByKey("table_count",intval($res[0]['value'])+ $sheetCount);  56  57  if($result === false) {  58  $this->error("数据上传失败！");  59  }  60  61  $unique_name_id = $res[0]['value'];  62  63 for($s = 0;$s<$sheetCount;$s++)  64  {  65  /**  66  * 保存表的信息  67  * @access public  68  * @param string $tablename 表名  69  * @param string $filename 文件名（全路径）  70  * @return null  71  */  72  $PHPExcel=$PHPReader->load($info["file"]['savename']);  73  74  //选择工作表  75  $currentSheet = $PHPExcel->getSheet($s);  76  77  //获取总列数  78  $allColumn=$currentSheet->getHighestColumn();  79  80  //获取总行数  81  $allRow=$currentSheet->getHighestRow();  82  83  //获取整张表，写入二维数组arr中 arr[行][列]  84 for($currentRow=1;$currentRow<=$allRow;$currentRow++){  85  //从哪列开始，A表示第一列  86  for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){  87 //数据坐标  88  $address=$currentColumn.$currentRow;  89 $cvalue = $currentSheet->getCell($address)->getValue();  90  91  //读取到的数据，保存到数组$arr中  92  $arr[$currentRow][$currentColumn]=$cvalue;  93  }  94  }  95  96  // 表、列、代表含义的映射  97  // 列位自定义  98  //field_0 为自增形id  99  $j = 1; 100  $data['map_table'] = $tablename; 101  $data['col_name'] = "field_".'0'; 102  $data['col_meaning'] = ""; 103 104  //从field_1 .... field_n,对应excel列名 105  $res = D('IfColumnMap')->saveData($data); 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 }

 下载此表格：

还有一些未用到的设置：

设置单元格宽度

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);

设置单元格高度

$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(40);

合并单元格

$objPHPExcel->getActiveSheet()->mergeCells('A18:E22');

拆分单元格

$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28');

设置保护cell,保护工作表

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); 
$objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

设置格式

$objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );

设置加粗

$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);

设置垂直居中

$objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

设置字号

$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(10);

设置边框

$objPHPExcel->getActiveSheet()->getStyle('A1:I20')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN); 

设置边框颜色

$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');
$objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300'); $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');

插入图像

$objDrawing = new PHPExcel_Worksheet_Drawing();
/*设置图片路径 切记：只能是本地图片*/ $objDrawing->setPath('图像地址'); /*设置图片高度*/ $objDrawing->setHeight(180);//照片高度 $objDrawing->setWidth(150); //照片宽度 /*设置图片要插入的单元格*/ $objDrawing->setCoordinates('E2'); /*设置图片所在单元格的格式*/ $objDrawing->setOffsetX(5); $objDrawing->setRotation(5); $objDrawing->getShadow()->setVisible(true); $objDrawing->getShadow()->setDirection(50); $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

设置单元格背景色

$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet(0)->getStyle('A1')->getFill()->getStartColor()->setARGB('FFCAE8EA');

 1 public function downloadFile() {
 2         $file_id = I('file_id');
 3  $tablename = D('IfUserTable')->getNameByFileid($file_id);  4  5  import("Org.Util.PHPExcel");  6  //不清楚为什么\PHPExcel()前要加\，不加会报错，大哥也没解释清楚  7  $objPHPExcel = new \PHPExcel();  8  import("Org.Util.PHPExcel.Reader.Excel5");  9 10  //或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的 11 12  // 实例化Create new PHPExcel object 13 14  /* @func 设置文档基本属性 */ 15  $objPHPExcel->getProperties() 16  ->setCreator("ctos") //设置创建人 17  ->setLastModifiedBy("ctos") //最后修改人 18  ->setTitle("Office 2007 XLSX Test Document") //标题 19  ->setSubject("Office 2007 XLSX Test Document") //备注 20  ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //设置描述 21  ->setKeywords("office 2007 openxml php") //设置关键字 | 标记 22  ->setCategory("Test result file"); //设置类别 23 24 25 for ($i = 0; $i < count($tablename); $i++) { 26 27  $Model = new \Think\Model(); 28 29  $name = $tablename[$i]['unique_name']; 30 31 $sqlString = "select * from ".$name; 32 33  $column_info[$i] = D('IfColumnMap')->getDataByTable($name); 34 35  $res[$i] = $Model->query($sqlString); 36 37  //首先要创建一个sheet的空间，否则都会写在同一个sheet中 38  $objPHPExcel->createSheet(); 39  $objPHPExcel->setActiveSheetIndex($i); 40 41  // 表头写入 42 for($currentColumn='A',$j=1;$j<count($column_info[$i]);$currentColumn++,$j++){ 43 44  $colunmname = $column_info[$i][$j]['col_meaning']; 45 46 $objPHPExcel->getActiveSheet() 47  ->setCellValue($currentColumn.'1', $colunmname); 48  } 49 50  // 写入内容 某个内容写进An，Bn... 51 for($currentRow=2,$j=0;$currentRow<=count($res[$i])+1;$currentRow++,$j++){ 52 53  for($currentColumn='A',$k=1;$k<count($column_info[$i]);$currentColumn++,$k++){ 54 //设置单元格左对齐 55  $objPHPExcel->getActiveSheet() 56  ->getStyle($currentColumn. $currentRow) 57  ->getAlignment() 58  ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 59 60  $objPHPExcel->getActiveSheet()->setCellValue($currentColumn. $currentRow, $res[$i][$j]['field_'.($k)]); 61  } 62 63  } 64  //设置sheet的标题 65  $objPHPExcel->getActiveSheet()->setTitle($tablename[$i]['sheetname']); 66 67  ob_end_clean(); //清空缓存 68  } 69  header("Pragma: public"); 70 71  header("Expires: 0"); 72 73  header("Cache-Control:must-revalidate,post-check=0,pre-check=0"); 74 75  header("Content-Type:application/force-download"); 76 77  header("Content-Type:application/vnd.ms-execl"); 78 79  header("Content-Type:application/octet-stream"); 80 81  header("Content-Type:application/download"); 82  //设置文件的名称 83  header('Content-Disposition:attachment;filename='.$tablename['0']['file_name'].'.xls'); 84 85  header("Content-Transfer-Encoding:binary"); 86 87  //不清楚为什么\PHPExcel_IOFactory前要加\，不加会报错，大哥也没解释清楚 88  $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 89 90  $objWriter->save('php://output'); 91 }

转载于:https://www.cnblogs.com/gaojianli/p/5230464.html