从原Excel表中抽出数据存入同一文件的新的Sheet
实现代码：
import pandas as pd
df = pd.read_excel("物料表.xlsx", header=2)
df.head()
# 请求单号 单位 物料编号 架位 批号 数量 日期
# 0 A19X9239156 EA 11010XR0073700 LC000001 PC00001 3 2019-01-03
# 1 A19X9239156 EA 11020XR0025500 LC000002 PC00002 10 2019-01-03
# 2 A19X9239156 EA 11030XR0013200 LC000003 PC00003 3 2019-01-03
# 3 A19X9239156 EA 11140XR0000100 LC000004 PC00004 3 2019-01-03
# 4 A19X9239156 EA 12121XR0172300 LC000005 PC00005 3 2019-01-03
df['月份'] = df['日期'].dt.month
df.head()
# 请求单号 单位 物料编号 架位 批号 数量 日期 月份
# 0 A19X9239156 EA 11010XR0073700 LC000001 PC00001 3 2019-01-03 1
# 1 A19X9239156 EA 11020XR0025500 LC000002 PC00002 10 2019-01-03 1
# 2 A19X9239156 EA 11030XR0013200 LC000003 PC00003 3 2019-01-03 1
# 3 A19X9239156 EA 11140XR0000100 LC000004 PC00004 3 2019-01-03 1
# 4 A19X9239156 EA 12121XR0172300 LC000005 PC00005 3 2019-01-03 1
df5 = df[df['月份'] == 5]
df5.head()
# 请求单号 单位 物料编号 架位 批号 数量 日期 月份
# 9649 A19X9280200 EA 36012XR0413200 LC009650 PC09650 66 2019-05-03 5
# 9650 A19X9280200 EA 60022XR2298300 LC009651 PC09651 66 2019-05-03 5
# 9651 A19X9280561 EA 72004XR2000001 LC009652 PC09652 604 2019-05-03 5
# 9652 A19X9286759 EA 62010XR0161048 LC009653 PC09653 50 2019-05-03 5
# 9653 A19X9286759 EA 62010XR0502208 LC009654 PC09654 100 2019-05-03 5
df8 = df[df['月份'] == 8]
df8.head()
# 请求单号 单位 物料编号 架位 批号 数量 日期 月份
# 17213 A19X9317364 EA 62010XR0500126 LC017214 PC17214 230 2019-08-01 8
# 17214 A19X9317364 EA 61010XR1120100 LC017215 PC17215 230 2019-08-01 8
# 17215 A19X9317364 EA 62012XR0102262 LC017216 PC17216 230 2019-08-01 8
# 17216 A19X9317364 EA 62010XR050150A LC017217 PC17217 950 2019-08-01 8
# 17217 A19X9317364 EA 61011XR1150500 LC017218 PC17218 230 2019-08-01 8
# 去掉不需要的月份的数据
def Remove_data(df, month=[]):
for i in month:
df = df[df['月份'] != i]
return df
df_rest = Remove_data(df, [5, 8])
df_rest.head()
# 请求单号 单位 物料编号 架位 批号 数量 日期 月份
# 0 A19X9239156 EA 11010XR0073700 LC000001 PC00001 3 2019-01-03 1
# 1 A19X9239156 EA 11020XR0025500 LC000002 PC00002 10 2019-01-03 1
# 2 A19X9239156 EA 11030XR0013200 LC000003 PC00003 3 2019-01-03 1
# 3 A19X9239156 EA 11140XR0000100 LC000004 PC00004 3 2019-01-03 1
# 4 A19X9239156 EA 12121XR0172300 LC000005 PC00005 3 2019-01-03 1
with pd.ExcelWriter('物料表_1.xlsx', engine='openpyxl',
datetime_format='YYYY-MM-DD') as writer:
df5.drop('月份', axis=1).to_excel(writer, sheet_name='5月', index=False)
df8.drop('月份', axis=1).to_excel(writer, sheet_name='8月', index=False)
df_rest.drop('月份', axis=1).to_excel(writer, sheet_name='剩余月份', index=False)
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
thin = Side(border_style="thin", color="000000") # 定义边框粗细及颜色
wb = load_workbook("物料表_1.xlsx")
for sheetname in wb.sheetnames:
ws = wb[sheetname]
# 调整列宽
ws.column_dimensions['A'].width = 12
ws.column_dimensions['C'].width = 15.5
ws.column_dimensions['G'].width = 10
# 设置字号，对齐，缩小字体填充，加边框
for row_number in range(2, ws.max_row + 1):
for col_number in range(1, ws.max_column + 1):
c = ws.cell(row=row_number, column=col_number)
c.font = Font(size=10)
c.border = Border(top=thin, left=thin, right=thin, bottom=thin)
c.alignment = Alignment(horizontal="left", vertical="center")
wb.save("物料表_1.xlsx")