格式转换
excel
Excel格式转换
- 使用 excel 打开 xls 文件并转换为 xlsx
python
1import pandas as pd
2import os
3
4file_path = '/Users/xxx/PycharmProjects/drissionpagedocs/MasterData/StockLedger-ABMNZ2023.xlsx';
5file_dir = os.path.dirname(file_path)
6file_name = os.path.basename(file_path)
7file_name_without_extension = os.path.splitext(file_name)[0]
8
9new_file_path = file_dir + '/新建文件夹/' + file_name_without_extension + '-after.xlsx'
10
11
12def saveAsNewFile(path):
13 df = pd.read_excel(path)
14 date = df.iloc[2, 0]
15
16 def fetchCompName(df):
17 name = ''
18 for index, value in enumerate(df.iloc[7:, 0]): # 从第 8 行开始（索引为 7）
19 if pd.isna(value): # 检查是否为缺失值
20 return name
21 else:
22 name = value
23
24 name = fetchCompName(df)
25 new_header = df.iloc[5].to_list()
26 print(f'当前处理文件:{new_header}')
27 print(f'匹配到表头:{new_header}')
28 print(f'匹配到日期: {date}')
29 print(f'匹配到主体: {name}')
30
31 new_file = df.drop(index=range(6)).reset_index(drop=True)
32 new_file.columns = new_header
33 new_file['日期'] = date
34 new_file.loc[new_file['法人主体'].isna(), '法人主体'] = name
35 new_file.to_excel(new_file_path, index=False)
36 print(f'保存新路径: {new_file_path}')
37
38
39saveAsNewFile(file_path)
40