def write_1_n_liyang_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku不适配车型", 0)
insert_list = (u'liyang_id', u'品牌', u'厂家', u'车系', u'车型', u'年款', u'排量',
u'进气形式', u'最大功率', u'燃料类型', u'机油滤清器', u'滤清器尺寸')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for liyang_id, goods_set in self.liyang_car_goods_dict.iteritems():
if len(goods_set) < 2:
continue
liyang_data = self.liyang_id_data_dict[liyang_id]
for goods_format in goods_set:
goods_size = self.goods_dict[goods_format]
self.write_excel_cell(
the_sheet, is_sheet_num,
(liyang_id, liyang_data['car_brand'],
liyang_data['factory_name'], liyang_data['car_series'],
liyang_data['vehicle_type'], liyang_data['model_year'],
liyang_data['displacement'], liyang_data['intake_style'],
liyang_data['max_power'], liyang_data['fuel_type'],
goods_format, goods_size))
is_sheet_num += 1
ew.save(filename=self.file_parent + '/4.liyang车型对应多个机滤.xlsx')
def main():
if len(sys.argv) != 3:
print_help()
return
excel_file = Workbook()
excel_writer = ExcelWriter(workbook=excel_file)
sheet = excel_file.worksheets[0]
csv_file = open(sys.argv[1], "r")
row = 1
for line in csv_file:
line = line.strip().decode("utf-8", "ignore")
ary = line.split("@")
col = 1
for item in ary:
col_letter = get_column_letter(col)
sheet.cell("%s%s" % (col_letter, row)).value = "%s" % (item.encode("utf-8", "ignore"))
col = col + 1
row = row + 1
excel_writer.save(filename=sys.argv[2])
return
def save_search_data(get_list, session, location=0, num=0):
# location: 是否要获取目标地址,1获取，0不获取
wb = load_workbook(SAVE_FILE_NAME)
ew = ExcelWriter(wb)
ws = wb.get_sheet_by_name(SHEET_NAME)
for i in get_list:
i = i.encode('utf-8')
i += u''
if location == 0:
coordinate = None
else:
coordinate = get_location(i, session)
temp_replace = re.compile('<[\s\S]+?>')
i = temp_replace.sub(' ', i)
i += u'\n'
i = i.decode('string-escape').encode('gbk')
i = unicode(i, 'unicode-escape')
i = i.encode('utf-8')
if coordinate:
num += 1
longitude = coordinate[0]
latitude = coordinate[1]
ws.cell(row=num+1, column=2).value = longitude
ws.cell(row=num+1, column=3).value = latitude
ws.cell(row=num+1, column=1).value = i
elif num == 0:
num += 1
ws.cell(row=num+1, column=1).value = i
ew.save(SAVE_FILE_NAME)
print (num)
return num
def save_search_data(get_list, session, location=0, num=0):
# location: 是否要获取目标地址,1获取，0不获取
wb = load_workbook(SAVE_FILE_NAME)
ew = ExcelWriter(wb)
ws = wb.get_sheet_by_name(SHEET_NAME)
for i in get_list:
i = i.encode('utf-8')
i += u''
if location == 0:
coordinate = None
else:
coordinate = get_location(i, session)
temp_replace = re.compile('<[\s\S]+?>')
i = temp_replace.sub(' ', i)
i += u'\n'
i = i.decode('string-escape').encode('gbk')
i = unicode(i, 'unicode-escape')
i = i.encode('utf-8')
if coordinate:
num += 1
longitude = coordinate[0]
latitude = coordinate[1]
ws.cell(row=num + 1, column=2).value = longitude
ws.cell(row=num + 1, column=3).value = latitude
ws.cell(row=num + 1, column=1).value = i
elif num == 0:
num += 1
ws.cell(row=num + 1, column=1).value = i
ew.save(SAVE_FILE_NAME)
print(num)
return num
def saveAsExcel(self,
fileName,
sheetName='data',
matrixOnly=False,
appendFile=True):
if not haveOpenpyxl:
raise ImportError(
'openpyxl is required for saving files in Excel (xlsx) format, but was not found.'
)
if not fileName.endswith('.xlsx'):
fileName += '.xlsx'
#create or load the file
if appendFile and os.path.isfile(fileName):
wb = load_workbook(fileName)
newWorkbook = False
else:
wb = Workbook() #create new workbook
newWorkbook = True
ew = ExcelWriter(workbook=wb)
if newWorkbook:
ws = wb.worksheets[0]
ws.title = sheetName
else:
ws = wb.create_sheet()
ws.title = sheetName
#write the data
# trials data
ws.cell('A1').value = 'Trial No'
ws.cell('B1').value = 'Stimulis'
ws.cell('C1').value = 'Step Size'
ws.cell('D1').value = 'Response'
for orienN, stimuli in enumerate(self.stimuli):
rowN = orienN + 2
ws.cell(column=1, row=rowN).value = orienN + 1
ws.cell(column=2, row=rowN).value = stimuli
ws.cell(column=3, row=rowN).value = self.stepSizes[orienN]
ws.cell(column=4, row=rowN).value = self.data[orienN]
# reversals data
ws.cell('E1').value = 'Directions'
ws.cell('F1').value = 'Reversal Numbers'
for revN, revIntens in enumerate(self.directions):
rowN = revN + 2
ws.cell(column=5, row=rowN).value = self.directions[revN]
ws.cell(column=6, row=rowN).value = self.Reversals[revN]
# other data
col = 1
for key in self.otherData.keys():
ws.cell(column=col + 6, row=1).value = key
for rtN, rtIntens in enumerate(self.otherData[key]):
rowN = rtN + 2
ws.cell(column=col + 6, row=rowN).value = rtIntens
col += 1
ew.save(filename=fileName)
def write_to_excel_with_openpyxl(self, records, head_row, save_excel_name="save.xlsx"):
#新建一个workbook
wb = Workbook()
#新建一个excelWriter
ew = ExcelWriter(workbook=wb)
#设置文件输出路径与名称
dest_filename = save_excel_name.decode('utf-8')
#第一个sheet是ws
ws = wb.worksheets[0]
#设置ws的名称
ws.title = "range names"
#写第一行，标题行
for h_x in range(1, len(head_row) + 1):
h_col = get_column_letter(h_x)
#print h_col
ws.cell('%s%s' % (h_col, 1)).value = '%s' % (head_row[h_x - 1])
i = 2
for record in records:
record_list = str(record).strip().split("\t")
for x in range(1, len(record_list) + 1):
col = get_column_letter(x)
ws.cell('%s%s' % (col, i)).value = '%s' % (record_list[x - 1].decode('utf-8'))
i += 1
ew.save(filename=dest_filename)
def save_dump(workbook, filename):
if workbook.worksheets == []:
workbook.create_sheet()
writer = ExcelWriter(workbook)
writer.comment_writer = DumpCommentWriter
writer.save(filename)
return True
def WriteExcel(tFileName, tDict):
if len(tFileName) == 0:
return
# 新建一个workbook
wb = Workbook()
# 新建一个excelWriter
ew = ExcelWriter(workbook=wb)
# 第一个sheet是ws
ws = wb.worksheets[0]
# wb.add_sheet()
# 设置ws的名称
ws.title = "第一主播"
# 设置文件输出路径与名称
ws.cell("A1").value = '23'
ws.cell("B1").value = '34'
# 向某个单元格中写入数据
lIndex = 1
for itorTuple in tDict:
lIndex += 1
cellA = str.format("A%s"% lIndex)
cellB = str.format("B%s"% lIndex)
ws.cell(cellA).value = itorTuple[0]
ws.cell(cellB).value = itorTuple[1]
# 最后保存文件
ew.save(filename=tFileName)
def save_excel_Eastern(total_info, updata_xlsx_file):
from openpyxl.reader.excel import load_workbook #讀取用
from openpyxl.writer.excel import ExcelWriter #存檔用
wb = load_workbook(updata_xlsx_file)
ws = wb.get_sheet_by_name("工作表1")
row_counts = ws.max_row #取得行列數
range_string = "A{}:H{}".format(row_counts + 1,
row_counts + 10) #一共三筆資料要存放
data = ws.iter_rows(range_string) #取出空的欄位
'''前置區 把抓到資料用城可讀取'''
abc_list = [] #給存放的list 才可以到 row
for i in total_info:
abc_list.append(i)
"""運算區 資料塞進到excel"""
row = 0 #從這 抓進去的資料從第0行開始
for a in data:
column = 0 #從這 抓進去的資列從第0欄開始
for cell in a:
cell.value = abc_list[row][column]
column = column + 1
row = row + 1
ew = ExcelWriter(workbook=wb) #新建立一個 excelWriter
ew.save(filename=updata_xlsx_file)
def writer_excel_testresult(self, case_list, result_info_list):
log_msg = a.RiZhi()
log_msg.log_def()
try:
wb = load_workbook(self.casefile_path)
ew = ExcelWriter(workbook=wb)
ws_sheet = wb.get_sheet_by_name(self.testsheetname)
row_sum = len(ws_sheet.rows)
#tmp_case_list = self.read_excel_caseinfo()
#print('excel中读取的caselist',tmp_case_list)
print('执行caselist:', case_list)
print('测试结果：', result_info_list)
print('开始回填结果判断')
i = 0
for case_id in case_list:
#for case_id_tmp in tmp_case_list:
#if case_id_tmp == case_id:
for row in range(3, row_sum):
case_id_TM = ws_sheet.cell('D%s' % row).value
if case_id == case_id_TM:
result_info_tmp = result_info_list[i]
ws_sheet.cell('o%s' % row).value = result_info_tmp
print('%s的结果回填完毕' % case_id)
i = i + 1
print(i)
else:
print('该case未执行，继续往下填写测试结果')
#else:
#print('执行case与用例case不匹配，继续查找。。。')
ew.save('test_result' + '.xlsx')
except Exception as e:
print("读取error %s" % e)
def write_sku_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku适配车型", 0)
insert_list = (u'云修号', u'car_id', u'品牌', u'车系', u'车型', u'排量', u'年款',
u'名称')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for yun_id, car_id_set in self.goods_car_dict.iteritems():
if len(car_id_set) == 0:
self.write_excel_cell(the_sheet, is_sheet_num,
(yun_id, u'None', u'None', u'None',
u'None', u'None', u'None', u'None'))
is_sheet_num += 1
else:
for car_id in car_id_set:
if car_id in self.not_car_list:
self.not_car_list.remove(car_id)
# id,brand,series,model,power,year,name
goods_car_data = self.car_category_dict[car_id]
self.write_excel_cell(
the_sheet, is_sheet_num,
(yun_id, str(car_id), str(goods_car_data['brand']),
str(goods_car_data['series']),
str(goods_car_data['model']),
str(goods_car_data['power']),
str(goods_car_data['year']),
str(goods_car_data['name'])))
is_sheet_num += 1
ew.save(filename=self.file_parent + '/1.滤清器适配车型.xlsx')
def wirteContent(file, data, cell):
wb = load_workbook(file)
ew = ExcelWriter(wb)
ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])
ws.cell(cell).value = data
ws.title = os.path.splitext(file)[0]
ew.save(file)
def write_to_xlsx_file(content, keys, file_path):
if os.path.exists(file_path) == False:
createFile(file_path)
wb = openpyxl.Workbook()
ew = ExcelWriter(workbook=wb)
wsheet = wb.worksheets[0]
is_close = raw_input('please be sure to close your xlsx file (y/n)?')
if is_close != 'y':
print '未关闭文件，退出程序'
return
rows = [row for row in wsheet.iter_rows(get_content_range_in_xlsx(keys.__len__() + 1))]
rows[0][0].value = TITLE_ANDROID_KEY
rows[0][1].value = TITLE_CN_STRING_VALUE
rows[0][2].value = TITLE_EN_STRING_VALUE
rows[0][3].value = TITLE_REMARKS
for i in range(0, keys.__len__()):
rows[i + 1][0].value = keys[i]
rows[i + 1][1].value = content[keys[i]]
continue
# print rows[i + 1][0].value, rows[i + 1][1].value
ew.save(file_path)
return
def write_to_excel(excel_file, data_list, column_width=20.0):
wb = Workbook()
excel_writer = ExcelWriter(workbook=wb)
sheet = wb.worksheets[0]
sheet.name = ''
merge_row = -1
for row_index, row_list in enumerate(data_list):
if len(row_list) == 1: # 合并单元格
merge_row = row_index + 1
for cell_index, cell in enumerate(row_list):
sheet.cell(row=row_index + 1,
column=cell_index + 1).value = cell
# 设置列宽度
max_column = sheet.max_column
for x in range(max_column):
sheet.column_dimensions[chr(65 + x)].width = column_width
# 合并单元格，设置单元格样式
if merge_row >= 0:
sheet.merge_cells('A1:%s1' % (chr(65 + max_column - 1)), 1, 1, 1,
max_column - 1)
cell = sheet.cell('A1')
font = Font(name=u'宋体', size=14, color=BLACK, bold=True)
align = Alignment(horizontal='center', vertical='center')
cell.font = font
cell.alignment = align
excel_writer.save(excel_file)
def save_dump(workbook, filename):
if workbook.worksheets == []:
workbook.create_sheet()
writer = ExcelWriter(workbook)
writer.comment_writer = DumpCommentWriter
writer.save(filename)
return True
def main(self):
print '=============start================'
# 10752 110884 211269 311294 411446
min_id = '411446'
file_index = 4
all_truck_car_sql = "select id,car_name,batch_number,car_type_id,car_category_id,car_remark from sc_truck_car where source= '商车网' and batch_number != '' and id > " + min_id + " order by id limit 100000"
print 'all_truck_car_sql : %s' % all_truck_car_sql
truck_array = self.dao.db.get_data(all_truck_car_sql)
truck_index = 1
max_size = 50000
wb = Workbook()
ew = ExcelWriter(workbook=wb)
self.create_heard(wb)
file_name = self.file_name.replace("NUMBER", str(file_index))
for truck_data in truck_array:
# if truck_index > max_size:
# ew.save(filename=file_name)
# truck_index = 1
# wb = Workbook()
# ew = ExcelWriter(workbook=wb)
# self.create_heard(wb)
# file_index += 1
# file_name = self.file_name.replace("NUMBER", str(file_index))
truck_index += 1
truck_id = truck_data['id']
# 数据存第一个sheet
self.car_sheet.cell(row=truck_index, column=1).value = truck_id
self.car_sheet.cell(row=truck_index,
column=2).value = truck_data['car_name']
self.car_sheet.cell(row=truck_index,
column=3).value = truck_data['batch_number']
self.car_sheet.cell(
row=truck_index,
column=4).value = self.type_name[truck_data['car_type_id']]
self.car_sheet.cell(
row=truck_index,
column=5).value = self.car_name[truck_data['car_category_id']]
self.car_sheet.cell(row=truck_index, column=6).value = self.source
self.car_sheet.cell(row=truck_index,
column=7).value = truck_data['car_remark']
# self.car_sheet.write(truck_index, 0, truck_id)
# self.car_sheet.write(truck_index, 1, truck_data['car_name'])
# self.car_sheet.write(truck_index, 2, truck_data['batch_number'])
# self.car_sheet.write(truck_index, 3, self.type_name[truck_data['car_type_id']])
# self.car_sheet.write(truck_index, 4, self.car_name[truck_data['car_category_id']])
# self.car_sheet.write(truck_index, 5, self.source)
# self.car_sheet.write(truck_index, 6, truck_data['car_remark'])
# 第二个sheet
self.add_data_to_other_sheet(truck_id)
if truck_index != 1:
ew.save(filename=file_name)
print 'end'
def save(self, xls_file):
"""Save log items as Excel files.
Args:
xls_file (str): path to the Excel file.
"""
ew = ExcelWriter(workbook=self.ner_log_workbook)
ew.save(filename=xls_file)
def __enter__(self):
wb = openpyxl.Workbook()
self.archive = ZipFile(self, 'w', ZIP_DEFLATED, allowZip64=True)
self.ws = wb.active
self.writer = ExcelWriter(wb, self.archive)
if self.head:
self.ws.append(self.head)
return self
def prepare_workbook(self):
wb = openpyxl.Workbook()
self.file = io.BytesIO()
self.archive = ZipFile(self.file, 'w', ZIP_DEFLATED)
self.ws = wb.active
self.writer = ExcelWriter(wb, self.archive)
if self.head:
self.ws.append(self.head)
def get_file(self):
fp = BytesIO()
archive = ZipFile(fp, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(self.work_book, archive)
writer.write_data()
archive.close()
fp.flush()
fp.seek(0)
return fp.read()
def dump(obj: Union[Dict, Iterable], file: Union[str, BinaryIO, PathLike]):
"""
Dumps an object hierarchy into an xlsx/xlsm file.
"""
# Create a new workbook and delete the default sheet.
workbook = Workbook()
workbook.remove(workbook.active)
header_font = Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
# Loop through all tables returned by object_hierarchy_to_tables.
for length, table in object_hierarchy_to_tables(obj).items():
# If it's a table of scalar elements (aka. length 0), create
# two columns. The first column has the element name and the
# second one the value.
if length == 0:
worksheet = workbook.create_sheet(f"scalar")
for i, row in enumerate(table.items()):
worksheet.append(row)
worksheet[i+1][0].font = header_font
else:
# For any other length create a header with the element names
# and list the values beneath them.
worksheet = workbook.create_sheet(f"size {str(length)}")
# Create the header and set the font
worksheet.append(tuple(table.keys()))
for i in range(len(table)):
worksheet[1][i].font = header_font
# Loop row by row, and list the values under the headers.
for row in zip(*[values for _, values in table.items()]):
worksheet.append(row)
# Freeze the first row.
worksheet.freeze_panes = "A2"
# Adjust the width of the columns to make sure the headers are visible.
adjust_column_width(worksheet)
# Create zip archive in with to write the excel data.
archive = ZipFile(file, 'w', ZIP_DEFLATED, allowZip64=True)
# Try to write the excel data.
writer = ExcelWriter(workbook, archive)
try:
writer.write_data()
finally:
archive.close()
def guardarWorkbook(workbook):
bufer = BytesIO()
writer = ExcelWriter(
workbook,
ZipFile(bufer, "w", compression=ZIP_STORED, allowZip64=False)
)
writer.save()
bytes = bufer.getvalue()
del(bufer)
return bytes
def main(self, fileName):
print '====start ======'
wb = Workbook()
ew = ExcelWriter(workbook=wb)
self.add_header_to_sheet(wb)
self.add_data_to_sheet(wb)
ew.save(filename=fileName)
print '====end ======'
def save_virtual_workbook(workbook, ):
"""Return an in-memory workbook, suitable for a Django response."""
temp_buffer = BytesIO()
archive = ZipFile(temp_buffer, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(workbook, archive)
try:
writer.write_data()
finally:
archive.close()
virtual_workbook = temp_buffer.getvalue()
temp_buffer.close()
return virtual_workbook
def lst2xl(records, fileName, row):
'''将list写入excel2007 需要指定写入的行'''
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.cell import get_column_letter
wb = Workbook()
ew = ExcelWriter(workbook=wb)
ws = wb.worksheets[0]
ws.title = '1'
for record in records:
for x in range(1, len(record) + 1):
col = get_column_letter(x)
ws.cell('%s%s' % (col, row)).value = '%s' % (record[x - 1])
row += 1
ew.save(filename=fileName)
def write_excel(filename, sheetname,headings, data):
wb=Workbook()
ew=ExcelWriter(workbook=wb)
ws=wb.worksheets[0]
ws.title=sheetname
#begin to write the headings
row=1
for i,heading in enumerate(headings):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,row)).value='%s'%(heading)
for row,rowdata in enumerate(data):
for i,celldata in enumerate(rowdata):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,(row+2))).value='%s'%(celldata)
ew.save(filename)
def store_xlsx(data,high,width):
accout=getpass.getuser()
excel=Workbook()
excel_writer=ExcelWriter(workbook = excel)
excel_outpath= r'/home/%s/mapdata/test1.xlsx'%accout
sheet = excel.create_sheet()
sheet=excel.worksheets[0]
sheet.title='testmap'
for row in range (1,(high+1)):
for col in range (1,(width+1)):
column = get_column_letter(col)
sheet.cell('%s%s'%(column,row)).value='%s'%data[row-1][col-1]
for col in range(1,width+1):
column = get_column_letter(col)
sheet.column_dimensions['%s'%column].width = 2.6
for row in range(1,high+1):
sheet.row_dimensions[row].height =14.15
sheet = excel.create_sheet()
sheet.title='parameters'
sheet.cell('A1').value='parameters'
sheet.cell('A2').value='This represents a 2-D grid map, in which each cell represents the probability of occupancy.'
sheet.cell('A7').value='Map width [cells]'
sheet.cell('A8').value='%s'%width
sheet.cell('C7').value='Map height [cells]'
sheet.cell('C8').value='%s'%high
excel.save(filename=excel_outpath)
print 'saving process done'
return 'ok'
def write_excel(filename, sheetname, headings, data):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
ws = wb.worksheets[0]
ws.title = sheetname
#begin to write the headings
row = 1
for i, heading in enumerate(headings):
col = get_column_letter(i + 1)
ws.cell('%s%s' % (col, row)).value = '%s' % (heading)
for row, rowdata in enumerate(data):
for i, celldata in enumerate(rowdata):
col = get_column_letter(i + 1)
ws.cell('%s%s' % (col, (row + 2))).value = '%s' % (celldata)
ew.save(filename)
def export(title,header,rows):
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = r'/opt/aoaola/web/html/excel/' + title +'.xlsx'
ws = wb.worksheets[0]
for x in range(1,len(header)+1):
col = get_column_letter(x)
ws.cell('%s%s'%(col, 1)).value = '%s' % (header[x-1])
for i in range(len(rows)):
assert len(rows[i]) == 6
for j in range(len(rows[i])):
ws.cell(row=i+1,column=j).value = rows[i][j]
ew.save(filename = dest_filename)
def txtToCSV(pdf, omit_pages):
for i, item in enumerate(pdf):
pages = layout.get_pages(item)
text = []
for index, page in enumerate(pages):
if index+1 not in omit_pages[i]:
text.append(page)
text = ''.join(text)
sents = sent_tokenize(text)
wb = Workbook()
ew = ExcelWriter(workbook=wb)
dest_filename = item.split('.')[0]+'.xlsx'
ws = wb.worksheets[0]
for index, line in enumerate(sents):
ws.cell(row=index, column=0).value = line
ew.save(filename=dest_filename)
class XLSXResponse(web.StreamResponse):
def __init__(self,
request,
*args,
fields=None,
head=None,
filename=None,
**kwargs):
if filename:
headers = kwargs.setdefault('headers', {})
v = 'attachment; filename="{}"'.format(filename)
headers['Content-Disposition'] = v
super().__init__(*args, **kwargs)
self.content_type = 'application/xlsx'
self.request = request
self.fields = fields
self.head = head
def prepare_workbook(self):
wb = openpyxl.Workbook()
self.file = io.BytesIO()
self.archive = ZipFile(self.file, 'w', ZIP_DEFLATED)
self.ws = wb.active
self.writer = ExcelWriter(wb, self.archive)
if self.head:
self.ws.append(self.head)
def append(self, data):
if not self.fields:
self.fields = list(data)
if self.head is None:
self.ws.append(self.fields)
self.ws.append([data[i] for i in self.fields])
def write_workbook(self):
self.writer.write_data()
self.archive.close()
async def __aenter__(self):
await self.prepare(self.request)
self.prepare_workbook()
return self
async def __aexit__(self, exc_type, exc, tb):
loop = asyncio.get_event_loop()
await loop.run_in_executor(None, self.write_workbook)
await super().write(self.file.getbuffer())
def create(self, tables, pdic):
doc = Workbook()
ew = ExcelWriter(workbook=doc)
for table in tables:
sheet = doc.worksheets[0]
sheet.title = table
i = 0
for port in tables[table]:
c = sheet.cell(row=0, column=i)
c.value = port + "[" + ` pdic[port][2] ` + "]"
i += 1
try:
ew.save(filename=self.file_name)
return True
except:
return False
def create(self, tables, pdic):
doc = Workbook()
ew = ExcelWriter(workbook=doc)
for table in tables:
sheet = doc.worksheets[0]
sheet.title = table
i = 0
for port in tables[table]:
c = sheet.cell(row=0, column=i)
c.value = port + '[' + ` pdic[port][2] ` + ']'
i += 1
try:
ew.save(filename=self.file_name)
return True
except:
return False
def new_excel_writer(self):
"""
:return: 返回一个写入Excel的对象
"""
wb = workbook()
ew = ExcelWriter(workbook=wb)
return ew
def write_not_car_excel(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
the_sheet = wb.create_sheet(u"sku不适配车型", 0)
insert_list = (u'car_id', u'品牌', u'车系', u'车型', u'排量', u'年款', u'名称')
self.write_excel_cell(the_sheet, 1, insert_list)
is_sheet_num = 2
for car_id in self.not_car_list:
goods_car_data = self.car_category_dict[car_id]
self.write_excel_cell(
the_sheet, is_sheet_num,
(str(car_id), str(goods_car_data['brand']),
str(goods_car_data['series']), str(goods_car_data['model']),
str(goods_car_data['power']), str(
goods_car_data['year']), str(goods_car_data['name'])))
is_sheet_num += 1
ew.save(filename=self.file_parent + '/2.滤清器不适配车型.xlsx')
class Excel2007Write():
excelWriter = path = workBook = sheet = None;
def __init__(self, path):
self.path = path
self.workBook = Workbook()
self.excelWriter = ExcelWriter(workbook=self.workBook)
def createSheet(self, index, name):
sheet = self.workBook.worksheets[index]
sheet.title = name
return sheet
def set(self, sheet_obj, x, y, value):
col = get_column_letter(x + 1)
sheet_obj.cell('%s%s' % (col, y + 1)).value = value
def save(self):
self.excelWriter.save(filename=path)
def main(self):
wb = Workbook()
ew = ExcelWriter(workbook=wb)
car_sheet = wb.create_sheet(u"新老分类", 0)
# 第一行
car_sheet.cell(row=1, column=1).value = u'一级分类'
car_sheet.cell(row=1, column=2).value = u'二级分类'
# 其余行
row_num = 2
for cat_id in self.second_cat_list:
car_sheet.cell(row=row_num, column=1).value = self.cate_id_name[
self.cate_id_parent[cat_id]]
car_sheet.cell(row=row_num,
column=2).value = self.cate_id_name[cat_id]
row_num += 1
ew.save(filename=self.file_name)
def test_write_images():
wb = Workbook()
ew = ExcelWriter(workbook=wb)
from openpyxl.drawing import Image
imagepath = os.path.join(DATADIR, "plain.png")
img = Image(imagepath)
buf = BytesIO()
archive = zipfile.ZipFile(buf, 'w')
ew._write_images([img], archive, 1)
archive.close()
buf.seek(0)
archive = zipfile.ZipFile(buf, 'r')
zipinfo = archive.infolist()
assert len(zipinfo) == 1
assert zipinfo[0].filename == 'xl/media/image1.png'
def test_write_images():
wb = Workbook()
ew = ExcelWriter(workbook=wb)
from openpyxl.drawing import Image
imagepath = os.path.join(DATADIR, "plain.png")
img = Image(imagepath)
buf = BytesIO()
archive = zipfile.ZipFile(buf, 'w')
ew._write_images([img], archive, 1)
archive.close()
buf.seek(0)
archive = zipfile.ZipFile(buf, 'r')
zipinfo = archive.infolist()
assert len(zipinfo) == 1
assert zipinfo[0].filename == 'xl/media/image1.png'
def write_to_excel_by_sheet_data_list(excel_file, sheet_data_list):
"""
数据写入多个sheet
:param excel_file:
:param sheet_data_list: sheet 数据字典列表
:return:
"""
wb = Workbook()
excel_writer = ExcelWriter(workbook=wb)
for sheet_index, sheet_data in enumerate(sheet_data_list):
if sheet_index == 0:
sheet = wb.worksheets[sheet_index]
else:
sheet = wb.create_sheet()
data_list = sheet_data['data_list']
column_width = 20.0
merge_row = -1
if 'title' in sheet_data:
sheet.title = sheet_data['title']
if 'column_width' in sheet_data:
column_width = sheet_data['column_width']
for row_index, row_list in enumerate(data_list):
if len(row_list) == 1: # 合并单元格
merge_row = row_index + 1
for cell_index, cell_value in enumerate(row_list):
sheet.cell(row=row_index + 1,
column=cell_index + 1).value = cell_value
# 设置列宽度
max_column = sheet.max_column
for x in range(max_column):
sheet.column_dimensions[chr(65 + x)].width = column_width
# 合并单元格，设置单元格样式
if merge_row >= 0:
sheet.merge_cells('A1:%s1' % (chr(65 + max_column - 1)), 1, 1,
1, max_column - 1)
cell = sheet.cell('A1')
font = Font(name=u'宋体', size=14, color=BLACK, bold=True)
align = Alignment(horizontal='center', vertical='center')
cell.font = font
cell.alignment = align
excel_writer.save(excel_file)
def test_write_images(datadir):
datadir.chdir()
wb = Workbook()
ew = ExcelWriter(workbook=wb)
from openpyxl.drawing import Image
img = Image("plain.png")
buf = BytesIO()
archive = zipfile.ZipFile(buf, "w")
ew._write_images([img], archive, 1)
archive.close()
buf.seek(0)
archive = zipfile.ZipFile(buf, "r")
zipinfo = archive.infolist()
assert len(zipinfo) == 1
assert zipinfo[0].filename == "xl/media/image1.png"
def test_write_images(datadir):
datadir.chdir()
wb = Workbook()
ew = ExcelWriter(workbook=wb)
from openpyxl.drawing.image import Image
img = Image("plain.png")
wb._images.append(ref(img))
buf = BytesIO()
archive = zipfile.ZipFile(buf, 'w')
ew._write_images(archive)
archive.close()
buf.seek(0)
archive = zipfile.ZipFile(buf, 'r')
zipinfo = archive.infolist()
assert len(zipinfo) == 1
assert zipinfo[0].filename == 'xl/media/image1.png'
def to_bytes(self, table, **kargs):
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
# Determine columns. We may need an extra (first) column which 'names' the row
columns = list(map(str, list(table.get_columns())))
ws.append(columns)
# Write rows to worksheet
for row in table.get_rows():
ws.append(tuple(_get_values(table, row)))
writer = ExcelWriter(wb)
# Need to do a little bit more work here, since the openpyxl library only
# supports writing to a filename, while we need a buffer here..
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, 'w', zipfile.ZIP_DEFLATED) as zf:
writer.write_data(zf)
return buffer.getvalue()
def to_bytes(self, table, **kargs):
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
# Determine columns. We may need an extra (first) column which 'names' the row
columns = list(map(str, list(table.get_columns())))
ws.append(columns)
# Write rows to worksheet
for row in table.get_rows():
ws.append(tuple(_get_values(table, row)))
writer = ExcelWriter(wb)
# Need to do a little bit more work here, since the openpyxl library only
# supports writing to a filename, while we need a buffer here..
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, 'w', zipfile.ZIP_DEFLATED) as zf:
writer.write_data(zf)
return buffer.getvalue()
def write_excel(directory,filename, sheetname,headings, data,comment=None):
wb=Workbook()
ew=ExcelWriter(workbook=wb)
ws=wb.worksheets[0]
ws.title=sheetname
#begin to write the headings
row=1
for i,heading in enumerate(headings):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,row)).value='%s'%(heading)
#write the cell content
for row,rowdata in enumerate(data):
for i,celldataKey in enumerate(rowdata):
col=get_column_letter(i+1)
ws.cell('%s%s'%(col,(row+2))).value='%s'%(rowdata[celldataKey])
if not os.path.exists(directory):
os.makedirs(directory)
ew.save(directory+filename)
def sendExcel(fileDir,matrixList,sheetNameList):
#check that input lists have same length (one sheet name for each matrix)
if len(matrixList) != len(sheetNameList):
print 'List of variables being exported and of sheet names must be equal.'
return
#Set up excel workbook
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = fileDir
#Create sheet and export data for every matrix being exported
for matrix,sheetN in zip(matrixList,sheetNameList):
ws = wb.create_sheet()
ws.title = sheetN
#For a matrix
if len(matrix.shape) == 2:
#Dump matrix in the Excel sheet
(r,c) = matrix.shape
for element in xrange(c):
col = get_column_letter(element+2) #2, so it will start printing on B
for row in xrange(r):
ws.cell('%s%s'%(col, row+2)).value = matrix[row][element]
#For a vector
elif len(matrix.shape) == 1:
r = matrix.shape
r = r[0]
col = get_column_letter(2) #2, so it will start printing on B
for row in xrange(r):
ws.cell('%s%s'%(col, row+2)).value = matrix[row]
else:
print 'Only exports matrices and vectors!'
print 'Weird element found. \n'
return
## #Create another sheet in same workbook and put Pi
## ws = wb.create_sheet()
## ws.title = 'Pi'
## ws.cell('F5').value = 3.14
ew.save(dest_filename)
print 'Matrix has been exported to Excel.'
class XlsxExcel(AbstractExcel):
'''处理Excel 2007的子类'''
eb = None
def __init__(self, excel_name):
AbstractExcel.__init__(self, excel_name)
def OpenExcel(self):
if not self.exists:
return False
self.wb = load_workbook(self.ename)
return self
def GetSheet(self, num):
self.sh = self.wb.get_sheet_by_name(self.wb.get_sheet_names()[int(num)])
return self
def GetRowsNum(self):
return self.sh.get_highest_row()
def GetColsNum(self):
return self.sh.get_highest_col()
def GetCell(self, _row, _column):
return str(self.sh.cell(row = _row, column = _column).value)
def CreateExcel(self):
self.eb = Workbook()
self.w_wb = ExcelWriter(workbook = self.eb)
return self
def CreateSheet(self, sheet_name):
self.w_sh = self.eb.worksheets[int(sheet_name)]
return self
def SetCell(self, _row, _column, content):
self.w_sh.cell(row = _row, column = _column).value = content
return self
def Save(self):
self.w_wb.save(filename = self.ename)
conn.select_db('DW_VTMetrics')
cur=conn.cursor()
get_all = "select * from video_trackingMeta"
print get_all
data = cur.execute(get_all)
data_list = cur.fetchall()
cur.close()
conn.close()
wb = Workbook()
ws = wb.worksheets[0]
ws.title = 'video_trackingMeta'
#ws.column_dimensions["A"].width = 35.0
ew = ExcelWriter(workbook = wb)
file_name = r'/Job/datawarehouse/opertaion/mailsender/data/video_trackingMeta.xlsx'
rows = len(data_list)
cols = len(data_list[0])
for rx in range(rows):
for cx in range(cols):
ws.cell(row = rx + 1, column = cx + 1).value = data_list[rx][cx]
ew.save(filename = file_name)
# iterate through rows, then each cell in row and print value
for row in ws.iter_rows():
for cell in row:
print cell.value
print "done"
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.cell import get_column_letter
# make new workbook
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = r'empty_book.xlsx'
ws = wb.worksheets[0]
ws.title = "range names"
# run through 1 - 40 columns and 1 - 600 rows and fill them with row/column name
for col_idx in xrange(1, 40):
col = get_column_letter(col_idx)
for row in xrange(1, 600):
ws.cell('%s%s'%(col, row)).value = '%s%s' % (col, row)
#create a new sheet and call it 'Pi'
ws = wb.create_sheet()
url='http://creditcard.pingan.com/cms-tmplt/creditecard/searchPreferentialInformation.do'
data = get_request(url)
chardit = chardet.detect(data)
page = etree.HTML(data.decode('utf-8'))
print chardit
list=page.xpath("/html/body/div[1]/div[2]/div[1]/div[1]/form/div[1]/div/div/ul/div/li/a")
#print list
citys=set([i.text for i in list])
#citys=set(["北京"])
print len(citys)
citysnum=0
wb = Workbook() # 创建工作薄
ew = ExcelWriter(workbook=wb) # 写入工作薄对象
ws = wb.worksheets[0] # 默认第一个表格
ws.title = "data"
cellnum = 1
titlelist=["名称","内容","范围","有效期","url","搜索量","点赞量"]
for i in range(len(titlelist)):
ws.cell(row=cellnum, column=i + 1).value = titlelist[i]
ew.save(filename='result.xlsx')
cellnum += 1
for city in citys:
refer = {"area": "", "city": city, "searchType": "new", "searchtitle": "", "shoparea": "", "shopptype": "",
"shopstype": ""}
citydata=post(url,refer)
citypage = etree.HTML(citydata.decode('utf-8'))
pagenum = citypage.xpath("/html/body/div[1]/div[2]/div[1]/div[2]/div[2]/dl/dd[2]/div/p[1]/a/span")[0].text
def create_excel(self,title,interface_info,all_case_num,all_pass_case_num,all_check_num):
# 新建一个ExcelWriter
excel_work = ExcelWriter(workbook = self.work_book)
# 设置文件输出路径与名称
dest_filename = self.excel_name + self.excel_suffix
# 创建sheet
for sheet_name in self.sheet_names:
ws = self.work_book.create_sheet()
ws.title = sheet_name
# ==============================================================================================================
# 根据sheet名称获取该sheet对象
# 第一个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[0])
'''
合并单元格
源码中该方法的定义
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
所以可以直接使用后面四个参数
'''
ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=9)
c_a1 = ws['A1']
c_a3 = ws['A3']
c_b3 = ws['B3']
c_c3 = ws['C3']
c_d3 = ws['D3']
c_e3 = ws['E3']
c_f3 = ws['F3']
c_g3 = ws['G3']
c_h3 = ws['H3']
c_i3 = ws['I3']
# 接口测试设计明细 sheet页的通用样式
general_style = Style(
font=Font(
name=u'微软雅黑',
size=10
),
fill=PatternFill(
start_color='DCE6F1',
end_color='DCE6F1',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_a3.value = u'涉及模块'
c_b3.value = u'接口数量'
c_c3.value = u'接口名称'
c_d3.value = u'测试Owner'
c_e3.value = u'契约文档提供'
c_f3.value = u'契约文档确认'
c_g3.value = u'SOA接口是否提供'
c_h3.value = u'预计用例数量'
c_i3.value = u'完成用例数量'
# 设置成通用样式
c_a3.style = c_b3.style = c_c3.style = c_d3.style = c_e3.style = c_f3.style = c_g3.style = c_h3.style = c_i3.style = general_style
# 单元格值
c_a1.value = title + u'接口测试明细'
# 单元格样式
c_a1.style = Style(
# 字体
font=Font(
name=u'微软雅黑',
size=22,
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center'
),
# 填充
fill=PatternFill(
start_color='FFC000',
end_color='FFC000',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
# ==============================================================================================================
# ==============================================================================================================
# 第二个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[1])
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=6)
ws.merge_cells(start_row=2, start_column=1, end_row=3, end_column=1)
ws.merge_cells(start_row=2, start_column=2, end_row=2, end_column=6)
c_a1 = ws['A1']
c_a2 = ws['A2']
c_b2 = ws['B2']
c_b3 = ws['B3']
c_c3 = ws['C3']
c_d3 = ws['D3']
c_e3 = ws['E3']
c_f3 = ws['F3']
c_a1.value = title + u'接口测试数据统计'
c_a2.value = title
c_b2.value = u'验收结果'
c_b3.value = u'测试用例'
c_c3.value = u'通过用例'
c_d3.value = u'失败用例'
c_e3.value = u'检查点数量'
c_f3.value = u'通过率'
c_a1.style = Style(
font=Font(
name=u'微软雅黑',
size=11,
color='FFFFFF',
bold='bold'
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center'
),
# 填充
fill=PatternFill(
start_color='0070C0',
end_color='0070C0',
fill_type='solid'
),
)
c_a2.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
# 垂直居中
vertical = 'center',
),
# 填充
fill=PatternFill(
start_color='00B050',
end_color='00B050',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_b2.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='FCD5B4',
end_color='FCD5B4',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_b3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_e3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_c3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='92D050',
end_color='92D050',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_d3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='FF0000',
end_color='FF0000',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_f3.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='DAEEF3',
end_color='DAEEF3',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
# ==============================================================================================================
# ==============================================================================================================
# 第三个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[2])
ws.merge_cells(start_row=2, start_column=1, end_row=3, end_column=1)
ws.merge_cells(start_row=2, start_column=2, end_row=3, end_column=2)
c_a1 = ws['A1']
c_b1 = ws['B1']
c_a1.value = u'涉及接口'
c_b1.value = u'问题说明'
c_a1.style = c_b1.style = Style(
font=Font(
name=u'微软雅黑',
size=11
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='DCE6F1',
end_color='DCE6F1',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
# ==============================================================================================================
# ==============================================================================================================
# 第四个sheet
ws = self.work_book.get_sheet_by_name(self.sheet_names[3])
c_a1 = ws['A1']
c_b1 = ws['B1']
c_c1 = ws['C1']
c_d1 = ws['D1']
c_e1 = ws['E1']
c_a6 = ws['A6']
c_a7 = ws['A7']
c_b7 = ws['B7']
c_c7 = ws['C7']
c_d7 = ws['D7']
c_e7 = ws['E7']
c_a10 = ws['A10']
c_a1.value = u'模块'
c_b1.value = u'概述'
c_c1.value = u'问题归属'
c_d1.value = u'备注'
c_e1.value = u'问题影响'
c_a6.value = u'今日已修复问题'
c_a7.value = u'模块'
c_b7.value = u'概述'
c_c7.value = u'问题归属'
c_d7.value = u'备注'
c_a10.value = u'无需解决问题'
c_a1.style = c_b1.style = c_c1.style = c_d1.style = c_e1.style = c_a7.style = c_b7.style = c_c7.style = c_d7.style = c_e7.style = Style(
font=Font(
name=u'微软雅黑',
size=10
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='DDD9C4',
end_color='DDD9C4',
fill_type='solid'
),
border=Border(
left=Side(color='000000',border_style='thin'),
right=Side(color='000000',border_style='thin'),
top=Side(color='000000',border_style='thin'),
bottom=Side(color='000000',border_style='thin'),
diagonal=Side(color='000000',border_style='thin'),
)
)
c_a6.style = c_a10.style = Style(
font=Font(
name=u'微软雅黑',
size=10
),
# 对齐方式
alignment = Alignment(
# 水平对齐 居中
horizontal = 'center',
),
# 填充
fill=PatternFill(
start_color='FFFF00',
end_color='FFFF00',
fill_type='solid'
),
)
# ==============================================================================================================
# 传入一个对象
#
# interface_info = [
# {
# 'name':u"这是一个接口名称1",
# 'owner':u'沈佳龙',
# 'contract_is_provide':u'是',
# 'is_confirm':u'是',
# 'soa_is_provide':u'是',
# 'case_num':10
# },
# {
# 'name':u"这是一个接口名称2",
# 'owner':u'张三',
# 'contract_is_provide':u'是',
# 'is_confirm':u'是',
# 'soa_is_provide':u'是',
# 'case_num':20
# },
# {
# 'name':u"这是一个接口名称3",
# 'owner':u'李四',
# 'contract_is_provide':u'是',
# 'is_confirm':u'是',
# 'soa_is_provide':u'是',
# 'case_num':20
# },
# ]
# 调用 self.insert_interface_info(case_info)
self.insert_interface_info(title,interface_info,all_case_num,all_pass_case_num,all_check_num)
excel_work.save(filename=dest_filename)
'set5_n':'L',
'set6_n':'M',
}
#libs for handling excel files:
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.cell import get_column_letter
#use a file open dialog to choose the files to include
files = gui.fileOpenDlg(tryFilePath=".", allowed='*.psydat')
if not files:#user pressed cancel
core.quit()
xlBook = Workbook()
xlsxWriter = ExcelWriter(workbook = xlBook)
xlSheet = xlBook.worksheets[0]#use the first worksheet (index 0)
xlSheet.title = groupName
#make a header row
for condition in outCols.keys():
xlSheet.cell(outCols[condition]+'1').value = condition
outRow = 2#starting row for data
#do the actual analysis, file-by-file
condNames = []
for fileName in files:
# try:
#create a python dict for each set of data
RTs={}
for condition in outCols.keys():
RTs[condition]=[]#a list for each condition that we'll append data to
def save(self, filename, i_max = None, j_max = None):
''' save table in ods format '''
if not i_max: i_max = self.table.i_max
if not j_max: j_max = self.table.j_max
# update cells text
self.table.updateTable(i_max, j_max)
# create new xlsx spreadsheet
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = filename
ws = wb.worksheets[0]
ws.title = "sheet 1"
# make sure values are up to date
# loop and update the cells value
for i in range(1, i_max):
for j in range(1, j_max):
# update the cell text and condition
cell = self.table.encodeColName(j) + str(i)
c = self.table.getCellAt(i, j)
# FIXME: excel output does not support conditional formating,
# we do fixed formating of the conditional formating
color = [c.color, c.condition_color][c.condition_state]
background_color = [c.background_color, c.condition_background_color][c.condition_state]
ws.cell(cell).style.font.name = c.font_family
ws.cell(cell).style.font.size = int(c.font_size[:-2])
ws.cell(cell).style.font.color = Color('FF' + color.upper()[1:])
if background_color != "default":
ws.cell(cell).style.fill.fill_type = 'solid'
ws.cell(cell).style.fill.start_color = Color('00' + background_color.upper()[1:])
ws.cell(cell).style.fill.end_color = Color('00' + background_color.upper()[1:])
if c.border_left != "none":
ws.cell(cell).style.borders.left.border_style = self.convertXlsBorderWidth(c.border_left)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_left)
if c.border_right != "none":
ws.cell(cell).style.borders.right.border_style = self.convertXlsBorderWidth(c.border_right)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_right)
if c.border_top != "none":
ws.cell(cell).style.borders.top.border_style = self.convertXlsBorderWidth(c.border_top)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_top)
if c.border_bottom != "none":
ws.cell(cell).style.borders.bottom.border_style = self.convertXlsBorderWidth(c.border_bottom)
ws.cell(cell).style.borders.left.color = self.convertXlsBorderColor(c.border_bottom)
# set xls text
if (c.formula and c.formula[0] == '='):
ws.cell(cell).value = c.formula.replace(";",",")
elif c.value_type == 'float':
ws.cell(cell).value = c.value
elif c.value_type == 'date':
ws.cell(cell).value = datetime.strptime(c.date_value, "%Y-%m-%d")
else:
ws.cell(cell).value = unicode(c.text, 'utf-8') + " "
ew.save(filename = filename)
def write_records_to_excel(output, population, markers, alleles = ['A','B']):
wb = Workbook()
ew = ExcelWriter(workbook = wb)
ws = wb.worksheets[0]
ws.title = "Level0"
# add marker columns - 2 columns for each allele
header = ["Individual"] + ["{0}_{1}".format(m,a) for m in markers for a in alleles]
#pdb.set_trace()
ws = write_header(ws, header)
max_depth = get_max_depth(population)
wb = create_additional_sheet(wb, header, max_depth)
pop_keys = population.keys()
pop_keys.sort()
for name_idx, name in enumerate(pop_keys):
# pdb.set_trace()
# get depth of stack
depth = population[name].shape[0]
# write invididual name at all depths
#for level in xrange(max_depth):
# ws = wb.get_sheet_by_name("Level{0}".format(level))
# ws.cell('A{0}'.format(name_idx + 2)).value = name
# ensure there is workbook at max stack depth
for level in xrange(max_depth):
# write the sample id for each row of all levels
ws = wb.get_sheet_by_name("Level{0}".format(level))
ws.cell('A{0}'.format(name_idx + 2)).set_value_explicit(value = name, data_type = 's')
# but only write the genotypes for the sample where there
# is a level
if level < depth:
for marker_idx, marker in enumerate(header[1:]):
cl = get_column_letter(marker_idx + 2)
marker, allele = marker.split('_')
if allele == 'A':
pos = 0
else:
pos = 1
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = population[name].lix[[str(level)]].lix[[marker]].x[pos]
# check all non-zero entries for similarity and store to summary
ws = wb.get_sheet_by_name("Summary".format(level))
ws.cell('A{0}'.format(name_idx + 2)).set_value_explicit(value = name, data_type = 's')
for marker_idx, marker in enumerate(header[1:]):
cl = get_column_letter(marker_idx + 2)
marker, allele = marker.split('_')
if allele == 'A':
pos = 0
else:
pos = 1
# if only one element, self = self
if depth <= 1:
identical = True
elif (population[name].lix[:,[marker],pos].x == '').all():
identical = True
else:
# don't penalize comparisons having '' in >= one column
empties = population[name].lix[:,[marker],pos].x != ''
genotypes_no_empties = population[name].lix[:,[marker],pos].x[empties]
# just test first element against all elements
identical = (genotypes_no_empties == genotypes_no_empties[0]).all()
if identical:
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = "T"
else:
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = "F"
#pdb.set_trace()
ew.save(filename = output)
def __init__(self, path):
self.path = path
self.workBook = Workbook()
self.excelWriter = ExcelWriter(workbook=self.workBook)
print 'slope ci', boots.getCI(0.75, thres_or_slope = 'slope')
#set up Excel file
stimOut = {'thresh' : 1, 'thresh ci upper' : 2, 'thresh ci lower' : 3}
# fileName = ('LumReverse_%s.xlsx') %(participant)
fileName = 'RJSLumChromReversal.xlsx'
if os.path.isfile(fileName):
wb = load_workbook(fileName)
newWorkbook = False
else:
wb = Workbook()
newWorkbook = True
ew = ExcelWriter(workbook = wb)
if analysisType == 'byCond':
sheetName = '%s' %(condition)
else:
sheetName = '%s%s' %(condition, picture)
if newWorkbook:
ws = wb.worksheets[0]
ws.title=sheetName
else:
ws=wb.create_sheet()
ws.title=sheetName
def _getExcelCellName(col, row):
"""Returns the excel cell name for a row and column (zero-indexed)
print(str(FinalResult[i])+"是水仙花数")
end = time.time()
TestTime[j]=end-start
print("第"+str(j+1)+"次结束于:%s" %datetime.datetime.now().strftime("%Y/%d/%m %H:%M:%S")+"\n"+"本次耗时："+str(TestTime[j]))
TotalTime = 0
for i in range(TestCount):
print("第" + str(i+1) + "次耗时",TestTime[i])
TotalTime = TotalTime + TestTime[i]
print("平均耗时",TotalTime/TestCount)
if CheckFileExist(ExcelFile):
wb = load_workbook(filename=ExcelFile, read_only=False)#获取一个已经存在的excel文件wb
#print("Worksheet name(s):",wb.get_sheet_names()[0])
ws=wb.get_sheet_by_name(wb.get_sheet_names()[0])#打开该文件wb需要用到的worksheet即ws
else:
message = "文件：%s\n不存在"
wb = Workbook()
ws = wb.active
ws.title = "Sheet1"
FreeColNum=GetEmptyCol()
ewb1=ExcelWriter(workbook=wb)#新建一个ExcelWriter，用来写wb
ws.cell(row=1,column=FreeColNum).value=FreeColNum-1 #写入列表头
for j in range(1,TestCount+1): #空列写入10个数据
ws.cell(row=j+1,column=FreeColNum).value=round(TestTime[j-1],3)
ewb1.save(filename=ExcelFile)#保存一定要有，否则不会有结果
print("成功写入Excel文件第"+chr(64+FreeColNum)+"列")
print("文件路径："+ExcelFile)
input("")
def roll_call_to_xlsx(year, minRC = 1, maxRC = 1):
"""
Get roll call data for a given year and stores in
"""
congressData = dict()
nameList = list()
voteData = dict()
#Check to see if proper arguments passed in function
if year not in range(1990, datetime.now().year+1):
print "Date out of range, must be between 1990 and current year"
return
#No value was entered, default to max
if maxRC == 1:
maxRC = get_max_roll_call(year)
if minRC > maxRC:
print "Max is less than min"
return
for rollcall in range(minRC, maxRC+1):
voteData.clear()
rollcallstr = str(rollcall)
url = 'http://clerk.house.gov/evs/' + str(year) + '/roll' + rollcallstr.zfill(3) + '.xml'
page = urllib2.urlopen(url)
soup = BeautifulStoneSoup(page)
votes = soup.findAll('recorded-vote')
numVotes = len(votes)
for i in range(0, numVotes):
name = votes[i].contents[0].contents[0]
rawVote = votes[i].contents[1].contents[0]
if 'Yes' in rawVote or 'Aye' in rawVote or 'Yea' in rawVote:
vote = 'Yes'
elif 'Not Voting' in rawVote:
vote = 'Not Voting'
elif 'Present' in rawVote:
vote = 'Present'
elif 'No' in rawVote or 'Nay' in rawVote:
vote = 'No'
else:
vote = rawVote
if name not in nameList:
nameList.append(name)
if name not in voteData:
voteData[name] = vote
if rollcall not in congressData:
congressData[rollcall] = voteData.copy()
print 'Added voteData for rollcall ' , rollcall
nameList.sort()
#Data received, now time for excel
wb = Workbook()
ew = ExcelWriter(workbook = wb)
dest_filename = str(get_congress(year)) +'_Congress_Roll_Call_Data.xlsx'
ws = wb.worksheets[0]
ws.title = str(get_congress(year)) + "Congress Roll Call Data"
colcount = len(congressData)
rowcount = len(nameList)
#Write roll call numbers as headers
for col in range(0, colcount):
ws.cell(row= 0, column= col + 1).value = minRC + col
#Write names in first column
for rw in range(rowcount):
ws.cell( row = rw + 1, column = 0 ).value = nameList[rw]
#Write the vote data for each roll call
i = 0
for col in range(minRC, minRC + colcount): #iterate through each column
vD = congressData[col] #get the vote data for this rollcall
print col
i = i + 1
for rw in range(0, rowcount): #iterate through the rows
if nameList[rw] in vD.keys():
ws.cell( row = rw + 1, column = i ).value = vD[nameList[rw]]
ew.save( filename = dest_filename )
return
# get saveas filename for output file *.xlsx
root = Tkinter.Tk()
root.withdraw()
destFileName = tkFileDialog.asksaveasfilename(filetypes=myFormats ,title="Save the output *.xlsx file as...")
if len(destFileName ) < 1:
print "bad file name"
exit()
#check for .xlsx extention
if '.xlsx' not in destFileName:
destFileName = destFileName + ".xlsx"
# make new workbook
wbnew = Workbook()
ew = ExcelWriter(workbook = wbnew)
wsnew = wbnew.worksheets[0]
wsnew.title = "UUtranRelation"
# copy headers from wsdump to wbnbr
rownum = 1
colnum = 1
for row in wsdump.iter_rows():
for cell in row:
colletter = get_column_letter(colnum)
#print colletter, rownum, cell.value
#print '%s%s'%(colletter,rownum)
wsnew.cell('%s%s'%(colletter,rownum)).value = cell.value
colnum = colnum + 1
colnum = 1
rownum = rownum + 1
