php导出excel
<?php
header ( "Content-type:application/vnd.ms-excel" );
header ( "Content-Disposition:filename=registration list.xls");
?>
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />
<title>无标题文档</title>
<style>
td{
text-align:center;
font-size:12px;
font-family:Arial, Helvetica, sans-serif;
border:#1C7A80 1px solid;
color:#152122;
width:100px;
}
table,tr{
border-style:none;
}
.title{
background:#7DDCF0;
color:#FFFFFF;
font-weight:bold;
}
</style>
</head>
<body>
<table width='800' border='1'>
<tr>
<td class='title'>报名ID</td>
<td class='title'>姓名</td>
<td class='title'>UID</td>
<td class='title'>学号</td>
<td class='title'>昵称</td>
<?php if($type>=1){?>
<td class='title'>职位</td>
<?php }?>
<td class='title'>单位</td>
<td class='title'>手机</td>
<td class='title'>QQ</td>
<td class='title'>报名时间</td>
<td class='title'>付款方式</td>
<?php if($type>=4){?>
<td class='title'>报名级别</td>
<?php }?>
<?php if($type>=5){?>
<td class='title'>最高学历</td>
<td class='title'>意向类型</td>
<td class='title'>意向专业</td>
<?php }?>
</tr>
<?php foreach($list as $r): ?>
<tr>
<td><?php echo $r['bmid']?></td>
<td><?php echo $r['name']?></td>
<td><?php echo $r['uid']?></td>
<td><?php echo $r['studentid']?></td>
<td><?php echo $r['nickname']?></td>
<?php if($type>=1){?>
<td><?php echo $r['position']?></td>
<?php }?>
<td><?php echo $r['company']?></td>
<td><?php echo $r['mobile']?></td>
<td><?php echo $r['qq']?></td>
<td><?php echo date('Y-m-d H:i', $r['dateline'])?></td>
<td><?php echo $r['pay']?></td>
<?php if($type>=4){?>
<td><?php echo $r['level']?></td>
<?php }?>
<?php if($type>=5){?>
<td><?php echo $r['zuigaoxueli']?></td>
<td><?php echo $r['yixiangleixing']?></td>
<td><?php echo $r['yixiangzhuanye']?></td>
<?php }?>
</tr>
<?php endforeach; ?>
</table>
</body>
</html>
数组分页函数
/**
* 数组分页函数
* $array 查询出来的所有数组
* $page 当前第几页
* $page_size 每页多少条数据
* order 0 - 不变 1- 反序
*/
function page_array($array, $page = 1, $page_size = 20, $order = 0)
{
// 判断当前页面是否为空 如果为空就表示为第一页面
$page = (empty($page)) ? '1' : $page;
// 计算每次分页的开始位置
$start = ($page - 1) * $page_size;
if ($order == 1) {
$array = array_reverse($array);
}
$pagedata = array_slice($array, $start, $page_size);
return $pagedata;
}
mysql 去重后 排序
classid去重后使用end_time排序
SELECT
a.*
FROM
group_class AS a
WHERE
group_id IN ( 111, 222 )
AND end_time = ( SELECT max( end_time ) FROM group_class WHERE group_id IN ( 111, 222 ) AND a.classid = classid)
python错误日志行号捕获
import sys, os
try:
raise NotImplementedError("No error")
except Exception as e:
exc_type, exc_obj, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
print(exc_type, fname, exc_tb.tb_lineno)
php导入cvs
// 导入
public function upload_export()
{
if ($_POST['dosubmit']) {
try {
$hattach = new hattach();
$tmpPath = $hattach->uploadfile($_FILES['excel'], array('csv'));
$myfile = file($tmpPath);
$i = 0;
$row_data = array();
for ($i = 0; $i < count($myfile); $i++) {
// 过滤第一条
if ($i == 0) {
continue;
}
$encoding = mb_detect_encoding($myfile[$i], array('GB2312', 'GBK', 'UTF-16', 'UCS-2', 'UTF-8', 'BIG5', 'ASCII'));
$r = explode(',', $myfile[$i]);
$row_data[] = array(
'mobile' => iconv($encoding, 'UTF-8', $r[0]),
'dateline' => $r[1] ? strtotime(iconv($encoding, 'UTF-8', $r[1])) : time(),
'contact' => iconv($encoding, 'UTF-8', $r[2]),
'company' => iconv($encoding, 'UTF-8', $r[3]),
'organize' => iconv($encoding, 'UTF-8', $r[4]),
'job' => iconv($encoding, 'UTF-8', $r[5]),
'area' => iconv($encoding, 'UTF-8', $r[6]),
'work_year' => iconv($encoding, 'UTF-8', $r[7]),
'skill' => iconv($encoding, 'UTF-8', $r[8]),
'cy' => iconv($encoding, 'UTF-8', $r[9]),
'cy_direction' => iconv($encoding, 'UTF-8', $r[10]),
'video' => iconv($encoding, 'UTF-8', $r[11]),
'video_name' => iconv($encoding, 'UTF-8', $r[12]),
'remark' => iconv($encoding, 'UTF-8', $r[13]),
'utm_source' => iconv($encoding, 'UTF-8', $r[14]),
'utm_medium' => iconv($encoding, 'UTF-8', $r[15]),
'utm_campaign' => iconv($encoding, 'UTF-8', $r[16]),
'utm_content' => iconv($encoding, 'UTF-8', $r[17]),
'utm_term' => iconv($encoding, 'UTF-8', $r[18]),
'source' => iconv($encoding, 'UTF-8', $r[19]),
'mobile_status' => iconv($encoding, 'UTF-8', $r[20]),
'progress' => $r[21] ? iconv($encoding, 'UTF-8', $r[21]) : 0,
'username_remark' => iconv($encoding, 'UTF-8', $r[22]),
'ownership' => iconv($encoding, 'UTF-8', $r[23]),
);
}
foreach ($row_data as $key => $value) {
// 缺少手机或者联系人自动过滤
if (!$value['mobile'] || intval($value['mobile']) < 100 || !$value['contact']) {
continue;
}
srv_spl_partner_collection::I()->save_collection($value);
$ownership=str_replace(array("\n", "\r"), '', $value['ownership']);
if ($ownership){
srv_spl_partner_collection::I()->update_collection(array('mobile' => $value['mobile']), array('status' => 2));
}
}
} catch (Exception $e) {
hmsg($e->getMessage());
}
hmsg('操作成功', '', 1000, 'edit');
} else {
include admtpl('admin_partner', 'upload_export');
}
}
php导出csv
//导出订单
private function _export_collection($data_v)
{
$data = array();
$up_status = $this->up_status();
$progress_status = $this->progress_status();
$mobile_status = $this->mobile_status();
$cy_status=$this->cy_status();
$data[] = array('手机号码', 'uid', '提交时间', '联系人', '公司名称', '机构类型', '当前职位', '所在地区', '工作年限', '擅长领域', '是否创业', '创业方向', '是否有课', '课程名称', '备注', '操作记录', '状态', 'utm_source', 'utm_medium', 'utm_campaign', 'utm_content', 'utm_term', '来源', '电话有效性', '进展', '跟进备注', '分配对象');
foreach ($data_v as $key => $mobile) {
$i = 1;
$data[] = array(
"\t" . $v['mobile'],
$v['uid'],
date('Y-m-d H:i:s', $v['dateline']),
$v['contact'],
$v['company'],
$v['organize'],
$v['job'],
$v['area'],
$v['work_year'],
$v['skill'],
$v['cy'] >= 1 ? $cy_status[$v['cy']] : '',
$v['cy_direction'],
$v['video'] >= 1 ? $cy_status[$v['video']] : '',
$v['video_name'],
$v['remark'],
$mobile['collection_log'][0]['dateline'] ? sprintf('%s %s %s',date('Y-m-d H:i:s', $mobile['collection_log'][0]['dateline']) , $mobile['collection_log'][0]['username'], str_replace(array(','),'|',$mobile['collection_log'][0]['remark'])) : '',
$up_status[$v['status']],
$v['utm_source'],
$v['utm_medium'],
$v['utm_campaign'],
$v['utm_content'],
$v['utm_term'],
$v['source'],
$v['mobile_status'] > 1 ? $mobile_status[$v['mobile_status']] : '',
$v['progress'] == 0 ? '' : $progress_status[$v['progress']]['name'],
$v['username_remark'],
$v['ownership'],
);
$i = $i + 1;
}
fx_phpexcel_utl::export_csv($data);
}
// CVS导出速度快
public static function export_csv($data)
{
$string = "";
foreach ($data as $key => $value) {
foreach ($value as $k => $val) {
$value[$k]= str_replace(array(","), '|', $value[$k]);
$value[$k] = iconv('utf-8', 'gbk//IGNORE', str_replace(array("\n", "\r"), '', $value[$k]));
}
$string .= implode(",", str_replace(PHP_EOL, '', $value)) . "\n"; //用英文逗号分开
}
$filename = date('Ymd') . '.csv'; //设置文件名
header("Content-type:text/csv");
header("Content-Disposition:attachment;filename=" . $filename);
header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
header('Expires:0');
header('Pragma:public');
echo $string;
}
python dict 字典
# -*- coding: utf-8 -*-
import json
class Dict(dict):
def __missing__(self, key):
rv = self[key] = type(self)()
return rv
$row_data=Dict()
print(json.dumps(row_data,indent=4,ensure_ascii=False))
python excel转json
#!/usr/bin/python
#-*- coding: utf-8 -*-
import json, sys
import xlrd
from datetime import date, datetime
from collections import OrderedDict
import json
import codecs
reload(sys)
sys.setdefaultencoding('utf-8')
wb = xlrd.open_workbook(r'E:\dir\school\2.xlsx')
sheet_list = wb.sheet_names()
filename=1for sheet in sheet_list:
print sheet
convert_list = []
sh = wb.sheet_by_name(sheet)
title = sh.row_values(0)
for rownum in range(1, sh.nrows):
rowvalue = sh.row_values(rownum)
single = OrderedDict()
for colnum in range(0, len(rowvalue)):
single[title[colnum]] = rowvalue[colnum]
convert_list.append(single)
j = json.dumps(convert_list,ensure_ascii=False,indent=2)
f=file('E:\dir\school\json\{}.json'.format(filename), 'w')
f.write(j)
f.close()
filename=filename+1
python匹配段落
Python 正则表达式匹配任意字符（包括换行符）的写法
想使用正则表达式来获取一段文本中的任意字符,写出如下匹配规则：
(.*)
结果运行之后才发现，无法获得换行之后的文本。于是查了一下手册，才发现正则表达式中，“.”(点符号)匹配的是除了换行符“\n”以外的所有字符。
以下为正确的正则表达式匹配规则：
([\s\S]*)
同时，也可以用 “([\d\D]*)”、“([\w\W]*)” 来表示
SQL优化
Mysql在多个LEFT JOIN的情况下使用ORDER BY排序，就算是其中一个表的主键也仍然使用file sort排序，数据量多的话就相当的慢。
优化前语句
SELECT * FROM a LEFT JOIN b ON a.id=b.a_id ORDER a.id DESC
优化后语句
SELECT * FROM a LEFT JOIN b ON a.id=b.a_id JOIN (SELECT id FROM a ORDER BY id DESC) a_order ON a.id = a_order.id
实际工作中100W+的几个表使用LEFT JOIN要20分钟才能得到结果，语句优化后3秒。
python 3des ECB加密解密
# -*- coding: utf-8 -*-'''
import base64
import pyDes
def encrypt_3des(clear_text, key):
clear_text_byte = clear_text.encode('utf-8')
key_byte = key.encode('utf-8')
key_byte = key_byte.ljust(24, "\0".encode('utf-8'))
if len(key_byte) > 24:
key_byte = key_byte[:24]
k = pyDes.triple_des(key_byte, pyDes.ECB, IV = None, pad = None, padmode = pyDes.PAD_PKCS5)
d = k.encrypt(clear_text_byte)
return base64.b64encode(d).decode('utf-8')
def decrypt_3des(data, key):
data_byte = base64.b64decode(data.encode('utf-8'))
key_byte = key.encode('utf-8')
key_byte = key_byte.ljust(24, "\0".encode('utf-8'))
if len(key_byte) > 24:
key_byte = key_byte[:24]
k = pyDes.triple_des(key_byte, pyDes.ECB, IV = None, pad = None, padmode = pyDes.PAD_PKCS5)
d = k.decrypt(data_byte)
return d.decode('utf-8')
redis并控制
/**
* 限制某个请求多少秒内，可以请求多少次
* @param $key value
* @param $expire 失效时间
* @return $num 次数
*/
function request_limit($key, $expire, $num)
{
$redis_get = fx_redis_service::I()->get($key);
if (!$redis_get) {
fx_redis_service::I()->incr($key);
fx_redis_service::I()->expire($key, $expire);
} else {
$total = fx_redis_service::I()->incr($key);
// 高并发的时候过期时间会变成-1,所以需要重置过期时间,否则这个IP永远不能请求
$ttl = fx_redis_service::I()->ttl($key);
if ($ttl < 1){
fx_redis_service::I()->expire($key, $expire);
}
if (intval($total) > $num) {
fx_log::logs2file(__FILE__, __FUNCTION__, $_GET, $_POST, $key . '请求太频繁..', '', fx_utl::hip(false));
$json = json_encode(array('result' => 1, 'msg' => '请求太频繁'), JSON_UNESCAPED_UNICODE);
echo $json;
exit();
}
}
}
Mysql COUNT 条件查询
SELECT FROM_UNIXTIME(dateline,'%Y-%m-%d') AS days,COUNT(DISTINCT(uid)) AS uids,SUM(is_share) AS `share`,COUNT(`id`) AS cj,
COUNT(IF(give = 1,TRUE,NULL)) AS xj,
COUNT(IF(give = 2,TRUE,NULL)) AS yhj,
COUNT(IF(give = 4,TRUE,NULL)) AS kc
FROM hrspl.`spl_luck` GROUP BY days;
Python 大街网登陆
大街网登陆
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import os
import time
import json
import sys
import requests
import re
#请求对象
session = requests.session()
#请求头信息
HEADERS = {
'Referer': 'https://passport.lagou.com/login/login.html',
'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:51.0) Gecko/20100101 Firefox/51.0',
}
def login(username, passwd):
login_headers = HEADERS.copy()
login_headers.update({
'Referer':'https://www.dajie.com/',
'x-requested-with':'XMLHttpRequest',
'Host':'www.dajie.com'
})
postData = {
'captcha' : '',
'email' : username,
'password': passwd,
'rememberMe': '1'
}
response=session.post('https://www.dajie.com/account/newloginsubmitm?callback=NEW_VERSION_LOGIN_CALLBACK&_CSRFToken=&ajax=1', data=postData, headers=login_headers)
print(response.content)
login_headers = HEADERS.copy()
login_headers.update({
'Host':'job.dajie.com',
'Referer':'https://www.dajie.com/'
})
response=session.get('https://job.dajie.com/auth/checking', headers=login_headers)
print(response.text)
if __name__ == "__main__":
username=''
passwd=''
login(username, passwd)
Python登陆58同城
登陆58同城，提供两种版本
第1种是模拟输入用户名密码登陆
第2中的调用58自带的js获取登陆参数在登陆
# -*- coding: utf-8 -*-
import time, sys, re
import requests
from selenium.webdriver.common.action_chains import ActionChains
from selenium import webdriver
from PIL import Image
username=''
passwd=''
driver=webdriver.PhantomJS(executable_path='C:\\Python27\\phantomjs-2.1.1-windows\\bin\\phantomjs.exe')
driver.get('https://passport.58.com/login')
time.sleep(2)
pwdLogin=driver.find_element_by_id('pwdLogin')
pwdLogin.click()
# 输入用户名
usernameUser=driver.find_element_by_id('usernameUser')
usernameUser.send_keys(username)
time.sleep(1)
passwordUserText=driver.find_element_by_id('passwordUserText')
passwordUserText.click()
# 输入密码
passwordUser=driver.find_element_by_id('passwordUser')
passwordUser.send_keys(passwd)
# 点击登陆
btnSubmitUser=driver.find_element_by_id('btnSubmitUser')
btnSubmitUser.click()
time.sleep(3)
''' 获取驱动Cookie '''
dict1_cookie={}
cookie_tmp=[]
for cookie in driver.get_cookies():
data="{}={}".format(cookie['name'], cookie['value'])
dict1_cookie[cookie['name']]=cookie['value']
cookie_tmp.append(data)
_cookie=';'.join(cookie_tmp)
HEADERS={
"User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:51.0) Gecko/20100101 Firefox/51.0",
"Accept":"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
"Accept-Language":"zh-CN,zh;q=0.8,en-US;q=0.5,en;q=0.3",
"Accept-Encoding":"gzip, deflate, br",
"Connection":"keep-alive",
"Host":"my.58.com",
"Cookie":_cookie
}
''' 通过COOKIE抓取数据'''
session = requests.session()
response=session.get('https://my.58.com/index', headers=HEADERS)
print(response.text)
第2中方法,调用自带的js进行模拟登陆
# -*- coding: utf-8 -*-
import time, sys, re, json
import requests
from selenium import webdriver
from selenium.webdriver import DesiredCapabilities
username=''
passwd=''
''' 设置浏览器的User-Agent '''
desired_capabilities= DesiredCapabilities.PHANTOMJS.copy()
desired_capabilities['phantomjs.page.customHeaders.User-Agent'] = (
'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/59.0.3071.115 Safari/537.36'
)
driver=webdriver.PhantomJS(executable_path='C:\\Python27\\phantomjs-2.1.1-windows\\bin\\phantomjs.exe', desired_capabilities=desired_capabilities)
driver.get('https://passport.58.com/login')
time.sleep(1)
''' 执行58js获取加密串 '''
rsaModulus=driver.find_element_by_id('rsaModulus').get_attribute('value')
rsaExponent=driver.find_element_by_id('rsaExponent').get_attribute('value')
''' 获取加密串密码 '''
timespan=str(int(round(time.time() * 1000)))
p1_user="return encryptString('{}{}', '{}', '{}')"
encrypt_passwd=driver.execute_script(p1_user.format(timespan, passwd, rsaExponent, rsaModulus))
Fingerprint2=driver.execute_script('return new Fingerprint2().get()')
getTokenId=driver.execute_script('return getTokenId()')
fingerprint=driver.find_element_by_id('fingerprint').get_attribute('value')
session = requests.session()
headers={
"User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:51.0) Gecko/20100101 Firefox/51.0",
"Accept":"text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
"Origin":"https://passport.58.com",
'Content-Type':'application/x-www-form-urlencoded',
"Upgrade-Insecure-Requests":"1",
'Referer':'https://passport.58.com/login?path=http://my.58.com/?pts=' + str(int(round(time.time() * 1000)))
}
postData={
"source":"pc-login",
"path":'http://my.58.com/?pts=' + str(int(round(time.time() * 1000))),
"password":encrypt_passwd,
"timesign":'',
"isremember":"false",
"callback":"successFun",
"yzmstate":"",
"fingerprint":"",
"finger2":fingerprint,
"tokenId":getTokenId,
"username":username,
"validcode":"",
"vcodekey":"",
"btnSubmit":"登录中..."
}
rep=session.post('https://passport.58.com/login/dologin', data=postData, headers=headers)
match=re.search('\((\{.*?\})\)', rep.text)
if match:
res_json=json.loads(match.group(1))
print(res_json)
if res_json['code'] == 0:
print('登陆成功!')
else:
print(res_json['msg'])
Selenium使用浏览器cookie登录
需要注意添加cookie需要设置path secure还有需要注意的一点是，要先打开一个同域下面的网站，在添加Cookie 在打开对应页面.