excel处理

excel处理

提取出有用字符串数据，并对列表进行排序。

import xlrd
import csv
from operator import itemgetter
import numpy as np
import pandas as pd
from openpyxl.workbook import Workbook
# 将xlsx文件转换为csv文件，需要安装xlrd 1.2.0b版本pip install xlrd==1.2.0
def xlsx_to_csv():
    workbook = xlrd.open_workbook('1.xlsx')
    table = workbook.sheet_by_index(0)
    # newline='', 解决写入excel有空行问题
    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'
# 阅读模式打开转换好的文件1，准备写入'排序完成.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) #生成numpy数列
	
	name = a[:,1]		#读取数列每行的第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])
# 利用pandas，将csv数据转换回xlsx
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("*************已完成排序***********")