您好,欢迎访问一九零五行业门户网

PHPexcel:多sheet上传和下载,phpexcelsheet上传_PHP教程

phpexcel:多sheet上传和下载,phpexcelsheet上传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;$sload($filename); 73 74 //选择工作表 75 $currentsheet = $phpexcel->getsheet($sheetnum); 76 77 //获取总列数 78 $allcolumn=$currentsheet->gethighestcolumn(); 79 80 //获取总行数 81 $allrow=$currentsheet->gethighestrow(); 82 83 //获取整张表,写入二维数组arr中 arr[行][列] 84 for($currentrow=1;$currentrowgetvalue(); 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 $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 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=utf8140 //自增类型 int primary key not null auto_increment141 $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 execute($sqlstring);152 153 // 为新建的数据表if_table_n添加数据 154 for($i = 2;$i $value) {157 $info['field_'.$k] = $arr[$i][$key];158 $k++;159 }160 m($tablename)->add($info);161 }162 163 // 插入 用户、表 数据之间的关系164 //if_user_table165 $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 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;$jgetactivesheet()47 ->setcellvalue($currentcolumn.'1', $colunmname);48 }49 50 // 写入内容 某个内容写进an,bn...51 for($currentrow=2,$j=0;$currentrowgetstyle($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 }
http://www.bkjia.com/phpjc/1104062.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/1104062.htmltecharticlephpexcel:多sheet上传和下载,phpexcelsheet上传 excel表格上传和下载,断断续续写了很久,赶紧记下来万一以后忘记就亏大了= = 数据库有三张表...
其它类似信息

推荐信息