洪雅新闻,gt电子搜博网开始,龙之飞翔
原生php实现的网页导出和导入excel文件实例，包括上传也是用的原生。还可在exportexcel方法里设置字体等表格样式。
导出和导入表单代码：
1 <p style="margin:10px 0"><a href="export.php" class="btn">导出</a></p> 2 <form action="import.php" method="post" enctype="multipart/form-data"> 3 <div class="control-group"> 4 <label>excel表格：</label> 5 <input type="file" name="file"/> 6 </div> 7 <div class="control-group"> 8 <input type="submit" value="导入" /> 9 </div> 10 </form>
excel导出：
我要评论 1 $query = mysql_query("select * from user limit 50");
2 $i =0;
3 $list = array();
4 while($row=mysql_fetch_array($query)){
5 $list[$i]['id'] = $row['id'];
6 $list[$i]['username'] = $row['username'];
7 $list[$i]['password'] = $row['password'];
8 $i++;
9 }
10
11 $title = array('id', '邮箱', '密码'); //设置要导出excel的表头
12 exportexcel($list, '素材火用户表', $title);
exportexcel方法代码：
1 function exportexcel($data, $savefile = null, $title = null, $sheetname = 'sheet1') {
2 require_once 'phpexcel.class.php';
3 //若没有指定文件名则为当前时间戳
4 if (is_null($savefile)) {
5 $savefile = time();
6 }
7 //若指字了excel表头，则把表单追加到正文内容前面去
8 if (is_array($title)) {
9 array_unshift($data, $title);
10 }
11 $objphpexcel = new phpexcel();
12 //excel内容
13 $head_num = count($data);
14
15 foreach ($data as $k => $v) {
16 $obj = $objphpexcel->setactivesheetindex(0);
17 $row = $k + 1; //行
18 $nn = 0;
19
20 foreach ($v as $vv) {
21 $col = chr(65 + $nn); //列
22 $obj->setcellvalue($col . $row, $vv); //列,行,值
23 $nn++;
24 }
25 }
26 //设置列头标题
27 for ($i = 0; $i < $head_num - 1; $i++) {
28 $alpha = chr(65 + $i);
29 $objphpexcel->getactivesheet()->getcolumndimension($alpha)->setautosize(true); //单元宽度自适应
30 $objphpexcel->getactivesheet()->getstyle($alpha . '1')->getfont()->setname("candara"); //设置字体
31 $objphpexcel->getactivesheet()->getstyle($alpha . '1')->getfont()->setsize(12); //设置大小
32 $objphpexcel->getactivesheet()->getstyle($alpha . '1')->getfont()->getcolor()->setargb(phpexcel_style_color::color_black); //设置颜色
33 $objphpexcel->getactivesheet()->getstyle($alpha . '1')->getalignment()->sethorizontal(phpexcel_style_alignment::horizontal_center); //水平居中
34 $objphpexcel->getactivesheet()->getstyle($alpha . '1')->getalignment()->setvertical(phpexcel_style_alignment::vertical_center); //垂直居中
35 $objphpexcel->getactivesheet()->getstyle($alpha . '1')->getfont()->setbold(true); //加粗
36 }
37
38 $objphpexcel->getactivesheet()->settitle($sheetname); //题目
39 $objphpexcel->setactivesheetindex(0); //设置当前的sheet
40 header('content-type: application/vnd.ms-excel');
41 header('content-disposition: attachment;filename="' . $savefile . '.xls"');//文件名称
42 header('cache-control: max-age=0');
43 $objwriter = phpexcel_iofactory::createwriter($objphpexcel, 'excel5'); //excel5
44 $objwriter->save('php://output');
45 }
excel导入：
1 $tmp = $_files['file']['tmp_name'];
2 if (empty($tmp)) {
3 echo '请选择要导入的excel文件！';
4 exit;
5 }
6
7 $save_path = "uploads/";
8 $filename = $save_path . date('ymdhis') . ".xls"; //上传后的文件保存路径和名称
9 if (copy($tmp, $filename)) {
10 require_once 'phpexcel.class.php';
11 require_once 'phpexcel/reader/excel5.php';
12
13
14 $phpreader = new phpexcel_reader_excel5(); //phpexcel_reader_excel2007 phpexcel_reader_excel5
15 //载入文件
16 $phpexcel = $phpreader->load($filename);
17
18 //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
19 $currentsheet = $phpexcel->getsheet(0);
20 //获取总列数
21 $allcolumn = $currentsheet->gethighestcolumn();
22 //获取总行数
23 $allrow = $currentsheet->gethighestrow();
24 //循环获取表中的数据，$currentrow表示当前行，从哪行开始读取数据，索引值从0开始
25 for ($currentrow = 1; $currentrow <= $allrow; $currentrow++) {
26 //从哪列开始，a表示第一列
27 for ($currentcolumn = 'a'; $currentcolumn <= $allcolumn; $currentcolumn++) {
28 //数据坐标
29 $address = $currentcolumn . $currentrow;
30 //读取到的数据，保存到数组$arr中
31 $data[$currentrow][$currentcolumn] = $currentsheet->getcell($address)->getvalue();
32 }
33 }
34
35 $add_time = date('y-m-d h:i:s', time());
36 foreach ($data as $k => $v) {
37 if ($k > 1) {
38 $sql = "insert into user (username,password) values ('" . $v['b'] . "', '" . $v['c'] . "')";
39
40 mysql_query($sql);
41 }
42 }
43
44 $sql = "select * from user";
45 $result = mysql_query($sql);
46 $tip = '用户导入成功' . '，现在' . mysql_num_rows($result) . '条数据了！';
47 echo "<script>alert('" . $tip . "');history.go(-1);</script>";
48 exit;
49 }
本文转自： 转载请注明出处！
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
YII2框架中使用RBAC对模块，控制器，方法的权限控制及规则的使用示例
YII2框架中ActiveDataProvider与GridView的配合使用操作示例
PhpStorm的使用教程(本地运行PHP+远程开发+快捷键)
网友评论