def excel(data, name):
book = Workbook()
title_info = []
for i in data:
for k, v in i.items():
title_info.append(k)
title = list(set(title_info))
if len(data) < 65535:
excel = book.add_sheet(name, cell_overwrite_ok=True)
for x in range(len(title)):
excel.write(0, x, title[x].decode('UTF-8'))
for i in range(len(data)):
for j in range(len(data[i])):
excel.write(i + 1, j, data[i][title[j]])
else:
page = len(data) / 65535
for k in range(1, page + 2):
excel = book.add_sheet(name + '_' + str(k), cell_overwrite_ok=True)
for x in range(len(title)):
excel.write(0, x, title[x].decode('UTF-8'))
if k < page + 1:
for i in range(65535 * (k - 1), 65535 * k):
for j in title:
excel.write(i - 65535 * (k - 1) + 1, j, data[i][title[j]])
else:
for i in range(65535 * k, len(data)):
for j in title:
excel.write(i - 65535 * k + 1, j, data[i][title[j]])
date = datetime.datetime.now().strftime('%Y%m%d')
book.save('../static/' + name + '.' + date + '.xls')
def init():
global ws,ws2,ws3,firstTime,wb,rs
if os.path.exists(excelFileName):
rb = open_workbook(excelFileName, formatting_info=True)
rs = rb.sheet_by_index(0)
wb = copy(rb)
ws = wb.get_sheet(0)
ws2 = wb.get_sheet(1)
ws3 = wb.get_sheet(2)
firstTime = False
else:
wb = Workbook()
ws = wb.add_sheet('string')
ws2 = wb.add_sheet('string_array')
ws3 = wb.add_sheet('plurals')
firstTime = True
def test_unicode1(self):
book = Workbook()
ws1 = book.add_sheet(six.u('\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER GAMMA}'))
ws1.write(0, 0, six.u('\N{GREEK SMALL LETTER ALPHA}\N{GREEK SMALL LETTER BETA}\N{GREEK SMALL LETTER GAMMA}'))
ws1.write(1, 1, six.u('\N{GREEK SMALL LETTER DELTA}x = 1 + \N{GREEK SMALL LETTER DELTA}'))
ws1.write(2, 0, six.u('A\u2262\u0391.')) # RFC2152 example
ws1.write(3, 0, six.u('Hi Mom -\u263a-!')) # RFC2152 example
ws1.write(4, 0, six.u('\u65E5\u672C\u8A9E')) # RFC2152 example
ws1.write(5, 0, six.u('Item 3 is \u00a31.')) # RFC2152 example
ws1.write(8, 0, six.u('\N{INTEGRAL}')) # RFC2152 example
book.add_sheet(six.u('A\u2262\u0391.')) # RFC2152 example
book.add_sheet(six.u('Hi Mom -\u263a-!')) # RFC2152 example
one_more_ws = book.add_sheet(six.u('\u65E5\u672C\u8A9E')) # RFC2152 example
book.add_sheet(six.u('Item 3 is \u00a31.')) # RFC2152 example
one_more_ws.write(0, 0, six.u('\u2665\u2665'))
book.add_sheet(six.u('\N{GREEK SMALL LETTER ETA WITH TONOS}'))
stream = six.BytesIO()
book.save(stream)
md5 = hashlib.md5()
md5.update(stream.getvalue())
self.assertEqual('0049fc8cdd164385c45198d2a75a4155', md5.hexdigest())
def init_xls(sheetname='1'):
book = Workbook()
if sheetname:
sheet = book.add_sheet(sheetname)
else:
sheet = book.add_sheet('1')
return book, sheet
def write(self, dirname=None):
if dirname is None:
dirname = self.description
self.create_workbooks()
dir = self.safe_mkdir(dirname)
print 'Saving annotation in directory %s' % dir
for workbook_name, sheets in self.workbooks.items():
workbook_name = self.escape_name(workbook_name)
workbook = Workbook()
for sheet_name, sentences in sorted(sheets.items()):
sheet_name = self.escape_name(sheet_name)
sheet = workbook.add_sheet(sheet_name)
sheet.col(1).width = 0x3000
sheet.col(3).width = 0x3000
for index, sentence in enumerate(sentences):
self.write_splitted(sentence, sheet, index)
meta_sheet = workbook.add_sheet('meta')
meta_sheet.write(0, 0, self.description)
meta_sheet.write(1, 0, str(datetime.now()))
outfile = os.path.join(dir, '%s.xls' % workbook_name)
workbook.save(outfile)
sentence_file_name = os.path.join(dir, 'sentences.json')
write_sentence_file(self.sentences, sentence_file_name)
def doxl():
'''Read raw account number and name strings, separate the data and
write to an excel spreadsheet. Properly capitalize the account
names and mark cells with no account number as 99999 with red fill
'''
try:
fp = open("hospdata.txt")
except:
print 'Failed to open hospdata.txt'
sys.exit(1)
lines = fp.readlines()
nameandnum = re.compile(r'(\d+)\s*(.*)\s*')
wb = Workbook()
wsraw = wb.add_sheet('Raw Data')
ws = wb.add_sheet('Account List')
ws.write(0,0,'Account Number')
ws.write(0,1,'Account Name')
ws.col(0).width = len('Account Number') * 256
ws.col(1).width = max([len(l) for l in lines]) * 256
r = 1
for line in lines:
wsraw.write(r,0,line.strip())
m = nameandnum.match(line)
if m:
ws.write(r,0,int(m.group(1)))
ws.write(r,1,' '.join([w.capitalize() for w in m.group(2).split()]))
else:
ws.write(r,0,99999,easyxf('pattern: pattern solid, fore_colour red;'))
ws.write(r,1,' '.join([w.capitalize() for w in line.split()]))
r += 1
wb.save('accounts.xls')
print 'Wrote accounts.xls'
def render(self):
book = Workbook(encoding='utf-8')
uids = {'next': 0}
root_organizations = self.get_root_organizations()
for organization in root_organizations:
sheet = book.add_sheet(organization.title)
for index, cell in enumerate(TITLES_ORGANIZATION):
sheet.row(0).write(index, cell)
self.write_organization(sheet, organization, uids)
root_registers = self.get_root_registers()
for register in root_registers:
sheet = book.add_sheet(register.title)
for index, cell in enumerate(TITLES_REGISTER):
sheet.row(0).write(index, cell)
self.write_register(sheet, register, uids)
filehandle = StringIO()
if root_organizations or root_registers:
book.save(filehandle)
response = filehandle.getvalue()
filehandle.seek(0, os.SEEK_END)
filesize = filehandle.tell()
filehandle.close()
self.request.RESPONSE.setHeader('Content-disposition', 'export.xls')
self.request.RESPONSE.setHeader('Content-Type', 'application/xls')
self.request.RESPONSE.setHeader('Content-Length', filesize)
return response
def list_of_dicts_to_xls(fileName, datalist, sheetName):
"""
Simple wrapper for xld to read excel file in as list of dictionaries
"""
w = Workbook()
# Add next "sheet x" if sheetName is None
if sheetName == None:
ws = w.add_sheet('sheet1')
else:
ws = w.add_sheet(sheetName)
#find index longest list - this has the most keys/headers
# making sure that's the number that will get created!
# get length of longest element
x = max([ len(x) for x in datalist])
# get index of first element that long
idx = [ datalist.index(p) for p in datalist if len(p)== x][0]
#### TODO find location of longest list
# get list of columns in that row
columns = list(datalist[idx].keys())
for colIdx, header in enumerate(datalist[idx]):
# Use dictionary keys as first row values(e.g. headers)
ws.write(0, colIdx, header)
for rowIdx,itemvalue in enumerate(datalist):
try:
ws.write(rowIdx + 1, colIdx, itemvalue[header])
except KeyError as e:
pass
w.save(fileName)
print "xls written"
return
def print_responseTime_persecond(self, folder = "." ):
book = Workbook()
sheet1 = book.add_sheet("response time")
sheet2 = book.add_sheet("transactions")
title = "Test Name: %s" % (self.name)
sheet1.write(0,0,title)
sheet2.write(0,0,title)
column = 1
for id in self.test_ids:
sheet1.write(1, column, "TEST" + id)
sheet2.write(1, column, "TEST" + id)
column += 1
results = self.perSecondResult
rows = range(1, self.max_second /TIME_INTERVAL + 1)
for row in rows:
sheet1.write(row + 1, 0, row*TIME_INTERVAL )
sheet2.write(row + 1, 0, row*TIME_INTERVAL )
column = 1
for id in self.test_ids:
key = id + "-" + str(row*TIME_INTERVAL)
if results.has_key(key):
result = results[key]
sheet1.write(row + 1, column, result.getAvg())
sheet2.write(row + 1, column, result.transactions)
column += 1
book.save(folder + "/" + self.name + "_bytime.xls")
def save_sch_report(year_range, results, count, category_lut, filename):
"""
Report will be a 2D matrix:
- X-Axis = School Type and Students Served Counts
- Y-Axis = 'Category' (FIPS Code, District, etc...)
Notes:
- idxes contains the data
- worksheets is a list of XLS worksheets, one per report in idxes
"""
wb = Workbook()
sch_types = wb.add_sheet('School Counts')
pop_perc = wb.add_sheet('Student Percentages')
worksheets = [sch_types, pop_perc]
y_offset = 2
x_offset = 2
# Create the headers/labels row/col
sch_types.write(1, 0, "Agency Name")
sch_types.write(1, 1, "State")
# Headers
for i, year in enumerate(year_range):
print "Write Report for: %d" % year
for ws in worksheets:
ws.write_merge(0, 0, (i*3)+x_offset, (i*3)+2+x_offset, year)
sch_types.write(1, (i*3)+x_offset, "Regular School")
sch_types.write(1, (i*3)+1+x_offset, "Charter School")
sch_types.write(1, (i*3)+2+x_offset, "Magnet School")
pop_perc.write(1, (i*3)+x_offset, "Regular School")
pop_perc.write(1, (i*3)+1+x_offset, "Charter School")
pop_perc.write(1, (i*3)+2+x_offset, "Magnet School")
# Print out the data
for j, result in enumerate(results[i]):
if j < count:
# Headers only on the first year
if (i == 0):
sch_types.write(y_offset+j, 0, category_lut[result[0]])
sch_types.write(y_offset+j, 1, fips_to_st[result[0][:2]][0])
sch_types.write(y_offset+j, (i*3)+2, result[2])
sch_types.write(y_offset+j, (i*3)+3, result[4])
sch_types.write(y_offset+j, (i*3)+4, result[6])
# Headers only on the first year
if (i == 0):
pop_perc.write(y_offset+j, 0, category_lut[result[0]])
pop_perc.write(y_offset+j, 1, fips_to_st[result[0][:2]][0])
member_count = result[1]
if member_count > 0:
pop_perc.write(y_offset+j, (i*3)+2, float(result[3])/member_count)
pop_perc.write(y_offset+j, (i*3)+3, float(result[5])/member_count)
pop_perc.write(y_offset+j, (i*3)+4, float(result[7])/member_count)
wb.save(filename)
class XlsWriter(object):
'''excel'''
def __init__(self, file_name, sheet_name='sheet'):
self.file_name = file_name + '.xls'
self.sheet_name = sheet_name
self.sheet_cnt = 1
self.row_cnt = 0
self.row_limit = 50000
self.column_limit = 256
self.cell_maxsize = 60000
self.xlsfile = Workbook()
self.table = self.xlsfile.add_sheet(self.sheet_name+str(self.sheet_cnt))
def close(self):
self.xlsfile.save(self.file_name)
def content(self):
return self.xlsfile
def stream(self):
return get_xls_stream(self.xlsfile.get_biff_data())
def get_attach(self):
'''获取附件格式'''
return {
'content': self.stream(),
'filename': self.file_name,
'mimetype': 'application/vnd.ms-excel',
}
def write(self, pos, data):
'''写入 excel'''
cnt = pos
for item in data:
is_type = isinstance(item, (str, unicode))
if is_type and (len(item) > self.cell_maxsize):
item = item[:self.cell_maxsize]
self.table.write(self.row_cnt, cnt, item)
cnt += 1
if cnt >= self.column_limit:
self.row_cnt += 1
cnt = pos
self.row_cnt += 1
if self.row_cnt >= self.row_limit:
self.row_cnt = 0
self.sheet_cnt += 1
self.table = self.xlsfile.add_sheet(
self.sheet_name+str(self.sheet_cnt)
)
def main(repo, developers):
basic_header = ['URL', 'Number', 'Title']
workbook = Workbook()
sheets = []
for developer in developers:
sheet_name = "{0} Estimates".format(developer)
sheet = workbook.add_sheet(sheet_name)
sheets.append(SheetInfo(sheet_name, developer, sheet))
consensus_sheet_name = "Consensus"
consensus_sheet_info = SheetInfo(
consensus_sheet_name,
None,
workbook.add_sheet(consensus_sheet_name)
)
sheets.append(consensus_sheet_info)
for sheet_info in sheets:
sheet = sheet_info.sheet
for column_index, column_header in enumerate(basic_header):
sheet.write(0, column_index, column_header)
if sheet_info.developer:
sheet.write(0, len(basic_header), "{0} Estimate".format(sheet_info.developer))
sheet.write(0, len(basic_header) + 1, "{0} Notes".format(sheet_info.developer))
else:
# If it's the consensus sheet, then add two columns for
# each developer.
column_index = len(basic_header)
for developer in developers:
sheet.write(0, column_index, "{0} Estimate".format(developer))
sheet.write(0, column_index + 1, "{0} Notes".format(developer))
column_index += 2
sheet.write(0, column_index, "Consensus")
for i, issue in enumerate(get_unestimated_open_issues(repo)):
row_index = i + 1
for sheet_info in sheets:
for column_index in range(len(basic_header)):
sheet_info.sheet.write(row_index, column_index, issue[column_index])
# In the Consensus sheet add a reference to each of the
# developer sheets' estimate and notes columns.
for i, sheet_info in enumerate(s for s in sheets if s.developer):
fmt = "'{sheet}'!{column}{row}"
for original_column_index in (len(basic_header) + i for i in range(2)):
formula_text = fmt.format(
sheet=sheet_info.name,
column=column_number_to_letters(original_column_index + 1),
row=row_index + 1)
consensus_sheet_info.sheet.write(
row_index,
original_column_index + 2 * i,
Formula(formula_text))
workbook.save('estimates.xls')
def toXls(data,filePath):
'''Save the data in an excel file of the same name as the original data file'''
#Check to see if the filepath.xls already exists
path = re.findall(r'(.+)\.TXT',filePath)[0]
newPath = "%s.xls" % (path)
cnt = 0
while (os.path.exists(newPath)):
newPath = "%s-%i.xls" % (path,cnt)
cnt+=1
print "Saving data to: ",newPath
#Save data in file
params = re.findall(r'(\w+)',data['settings'][1][1])
wb = Workbook()
wsA = wb.add_sheet(params[0])
wsB = wb.add_sheet(params[1])
wsSet = wb.add_sheet('settings')
#Settings
col= 0
for set in data['settings']:
wsSet.write(col,0,set[0])
wsSet.write(col,1,set[1])
col+=1
#TraceA
# move these two trace sections to an inline fun
col=0
for head in data['headA']:
wsA.write(0,col,head)
col+=1
row = 1
for pts in data['dataA']:
col = 0
for pt in pts:
wsA.write(row,col,float(pt))
col+=1
row+=1
#TraceB
col=0
for head in data['headB']:
wsB.write(0,col,head)
col+=1
row = 1
for pts in data['dataB']:
col = 0
for pt in pts:
wsB.write(row,col,pt)
col+=1
row+=1
wb.save(newPath)
wb.save(TemporaryFile())
def excel_styles():
book = Workbook(encoding='utf-8')
colorSheet = book.add_sheet('colors')
sizeSheet = book.add_sheet('size')
show_color(colorSheet)
show_size(sizeSheet)
styleFile = '/home/user/0_Daily_work/python/excel_styles.xls'
book.save(styleFile)
print 'saved to "%s"' % styleFile
return excel_styles
def get_excel(sites, surveys):
book = Workbook()
raw = book.add_sheet('Raw Benthic Data')
primer = book.add_sheet('Data for PRIMER')
meta = book.add_sheet('Metadata')
add_raw_primer_data(primer, raw, surveys)
add_meta_data(meta, sites)
return book
def save_report(year_range, idxes, count, category_list, category_txt, category_txt2, filename):
"""
Write out a bunch of report data to a spreadsheet report.
Report will be a 2D matrix:
- X-Axis = school year
- Y-Axis = 'Category' (FIPS Code, District, etc...)
Notes:
- idxes contains the data
- worksheets is a list of XLS worksheets, one per report in idxes
"""
wb = Workbook()
dws = wb.add_sheet('Dissimilarity Index')
ews = wb.add_sheet('Exposure Index')
iws = wb.add_sheet('Isolation Index')
min = wb.add_sheet('Minority Student Count')
tot = wb.add_sheet('Student Count')
mper = wb.add_sheet('Minority Proportion')
pmag = wb.add_sheet('Magnet Proportion')
pchr = wb.add_sheet('Charter Proportion')
pchc = wb.add_sheet('Choice Proportion')
worksheets = [dws, ews, iws, min, tot, mper, pmag, pchr, pchc]
# Create the headers/labels row/col
for ws in worksheets:
ws.write(0, 0, "Agency Name")
for j, st in enumerate(category_list):
if j < count:
if len(category_txt[st]) == 2: # Don't change caps for State abbr.
ws.write(j+1, 0, category_txt[st])
else:
ws.write(j+1, 0, category_txt[st].title())
offset = 1
if category_txt2:
for ws in worksheets:
ws.write(0, 1, "State")
for j, st in enumerate(category_list):
if j < count:
ws.write(j+1, 1, fips_to_st[category_txt2[st]][0])
offset = 2
# Print out the data
for i, year in enumerate(year_range):
print "Write Report for: %d" % year
for ws in worksheets:
ws.write(0, i+offset, year)
for j, st in enumerate(category_list):
if j < count:
for k, idx in enumerate(idxes):
try:
if k <= 5 and idx[i][st] < 0.001:
worksheets[k].write(j+1, i+offset, "")
else:
worksheets[k].write(j+1, i+offset, idx[i][st])
except KeyError:
worksheets[k].write(j+1, i+offset, "")
wb.save(filename)
def getSummary(data, dirname=None):
for fish in data.keys():
for CSname in data[fish].keys():
if dirname:
pp = PdfPages(os.path.join(dirname, '%s_for_%s.pdf' % (CSname,fish)))
print 'generating %s_for_%s.pdf' % (CSname,fish)
book = Workbook()
sheet1 = book.add_sheet('speed3D')
avgs = plotTrials(data, fish, CSname, 'speed3D', 30, pp=pp)
putNp2xls(avgs, sheet1)
sheet2 = book.add_sheet('d2inflow')
avgs = plotTrials(data, fish, CSname, 'd2inflow', 200, pp=pp)
putNp2xls(avgs, sheet2)
# sheet3 = book.add_sheet('smoothedz')
sheet3 = book.add_sheet('Z')
# avgs = plotTrials(data, fish, CSname, 'smoothedz', 100, pp=pp)
avgs = plotTrials(data, fish, CSname, 'Z', 30, pp=pp)
putNp2xls(avgs, sheet3)
sheet4 = book.add_sheet('ringpixels')
avgs = plotTrials(data, fish, CSname, 'ringpixels', 1200, pp=pp)
putNp2xls(avgs, sheet4)
sheet5 = book.add_sheet('peaks_within')
avgs = plotTrials(data, fish, CSname, 'peaks_within', 1.5, pp=pp)
putNp2xls(avgs, sheet5)
sheet6 = book.add_sheet('swimdir_within')
avgs = plotTrials(data, fish, CSname, 'swimdir_within', 1.5, pp=pp)
putNp2xls(avgs, sheet6)
sheet7 = book.add_sheet('xy_within')
avgs = plotTrials(data, fish, CSname, 'xy_within', 1.5, pp=pp)
putNp2xls(avgs, sheet7)
sheet8 = book.add_sheet('turns_shape')
avgs = plotTrials(data, fish, CSname, 'turns_shape', 1.5, pp=pp)
putNp2xls(avgs, sheet8)
sheet9 = book.add_sheet('turns_vel')
avgs = plotTrials(data, fish, CSname, 'turns_vel', 1.5, pp=pp)
putNp2xls(avgs, sheet9)
if dirname:
pp.close()
book.save(os.path.join(dirname, '%s_for_%s.xls' % (CSname,fish)))
close('all')
else:
show()
def detailed_weekly(self, roster, fout, filter=None):
book = Workbook()
sh_sum = book.add_sheet('Summary')
if filter is None: filter = 'rank_group not navy' #filter out navy
scope = self._select(roster, filter)
data = self.weekly_report(scope, filter, delim=',', describe=True).splitlines()
#write summary info to the Summary Sheet (data from weekly_report)
for r in range(0, len(data)):
cols = data[r].split(',')
for c in range(0, len(cols)):
sh_sum.write(r, c, cols[c])
#scope = sorted(self._select(roster, filter), key=op.attrgetter('lname')) #filter/sort list of marines in scope
#now write list of marines incomplete/complete for each code to its own sheet
for code in self._weekly_codes:
if code == 'P*':
sname = 'NLAMB'
else:
sname = code
sh_code = book.add_sheet(sname) #create the new sheet in workbook
btry_rosters = {}
#sort untrained marines into list by battery
for marine in self.select_untrained(code, scope, filter):
if marine.btry not in btry_rosters:
btry_rosters[marine.btry] = list()
btry_rosters[marine.btry].append(marine)
else:
btry_rosters[marine.btry].append(marine)
#generate list of marines incomplete by btry
data = ''
for btry in btry_rosters.iterkeys():
data += 'Btry: {0},({1}),{2}{nl}'.format(btry, code, self._desc[code], nl=self._eol)
data += 'BTRY,PLT,RANK,EIPID,LNAME,FNAME,MI,CODE,DATE TRAINED{nl}'.format(nl=self._eol)
for marine in btry_rosters[btry]:
data += marine.write_csv(code, self._eol)
data += self._eol * 3
#write data to sheet
rows = data.splitlines()
for r in range(0, len(rows)):
cols = rows[r].split(',')
for c in range(0, len(cols)):
sh_code.write(r, c, cols[c])
with open('../data/'+fout, 'wb') as fhandle:
book.save(fhandle)
def write_xls(self,filename):
book = Workbook()
sheet1 = book.add_sheet('Whole PT/PX Combinations',cell_overwrite_ok=True)
sheet2 = book.add_sheet('Desired thermo',cell_overwrite_ok=True)
sheet3 = book.add_sheet('All submitted templates',cell_overwrite_ok=True)
row11 = sheet1.row(0)
row11.write(0,'PT_name')
row11.write(1,'PT_sequence')
row11.write(2,'PX_name')
row11.write(3,'PX_sequence')
i=1
for record in SeqIO.parse(seq_file, "fasta") :
if i%2==1:
row11 = sheet1.row(i)
row11.write(0, str(record.id))
row11.write(1, str(record.seq))
else:
row11.write(2, str(record.id))
row11.write(3, str(record.seq))
i=i+1
row21 = sheet2.row(0)
row21.write(0,'name')
row21.write(1,'sequence')
row21.write(2,'bayes_class')
row21.write(3,'pwm_class')
row21.write(4,'p90 score')
row21.write(5,'diff score')
row21.write(6,'tri-temp Tm')
row21.write(7,'temp-temp Tm')
row21.write(8,'bonds')
row31 = sheet3.row(0)
row31.write(0,'name')
row31.write(1,'sequence')
row31.write(2,'bayes_class')
row31.write(3,'pwm_class')
row31.write(4,'p90 score')
row31.write(5,'diff score')
row31.write(6,'tri-temp Tm')
row31.write(7,'temp-temp Tm')
row31.write(8,'bonds')
book.save(filename)
def render_to_response(self, context, **response_kwargs):
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet(self.derive_title())
fields = self.derive_fields()
# build up our header row
for col in range(len(fields)):
field = fields[col]
sheet1.write(0, col, unicode(self.lookup_field_label(dict(), field)))
# then our actual values
for row in range(len(self.object_list)):
obj = self.object_list[row]
for col in range(len(fields)):
field = fields[col]
value = unicode(self.lookup_field_value(dict(), obj, field))
# skip the header
sheet1.write(row + 1, col, value)
# Create the HttpResponse object with the appropriate header.
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s' % self.derive_filename()
book.save(response)
return response
def render(self, request, context, **response_kwargs):
from xlwt import Workbook, XFStyle, easyxf
w = Workbook(encoding='utf-8')
ws = w.add_sheet('Report')
style = XFStyle()
row = 0
heading_xf = easyxf('font:height 200; font: bold on; align: wrap on, vert centre, horiz center')
ws.write(row, 0, '#', style)
for col, fieldname in enumerate(context['report'].headers, start=1):
ws.write(row, col, str(fieldname), heading_xf)
ws.col(col).width = 5000
ws.row(row).height = 500
# we have to prepare all the styles before going into the loop
# to avoid the "More than 4094 XFs (styles)" Error
styles = self._get_styles(context)
for rownum, data in enumerate(context['report']):
ws.write(rownum + 1, 0, rownum + 1)
for idx, (fieldname, rowvalue) in enumerate(data.items()):
style = styles[rowvalue.column.name]
try:
ws.write(rownum + 1, idx + 1, with_widget(rowvalue, format='xls'), style)
except Exception:
#logger.warning("TODO refine this exception: %s" % e)
ws.write(rownum + 1, idx + 1, smart_str(with_widget(rowvalue)), style)
f = StringIO.StringIO()
w.save(f)
f.seek(0)
return f.read()
class ExcelWriter(object):
def __init__(self):
# grab excel file and columns
self.wb_read = open_workbook('pids.xlsx')
self.wb_write = Workbook()
self.output = self.wb_write.add_sheet('PID and Names')
def go(self):
row_counter = 1
for sheet in self.wb_read.sheets():
for row in range(sheet.nrows):
pid_to_query = sheet.cell(row,3).value.replace('-','')
pid_to_query = str(pid_to_query)
owner_name = pid_getter.get_pid(pid_to_query)
print row_counter, "Query of PID", sheet.cell(row,0).value, "Owner name = ", owner_name
# write to new file
# self.output.write(row_counter,0,pid_to_query)
# self.output.write(row_counter,1,owner_name)
# writing to a new file by copying everything else
for col in range(sheet.ncols):
self.output.write(row_counter,col,sheet.cell(row,col).value)
self.output.write(row_counter,sheet.ncols,owner_name)
self.wb_write.save('names.xlsx')
row_counter += 1
def output_mesg(company_lack):
book = Workbook()
sheet1 = book.add_sheet(u'1')
i = 0
num = 1
for key, value in company_lack.items():
for s, d in value.items():
sheet1.write(i, 0, key)
sheet1.write(i, num, s)
sheet1.write(i, num+1, d)
i = i + 1
num = 1
book.save('4.xls') # 存储excel
book = xlrd.open_workbook('4.xls')
print('----------------------------------------------------------------------------------------')
print('----------------------------------------------------------------------------------------')
print(u'计算完成')
print('----------------------------------------------------------------------------------------')
print('----------------------------------------------------------------------------------------')
time.sleep(10)
def write_excel(totals):
"""Write out the collected totals to an excel file
"""
workbook = Workbook()
worksheet = workbook.add_sheet('New Sheet')
# write the header for the first block
total_hours = 0
total_tasks = 0
block_tasks, block_total = write_time_block(worksheet, totals)
total_hours += block_total
total_tasks += block_tasks
block_tasks, block_total = write_time_block(worksheet, totals, block_tasks)
total_hours += block_total
total_tasks += block_tasks
# write out the total hours
worksheet.write(total_tasks + 6, 0, 'Monthly Total', summary_total_header)
worksheet.write(total_tasks + 6, 1, total_hours, data_cell)
# write out the user and date
name = raw_input('Who is this report for? ')
worksheet.write(total_tasks + 8, 0, 'Name: %s' % (name))
worksheet.write(total_tasks + 9, 0, 'Date: %s' % (datetime.strftime(datetime.today(), '%m/%d/%Y')))
# write the signature field
worksheet.write(total_tasks + 8, 6, 'Signature:')
# save the file to disk
curpath = os.path.dirname(__file__)
workbook.save(os.path.join(curpath, 'test.xls'))
def make_xls(sheetname='sheet_1', filename='filename.xls', columns=[], objs=[]):
book = Workbook()
sheet = book.add_sheet(sheetname)
def index_of(key, list_2d):
for i, one in enumerate(list_2d):
if key == one[0]:
return i
attrs = []
for inner_list in columns:
attrs.append(inner_list[0])
for i in xrange(len(columns)):
sheet.write(0, i, columns[i][1])
sheet.col(i).width = columns[i][2] * 256
for i, obj in enumerate(objs, start=1):
for attr in attrs:
if isinstance(obj, dict):
sheet.write(i, index_of(attr, columns), obj[attr])
else:
sheet.write(i, index_of(attr, columns), obj.__getattribute__(attr))
book.save(filename)
def write_entries_to_xl(self, contras):
xl = Workbook()
self.xlsh = xl.add_sheet("POSTINGS")
self.row_in_xlsh = 0
self.write_batch_no_and_headers()
sequence = 1
total_dr, total_cr = 0, 0
for contra in contras:
self.write_xl_row(
(sequence, contra.account.number, abs(contra.amount),
contra.dr_cr(), contra.entry_code(), contra.narration,
contra.ref, contra.branch_for_itf_int,),
self.row_in_xlsh)
sequence += 1
self.row_in_xlsh += 1
total_dr += contra.amount if contra.dr_cr() == 'DR' else 0
total_cr += contra.amount if contra.dr_cr() == 'CR' else 0
contra.entry.time_processed_for_posting = datetime.now()
contra.entry.save()
return xl
def create_xls(data):
work = Workbook(encoding='utf-8')
work_sheet = work.add_sheet(u'账单')
#head of table
work_sheet.write(0, 0, 'ID')
work_sheet.write(0, 1, u'名字')
work_sheet.write(0, 2, u'编码')
work_sheet.write(0, 3, u'数量')
work_sheet.write(0, 4, u'单价')
work_sheet.write(0, 5, u'合计')
work_sheet.write(0, 6, u'备注')
i = 1
total_price = 0
for row in data:
work_sheet.write(i, 0, str(i))
work_sheet.write(i, 1, data[row]['name'])
work_sheet.write(i, 2, data[row]['code'])
work_sheet.write(i, 3, data[row]['number'])
work_sheet.write(i, 4, data[row]['price'])
work_sheet.write(i, 5, data[row]['total_price'])
work_sheet.write(i, 6, data[row]['comment'])
total_price += data[row]['number'] * data[row]['price']
i = i + 1
work_sheet.write(i, 4, u'总价:')
work_sheet.write(i, 5, total_price)
time_stamp = datetime.datetime.now().strftime("%Y%m%d%H%M%S")
file_name = "download_xls/bill_%s.xls" % time_stamp
work.save(file_name)
return file_name
def write_file(result_list, deal_date, company_name, filename):
'''
given a list, put it into excel file.
deal_date specifies a string which will be rendered as bold
company_name and filename are self-explanatory
'''
w = Workbook()
sheet = w.add_sheet(company_name)
row = 2
boldfont = easyxf(strg_to_parse='font: bold on')
normalfont = easyxf(strg_to_parse='')
sheet.write(0, 0, company_name)
sheet.write(1, 0, 'Date')
sheet.write(1, 1, 'Open')
sheet.write(1, 2, 'Close')
for line in result_list:
elements = line.decode('utf8').split(',')
date_string = elements[0]
open_value = elements[1]
close_value = elements[4]
if datetime.strptime(date_string, '%Y-%m-%d') == deal_date:
style = boldfont
else:
style = normalfont
sheet.write(row, 0, date_string, style)
sheet.write(row, 1, open_value, style)
sheet.write(row, 2, close_value, style)
row += 1
print(date_string, open_value, close_value)
w.save(filename)
def create_excel_file(db, kurzus):
book = Workbook(encoding='utf-8')
#sheet = book.add_sheet('{0} - {1} - {2}'.format(kurzus['nev'],kurzus['nap'],kurzus['sav']))
sheet = book.add_sheet('Névsor')
sheet = create_shit_head(sheet, kurzus_infok)
kurzus_hallgatok = get_kurzus_hallgatok(db, kurzus['id'])
sorszam = 1
for user in kurzus_hallgatok:
sheet.write(sorszam+4,0,sorszam,easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
sheet.write(sorszam+4,1,user['neptun'],easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
sheet.write(sorszam+4,2,user['nev'],easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
for i in xrange(3,20):
sheet.write(sorszam+4,i,'',easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
sorszam = sorszam + 1
book.save('{0}_{1}_{2}.xls'.format(ekezet_eltunteto(kurzus['nev'].lower()),
ekezet_eltunteto(kurzus['nap'].lower()),
kurzus['sav']))
def write_datatests(crisis_def_list, location = "./out", suffix = ""):
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
result_crises = combine_crises(crisis_def_list)
row_num = 0
for country_code in sort(result_crises.keys()):
years = sort(list(result_crises[country_code]))
try:
len(years)
except:
print(years)
print(country_code)
sheet1.write(row_num, 0, country_code)
crisis_row = sheet1.row(row_num)
crisis_row.write(1, "crisis")
for j in range(len(years)):
crisis_row.write(j+2, years[j])
normal_row = sheet1.row(row_num+1)
normal_row.write(1, "normal")
normal_years = pick_normal_years(years)
for j in range(len(normal_years)):
normal_row.write(j+2, normal_years[j])
row_num+=2
saveloc = os.path.expanduser(location)+suffix+".xls"
book.save(saveloc)
#!/usr/bin/env python3
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook
input_file = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('set_of_worksheets')
my_sheets = [0, 1]
threshold = 1900.0
sales_column_index = 3
first_worksheet = True
with open_workbook(input_file) as workbook:
data = []
for sheet_index in range(workbook.nsheets):
worksheet = workbook.sheet_by_index(sheet_index)
if first_worksheet:
header_row = worksheet.row_values(0)
data.append(header_orw)
first_worksheet = False
for row_index in range(1, workbook.nrows):
row_list = []
sale_amount = worksheet.cell_value\
(row_index, sales_column_index)
if sale_amount > threshold:
for column_index in range(worksheet.ncols):
cell_value = worksheet.cell_value\
(row_index, column_index)
# Date:2020/5/25
# Author:Lingchen
# Mark: 使用列标题来筛选Excel中的特定列
# python 8_excel_column_by_name.py data/sales_2013.xlsx data/output/8_output.xlsx
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook
input_file = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('jan_2013_output')
# 指定列标题
my_columns = ['Customer Name', 'Purchase Date']
with open_workbook(input_file) as workbook:
worksheet = workbook.sheet_by_name('january_2013')
data = [my_columns]
header_list = worksheet.row_values(0)
header_index_list = []
# 根据特定列标题找出对应的列索引
for header_index in range(len(header_list)):
if header_list[header_index] in my_columns:
header_index_list.append(header_index)
for row_index in range(1, worksheet.nrows):
def writeResults(C1,C2,C3,R2,R3,f,magCL,magOL,phaseOL,magvcoTF,PFDCPNoiseOut,PrescalerNoiseOut,VCONoiseOut,R2NoiseOut,R3NoiseOut,XTALNoiseOut,SDNoiseOut,TotalNoise,t,fT):
book = Workbook()
parameter = easyxf('font: name Arial, bold True, height 280; alignment: horizontal center')
parameterValue = easyxf('font: name Arial, height 280; alignment: horizontal center', num_format_str='0.000E+00')
parameterValue2 = easyxf('font: name Arial, height 280; alignment: horizontal center', num_format_str='0.000')
columnHeader = easyxf('font: name Arial, bold True, height 240; alignment: horizontal center')
#Write Loop Filter Components worksheet:
sheetLoopFilter = book.add_sheet('Loop Filter Components')
sheetLoopFilter.col(1).width = 5000
sheetLoopFilter.write(0,0,'C1',parameter)
sheetLoopFilter.write(0,1,C1,parameterValue)
sheetLoopFilter.write(1,0,'C2',parameter)
sheetLoopFilter.write(1,1,C2,parameterValue)
sheetLoopFilter.write(2,0,'C3',parameter)
sheetLoopFilter.write(2,1,C3,parameterValue)
sheetLoopFilter.write(3,0,'R2',parameter)
sheetLoopFilter.write(3,1,R2,parameterValue)
sheetLoopFilter.write(4,0,'R3',parameter)
sheetLoopFilter.write(4,1,R3,parameterValue)
#Write Loop Response worksheet:
sheetLoopResponse = book.add_sheet('Loop Response Data')
sheetLoopResponse.col(0).width = 6000
sheetLoopResponse.write(0,0,'Frequency (Hz)',columnHeader)
sheetLoopResponse.col(1).width = 13000
sheetLoopResponse.write(0,1,'Closed Loop Response Magnitude (dB)',columnHeader)
sheetLoopResponse.col(2).width = 13000
sheetLoopResponse.write(0,2,'Open Loop Response Magnitude (dB)',columnHeader)
sheetLoopResponse.col(3).width = 13000
sheetLoopResponse.write(0,3,'Open Loop Response Phase (dB)',columnHeader)
sheetLoopResponse.col(4).width = 13000
sheetLoopResponse.write(0,4,'VCO Transfer Function Magnitude (dB)',columnHeader)
for i in range(len(f)):
sheetLoopResponse.write(i+1,0,f[i],parameterValue)
sheetLoopResponse.write(i+1,1,magCL[i],parameterValue2)
sheetLoopResponse.write(i+1,2,magOL[i],parameterValue2)
sheetLoopResponse.write(i+1,3,phaseOL[i],parameterValue2)
sheetLoopResponse.write(i+1,4,magvcoTF[i],parameterValue2)
#Write Noise Results worksheet:
sheetPLLNoise = book.add_sheet('PLL Noise Contributors')
sheetPLLNoise.col(0).width = 6000
sheetPLLNoise.write(0,0,'Frequency (Hz)',columnHeader)
sheetPLLNoise.col(1).width = 6000
sheetPLLNoise.write(0,1,'PFDCP (dBc/Hz)',columnHeader)
sheetPLLNoise.col(2).width = 6000
sheetPLLNoise.write(0,2,'Prescaler (dBc/Hz)',columnHeader)
sheetPLLNoise.col(3).width = 6000
sheetPLLNoise.write(0,3,'VCO (dBc/Hz)',columnHeader)
sheetPLLNoise.col(4).width = 6000
sheetPLLNoise.write(0,4,'R2 (dBc/Hz)',columnHeader)
sheetPLLNoise.col(5).width = 6000
sheetPLLNoise.write(0,5,'R3 (dBc/Hz)',columnHeader)
sheetPLLNoise.col(6).width = 6000
sheetPLLNoise.write(0,6,'XTAL (dBc/Hz)',columnHeader)
sheetPLLNoise.col(7).width = 7000
sheetPLLNoise.write(0,7,'Sigma Delta (dBc/Hz)',columnHeader)
sheetPLLNoise.col(8).width = 7000
sheetPLLNoise.write(0,8,'Total Noise (dBc/Hz)',columnHeader)
for i in range(len(f)):
sheetPLLNoise.write(i+1,0,f[i],parameterValue)
sheetPLLNoise.write(i+1,1,PFDCPNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,2,PrescalerNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,3,VCONoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,4,R2NoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,5,R3NoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,6,XTALNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,7,SDNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,8,TotalNoise[i],parameterValue2)
#Write Time Response worksheet:
sheetPLLTime = book.add_sheet('Time Response')
sheetPLLTime.col(0).width = 5000
sheetPLLTime.write(0,0,'Time (s)',columnHeader)
sheetPLLTime.col(1).width = 8000
sheetPLLTime.write(0,1,'Output Frequency (Hz)',columnHeader)
for i in range(len(t)):
sheetPLLTime.write(i+1,0,t[i],parameterValue)
sheetPLLTime.write(i+1,1,fT[i],parameterValue)
return book
# 引入相机矩阵
import recammat
mtx, dist = recammat.recammat()
print(mtx)
# 显示图像，按下s键时开始采集十秒内的十张照片
camera = int(1)# 使用webcam：1
i = 0
j = 0
# 数据进行excel表格保存
wb = Workbook()
incident_angle = input('输入入射天顶角：')
chushetianding = input('输入出射天顶角： ')
chushefangwei = input('输入出射方位角： ')
sheet1 = wb.add_sheet('Brightness data' +'(' + str(incident_angle) + ', '+str(chushetianding)+', '+str(chushefangwei) + ')' + '.xls')
sheet1.write(0, 0, 'Picture Number')
sheet1.write(0, 1, 'Brightness')
sheet1.write(0, 2, 'Center Value')
sheet1.write(0, 3, 'Incident angle')
sheet1.write(0, 4, 'Reflective angle')
while (1):
cap = cv2.VideoCapture(camera)
# cap.set(cv2.CAP_PROP_EXPOSURE, 0) #useless
ret, frame = cap.read()
gray = cv2.cvtColor(frame, cv2.COLOR_BGR2GRAY)
#划线
def getElection():
# from PIL import Image, ImageFile
x_index = 1
root.directory = filedialog.askdirectory()
mypath = root.directory
onlyfiles = [f for f in listdir(mypath) if isfile(join(mypath, f))]
files_length = len(onlyfiles)
# print(files_length)
# Workbook is created
wb = Workbook()
# add_sheet is used to create sheet.
sheet1 = wb.add_sheet('Sheet 1', cell_overwrite_ok=True)
# Write Headers to sheet
sheet1.write(0, 0, 'S.No')
sheet1.write(0, 1, 'Election Number')
sheet1.write(0, 2, 'Gender')
sheet1.write(0, 3, 'Age')
sheet1.write(0, 4, 'Name')
sheet1.write(0, 5, 'Fathers Name')
sheet1.write(0, 6, 'File Name')
sheet1.write(0, 7, 'Text')
ixsheet = 0
# print(files_lenth)
while ixsheet < files_length:
ImageFile.LOAD_TRUNCATED_IMAGES = True
x = "'D:\Personal\Machine Learning\Election Commission\'"
dir_path = x.replace("'", "")
file_path = onlyfiles[ixsheet]
join_path = join(dir_path, file_path)
# print(join_path)
im = Image.open(join_path)
# load the example image and convert it to grayscale
image = cv2.imread(join_path)
gray = prePrcoessing(image)
# write the grayscale image to disk as a temporary file so we can
# apply OCR to it
filename = "{}.png".format(os.getpid())
cv2.imwrite(filename, gray)
##############################################################################################################
######################################## Section 3: Running PyTesseract ######################################
##############################################################################################################
# load the image as a PIL/Pillow image, apply OCR, and then delete
# the temporary file
# pytesseract.pytesseract.tesseract_cmd = 'D:\\Tesseract-OCR\\tesseract.exe'
text = pytesseract.image_to_string(Image.open(filename), lang='eng')
# add +hin after eng within the same argument to extract hindi specific text - change encoding to utf-8 while writing
os.remove(filename)
# writing extracted data into a text file
text_output = open('outputbase.txt', 'w', encoding='utf-8')
text_output.write(text)
text_output.close()
file = open('outputbase.txt', 'r', encoding='utf-8')
text = file.read()
# Cleaning all the gibberish text
bad_chars = [
'~', '`', '!', '@', '#', '$', '%', '^', '&', '*', '(', ')', '{',
'}', "'", '[', ']', '|', ':', ';', ',', '<', '>', '.', '?', '/',
'+', '=', '_'
]
for i in bad_chars:
text = text.replace(i, '')
############################################################################################################
###################################### Section 4: Extract relevant information #############################
############################################################################################################
# Initializing data variable
name = None
fname = None
dob = None
pan = None
nameline = []
dobline = []
panline = []
text0 = []
text1 = []
text2 = []
# Searching for PAN
lines = text.split('\n')
for lin in lines:
s = lin.strip()
s = lin.replace('\n', '')
s = s.rstrip()
s = s.lstrip()
text1.append(s)
text1 = list(filter(None, text1))
# print(text1)
# to remove any text read from the image file which lies before the line 'Income Tax Department'
lineno = 0 # to start from the first line of the text file.
for wordline in text1:
xx = wordline.split('\n')
if ([
w for w in xx
if re.search('(ELECTION|COMMISSION|INDIA|NDIA)$', w)
]):
text1 = list(text1)
lineno = text1.index(wordline)
break
# text1 = list(text1)
text0 = text1[lineno + 1:]
#print(text0) # Contains all the relevant extracted text in form of a list - uncomment to check
###############################################################################################################
######################################### Section 5: Dishwasher part ##########################################
###############################################################################################################
# Cleaning first names, better accuracy
namelist = text.split(' ')
while ("" in namelist):
namelist.remove("")
name = re.findall(r'\w{0}[N,n]\w{0}[A,a]\w{0}[M,m]\w{0}[E,e]', text)
for n in name:
indices = [i for i, x in enumerate(namelist) if x == n]
# print(indices)
list_names = list()
if name is not None:
for n in indices:
list_names.append((namelist[n + 1] + " " + namelist[n + 2]))
# Cleaning Father's name
Person_name = ""
# print(len(list_names))
if len(list_names) > 0:
if list_names[0] is not None:
Person_name = list_names[0]
else:
Person_name = ""
fname = ""
if len(list_names) == 2:
if list_names[1] is not None:
fname = list_names[1]
else:
fname = ""
gender = re.findall(
r'\w{0}[F,f]\w{0}[e,E]\w{0}[M,m]\w{0}[A,a]\w{0}[a-zA-Z]\w{0}[E,e]',
text)
if gender == []:
gender = re.findall(r'\w{0}[M,m]\w{0}[A,a]\w{0}[a-zA-Z]\w{0}[E,e]',
text)
# Cleaning DOB
dob = re.findall(r'\d{2}[-/|-]\d{2}[-/|-]\d{4}', text)
electiono = re.findall(r'\w{2}[a-zA-Z]\w{6}[0-9]', text)
# print(electiono)
# Making tuples of data
data = {}
data['Name'] = Person_name
data['Father Name'] = fname
data['Gender'] = gender
data['Election No'] = electiono
sheet1.write(ixsheet + 1, 0, ixsheet + 1)
sheet1.write(ixsheet + 1, 1, data['Election No'])
sheet1.write(ixsheet + 1, 2, data['Gender'])
sheet1.write(ixsheet + 1, 4, data['Name'])
sheet1.write(ixsheet + 1, 5, data['Father Name'])
sheet1.write(ixsheet + 1, 6, join_path)
sheet1.write(ixsheet + 1, 7, text)
ixsheet = ixsheet + 1
wb.save('Election Card DATA.xls')
# Writing to an excel
# sheet using Python
import xlwt
from xlwt import Workbook
# Workbook is created
wb = Workbook()
# add_sheet is used to create sheet.
sheet1 = wb.add_sheet('Sheet 1')
#This is defining the fuction that im going use
def write_to_thing():
do_write_exspense = input('Do you wanna write all the exspenses for today')
if do_write_exspense == 'yes':
exspense = input('input the exspense:')
sheet1.write(1, 0, exspense)
wb.save('xlwt example.xls')
write_to_thing()
import getphone
import getbusiness
import getaddress
import xlwt
from xlwt import Workbook
website_base_url = 'https://www.tripadvisor.co.hu'
wb = Workbook()
sheet1 = wb.add_sheet('Elérhetőségek')
name_pos = 1
phone_pos = 1
business_pos = 1
sheet1.write(0, 0, 'Név')
sheet1.write(0, 1, "Cím")
sheet1.write(0, 2, "Telefonszám")
with open('./restaurants_raw.txt') as f:
restaurant_list_raw = f.read().splitlines()
restaurant_list_clean = list(dict.fromkeys(restaurant_list_raw))
f.close()
print(len(restaurant_list_clean))
for element in restaurant_list_clean:
clean_file = open("restaurants_clean_v1.2.txt", "a")
businessURL = website_base_url + element
businessName = getbusiness.getBusiness(businessURL)
businessPhone = getphone.getPhone(businessURL)
businessAddress = getaddress.get(businessURL)
if businessName is not None and businessPhone is not None and businessAddress is not None:
import xlwt
from xlwt import Workbook
import xlrd
wb = Workbook()
loc = "C:/Users/Shanmukhi/Dropbox/Shanmukhi Ayush 2020/Data/Capacities of CCPs/Capacity of Cold chain Points(Bihar).xlsx"
workbook = xlrd.open_workbook(loc)
sheet = workbook.sheet_by_index(3)
sheet1 = wb.add_sheet('Clinics')
sheet1.write(0,0,'i')
sheet1.write(0,1,'t')
sheet1.write(0,2,'Capacity')
i = 606
t = 12
row = 1
for I in range(1,i+1):
for T in range(1,t+1):
sheet1.write(row,0,I)
sheet1.write(row,1,T)
sheet1.write(row,2,sheet.cell_value(I,7))
row = row + 1
wb.save("C:/Users/Shanmukhi/Dropbox/Shanmukhi Ayush 2020/Data/Capacities of CCPs/capacity_clinics.xls")
#!/bin/python3
import xlwt
from xlwt import Workbook
f = open("inception_metrics.txt")
wb = Workbook()
sheet1 = wb.add_sheet("Sheet 1")
loss, acc, val_loss, val_acc = [], [], [], []
for line in f:
tokens = line.split()
if len(tokens) == 0:
continue
if tokens[0] == "110/110":
loss.append(tokens[7])
acc.append(tokens[10])
val_loss.append(tokens[13])
val_acc.append(tokens[16])
for epoch in range(0, 25):
sheet1.write(epoch, 0, epoch)
sheet1.write(epoch, 1, loss[epoch])
sheet1.write(epoch, 2, acc[epoch])
sheet1.write(epoch, 3, val_loss[epoch])
sheet1.write(epoch, 4, val_acc[epoch])
def get_render_context(self, request, extra_context={}, by_row=None):
context_request = request or self.request
related_fields = []
filter_related_fields = {}
if self.parent_report and by_row:
for mfield, cfield, index in self.related_inline_filters:
filter_related_fields[cfield] = by_row[index].value
try:
form_groupby = self.get_form_groupby(context_request)
form_filter = self.get_form_filter(context_request)
form_config = self.get_form_config(context_request)
column_labels = self.get_column_names(filter_related_fields)
report_rows = []
groupby_data = None
filter_kwargs = None
report_anchors = []
chart = None
context = {
'report': self,
'form_groupby': form_groupby,
'form_filter': form_filter,
'form_config': form_config if self.type == 'chart' else None,
'chart': chart,
'report_anchors': report_anchors,
'column_labels': column_labels,
'report_rows': report_rows,
}
if context_request.GET:
groupby_data = form_groupby.get_cleaned_data() if form_groupby else None
filter_kwargs = filter_related_fields or form_filter.get_filter_kwargs()
if groupby_data:
self.__dict__.update(groupby_data)
else:
self.__dict__['onlytotals'] = False
report_rows = self.get_rows(context_request, groupby_data, filter_kwargs, filter_related_fields)
for g, r in report_rows:
report_anchors.append(g)
if len(report_anchors) <= 1:
report_anchors = []
if self.type == 'chart' and groupby_data and groupby_data['groupby']:
config = form_config.get_config_data()
if config:
chart = self.get_chart(config, report_rows)
if self.onlytotals:
for g, rows in report_rows:
for r in list(rows):
if r.is_value():
rows.remove(r)
if not context_request.GET.get('export', None) is None and not self.parent_report:
if context_request.GET.get('export') == 'excel':
book = Workbook(encoding='utf-8')
sheet1 = FitSheetWrapper(book.add_sheet(self.get_title()[:20]))
stylebold = easyxf('font: bold true; alignment:')
stylevalue = easyxf('alignment: horizontal left, vertical top;')
row_index = 0
for index, x in enumerate(column_labels):
sheet1.write(row_index, index, u'%s' % x, stylebold)
row_index += 1
for g, rows in report_rows:
if g:
sheet1.write(row_index, 0, u'%s' % x, stylebold)
row_index += 1
for row in list(rows):
if row.is_value():
for index, x in enumerate(row):
if isinstance(x.value, (list, tuple)):
xvalue = ''.join(['%s\n' % v for v in x.value])
else:
xvalue = x.text()
sheet1.write(row_index, index, xvalue, stylevalue)
row_index += 1
elif row.is_caption:
for index, x in enumerate(row):
if not isinstance(x, unicode):
sheet1.write(row_index, index, x.text(), stylebold)
else:
sheet1.write(row_index, index, x, stylebold)
row_index += 1
elif row.is_total:
for index, x in enumerate(row):
sheet1.write(row_index, index, x.text(), stylebold)
sheet1.write(row_index + 1, index, ' ')
row_index += 2
response = HttpResponse(mimetype="application/ms-excel")
response['Content-Disposition'] = 'attachment; filename=%s.xls' % self.slug
book.save(response)
return response
if context_request.GET.get('export') == 'pdf':
inlines = [ir(self, context_request) for ir in self.inlines]
report_anchors = None
setattr(self, 'is_export', True)
context = {
'report': self,
'column_labels': column_labels,
'report_rows': report_rows,
'report_inlines': inlines,
}
context.update({'pagesize': 'legal landscape'})
return render_to_pdf(self, 'model_report/export_pdf.html', context)
inlines = [ir(self, context_request) for ir in self.inlines]
is_inline = self.parent_report is None
render_report = not (len(report_rows) == 0 and is_inline)
context = {
'render_report': render_report,
'is_inline': is_inline,
'inline_column_span': 0 if is_inline else len(self.parent_report.get_column_names()),
'report': self,
'form_groupby': form_groupby,
'form_filter': form_filter,
'form_config': form_config if self.type == 'chart' else None,
'chart': chart,
'report_anchors': report_anchors,
'column_labels': column_labels,
'report_rows': report_rows,
'report_inlines': inlines,
}
if extra_context:
context.update(extra_context)
context['request'] = request
return context
finally:
globals()['_cache_class'] = {}
#!/usr/bin/env python3
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook
input_file = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('mar_2013_output')
my_columns = [2, 3]
with open_workbook(input_file) as workbook:
worksheet = workbook.sheet_by_name('march_2013')
data = []
for row_index in range(worksheet.nrows):
row_list = []
for column_index in my_columns:#在我们需要的行时进行操作
cell_value = worksheet.cell_value(row_index,column_index)
cell_type = worksheet.cell_type(row_index, column_index)
if cell_type == 3:
date_cell = xldate_as_tuple(cell_value,workbook.datemode)
date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
row_list.append(date_cell)
else:
row_list.append(cell_value)
data.append(row_list)
for list_index, output_list in enumerate(data):
for element_index, element in enumerate(output_list):
def check_zhangdan_with_taizhang(dictmesg_with_name, taizhang):
smulu = []
for x in dictmesg_with_name:
dlist = list(x.keys())
smulu = smulu + dlist
# 核对一下账目和台账是否匹配
lackzm = []
for x in smulu:
aa = {x: []}
lackzm.append(aa)
name4 = renming[-1]
lacktj = []
book1 = Workbook()
for x in smulu:
zmsj = []
for y in dictmesg_with_name:
if x in y:
zmsj = copy.deepcopy(y.get(x))
for z in zmsj:
del z[3]
z.pop()
if x in name4:
del z[4:]
break
tj = []
for z in range(len(taizhang)):
if x in taizhang[z][0]:
tj = copy.deepcopy(taizhang[z])
break
if tj != []:
for m in range(len(zmsj)):
flag = 0
for n in range(len(tj)):
if zmsj[m][1] == tj[n][1] and zmsj[m][2] == tj[n][
2] and zmsj[m][3] == tj[n][3] and tj[n][-1] != u'o':
try:
if 0 <= abs(zmsj[m][0] -
tj[n][0]) <= 5 or 365 <= abs(
zmsj[m][0] - tj[n][0]) <= 371:
flag = 1
zmsj[m].append(u'o')
tj[n].append(u'o')
break
except:
pass
if flag == 0:
zmsj[m].append(u'x')
for mm in lackzm:
if x in mm:
dd = mm.get(x)
for k in zmsj:
dd.append(k)
titles = x
sheet1 = book1.add_sheet(titles)
for i in range(1, len(tj)):
for j in range(len(tj[i])):
if is_chinese(tj[i][j]):
tj[i][j].encode('utf-8')
elif is_num(tj[i][j]) == 1:
if math.modf(tj[i][j])[0] == 0 or tj[i][j] == 0:
tj[i][j] = int(tj[i][j])
if j == 0:
try:
cccc = pandas.to_datetime(tj[i][j] - 25569, unit='d')
tj[i][j] = str(pandas.Period(cccc, freq='D'))
except:
pass
sheet1.write(i - 1, j, tj[i][j])
book1.save('no danzi.xls') # 存储excel
book1 = xlrd.open_workbook('no danzi.xls')
book2 = Workbook()
for x in smulu:
sheet1 = book2.add_sheet(x)
for y in lackzm:
if x in y:
sss = copy.deepcopy(y.get(x))
for i in range(len(sss)):
for j in range(len(sss[i])):
if is_chinese(sss[i][j]):
sss[i][j].encode('utf-8')
elif is_num(sss[i][j]) == 1:
if math.modf(sss[i][j])[0] == 0 or sss[i][j] == 0:
sss[i][j] = int(sss[i][j])
if j == 0:
try:
cccc = pandas.to_datetime(sss[i][j] - 25569,
unit='d')
sss[i][j] = str(pandas.Period(cccc, freq='D'))
except:
pass
sheet1.write(i, j, sss[i][j])
book2.save('no taizhang.xls') # 存储excel
book2 = xlrd.open_workbook('no taizhang.xls')
return smulu
from Crypto.Cipher import AES
from datetime import datetime
from Crypto import Random
from xlwt import Workbook
wb = Workbook()
output = wb.add_sheet('AES')
output.write(0, 0, 'Message Size')
output.write(0, 1, 'Encryption time')
output.write(0, 2, 'Decryption time')
output.write(0, 3, 'Cipher text Size')
def encryption(message):
start = datetime.now()
encryption_suite = AES.new(key, AES.MODE_CFB, iv)
cipher_text = encryption_suite.encrypt(message)
end = datetime.now()
total = end - start
total = total.total_seconds()
print("Encryption time:", total)
return cipher_text, total
#print (cipher_text)
#time2=time.time()
#print((time2-time1)*1000)
def decryption(cipher_text):
start = datetime.now()
# 목적 : 모든 워크시트에서 특정 행 필터링
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook
input_file = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('filtered_rows_all_worksheets')
sales_column_index = 3
threshold = 2000.0
first_worksheet = True
with open_workbook(input_file) as workbook:
data = []
for worksheet in workbook.sheets():
if first_worksheet:
header_row = worksheet.row_values(0)
data.append(header_row)
first_worksheet = False
for row_index in range(1, worksheet.nrows):
row_list = []
sale_amount = worksheet.cell_value(row_index, sales_column_index)
sale_amount = float(
str(sale_amount).replace('$', '').replace(',', ''))
if sale_amount > threshold:
for column_index in range(worksheet.ncols):
if sheet.cell_value(
i, 1
) != "": # Nếu giá trị các ô trong cột nhắn tin riêng không rỗng thì mới thêm vào mảng. Tương tự ở dưới cho tham quan, xem mẫu
inb.append(sheet.cell_value(i, 1))
if sheet.cell_value(i, 2) != "":
visit.append(sheet.cell_value(i, 2))
# Đường dẫn đến các giá trị cần được dán nhãn
path = ("result.xls")
wb = xlrd.open_workbook(path)
sheet = wb.sheet_by_index(1) # Sheet thứ 2 - Comment
# Tạo file excel mới chuẩn bị truy xuất dữ liệu intent của comment
wb = Workbook('test2.xlsx')
sheet1 = wb.add_sheet('Comment') # Tạo sheet tên comment
# Comment column
com = 1 # File anh Tuan Anh
# com = 2 File của Mai
# Kiểm tra dữ liệu theo từng dòng
# for i in range(69, 70):
for i in range(sheet.nrows):
# Biến lưu điểm của từng intent, intent nào có điểm cao nhất thì comment sẽ được gán cho intent đó.
tagScore = 0
inbScore = 0
visitScore = 0
# Nếu comment chỉ là số điện thoại thì đó ngay lập tức là nhắn tin riêng.
if sheet.cell_value(i, com).isdigit():
# -*- coding: utf-8 -*-
"""
Created on Mon Feb 15 14:35:13 2021
@author: dhtmd
"""
from xlrd import open_workbook #excel 파일 읽기 위한 모듈
from xlwt import Workbook #excel 파일을 쓰기 위한 모듈, pip install xlwt
infile = "ssec1804.xls" #원본 데이터
outfile = "ssec1804out.xls" #복사데이터(하나의 sheet만 저장)
outworkbook = Workbook() #비어있는 xls 파일
out_sheet = outworkbook.add_sheet("전체증감") #출력할 xls 파일에 sheet 추가, 이름 설정(전체증감)
#open_workbook(infile) : 원본데이터 xls파일
with open_workbook(infile) as workbook:
#worksheet : 원본 xls 파일의 sheet 의 이름이 "1.전체증감" sheet 데이터
worksheet = workbook.sheet_by_name("1.전체증감")
#out_sheet.write(rindex, cindex, 원본 cell) : 원본 cell 데이터를 rindex, cindex 데이터로 저
for rindex in range(worksheet.nrows):
for cindex in range(worksheet.ncols):
out_sheet.write(rindex, cindex,
worksheet.cell_value(rindex, cindex))
print(worksheet.cell_value(rindex, cindex))
#outworkbook 의 데이터를 ssec1804out.xls 파일로 저장
outworkbook.save(outfile)
#!/usr/bin/env python3
import glob
import os
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook
input_folder = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('sums_and_averages')
all_data = []
sales_column_index = 3
header = ['workbook', 'worksheet', 'worksheet_total', 'worksheet_average',\
'workbook_total', 'workbook_average']
all_data.append(header)
for input_file in glob.glob(os.path.join(input_folder, '*.xls*')):
with open_workbook(input_file) as workbook:
list_of_totals = []
list_of_numbers = []
workbook_output = []
for worksheet in workbook.sheets():
total_sales = 0
number_of_sales = 0
worksheet_list = []
worksheet_list.append(os.path.basename(input_file))
def jiantuan():
csvFile = open("./统计结果/昆曲字频.csv", "rb")
reader = unicodecsv.reader(csvFile,encoding='utf-8-sig')
zidict = []
for item in reader:
zidict.append(item)
a=sorted(zidict, key=lambda x: int(x[1]), reverse=True)
t500=np.array(a[0:500])
t1000=np.array(a[0:-1])
wb = Workbook()
st1=wb.add_sheet('入声及尖团')
st1.write(0, 0, '昆曲入声及尖团字统计，计频率前800')
st1.write(1,0,'顺序')
st1.write(1,1,'字')
st1.write(1, 2, '尖团')
st1.write(1, 3, '声调')
st1.write(1, 4, '频率(%)')
st2 = wb.add_sheet('尖团')
st2.write(0, 0, '昆曲尖团字统计，计频率前500')
st2.write(1, 0, '顺序')
st2.write(1, 1, '字')
st2.write(1, 2, '尖团')
st2.write(1, 3, '频率(%)')
st3 = wb.add_sheet('入声')
st3.write(0, 0, '昆曲入声字统计，计频率前500')
st3.write(1, 0, '顺序')
st3.write(1, 1, '字')
st3.write(1, 2, '频率(%)')
n1 = 1
n2=1
n3=1
#style = xlwt.easyxf(' color red;')
for count in range(len(t1000[:,0])):
freq=int(t1000[count,1])/np.sum(t1000[:,1].astype(float))*100
i=t1000[count,0]
if isjian(i)=='非'and isru(i)=='非入' :
continue
else:
if n1<801:
st1.write(n1 + 1, 0, n1)
st1.write(n1+1, 1, i)
st1.write(n1+1, 2, isjian(i))
st1.write(n1+1, 3, isru(i))
if round(freq,2)==0:
st1.write(n1 + 1, 4,'<0.01')
else:
st1.write(n1+1, 4, round(freq,2))
n1+=1
if isjian(i) in ['尖','团'] and n2<501:
st2.write(n2 + 1, 0, n2)
st2.write(n2+1, 1, i)
st2.write(n2 + 1, 2, isjian(i))
if round(freq,2)==0:
st2.write(n2 + 1, 3,'<0.01')
else:
st2.write(n2+1, 3, round(freq,2))
n2+=1
if isru(i)=='入声'and n3<501:
st3.write(n3 + 1, 0, n3)
st3.write(n3+1, 1, i)
if round(freq,2)==0:
st3.write(n3 + 1, 2,'<0.01')
else:
st3.write(n3+1, 2, round(freq,2))
n3+=1
wb.save('./统计结果/昆曲入声及尖团统计.xls')
# 목적: 여러 개의 통합 문서 합치기
import glob
import os
import sys
from datetime import date
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook
input_folder = sys.argv[1] # .
output_file = sys.argv[2] # output_files/13_output_basic.xls
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('all_data_all_workbooks')
data = []
first_worksheet = True
for input_file in glob.glob(os.path.join(input_folder, '*.xls*')):
print(os.path.basename(input_file))
with open_workbook(input_file) as workbook:
for worksheet in workbook.sheets():
if first_worksheet:
header_row = worksheet.row_values(0)
data.append(header_row)
first_worksheet = False
for row_index in range(1, worksheet.nrows):
row_list = []
for column_index in range(worksheet.ncols):
cell_value = worksheet.cell_value(row_index, column_index)
cell_type = worksheet.cell_type(row_index, column_index)
if cell_type == 3:
class_error_final.append(classification_error_advex)
#Storage of classification
class_emb_final.append(classifier_emb_array)
class_advex_final.append(classifier_advex_emb_array)
#Tracking person number
person_number = person_number + 1
print(np.mean(class_error_final))
cntForEps = 0
wb = Workbook()
results_excel = wb.add_sheet('Results')
results_excel.write(0, 0, 'Epsilon')
results_excel.write(0, 1, 'Successful classification')
results_excel.write(0, 2, 'Successful prediction')
results_excel.write(0, cntForEps+8, 'Iteration number')
results_excel.write(0, cntForEps+9, iterationNumber)
results_excel.write(0, cntForEps+12, 'Epsilon values')
transability = np.asarray(transability)
SVM_final = (transability[transability == 1].size) / (transability.size)
results_excel.write(3, 4, SVM_final)
for eps in epsilons_sex:
results_excel.write(0, cntForEps+13, epsilons_sex[cntForEps])
cntForEps = cntForEps + 1
def main():
print '*' * 30
if run_check('sheetlist.txt') == 0:
return 0
if run_check('location.txt') == 0:
return 0
file_name = 'report.xls'
is_file_exist = 0
if os.path.isfile(file_name):
print 'This "report.xls" is already, will be deleted'
os.remove(file_name)
is_file_exist = 1
print 'The "report.xls" has been deleted'
print 'write data to excel'
wb = Workbook()
sheet_list_file = open('sheetlist.txt', 'r')
sheet_list = sheet_list_file.readline().split('\t')
sheet_list_file.close()
number = len(sheet_list)
for index in range(number):
ws = wb.add_sheet(sheet_list[index].decode('utf-8'))
if index > 0:
row = 8
location_file = open('location.txt', 'r')
location_line = location_file.readline()
while location_line:
name = location_line.split('+')[0]
#找到相应sheet的位置信息，写入
if name in sheet_list[index]:
date = location_line.split('+')[1]
time_start = location_line.split('+')[2]
location_start = location_line.split('+')[3]
time_end = location_line.split('+')[4]
location_end = location_line.split('+')[5]
ws.write_merge(row, row + 2, 0, 0, date)
ws.write_merge(row, row + 2, 2, 2, time_start)
ws.write_merge(row, row + 2, 3, 3,
location_start.decode('utf-8'))
ws.write_merge(row, row + 2, 12, 12, time_end)
ws.write_merge(row, row + 2, 13, 13,
location_end.decode('utf-8'))
row += 3
location_line = location_file.readline()
location_file.close()
wb.save(file_name)
if is_file_exist == 0:
print 'report.xls has been created'
else:
print 'report.xls has been updated'
print '*' * 30
file_name2 = 'report_jicheng.xls'
is_file_exist = 0
sheet_list_merge_file = open('sheetlist_jicheng.txt', 'r')
sheet_list_merge = sheet_list_merge_file.readline().split('\t')
sheet_list_merge_file.close()
number2 = len(sheet_list_merge)
wb2 = Workbook()
if os.path.isfile(file_name2):
print 'This "gps_jicheng.xls" is already, will be deleted'
os.remove(file_name2)
is_file_exist = 1
print 'The "gps_jicheng.xls" has been deleted'
print 'write data to excel'
for index in range(number2):
ws = wb2.add_sheet(sheet_list_merge[index].decode('utf-8'))
row = 3
location_file = open('location.txt', 'r')
location_line = location_file.readline()
while location_line:
name = location_line.split('+')[0]
#找到相应sheet的位置信息，写入
if name in sheet_list_merge[index]:
date = location_line.split('+')[1]
time_start = location_line.split('+')[2]
location_start = location_line.split('+')[3]
time_end = location_line.split('+')[4]
location_end = location_line.split('+')[5]
work_time = calc_work_time(time_end, time_start)
if row == 3:
ws.write_merge(0, 0, 0, 5, 'GPS行程简表'.decode('utf-8'))
ws.write_merge(1, 1, 0, 0, '姓名:'.decode('utf-8'))
ws.write_merge(1, 1, 1, 1, name.decode('utf-8'))
ws.write_merge(2, 2, 0, 0, '日期'.decode('utf-8'))
ws.write_merge(2, 2, 1, 1, '出发地点'.decode('utf-8'))
ws.write_merge(2, 2, 2, 2, '拜访客户/终端'.decode('utf-8'))
ws.write_merge(2, 2, 3, 3, '收工地点'.decode('utf-8'))
ws.write_merge(2, 2, 4, 4, '出差天数'.decode('utf-8'))
ws.write_merge(2, 2, 5, 5, '备注'.decode('utf-8'))
ws.write_merge(row, row, 0, 0, date)
ws.write_merge(row, row, 1, 1, location_start.decode('utf-8'))
#ws.write_merge(row, row, 2, 2, '拜访客户/终端')
ws.write_merge(row, row, 3, 3, location_end.decode('utf-8'))
ws.write_merge(row, row, 4, 4, work_time)
#ws.write_merge(row, row, 5, 5, '备注')
row += 1
location_line = location_file.readline()
ws.write_merge(row + 1, row + 1, 0, 0, '合计'.decode('utf-8'))
location_file.close()
if is_file_exist == 0:
print 'report.xls has been created'
else:
print 'report.xls has been updated'
print '*' * 30
wb2.save(file_name2)
random.seed(seed)
ok = True
else:
print('Неверный ввод! Введите произвольное число или нажмите Enter, чтобы пропустить')
document = Document()
rb = open_workbook('data\\answers.xlsx')
sheet = rb.sheet_by_index(0)
font0 = Font()
font0.name = 'Times New Roman'
font0.colour_index = 2
font0.bold = True
wb = Workbook()
ws = wb.add_sheet('Лист1')
l = random.sample(l, ll)
f = open('data\\questions.txt', 'r').read()
for n, q in enumerate(l):
add_question(f, q, n+1, images, document, sheet, ws)
document.save('questions.docx')
wb.save('new_answers.xls')
print('Файл с вопросами: questions.docx')
print('Файл с ответами: new_answers.xls')
print('Нажмите Enter')
a = input()
def load_zhangdan(file_name):
#renming为每个分组的人员组成，zhangdan为整个账簿的信息
renming = [[1], [2], [3], [4]]
zhangdan = []
load_file(file_name)
Sheetname = workbook.sheet_names()
for name in range(len(Sheetname)):
table = workbook.sheets()[name]
nrows = table.nrows
title = table.row_values(1)
for x in range(len(title)):
if '.' in title[x]:
gongxuleixing = title[x].split(u'.')[0]
for y in renming:
if int(gongxuleixing) == y[0]:
zuhemingcheng = title[x].split(u'.')[1]
if u'/' in zuhemingcheng:
splitxingming = zuhemingcheng.split(u'/')
for z in splitxingming:
if z not in y:
y.append(z)
else:
y.append(zuhemingcheng)
for x in renming:
del x[0]
for n in range(2, nrows):
a = table.row_values(n)
mid = []
for i in range(len(a)):
if is_chinese(a[i]):
a[i].encode('utf-8')
elif is_num(a[i]) == 1:
if math.modf(a[i])[0] == 0 or a[i] == 0:
a[i] = int(a[i])
try:
a[i] = a[i].strip()
except:
pass
mid.append(title[i])
mid.append(a[i])
zhangdan.append(mid)
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
for i in range(len(zhangdan)):
for j in range(len(zhangdan[i])):
if is_chinese(zhangdan[i][j]):
zhangdan[i][j].encode('utf-8')
elif is_num(zhangdan[i][j]) == 1:
if math.modf(zhangdan[i][j])[0] == 0 or zhangdan[i][j] == 0:
zhangdan[i][j] = int(zhangdan[i][j])
sheet1.write(i, j, zhangdan[i][j])
book.save('allmesg.xls') # 存储excel
book = xlrd.open_workbook('allmesg.xls')
return renming, zhangdan
print('')
def fuzzy_matching(nodanzi, notaizhang):
pijiaoout = []
for j in range(len(nodanzi)):
mid3 = []
mid3.append(nodanzi[j][0])
for x in range(1, len(nodanzi[j])):
for y in range(1, len(notaizhang[j])):
flag = 0
if nodanzi[j][x][0] != notaizhang[j][y][0]:
flag += 10
if nodanzi[j][x][1] != notaizhang[j][y][1]:
flag += 10
if nodanzi[j][x][2] != notaizhang[j][y][2]:
flag += 10
if nodanzi[j][x][3] != notaizhang[j][y][3]:
flag += 15
if flag <= 14:
nodanzi[j][x][-1] = u'ok'
notaizhang[j][y].append(u'ok')
elif flag == 15:
mid = []
mid.extend(nodanzi[j][x])
mid.append(u'')
mid.append(u'')
if u'ok' not in mid:
mid.append(u'')
mid.append(u'')
mid.extend(notaizhang[j][y])
mid2 = []
for z in mid:
mid2.append(u'')
mid3.append(mid)
mid3.append(mid2)
nodanzi[j][x][-1] = u'ok'
notaizhang[j][y].append(u'ok')
pijiaoout.append(mid3)
book5 = Workbook()
for z in range(len(pijiaoout)):
sheet1 = book5.add_sheet(pijiaoout[z][0])
for i in range(1, len(pijiaoout[z])):
for j in range(len(pijiaoout[z][i])):
if is_chinese(pijiaoout[z][i][j]):
pijiaoout[z][i][j].encode('utf-8')
elif is_num(pijiaoout[z][i][j]) == 1:
if math.modf(pijiaoout[z][i]
[j])[0] == 0 or pijiaoout[z][i][j] == 0:
pijiaoout[z][i][j] = int(pijiaoout[z][i][j])
sheet1.write(i, j, pijiaoout[z][i][j])
book5.save('mohu.xls')
book5 = xlrd.open_workbook('mohu.xls')
# #模糊匹配失败的，查看前后是否有同型号，同客户，同数量的也算漏记
nodanzicopy = copy.deepcopy(nodanzi)
for x in range(len(nodanzi)):
for y in range(len(nodanzicopy)):
for z in range(1, len(nodanzi[x])):
for i in range(1, len(nodanzicopy[y])):
if len(nodanzi[x]) > 1 and len(nodanzicopy[y]) > 1:
if nodanzi[x][z][1:] == nodanzicopy[y][i][
1:] and nodanzi[x][0] != nodanzicopy[y][0]:
if (not (nodanzi[x][0] in renming[0]
and nodanzicopy[y][0] in renming[0])):
if nodanzi[x][z][-1] != 'ok' and nodanzicopy[
x][z][-1] != 'ok':
nodanzi[x][z][-1] = u'find'
# 模糊匹配都失败的单独拿出来
book6 = Workbook()
for z in range(len(nodanzi)):
sheet1 = book6.add_sheet(nodanzi[z][0])
line = -1
for i in range(1, len(nodanzi[z])):
if nodanzi[z][i][-1] != u'ok':
line = line + 1
for j in range(len(nodanzi[z][i])):
if is_chinese(nodanzi[z][i][j]):
nodanzi[z][i][j].encode('utf-8')
elif is_num(nodanzi[z][i][j]) == 1:
if math.modf(nodanzi[z][i]
[j])[0] == 0 or nodanzi[z][i][j] == 0:
nodanzi[z][i][j] = int(nodanzi[z][i][j])
sheet1.write(line, j, nodanzi[z][i][j])
line = -1
for i in range(1, len(notaizhang[z])):
if notaizhang[z][i][-1] != u'ok':
line = line + 1
for j in range(len(notaizhang[z][i])):
if is_chinese(notaizhang[z][i][j]):
notaizhang[z][i][j].encode('utf-8')
elif is_num(notaizhang[z][i][j]) == 1:
if math.modf(notaizhang[z][i]
[j])[0] == 0 or notaizhang[z][i][j] == 0:
notaizhang[z][i][j] = int(notaizhang[z][i][j])
sheet1.write(line, j + 6, notaizhang[z][i][j])
book6.save('lackmohu.xls')
book6 = xlrd.open_workbook('lackmohu.xls')
@author: myung
exam1.py : ssec1804.xls 파일에서 1.남자,1.여자 sheet의 데이터를
ssec1804mf.xls 파일에 남자,여자 sheet의 데이터로 저장하기
"""
from xlrd import open_workbook
from xlwt import Workbook
def makesheet(output_sheet): # output_sheet_female : "여자"인 sheet
for row_index in range(worksheet.nrows):
for column_index in range(worksheet.ncols):
output_sheet.write(row_index, column_index,
worksheet.cell_value(row_index, column_index))
# print(worksheet.cell_value(row_index, column_index))
infile = "ssec1804.xls"
outfile = "ssec1804mf.xls"
worksheet = None #전역변수
outworkbook = Workbook() #빈데이터. 출력될 xls 파일의 내용
output_sheet_male = outworkbook.add_sheet("남자") #outworkbook에 남자 sheet 추가
output_sheet_female = outworkbook.add_sheet("여자") #outworkbook에 여자 sheet 추가
#workbook : ssec1804.xls 의 모드 데이터
with open_workbook(infile) as workbook:
#worksheet : 1.남자 sheet 데이터
worksheet = workbook.sheet_by_name("1.남자")
makesheet(output_sheet_male)
worksheet = workbook.sheet_by_name("1.여자")
makesheet(output_sheet_female)
outworkbook.save(outfile)
from pathlib import Path
from random import uniform
from xlwt import easyxf, Workbook
wb = Workbook()
style0 = easyxf(
'font: name Times New Roman, color-index black, bold on',
num_format_str='#,##0.00'
)
style1 = easyxf(num_format_str='#,##0.00')
ws = wb.add_sheet('Device')
for index, header in enumerate((
'name',
'longitude',
'latitude',
'subtype',
'ip_address'
)):
ws.write(0, index, header)
for i in range(1, 10000):
ws.write(i, 0, i)
ws.write(i, 1, uniform(-40., 40.))
ws.write(i, 2, uniform(-40., 40.))
ws.write(i, 3, 'router')
ws.write(i, 4, i)
wb.save(Path.cwd() / 'test.xls')
create_user_password_security_context
#sys.path.append("/opt/anaconda3/lib/python3.7/site-packages")
sys.path.append(
"/Users/challagandlp/playground/python/nsxtapi/env-nsxt/lib/python3.7/site-packages"
)
#from configreader import configreader
from configreader.configreader import Configmap
# Output directory for Excel Workbook
#os.chdir("./Output")
# Setup excel workbook and worksheets
groups_wkbk = Workbook()
sheet1 = groups_wkbk.add_sheet('Groups')
#Set Excel Styling
style_db_center = xlwt.easyxf(
'pattern: pattern solid, fore_colour blue_grey;'
'font: colour white, bold True; align: horiz center')
style_alignleft = xlwt.easyxf(
'font: colour black, bold False; align: horiz left, wrap True')
#Setup Column widths
columnA = sheet1.col(0)
columnA.width = 256 * 30
columnB = sheet1.col(1)
columnB.width = 256 * 30
columnC = sheet1.col(2)
columnC.width = 256 * 30
from tempfile import TemporaryFile
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')
sheet1.write(0, 0, 'A1')
sheet1.write(0, 1, 'B1')
row1 = sheet1.row(1)
row1.write(0, 'A2')
row1.write(1, 'B2')
col1 = sheet1.col(1)
col1.write(0, 'C2')
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0, 'Sheet 2 A1')
sheet2.row(0).write(1, 'Sheet 2 B1')
sheet2.flush_row_data()
sheet2.write(1, 0, 'Sheet 2 A3')
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save('simple.xls')
book.save(TemporaryFile())
def getPanCard():
# from PIL import Image, ImageFile
x_index = 1
root.directory = filedialog.askdirectory()
mypath = root.directory
onlyfiles = [f for f in listdir(mypath) if isfile(join(mypath, f))]
files_length = len(onlyfiles)
# print(files_length)
# Workbook is created
wb = Workbook()
# add_sheet is used to create sheet.
sheet1 = wb.add_sheet('Sheet 1', cell_overwrite_ok=True)
# Write Headers to sheet
sheet1.write(0, 0, 'S.No')
sheet1.write(0, 1, 'PAN Number')
sheet1.write(0, 2, 'Date of Birth')
sheet1.write(0, 3, 'Name')
sheet1.write(0, 4, 'Fathers Name')
sheet1.write(0, 5, 'File Name')
ixsheet = 0
# print(files_lenth)
while ixsheet < files_length:
ImageFile.LOAD_TRUNCATED_IMAGES = True
x = mypath
dir_path = x.replace("'", "")
file_path = onlyfiles[ixsheet]
join_path = join(dir_path, file_path)
# print(join_path)
im = Image.open(join_path)
# load the example image and convert it to grayscale
image = cv2.imread(join_path)
gray = prePrcoessing(image)
# write the grayscale image to disk as a temporary file so we can
# apply OCR to it
filename = "{}.png".format(os.getpid())
cv2.imwrite(filename, gray)
'''
A blurring method may be applied. We apply a median blur when the --preprocess flag is set to blur.
Applying a median blur can help reduce salt and pepper noise, again making it easier for Tesseract
to correctly OCR the image.
After pre-processing the image, we use os.getpid to derive a temporary image filename based on the process ID
of our Python script.
The final step before using pytesseract for OCR is to write the pre-processed image, gray,
to disk saving it with the filename from above
'''
##############################################################################################################
######################################## Section 3: Running PyTesseract ######################################
##############################################################################################################
# load the image as a PIL/Pillow image, apply OCR, and then delete
# the temporary file
# pytesseract.pytesseract.tesseract_cmd = 'D:\\Tesseract-OCR\\tesseract.exe'
#from pytesseract import Output
#import pytesseract
text = pytesseract.image_to_string(Image.open(filename), lang='eng')
# add +hin after eng within the same argument to extract hindi specific text - change encoding to utf-8 while writing
os.remove(filename)
# print(text)
# show the output images
# cv2.imshow("Image", image)
# cv2.imshow("Output", gray)
# cv2.waitKey(0)
# writing extracted data into a text file
text_output = open('outputbase.txt', 'w', encoding='utf-8')
text_output.write(text)
text_output.close()
file = open('outputbase.txt', 'r', encoding='utf-8')
text = file.read()
# print(text)
# Cleaning all the gibberish text
bad_chars = [
'~', '`', '!', '@', '#', '$', '%', '^', '&', '*', '(', ')', '{',
'}', "'", '[', ']', '|', ':', ';', ',', '<', '>', '.', '?', '+',
'=', '_'
]
for i in bad_chars:
text = text.replace(i, '')
'''for god_damn in text:
if nonsense(god_damn):
text.remove(god_damn)
else:
print(text)'''
# print(text)
############################################################################################################
###################################### Section 4: Extract relevant information #############################
############################################################################################################
# Initializing data variable
name = None
fname = None
dob = None
pan = None
nameline = []
dobline = []
panline = []
text0 = []
text1 = []
text2 = []
# Searching for PAN
lines = text.split('\n')
for lin in lines:
s = lin.strip()
s = lin.replace('\n', '')
s = s.rstrip()
s = s.lstrip()
text1.append(s)
text1 = list(filter(None, text1))
# print(text1)
# to remove any text read from the image file which lies before the line 'Income Tax Department'
lineno = 0 # to start from the first line of the text file.
for wordline in text1:
xx = wordline.split('\n')
if ([
w for w in xx if re.search(
'(INCOMETAXDEPARWENT @|mcommx|INCOME|TAX|GOW|GOVT|GOVERNMENT|OVERNMENT|VERNMENT|DEPARTMENT|EPARTMENT|PARTMENT|ARTMENT|INDIA|NDIA)$',
w)
]):
text1 = list(text1)
lineno = text1.index(wordline)
break
# text1 = list(text1)
text0 = text1[lineno + 1:]
# print(text0) # Contains all the relevant extracted text in form of a list - uncomment to check
def findword(textlist, wordstring):
lineno = -1
for wordline in textlist:
xx = wordline.split()
if ([w for w in xx if re.search(wordstring, w)]):
lineno = textlist.index(wordline)
textlist = textlist[lineno + 1:]
return textlist
return textlist
###############################################################################################################
######################################### Section 5: Dishwasher part ##########################################
###############################################################################################################
try:
# Cleaning first names, better accuracy
name = text0[0]
name = name.rstrip()
name = name.lstrip()
name = name.replace("8", "B")
name = name.replace("0", "D")
name = name.replace("6", "G")
name = name.replace("1", "I")
name = re.sub('[^a-zA-Z] +', ' ', name)
# Cleaning Father's name
fname = text0[1]
fname = fname.rstrip()
fname = fname.lstrip()
fname = fname.replace("8", "S")
fname = fname.replace("0", "O")
fname = fname.replace("6", "G")
fname = fname.replace("1", "I")
fname = fname.replace("\"", "A")
fname = re.sub('[^a-zA-Z] +', ' ', fname)
# Cleaning DOB
dob = re.findall(r'\d{2}[-/|-]\d{2}[-/|-]\d{4}', text)
# Cleaning PAN Card details
text0 = findword(
text1,
'(Pormanam|Number|umber|Account|ccount|count|Permanent|ermanent|manent|wumm)$'
)
pan = re.findall(
r'\w{2}[a-zA-Z]\w{0}[P,C,H,A,B,G,J,L,F,T]\w{0}[A-Z]\w{3}[0-9]\w{0}[A-Z]',
text)
if pan == []:
pan = re.findall(r'\w{7}[A-Z]', text)
finlen = len(pan)
pan = pan[finlen - 1]
# print(pan)
except:
pass
# Making tuples of data
data = {}
data['Name'] = name
data['Father Name'] = fname
data['Date of Birth'] = dob
data['PAN'] = pan
sheet1.write(ixsheet + 1, 0, ixsheet + 1)
sheet1.write(ixsheet + 1, 1, data['PAN'])
sheet1.write(ixsheet + 1, 3, data['Name'])
sheet1.write(ixsheet + 1, 4, data['Father Name'])
if dob:
sheet1.write(ixsheet + 1, 2, data['Date of Birth'])
sheet1.write(ixsheet + 1, 5, join_path)
ixsheet = ixsheet + 1
wb.save('PAN CARD DATA.xls')
import sys
from xlrd import open_workbook, xldate_as_tuple
from xlwt import Workbook
from datetime import date
input_file = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('select_columns_all_worksheets')
my_columns = ['Customer Name', 'Sale Amount']
first_worksheet = True
with open_workbook(input_file) as workbook:
data = []
data.append(my_columns)
my_columns_index = []
for worksheet in workbook.sheets():
if first_worksheet:
header = worksheet.row_values(0)
for index in range(len(header)):
if header[index] in my_columns:
my_columns_index.append(index)
first_worksheet = False
for row_index in range(1, worksheet.nrows):
row_list = []
for col_index in my_columns_index:
cell_value = worksheet.cell_value(row_index, col_index)
cell_type = worksheet.cell_type(row_index, col_index)
if cell_type == 3:
date_cell = xldate_as_tuple(cell_value, workbook.datemode)
date_cell = data(*date_cell[0:3]).strftime('%m/%d/%Y')
