def excel_to_json(self):
'''
excel转换为json文件
'''
self.files = util.get_files(self._src, '.xlsx')
self.files.extend(util.get_files(self._src, '.xls'))
for item in self.files:
basename = os.path.basename(item)
header = []
res_arr = []
tables = self.excel_table_byindex(item, header)
if None == tables: continue
real_header = []
for key in header:
if '' != key and '#' != key[0]:
real_header.append(key)
for row in tables:
row_arr = []
for key in real_header:
if row.has_key(key):
if type(row[key]
) is types.FloatType and row[key] == int(
row[key]):
row[key] = int(row[key])
if type(row[key]) is types.IntType or type(
row[key]) is types.FloatType:
row[key] = unicode(row[key])
else:
row[key] = '"%s"' % unicode(row[key])
row_arr.append('"%s": %s' % (key, row[key]))
res_arr.append('{%s}' % util.join(', ', row_arr))
self.write_data(item, '[\n%s\n]' % util.join(',\n', res_arr),
'.json', True)
return 'ok'
def excel_to_xml(self,
_pre=None,
_post=None,
_among=None,
_filter=None,
filelist=None):
'''
excel转换为xml文件
'''
self.files = util.get_files(self._src, '.xlsx')
self.files.extend(util.get_files(self._src, '.xls'))
for item in self.files:
filename = os.path.basename(item)
basename = util.get_basename(filename)
if filelist != None:
filelist.append(util.get_basename(filename))
if '' != _pre:
if basename.find(_pre) != 0:
continue
if '' != _post:
if basename.rfind(_post) != len(basename) - len(_post):
continue
if '' != _among:
if basename.find(_among) == -1:
continue
if '' != _filter:
if item.find(_filter) != -1:
continue
header = []
res_arr = []
tables = self.excel_table_byindex(item, header)
if None == tables: continue
real_header = []
type_map = {}
for key in header:
if '' != key and '#' != key[0]:
real_header.append(key)
type_map[key] = tables[0][key]
res_arr.append('<?xml version="1.0" encoding="utf8"?>')
res_arr.append('<config>')
for i in range(1, len(tables)):
row_arr = []
row = tables[i]
for key in real_header:
if row.has_key(key):
if type(row[key]
) is types.FloatType and row[key] == int(
row[key]):
row[key] = int(row[key])
if type(row[key]) is types.IntType or type(
row[key]) is types.FloatType:
row[key] = unicode(row[key])
row_arr.append(key + '="' + row[key] + '"')
res_arr.append('\t<' + basename + ' ' +
util.join(' ', row_arr) + ' />')
res_arr.append('</config>')
self.write_data(item, util.join('\n', res_arr), '.xml')
return 'ok'
def excel_to_lua(self,
_pre=None,
_post=None,
_among=None,
_filter=None,
filelist=None):
'''
excel转换为lua文件
'''
self.files = util.get_files(self._src, '.xlsx')
self.files.extend(util.get_files(self._src, '.xls'))
for item in self.files:
filename = os.path.basename(item)
basename = util.get_basename(filename)
if filelist != None:
filelist.append(util.get_basename(filename))
if '' != _pre:
if basename.find(_pre) != 0:
continue
if '' != _post:
if basename.rfind(_post) != len(basename) - len(_post):
continue
if '' != _among:
if basename.find(_among) == -1:
continue
if None != _filter:
if util.indexOf(_filter, basename) != -1:
continue
header = []
res_arr = []
tables = self.excel_table_byindex(item, header)
if None == tables: continue
real_header = []
type_map = {}
for key in header:
if '' != key and '#' != key[0]:
real_header.append(key)
type_map[key] = tables[0][key]
res_arr.append('cc.exports.' + basename + 'ConfigData = {')
for i in range(1, len(tables)):
row_arr = []
row_arr.append('\t{')
row = tables[i]
for key in real_header:
if row.has_key(key):
row[key] = unicode(
str(self.str_to_type(row[key], type_map[key])))
row_arr.append('\t\t' + key + ' = ' + row[key] + ',')
row_arr.append('\t},')
res_arr.append(util.join('\n', row_arr))
res_arr.append('}')
self.write_data(item, util.join('\n', res_arr), 'ConfigData.lua',
True)
return 'ok'
def excel_to_txt(self,
_pre=None,
_post=None,
_among=None,
_filter=None,
filelist=None):
'''
excel转换为txt文件
'''
self.files = util.get_files(self._src, '.xlsx')
self.files.extend(util.get_files(self._src, '.xls'))
for item in self.files:
filename = os.path.basename(item)
basename = util.get_basename(filename)
if filelist != None:
filelist.append(util.get_basename(filename))
if '' != _pre:
if basename.find(_pre) != 0:
continue
if '' != _post:
if basename.rfind(_post) != len(basename) - len(_post):
continue
if '' != _among:
if basename.find(_among) == -1:
continue
if '' != _filter:
if item.find(_filter) != -1:
continue
header = []
res_arr = []
tables = self.excel_table_byindex(item, header)
if None == tables: continue
real_header = []
for key in header:
if '' != key and '#' != key[0]:
real_header.append(key)
res_arr.append(util.join('\t', real_header))
for row in tables:
row_arr = []
for key in real_header:
if row.has_key(key):
if type(row[key]
) is types.FloatType and row[key] == int(
row[key]):
row[key] = int(row[key])
if type(row[key]) is types.IntType or type(
row[key]) is types.FloatType:
row[key] = unicode(row[key])
row_arr.append(row[key])
res_arr.append(util.join('\t', row_arr))
self.write_data(item, util.join('\n', res_arr), '.txt')
return 'ok'
def copy_files(self, _pre, _post, _among, _filter, _keepdir, _ext):
'''
拷贝将满足条件的文件
'''
self.files = util.get_files(self._src, _ext)
for item in self.files:
filename = os.path.basename(item)
basename = util.get_basename(filename)
if '' != _pre:
if basename.find(_pre) != 0:
continue
if '' != _post:
if basename.rfind(_post) != len(basename) - len(_post):
continue
if '' != _among:
if basename.find(_among) == -1:
continue
if '' != _filter:
if item.find(_filter) != -1:
continue
if int(_keepdir) == 0:
outfile = self._dest + '/' + filename
else:
outfile = item.replace(self._src, self._dest)
outfile = os.path.normpath(util.format_path(outfile))
util.mkdir(os.path.dirname(outfile))
util.copy_file(item, outfile)
util.log('File successfully copied : ' + outfile,
console=self._console)
return 'ok'
def txt_to_json(self, _type=True):
'''
txt转换为json文件
'''
self.files = util.get_files(self._src, '.txt')
for item in self.files:
f = open(item, 'r')
lines = f.readlines()
head_arr = []
type_arr = []
res_arr = []
for i, line in enumerate(lines):
line = line.strip('\n')
if 0 == i: #表头
head_arr = line.replace('\xef\xbb\xbf', '').split('\t')
continue
if 1 == i and _type:
type_arr = line.split('\t')
continue
tmp_arr = []
sub_arr = line.split('\t')
for j, _str in enumerate(sub_arr):
if j >= len(head_arr): continue
value = _str.decode('utf8')
if j < len(type_arr):
value = str(self.str_to_type(value, type_arr[j]))
tmp_arr.append('"%s": %s' % (head_arr[j], value))
res_arr.append('{%s}' % util.join(', ', tmp_arr))
f.close()
self.write_data(item, '[\n%s\n]' % util.join(',\n', res_arr),
'.json')
return 'ok'
def excel_to_csv(self):
'''
excel转换为csv文件
'''
self.files = util.get_files(self._src, '.xlsx')
for item in self.files:
header = []
res_arr = []
tables = self.excel_table_byindex(item, header)
if None == tables: continue
res_arr.append(util.join(',', header))
for row in tables:
row_arr = []
for key in header:
if row.has_key(key):
if type(row[key]
) is types.FloatType and row[key] == int(
row[key]):
row[key] = int(row[key])
if type(row[key]) is types.IntType or type(
row[key]) is types.FloatType:
row[key] = unicode(row[key])
row_arr.append(row[key])
res_arr.append(util.join(',', row_arr))
self.write_data(item, util.join('\n', res_arr), '.csv')
return 'ok'
def merge_json(self, _match, _filter, _id, _outfile):
'''
合并所给的所有json文件为一个json文件
'''
self.files = util.get_files(self._src, '.json')
values = []
for item in self.files:
if item.find(_match) == -1 or item.find(_filter) != -1:
continue
try:
f = open(item, 'r')
data_string = f.read()
f.close()
decoded = json.loads(data_string)
except Exception, e:
util.log("Error, %s, read file: %s" % (e, item), console=None)
continue
for each in decoded:
if not each.has_key(_id):
continue
values.append(each)
def excel_dy(self, _startDate, _endDate, _checkIn, _lunchHour, _afternoon,
_checkOut, _overtime, _dutyCheckIn, _dutyTime, _holidays,
_weekend):
'''
excel考勤表处理
'''
self.files = util.get_files(self._src, '.xlsx')
self.files.extend(util.get_files(self._src, '.xls'))
out_header = [
'人员编号', '姓名', '迟到次数', '迟到日期', '早退次数', '早退日期', '加班次数', '加班日期',
'周末加班次数', '周末加班日期', '值班次数', '值班日期', '上班未打卡次数', '上班未打卡日期',
'下班未打卡次数', '下班未打卡日期', '旷工次数', '旷工日期'
]
holidays = _holidays.split(',')
weekend = _weekend.split(',')
for item in self.files:
filename = os.path.basename(item)
basename = util.get_basename(filename)
header = []
tables = self.excel_table_byindex(item, header)
if None == tables: continue
res_arr = []
res_arr.append(util.join(',', out_header))
staff_dict = {}
for row in tables:
uid = str(row[unicode('人员编号')])
if not staff_dict.has_key(uid):
staff_dict[uid] = []
staff_dict[uid].append(row)
attendance_info = []
d1 = datetime.datetime.strptime(_startDate, '%Y-%m-%d')
d2 = datetime.datetime.strptime(_endDate, '%Y-%m-%d')
delta = d2 - d1
keys = staff_dict.keys()
keys.sort()
for x in xrange(0, len(keys)):
value = staff_dict[keys[x]]
#if value[0][unicode('姓名')] != unicode('张建斌1'): continue
uncheckInTimes = []
uncheckOutTimes = []
lateTimes = []
earlyTimes = []
overTimes = []
dutyTimes = []
weekendTimes = []
absentTimes = []
for i in range(0, delta.days):
date = d1 + datetime.timedelta(days=i)
date = date.strftime('%Y-%m-%d')
checkIn = self.early_datetime(date, value)
checkOut = self.last_datetime(date, value)
if util.indexOf(holidays, date) != -1:
if checkIn != None:
dutyTimes.append(date)
continue
if util.indexOf(weekend, date) != -1:
if checkIn != None and checkOut != None:
t1 = datetime.datetime.strptime(checkIn, '%H:%M')
t2 = t1 + datetime.timedelta(hours=int(_dutyTime))
if t2.strftime('%H:%M') <= checkOut:
weekendTimes.append(date)
continue
if checkIn != None and checkIn > _checkIn and checkIn < _lunchHour:
lateTimes.append(date)
if checkOut != None and checkOut < _checkOut and checkOut >= _afternoon:
earlyTimes.append(date)
elif checkOut != None and checkOut >= _overtime:
overTimes.append(date)
if checkIn == None and checkOut == None:
absentTimes.append(date)
elif checkIn == None or checkIn >= _lunchHour:
uncheckInTimes.append(date)
elif checkOut == None or checkOut < _afternoon:
uncheckOutTimes.append(date)
row_arr = []
row_arr.append(keys[x])
row_arr.append(value[0][unicode('姓名')])
row_arr.append(len(lateTimes))
row_arr.append('|'.join(lateTimes))
row_arr.append(len(earlyTimes))
row_arr.append('|'.join(earlyTimes))
row_arr.append(len(overTimes))
row_arr.append('|'.join(overTimes))
row_arr.append(len(weekendTimes))
row_arr.append('|'.join(weekendTimes))
row_arr.append(len(dutyTimes))
row_arr.append('|'.join(dutyTimes))
row_arr.append(len(uncheckInTimes))
row_arr.append('|'.join(uncheckInTimes))
row_arr.append(len(uncheckOutTimes))
row_arr.append('|'.join(uncheckOutTimes))
row_arr.append(len(absentTimes))
row_arr.append('|'.join(absentTimes))
res_arr.append(util.join(',', row_arr))
self.write_data(item, util.join('\n', res_arr), '.csv')
return 'ok'