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)