简单的读取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
| import xlwings as xw
import pandas as pd
app=xw.App(visible=True,add_book=False)
wb=app.books.open('D:\Excel\8.1.1节\超市销售数据2020-9.xlsx')
sht=wb.sheets('销售数据')
data_pd=sht.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value
#print(data_pd)
data_sift=data_pd.groupby(['商品码','商品名称']).aggregate({'数量':'sum','售价':'count'})
#print(data_sift)
data_sort=data_sift.sort_values(by=['数量'],ascending=False).head(10)
print(data_sort)
wb.close()
app.quit()
数量 售价
商品码 商品名称
04001 400g沁州黄 165.0 165
03001 300g壶瓶枣 163.0 163
07004 水晶梨 141.0 141
08001 通心面 121.0 101
06003 蜻蜓扑克 121.0 101
06023 安利香皂 120.0 120
05007 牛肉礼盒6 120.0 40
09003 芙蓉王 104.0 104
02004 10年45°吉祥汾 102.0 102
07001 香瓜 101.0 101 |
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
| import xlwings as xw
import pandas as pd
import os
file_path='D:\Excel\8.1.2节\商品分析'
file_list=os.listdir(file_path)
app=xw.App(visible=True,add_book=False)
data_pd=pd.DataFrame()
for x in file_list:
if x.startswith('~$'):
continue
wb=app.books.open(file_path+'\'+x)
sht=wb.sheets('销售数据')
data1=sht.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value
data_pd=data_pd.append(data1)
data_sift=data_pd.groupby(['商品码','商品名称']).aggregate({'数量':'sum','销售金额':'max'})
data_sort=data_sift.sort_values(by=['数量','销售金额'],ascending=False).head(10)
print(data_sort)
批量替换工作簿的单元格数据
from pathlib import Path
import xlwings as xw
src_folder = Path('D:\\python\\app_excel\\use python6\\月销售统计\')
file_list = list(src_folder.glob('*.xlsx'))
app = xw.App(visible=False, add_book=False)
for i in file_list:
if i.name.startswith('~$'):
continue
workbook = app.books.open(i)
for j in workbook.sheets:
data = j['A2'].expand('table').value
for index, val in enumerate(data):
if val[2] == '背包':
val[2] = '双肩包'
data[index] = val
j['A2'].expand('table').value = data
workbook.save()
workbook.close()
app.quit()
数量 销售金额
商品码 商品名称
04001 400g沁州黄 495.0 6.5
03001 300g壶瓶枣 489.0 10.0
07004 水晶梨 423.0 2.0
06003 蜻蜓扑克 363.0 6.0
08001 通心面 363.0 6.0
05007 牛肉礼盒6 360.0 425.0
06023 安利香皂 360.0 18.0
09003 芙蓉王 312.0 35.0
02004 10年45°吉祥汾 306.0 98.0
09006 中南海0.8 303.0 8.0 |
将多个工作表合并为一个工作表
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
| from pathlib import Path
import xlwings as xw
src_folder = Path('F:\\代码文件\\第6章\\月销售统计\')
file_list = list(src_folder.glob('*.xlsx'))
app = xw.App(visible=False, add_book=False)
sheet_name = '产品销售统计'
header = None
all_data = []
for i in file_list:
if i.name.startswith('~$'):
continue
workbook = app.books.open(i)
for j in workbook.sheets:
if j.name == sheet_name:
if header is None:
header = j['A1:I1'].value
data = j['A2'].expand('table').value
all_data = all_data + data
workbook.close()
new_workbook = xw.Book()
new_worksheet = new_workbook.sheets.add(sheet_name)
new_worksheet['A1'].value = header
new_worksheet['A2'].value = all_data
new_worksheet.autofit()
new_workbook.save(src_folder / '上半年产品销售统计表.xlsx')
new_workbook.close()
app.quit() |
将一个工作表拆分为多个工作簿
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
| from pathlib import Path
import xlwings as xw
src_file = Path('F:\\代码文件\\第6章\\产品统计表.xlsx')
des_folder = Path('F:\\代码文件\\第6章\\拆分后的产品统计表\')
if not des_folder.exists():
des_folder.mkdir(parents=True)
app = xw.App(visible=False, add_book=False)
workbook = app.books.open(src_file)
worksheet = workbook.sheets['统计表']
header = worksheet['A1:H1'].value
data1 = worksheet.range('A2').expand('table').value
data2 = dict()
for i in range(len(data1)):
product_name = data1[i][1]
if product_name not in data2:
data2[product_name] = []
data2[product_name].append(data1[i])
for k, v in data2.items():
new_workbook = xw.books.add()
new_worksheet = new_workbook.sheets.add(k)
new_worksheet['A1'].value = header
new_worksheet['A2'].value = v
new_worksheet.autofit()
new_workbook.save(des_folder / f'{k}.xlsx')
new_workbook.close()
app.quit() |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| from pathlib import Path
import xlwings as xw
import pandas as pd
src_folder = Path('F:\\代码文件\\第6章\\每月进货统计表\')
file_list = list(src_folder.glob('*.xlsx'))
app = xw.App(visible=False, add_book=False)
for i in file_list:
if i.name.startswith('~$'):
continue
workbook = app.books.open(i)
worksheet = workbook.sheets['Sheet1']
data = worksheet.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value
new_data = data['产品尺寸（mm）'].str.split('*', expand=True)
new_data.columns = ['长（mm）', '宽（mm）', '高（mm）']
for j in range(new_data.shape[1] - 1):
worksheet['F:F'].insert(shift='right', copy_origin='format_from_left_or_above')
worksheet['F1'].options(index=False).value = new_data
worksheet.autofit()
workbook.save()
workbook.close()
app.quit() |
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
| import xlwings as xw
import pandas as pd
app = xw.App(visible=False, add_book=False)
workbook = app.books.open('F:\\代码文件\\第6章\\产品销售统计表.xlsx')
worksheets = workbook.sheets
table = pd.DataFrame()
for i, j in enumerate(worksheets):
data = j.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value
data = data.reindex(columns=['单号', '销售日期', '产品名称', '成本价（元/个）', '销售价（元/个）', '销售数量（个）', '产品成本（元）', '销售收入（元）', '销售利润（元）'])
table = table.append(data, ignore_index=True)
table = table.groupby('产品名称')
new_workbook = xw.books.add()
for idx, group in table:
new_worksheet = new_workbook.sheets.add(idx)
new_worksheet['A1'].options(index=False).value = group
last_cell = new_worksheet['A1'].expand('table').last_cell
last_row = last_cell.row
last_column = last_cell.column
last_column_letter = chr(64 + last_column)
sum_cell_name = f'{last_column_letter}{last_row + 1}'
sum_last_row_name = f'{last_column_letter}{last_row}'
formula = f'=SUM({last_column_letter}2:{sum_last_row_name})'
new_worksheet[sum_cell_name].formula = formula
new_worksheet.autofit()
new_workbook.save('F:\\代码文件\\第6章\\产品销售统计表.xlsx')
app.quit() |
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
| import xlwings as xw
import pandas as pd
from datetime import datetime
app=xw.App(visible=True,add_book=False)
wb=app.books.open('D:\Excel\8.2.1节\超市销售数据2020-9.xlsx')
sht=wb.sheets('销售数据')
sht.range('E:E').api.NumberFormat='H'
data_pd=sht.range('A1').options(pd.DataFrame,header=1,index=False,expand='table').value
#print(data_pd['销售日期'])
#data_pd1=data_pd[data_pd['销售日期']==datetime(2020,9,2)]
data_pd1=data_pd
data_pd2=data_pd1[['销售时间','小票号']].drop_duplicates()
print(data_pd2['销售时间'])
data_pd2['销售时间']=[int(x*24) for x in data_pd2['销售时间']]
print(data_pd2)
data_sort=data_pd2.groupby('销售时间').aggregate({'小票号':'count'})
new_wb=app.books.add()
new_sht=new_wb.sheets.add('客流分析')
new_sht.range('A1').options(transform= True).value=data_sort
new_sht.autofit()
new_wb.save('D:\Excel\8.2.1节\客流分析-日.xlsx')
new_wb.close()
wb.close()
app.quit() |
XML 读取
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
| import os #目标检测中将已有的.XML数据集转换成.TXT数据集
import xml.etree.ElementTree as ET
from PIL import Image
import numpy as np
#PYTHON将VOC数据集归一化后的LABELS（.TXT）文件批量转成XML文件
img_path = '/home/anngic/BDD100K/JPEGImages/' #原图.jpg文件的路径
labels_path = '/home/anngic/BDD100K/labels/' #labels中.txt文件的路径
annotations_path = '/home/anngic/BDD100K/Annotations/' #生成的xml文件需要保存的路径
labels = os.listdir(labels_path)
clsnames_path = '/home/anngic/label/bdd100k_labels/BD100K_10.names' #names文件的路径
with open(clsnames_path,'r') as f:
classes = f.readlines()
classes = [cls.strip('\n') for cls in classes]
def write_xml(imgname,filepath,labeldicts): #参数imagename是图片名（无后缀）
root = ET.Element('Annotation') #创建Annotation根节点
ET.SubElement(root, 'filename').text = str(imgname) #创建filename子节点（无后缀）
sizes = ET.SubElement(root,'size') #创建size子节点
ET.SubElement(sizes, 'width').text = '1280' #没带脑子直接写了原图片的尺寸......
ET.SubElement(sizes, 'height').text = '720'
ET.SubElement(sizes, 'depth').text = '3' #图片的通道数：img.shape[2]
for labeldict in labeldicts:
objects = ET.SubElement(root, 'object') #创建object子节点
ET.SubElement(objects, 'name').text = labeldict['name'] #BDD100K_10.names文件中
#的类别名
ET.SubElement(objects, 'pose').text = 'Unspecified'
ET.SubElement(objects, 'truncated').text = '0'
ET.SubElement(objects, 'difficult').text = '0'
bndbox = ET.SubElement(objects,'bndbox')
ET.SubElement(bndbox, 'xmin').text = str(int(labeldict['xmin']))
ET.SubElement(bndbox, 'ymin').text = str(int(labeldict['ymin']))
ET.SubElement(bndbox, 'xmax').text = str(int(labeldict['xmax']))
ET.SubElement(bndbox, 'ymax').text = str(int(labeldict['ymax']))
tree = ET.ElementTree(root)
tree.write(filepath, encoding='utf-8')
for label in labels: #批量读.txt文件
with open(labels_path + label, 'r') as f:
img_id = os.path.splitext(label)[0]
contents = f.readlines()
labeldicts = []
for content in contents:
img = np.array(Image.open(img_path+label.strip('.txt') + '.jpg'))
sh,sw = img.shape[0],img.shape[1] #img.shape[0]是图片的高度720
#img.shape[1]是图片的宽度720
content = content.strip('\n').split()
x=float(content[1])*sw
y=float(content[2])*sh
w=float(content[3])*sw
h=float(content[4])*sh
new_dict = {'name': classes[int(content[0])],
'difficult': '0',
'xmin': x+1-w/2, #坐标转换公式看另一篇文章....
'ymin': y+1-h/2,
'xmax': x+1+w/2,
'ymax': y+1+h/2
}
labeldicts.append(new_dict)
write_xml(img_id, annotations_path + label.strip('.txt') + '.xml', labeldicts) |
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
| # xml解析包 目标检测中将已有的.XML数据集转换成.TXT数据集
import xml.etree.ElementTree as ET
import pickle
import os
# os.listdir() 方法用于返回指定的文件夹包含的文件或文件夹的名字的列表
from os import listdir, getcwd
from os.path import join
sets = ['train', 'test', 'val']
classes = ['metal', 'plastic', 'guard']
# 进行归一化操作
def convert(size, box): # size:(原图w,原图h) , box:(xmin,xmax,ymin,ymax)
dw = 1./size[0] # 1/w
dh = 1./size[1] # 1/h
x = (box[0] + box[1])/2.0 # 物体在图中的中心点x坐标
y = (box[2] + box[3])/2.0 # 物体在图中的中心点y坐标
w = box[1] - box[0] # 物体实际像素宽度
h = box[3] - box[2] # 物体实际像素高度
x = x*dw # 物体中心点x的坐标比(相当于 x/原图w)
w = w*dw # 物体宽度的宽度比(相当于 w/原图w)
y = y*dh # 物体中心点y的坐标比(相当于 y/原图h)
h = h*dh # 物体宽度的宽度比(相当于 h/原图h)
return (x, y, w, h) # 返回 相对于原图的物体中心点的x坐标比,y坐标比,宽度比,高度比,取值范围[0-1]
# year ='2012', 对应图片的id（文件名）
def convert_annotation(image_id):
'''
将对应文件名的xml文件转化为label文件，xml文件包含了对应的bunding框以及图片长款大小等信息，
通过对其解析，然后进行归一化最终读到label文件中去，也就是说
一张图片文件对应一个xml文件，然后通过解析和归一化，能够将对应的信息保存到唯一一个label文件中去
labal文件中的格式：calss x y w h 同时，一张图片对应的类别有多个，所以对应的ｂｕｎｄｉｎｇ的信息也有多个
'''
# 对应的通过year 找到相应的文件夹，并且打开相应image_id的xml文件，其对应bund文件
in_file = open('data/Annotations/%s.xml' % (image_id), encoding='utf-8')
# print(in_file.name)
# 准备在对应的image_id 中写入对应的label，分别为
# <object-class> <x> <y> <width> <height>
out_file = open('data/labels/%s.txt' % (image_id), 'w', encoding='utf-8')
# print(out_file.name)
# 解析xml文件
tree = ET.parse(in_file)
# 获得对应的键值对
root = tree.getroot()
# 获得图片的尺寸大小
size = root.find('size')
# 获得宽
w = int(size.find('width').text)
# 获得高
h = int(size.find('height').text)
# 遍历目标obj
for obj in root.iter('object'):
# 获得difficult ？？
difficult = obj.find('difficult').text
# 获得类别 =string 类型
cls = obj.find('name').text
# 如果类别不是对应在我们预定好的class文件中，或difficult==1则跳过
if cls not in classes or int(difficult) == 1:
continue
# 通过类别名称找到id
cls_id = classes.index(cls)
# 找到bndbox 对象
xmlbox = obj.find('bndbox')
# 获取对应的bndbox的数组 = ['xmin','xmax','ymin','ymax']
b = (float(xmlbox.find('xmin').text), float(xmlbox.find('xmax').text), float(xmlbox.find('ymin').text),
float(xmlbox.find('ymax').text))
print(image_id, cls, b)
# 带入进行归一化操作
# w = 宽, h = 高， b= bndbox的数组 = ['xmin','xmax','ymin','ymax']
bb = convert((w, h), b)
# bb 对应的是归一化后的(x,y,w,h)
# 生成 calss x y w h 在label文件中
out_file.write(str(cls_id) + " " + " ".join([str(a) for a in bb]) + '\n')
# 返回当前工作目录
wd = getcwd()
print(wd)
for image_set in sets:
'''
对所有的文件数据集进行遍历
做了两个工作：
１．讲所有图片文件都遍历一遍，并且将其所有的全路径都写在对应的txt文件中去，方便定位
２．同时对所有的图片文件进行解析和转化，将其对应的bundingbox 以及类别的信息全部解析写到label 文件中去
最后再通过直接读取文件，就能找到对应的label 信息
'''
# 先找labels文件夹如果不存在则创建
if not os.path.exists('data/labels/'):
os.makedirs('data/labels/')
# 读取在ImageSets/Main 中的train、test..等文件的内容
# 包含对应的文件名称
image_ids = open('data/ImageSets/%s.txt' % (image_set)).read().strip().split()
# 打开对应的2012_train.txt 文件对其进行写入准备
list_file = open('data/%s.txt' % (image_set), 'w')
# 将对应的文件_id以及全路径写进去并换行
for image_id in image_ids:
list_file.write('data/images/%s.jpg\n' % (image_id))
# 调用 year = 年份 image_id = 对应的文件名_id
convert_annotation(image_id)
# 关闭文件
list_file.close() |
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
34
35
36
37
38
39
| from openpyxl import load_workbook
workbook = load_workbook('D:\\python\\app_excel\\use python6\\出货统计表.xlsx')
worksheet = workbook['Sheet1']
data = {}
for row in range(2, worksheet.max_row + 1):
date = worksheet['B' + str(row)].value.date()
customer = worksheet['C' + str(row)].value
product = worksheet['D' + str(row)].value
number = worksheet['E' + str(row)].value
model = worksheet['G' + str(row)].value
info_list = [customer, product, number, model]
data.setdefault(date, [])
data[date].append(info_list)
for key, value in data.items():
print(key, value)
workbook_day = load_workbook('D:\\python\\app_excel\\use python6\\出货清单模板.xlsx')
worksheet_day = workbook_day['出货清单模板']
for date in data.keys():
worksheet_new = workbook_day.copy_worksheet(worksheet_day)
worksheet_new.title = str(date)[-5:]
worksheet_new.cell(row=2, column=5).value = date
i = 4
for product in data[date]:
worksheet_new.cell(row=i, column=2).value = product[0]
worksheet_new.cell(row=i, column=3).value = product[1]
worksheet_new.cell(row=i, column=4).value = product[2]
worksheet_new.cell(row=i, column=5).value = product[3]
i += 1
workbook_day.save('D:\\python\\app_excel\\use python6\\33产品出货清单.xlsx')
2020-02-08 [['创普电器店', '冰箱', 120, 'BCD-121W'],
2020-02-15 [['湛美电器店', '微波炉', 50, 'M1-230E']]
2020-02-18 [['墨森电器店', '冰箱', 200, 'BCD-120A']]
2020-02-24 [['美好生活电器店', '电饭煲', 360, 'GF-LP100YC'], ['生活日记电器店', '电吹风', 400, 'AH7600I']]
2020-02-28 [['福满电器店', '洗衣机', 500, 'XQG80-HBD1426']]
2020-03-01 [['峰洁电器店', '微波炉', 80, 'M3-208E'], ['新长弘电器店', '冰箱', 60, 'BCD-182']]
2020-03-05 [['华江电器店', '洗衣机', 90, 'XQG80-HBD1426']]
2020-03-14 [['千邦电器店', '电吹风', 40, 'AH7600I']]
2020-03-18 [['科宏电器店', '微波炉', 20, 'M1-230E']] |
