python合并Excel
---原创,转载请说明
python操作多个Excel表格合并为同一个表格的不同sheet,并且将sheet名字命名为原表格的表名
缺陷:Excel表格第一行第一列不能空,不然会缺失列
速度很慢
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
# -*- coding:utf-8 -*-
import
pandas as pd
import
os
# 结果文件
result
=
pd.ExcelWriter(
'123.xls'
)
# 获取表格列表
origin_file_list
=
os.listdir(
'E:/Work/目标路径'
)
print
origin_file_list
# 循环遍历表格
for
i
in
origin_file_list:
print
i
excel_file_name
=
i
#拼接每个文件的路径
file_path
=
'E:/Work/123/%s'
%
i
print
file_path
# 读取文件内容
# 跳过0行
content
=
pd.read_excel(file_path, skiprows
=
[
0
])
# 有缺陷,会扩展列名
# content = pd.read_excel(file_path)
# 重新定义sheet名字
sheet_name
=
i[:
len
(i)
-
4
]
# 转换为同一个表多个sheet
content.to_excel(result, sheet_name, index
=
False
)
# content.to_excel()
result.save()
|
二:Excel表格宏命令合并
新建一个Excel表格,点击底部sheet,右键查看代码,粘贴代码,顶部运行,选择对应的多个Excel即可生成。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
Sub Books2Sheets()
Dim fd As FileDialog
Set
fd
=
Application.FileDialog(msoFileDialogFilePicker)
Dim newwb As Workbook
Set
newwb
=
Workbooks.Add
With fd
If .Show
=
-
1
Then
Dim vrtSelectedItem As Variant
Dim i As Integer
i
=
1
For Each vrtSelectedItem In .SelectedItems
Dim tempwb As Workbook
Set
tempwb
=
Workbooks.
Open
(vrtSelectedItem)
tempwb.Worksheets(
1
).Copy Before:
=
newwb.Worksheets(i)
'把新工作簿的工作表名字改成被复制工作簿文件名,这儿应用于xls文件,即Excel97
-
2003
的文件,如果是Excel2007,需要改成xlsx
newwb.Worksheets(i).Name
=
VBA.Replace(tempwb.Name,
".xls"
, "")
'关闭
tempwb.Close SaveChanges:
=
False
i
=
i
+
1
Next
vrtSelectedItem
End If
|