excel处理
提取出有用字符串数据,并对列表进行排序。
import xlrd
import csv
from operator import itemgetter
import numpy as np
import pandas as pd
from openpyxl.workbook import Workbook
def xlsx_to_csv():
workbook = xlrd.open_workbook('1.xlsx')
table = workbook.sheet_by_index(0)
with open('1.csv', 'w',newline='') as f:
write = csv.writer(f)
for row_num in range(table.nrows):
row_value = table.row_values(row_num)
write.writerow(row_value)
if __name__ == '__main__':
xlsx_to_csv()
filename1 = '1.csv'
filename2 = '排序完成.csv'
with open(filename1, "r") as fr, open(filename2, "w", newline='') as fw:
freader = csv.reader(fr)
fwriter = csv.writer(fw)
header_row = next(freader)
mes = []
for row in freader:
mes.append(row)
a = np.array(mes)
name = a[:,1]
professal = a[:,6]
classs = a[:,7]
new_mes = np.array([name, professal, classs]).T
new_mes = sorted(new_mes, key=lambda x:[x[1], x[2]])
for i in range(len(new_mes)):
fwriter.writerow(new_mes[i])
def csv_to_xlsx_pd():
csv = pd.read_csv('2.csv', encoding='gbk')
csv.to_excel('排序完成.xlsx', sheet_name='data')
if __name__ == '__main__':
csv_to_xlsx_pd()
print("*************已完成排序***********")