def csv_to_excel(fn="test.csv", coding="utf-8"):
with open(fn, encoding=coding) as fr:
rows = csv.reader(fr)
data = [r for r in rows]
wb = pyexcelerate.Workbook()
wb.new_sheet(fn.split(".")[0], data=data)
wb.save(fn.replace("csv", 'xlsx'))
def benchmark_pyexcelerate():
import pyexcelerate
workbook = pyexcelerate.Workbook()
data = [[value for column in range(COLUMNS)]
for __, value in zip(range(ROWS), VALUES)]
workbook.new_sheet('Test 1', data=data)
workbook.save('benchmark_pyexcelerate.xlsx')
def pyexcelerate_to_excel(workbook_or_filename,
df,
sheet_name='Sheet1',
origin=(1, 1),
columns=True,
index=False):
"""
Write DataFrame to excel file using pyexelerate library
"""
import pyexcelerate
if not isinstance(workbook_or_filename, pyexcelerate.Workbook):
location = workbook_or_filename
workbook_or_filename = pyexcelerate.Workbook()
else:
location = None
worksheet = workbook_or_filename.new_sheet(sheet_name)
# Account for space needed for index and column headers
column_offset = 0
row_offset = 0
if index:
index = df.index.tolist()
ro = origin[0] + row_offset
co = origin[1] + column_offset
worksheet.range((ro, co), (ro + 1, co)).value = [['Index']]
worksheet.range(
(ro + 1, co),
(ro + 1 + len(index), co)).value = list(map(lambda x: [x], index))
column_offset += 1
if columns:
columns = df.columns.tolist()
ro = origin[0] + row_offset
co = origin[1] + column_offset
worksheet.range((ro, co), (ro, co + len(columns))).value = [[*columns]]
row_offset += 1
# Write the data
row_num = df.shape[0]
col_num = df.shape[1]
ro = origin[0] + row_offset
co = origin[1] + column_offset
worksheet.range((ro, co),
(ro + row_num, co + col_num)).value = df.values.tolist()
if location:
workbook_or_filename.save(location)
def time_pyexcelerate():
""" Run pyexcelerate in "faster" mode. """
start_time = clock()
workbook = pyexcelerate.Workbook()
worksheet = workbook.new_sheet('Sheet1')
for row in range(row_max // 2):
for col in range(col_max):
worksheet.set_cell_value(row * 2 + 1, col + 1, "Row: %d Col: %d" % (row, col))
for col in range(col_max):
worksheet.set_cell_value(row * 2 + 2, col + 1, row + col)
workbook.save('pyexcelerate.xlsx')
elapsed = clock() - start_time
print_elapsed_time('pyexcelerate', elapsed)
def to_excel_fast(self, filename, sheet_name='Sheet1', origin=(1, 1), columns=True, index=False):
"""
Write DataFrame to excel file using pyexelerate library. Fast writing, with data integrity traded-off
"""
import pyexcelerate
df = self._obj
if not isinstance(filename, pyexcelerate.Workbook):
location = filename
filename = pyexcelerate.Workbook()
else:
location = None
worksheet = filename.new_sheet(sheet_name)
# Account for space needed for index and column headers
column_offset = 0
row_offset = 0
if index:
index = df.index.tolist()
ro = origin[0] + row_offset
co = origin[1] + column_offset
worksheet.range((ro, co), (ro + 1, co)).value = [['Index']]
worksheet.range((ro + 1, co), (ro + 1 + len(index), co)).value = list(map(lambda x: [x], index))
column_offset += 1
if columns:
columns = df.columns.tolist()
ro = origin[0] + row_offset
co = origin[1] + column_offset
worksheet.range((ro, co), (ro, co + len(columns))).value = [[*columns]]
row_offset += 1
# Write the data
row_num = df.shape[0]
col_num = df.shape[1]
ro = origin[0] + row_offset
co = origin[1] + column_offset
worksheet.range((ro, co), (ro + row_num, co + col_num)).value = df.values.tolist()
if location:
filename.save(location)
print("Successfully exported to " + str(location))
def xml2xls_single(self, xls_dir, input_file_path):
# type: (str, str) -> object
if not xls_dir or not os.path.exists(xls_dir):
Log.error(
Constant.Error(Constant.ERROR_DIR_NOT_EXIST,
"excel dir").get_desc_en())
return Constant.Error(Constant.ERROR_DIR_NOT_EXIST, "excel dir")
if not input_file_path or not os.path.exists(input_file_path):
Log.error(
Constant.Error(
Constant.ERROR_XML_FILE_NOT_EXIST).get_desc_en())
return Constant.Error(Constant.ERROR_XML_FILE_NOT_EXIST)
xlsPath = os.path.join(xls_dir, Constant.Config.export_excel_name)
workbook = pyexcelerate.Workbook()
ws = workbook.new_sheet('Sheet1')
# row col content
ws[1][1] = Constant.Config.keyTitle
dic = XMLParse.get_value_and_key(input_file_path)
writeDict(ws, dic, 2, 1, None, True)
workbook.save(xlsPath)
Log.info(Constant.Error(Constant.SUCCESS).get_desc_en())
return Constant.Error(Constant.SUCCESS)
for file_name in dirlist:
temp = file_name.split('-')[-1]
well = temp.split('_')[0]
well2file_dict[well] = file_name
msr_types = [
'Area', 'Eccentricity', 'MajorAxisLength', 'MinorAxisLength', 'NumNuc',
'NucArea', 'MaxNuc'
]
outpath = path + 'output_xls/'
condit_xls = os.listdir(outpath)
if True:
wb = xlerate.Workbook()
i = 0
m_cols_dict = {}
for msr_type in msr_types:
m_cols_dict[msr_type] = []
headings = []
for condit in condit_xls:
headings.append(condit[:-5])
rows = xlrd_utils.xls2mat(outpath + condit)
del rows[0]
cols = autils.rotate(rows)
for i in range(0, len(msr_types)):
'Percent': 'Daily utilization, %',
'Speed': 'Daily utilization, b/s'
},
inplace=True)
x_total.reset_index(inplace=True)
x_total.rename(columns={
0: 'Days in a row',
'Speed': 'Total utilization for selected days, b/s',
'speed_int': 'Average daily utilization, b/s'
},
inplace=True)
# В excel файл выводим таблицу df в второй лист (Extended data), и обработанные данные x_total в первый лист(Calculated data)
file_name = "OUT\\{}_{}_{}-mbh_10Gb.xlsx".format(year, month, day_end)
wb = xls.Workbook()
data = [
x_total.columns.tolist(),
] + x_total.values.tolist()
ws1 = wb.new_sheet('Calculated data', data=data)
ws1.set_row_style(
1,
xls.Style(alignment=xls.Alignment(horizontal='center'),
font=xls.Font(bold=True)))
ws1.set_col_style(1, xls.Style(size=-1))
ws1.set_col_style(2, xls.Style(size=-1))
ws1.set_col_style(3, xls.Style(size=0))
ws1.set_col_style(4, xls.Style(size=27))
ws1.set_col_style(5, xls.Style(size=27))
def xml2xls(self, xls_dir, input_dir):
"""
xml 转换成 xls
:param xls_dir: 表格所在目录，会主动在改目录下创建个 xls
:param input_dir: xml 所在目录
:return: 输出状态 Constant.Error
"""
if not xls_dir or not os.path.exists(xls_dir):
# 表格目录不存在
Log.error(
Constant.Error(Constant.ERROR_DIR_NOT_EXIST,
"excel dir").get_desc_en())
return Constant.Error(Constant.ERROR_DIR_NOT_EXIST, "excel dir")
if not input_dir or not os.path.exists(input_dir):
# xml 目录不存在
Log.error(
Constant.Error(Constant.ERROR_DIR_NOT_EXIST,
"xml dir").get_desc_en())
return Constant.Error(Constant.ERROR_DIR_NOT_EXIST, "xml dir")
xlsPath = os.path.join(xls_dir, Constant.Config.export_excel_name)
workbook = pyexcelerate.Workbook()
ws = workbook.new_sheet('Sheet1')
# row col content
# write title: module key lan, begin from 1
ws[1][1] = Constant.Config.moduleTitle
ws[1][2] = Constant.Config.keyTitle
ws[1][3] = Constant.Config.export_base_title
# 获取某个文件夹的所有文件，作为标准 这里是 value-zh
base_dir = os.path.join(input_dir, Constant.Config.export_base_dir)
if not os.path.exists(base_dir):
# 标准文件夹不存在
Log.error(
Constant.Error(
Constant.ERROR_DIR_NOT_EXIST,
"base_dir\nU can change base dir in Constant-->Config").
get_desc_en())
return Constant.Error(
Constant.ERROR_DIR_NOT_EXIST,
"base_dir\nU can change base dir in Constant-->Config")
# os.walk(path)返回三个值：
# parent, 表示path的路径、
# dirnames, path路径下的文件夹的名字
# filenames path路径下文件夹以外的其他文件。
Log.info("input_dir ---> " + input_dir)
sub_dir_names = []
for _, dir_names, _ in os.walk(input_dir):
if dir_names:
sub_dir_names = dir_names
break
Log.info("sub_dir_names-->")
Log.info(sub_dir_names)
# 标题下一行写入数据
row = 2
# 标准文件夹下所有文件
files = os.listdir(base_dir)
Log.info("标准文件夹下所有文件：")
Log.info(files)
for filename in files:
module_name = getModuleName(filename)
if not module_name:
continue
file_path = os.path.join(base_dir, filename) # 文件路径
base_dict = XMLParse.get_value_and_key(file_path)
col = 4 # base_dic 去掉前三列
# 开始找其他文件语言下的对应 module
for dir_name in sub_dir_names:
cur_dir_path = os.path.join(input_dir, dir_name)
if cur_dir_path == base_dir:
continue # 标准文件夹不处理
# 当前文件夹的语言
lan = getDirLan(input_dir, cur_dir_path)
Log.info("current language: " + lan)
if not lan: # 文件夹爱不符合规范不处理（values-lan 或 values）
continue
# 获取其他按文件夹下的该文件路径
cur_file = os.path.join(cur_dir_path, filename)
if not os.path.exists(cur_file):
# 路径不存在，不处理，跳过
continue
# 写标题(语言）
ws[1][col] = lan
cur_dict = XMLParse.get_value_and_key(cur_file)
(base_dict, cur_dict) = sortDic(base_dict, cur_dict)
writeDict(ws, cur_dict, row, col, None,
False) # 仅写 非标注value, base_dict 会被一直被更新，
col += 1 # 写完非标准文件的内容，坐标右移（列+1）
# 最后写 标准文件的 key（0）-values（1）
writeDict(ws, base_dict, row, 1, module_name, True)
row += len(base_dict)
Log.info("row = %s" % row)
workbook.save(xlsPath)
return Constant.Error(Constant.SUCCESS)