def save_xlsx_template(file_path, data, header=None, col_id=None):
"""将输入保存到 Excel 文件中。使用文件模板
全部保存为文本。
Args:
file_path (str): xlsx 文件的路径
data (list[list]): 要保存的数据，二维
header (list): 第一行
col_id (list[int]): data 中列号到 xlsx 中列号的映射
"""
if len(data) <= 0:
return save_xlsx(file_path, data, header, col_id)
cd = os.path.dirname(os.path.abspath(__file__))
num_col = len(data[0])
if num_col == 9:
shutil.copy(os.path.join(cd, 'data/prototype_list.xlsx'), file_path)
elif num_col == 12:
shutil.copy(os.path.join(cd, 'data/prototype_pair.xlsx'), file_path)
elif num_col == 8:
shutil.copy(os.path.join(cd, 'data/prototype_ui.xlsx'), file_path)
else:
return save_xlsx(file_path, data, header, col_id)
if col_id is None:
col_id = list(range(0, num_col))
max_col_id = max(col_id)
workbook = openpyxl.load_workbook(file_path)
sheet = workbook['sheet 1']
# 格式
fonts = [copy.copy(sheet.cell(row=1, column=i + 1).font) for i in col_id]
fills = [copy.copy(sheet.cell(row=1, column=i + 1).fill) for i in col_id]
alignments = [
copy.copy(sheet.cell(row=1, column=i + 1).alignment) for i in col_id
]
number_formats = [
sheet.cell(row=1, column=i + 1).number_format for i in col_id
]
# 写入内容
row_id = 1
for row in data:
row_cells = [
'',
] * (max_col_id + 1)
for j in range(0, num_col):
cell = WriteOnlyCell(sheet, value=str(row[j]))
cell.font = fonts[j]
cell.fill = fills[j]
cell.alignment = alignments[j]
cell.number_format = number_formats[j]
row_cells[col_id[j]] = cell
sheet.append(row_cells)
row_id += 1
workbook.save(file_path)
def to_cell(self, ws):
if self.bg is None and self.w is None:
return self.val
else:
cell = WriteOnlyCell(ws, self.val)
if self.bg is not None:
cell.fill = self.bg
return cell
def save_xlsx_template(file_path, data, header=None, col_id=None):
"""将输入保存到 Excel 文件中。使用文件模板
全部保存为文本。
Args:
file_path (str): xlsx 文件的路径
data (list[list]): 要保存的数据，二维
header (list): 第一行
col_id (list[int]): data 中列号到 xlsx 中列号的映射
"""
if len(data) <= 0:
return save_xlsx(file_path, data, header, col_id)
cd = os.path.dirname(os.path.abspath(__file__))
num_col = len(data[0])
if num_col == 9:
shutil.copy(os.path.join(cd, 'data/prototype_list.xlsx'), file_path)
elif num_col == 12:
shutil.copy(os.path.join(cd, 'data/prototype_pair.xlsx'), file_path)
elif num_col == 8:
shutil.copy(os.path.join(cd, 'data/prototype_ui.xlsx'), file_path)
else:
return save_xlsx(file_path, data, header, col_id)
if col_id is None:
col_id = list(range(0, num_col))
max_col_id = max(col_id)
workbook = openpyxl.load_workbook(file_path)
sheet = workbook['sheet 1']
# 格式
fonts = [copy.copy(sheet.cell(row=1, column=i + 1).font) for i in col_id]
fills = [copy.copy(sheet.cell(row=1, column=i + 1).fill) for i in col_id]
alignments = [copy.copy(sheet.cell(row=1, column=i + 1).alignment) for i in col_id]
number_formats = [sheet.cell(row=1, column=i + 1).number_format for i in col_id]
# 写入内容
row_id = 1
for row in data:
row_cells = ['', ] * (max_col_id + 1)
for j in range(0, num_col):
cell = WriteOnlyCell(sheet, value=str(row[j]))
cell.font = fonts[j]
cell.fill = fills[j]
cell.alignment = alignments[j]
cell.number_format = number_formats[j]
row_cells[col_id[j]] = cell
sheet.append(row_cells)
row_id += 1
workbook.save(file_path)
def run():
# 创建句柄
wb = Workbook()
ws_w = wb.active
fpath = '.'
i = 1
files = []
# 获取fpath下所有的excel文件
for fname in os.listdir(fpath):
if fname.endswith(".xlsx"):
files.append(fname)
# 按照最后更改时间排序，一开始用getctime但是mac上有问题（一个文件下载然后复制到别的文件夹的时候，ctime是复制的时间），于是改成getmtime
files.sort(key=lambda x: os.path.getmtime(x))
for fname in files:
print(fname)
wb_r = load_workbook(filename=fname)
ws_r = wb_r.worksheets[0]
j = 1
for row in ws_r.rows:
new_cells = []
if i == 1: # 第一个文件，特殊处理，
for cell in row:
new_cell = WriteOnlyCell(ws_w, value=cell.value)
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
new_cells.append(new_cell)
if j == 1:
ws_w.title = ws_r.title
ws_w.column_dimensions = ws_r.column_dimensions
ws_w.append(new_cells)
else: # 其他文件
if j >= 4:
for cell in row:
new_cell = WriteOnlyCell(ws_w, value=cell.value)
if cell.column == "A":
new_cell.value = (i - 1) * 10 + int(cell.value)
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
new_cells.append(new_cell)
ws_w.append(new_cells)
j += 1
i += 1
today = time.strftime('%Y%m%d', time.localtime(time.time()))
wb.save('new_file_%s.xlsx' % today)