import xlsxwriter

#新建excel表
workbook = xlsxwriter.Workbook('D:/hello.xlsx')
<!--more-->

#新建sheet（sheet的名称为"sheet1"）
worksheet = workbook.add_worksheet()

#定义表头内容
Title = ['实例ID', '实例配置', '实例名', '地域', '安全组', 'EIP']

#定义标题格式
merge_format = workbook.add_format({
+ 'bold': True,
+ 'border': 1,
+ 'align': 'center',
+ 'valign': 'vcenter',
+ 'fg_color': '#E0FFFF'
})

#定义表头格式
title_format = workbook.add_format({
+ 'bold': True,
+ 'border': 1,
+ 'align': 'center',
+ 'valign': 'vcenter',
+ 'fg_color': '#87CEFF'
})

#定义内容格式
data_format = workbook.add_format({
+ 'border': 1,
+ 'align': 'center',
+ 'valign': 'vcenter',
+ 'fg_color': '#EED8AE'
})

#拟数据
data = [['i-1', '1C1G', 'demo1', '上海', 'sg-1', '172.20.2.10'],
+ ['i-2', '1C1G', 'demo2', '郑州', 'sg-2', '172.20.2.11'],
+ ['i-3', '1C1G', 'demo3', '北京', 'sg-3', '172.20.2.12']]

#合并 A1 - F1 单元格作为标题，传入参数：1：合并单元格，2：标题文字，3：标题格式
worksheet.merge_range('A1:F1', 'ECS信息表', merge_format)

#设置列宽，指定 A - F 的列宽为 25
worksheet.set_column('A:F', 25)

#设置行号，指定行数，高度，A1 为 0，A2 为 1，以此类推
worksheet.set_row(0, 60)

#write_row ，写行，传入参数：1：行，2：内容，3：格式
worksheet.write_row('A2', Title, title_format)
i = 3
for ECS in data:
+ worksheet.write_row('A' + str(i), ECS, data_format)
+ i += 1

#将excel文件保存关闭，如果没有这一行运行代码会报错
workbook.close()

import xlwt
#1. 创建workbook对象
workbook = xlwt.Workbook(encoding="utf-8")
#2. 增加一个sheet # 设置成可以覆盖原先内容
worksheet = workbook.add_sheet("sheet1", cell_overwrite_ok=True)
#3. 在单元格中添加内容，第一个参数为行，第二个参数为列，第三个参数为内容
worksheet.write(0,0, "hello")
#4. 保存数据表
workbook.save("./student.xls")

Sub 保留表头拆分数据为若干新工作簿()
Dim arr, d As Object, k, t, i&, lc%, rng As Range, c%
c = Application.InputBox("请输入拆分列号", , 4, , , , , 1)

If c = 0 Then Exit Sub
Application.ScreenUpdating = False
Application.DisplayAlerts = False
arr = [a1].CurrentRegion
lc = UBound(arr, 2)
Set rng = [a1].Resize(, lc)
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
If Not d.Exists(arr(i, c)) Then
Set d(arr(i, c)) = Cells(i, 1).Resize(1, lc)
Else
Set d(arr(i, c)) = Union(d(arr(i, c)), Cells(i, 1).Resize(1, lc))
End If
Next
k = d.Keys
t = d.Items
For i = 0 To d.Count - 1
With Workbooks.Add(xlWBATWorksheet)
rng.Copy .Sheets(1).[a1]
t(i).Copy .Sheets(1).[a2]
.SaveAs Filename:=ThisWorkbook.Path & "\" & k(i) & ".xls"
.Close
End With
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "完毕"
End Sub

Sub 合并当前目录下所有工作簿的全部工作表()
Dim MyPath, MyName, AWbName
Dim Wb As Workbook, WbN As String
Dim G As Long

Dim Num As Long
Dim BOX As String
Application.ScreenUpdating = False
MyPath = ActiveWorkbook.Path
MyName = Dir(MyPath & "\" & "*.csv")
AWbName = ActiveWorkbook.Name
Num = 0
Do While MyName <> ""
If MyName <> AWbName Then
Set Wb = Workbooks.Open(MyPath & "\" & MyName)
Num = Num + 1
With Workbooks(1).ActiveSheet
.Cells(.Range("B65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
For G = 1 To Sheets.Count
Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B65536").End(xlUp).Row + 1, 1)
Next
WbN = WbN & Chr(13) & Wb.Name
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("B1").Select
Application.ScreenUpdating = True
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"
End Sub

import os
import pandas as pd

path = r'地址'<!--more-->
file_list = []

dfs = []

for root,dirs,files in os.walk(path):
	for file in files:
		file_path = os.path.join(root, file)
		file_list.append(file_path)
		df = pd.read_csv(file_path,encoding="gbk")
		dfs.append(df)
df = pd.concat(dfs)
df.to_excel(r'地址\汇总.xls', index=False)

import pandas as pd
data = pd.read_excel(r'地址\汇总.xls',keep_default_na=False)
unit_list = list(data['姓名'].drop_duplicates())
for i in unit_list:
	data_new = data[data['姓名'] == i]
	if i == '':
		data_new.to_excel()
	else:
		data_new.to_excel(r"地址\%s.xls" % (i),index=False)

import pandas as pd

data=pd.read_excel(r"地址\汇总.xls")
xlsx = pd.ExcelWriter(r"地址\汇总-拆分完成.xlsx")

for i in data["姓名"].unique():
	data[data["姓名"]==i].to_excel(xlsx,sheet_name=i,index=False)

xlsx.close()