Python excel文件的读写

#!/usr/bin/env python3
# encoding: utf-8
"""
@version: v1.0
@author: 
@license: Apache Licence
@contact: 
@site: 
@software: PyCharm
@file: 04-Excel文件读写.py
@C_time: 2021/6/19 12:43 上午
"""


file = '/../test.xlsx'
def xlsTolist(file):
	try:
		wb = xlrd.open_workbook(file)
		sheet = wb.sheet_by_index(0)
		# 获取Excel第一行列名
		title = sheet.row_values(0)
		# 定义一个列表用于后期存放excel读取的数据
		datalist =[]
		# 定义一个空字典用于存入列名对应的列值
		dd = {}
		# 使用for 循环遍历excel中sheet中的数据(第0行为列名故不遍历范围)
		for i in range(1, sheet.nrows):
			# 定义一个变量用来存放遍历的每行数据
		    values = sheet.row_values(i)
		    # 使用for循环将列表与列值进行对应
		    for j in range(len(title)):
		    	# 向字典dd中添加元素
		        dd[title[j]] = values[j]
		    # 将字典数据添加到列表中
		    datalist.append(dd)
		return datalist
	except Exception as Error:
        return Error

# 使用for循环打印出处理后的数据
for d in datalist:
    print(d)


# 将datalist数据写入Excel文件
df = pandas.DataFrame()
newlist=[]
for data in datalist:
    content = '[' + json.dumps(data) + ']'
    dd = json.loads(content)
    newlist.append(dd)
for data in newlist:
    print(data)
    for i in data:
        dd = pandas.DataFrame([i])
        df = df.append(dd)

df.to_excel('test.xlsx', sheet_name='data', startcol=0, index=False)

import json
import xlrd
from collections import OrderedDict
# collections模块中的OrderedDict类来控制元素最初添加的顺序使字典变得有序


def xlsToJson(filepath):
    filename = filepath
    try:
        wb = xlrd.open_workbook(filename)
        sheet = wb.sheet_by_index(0)
        datalist = []
        title = sheet.row_values(0)
        for j in range(1, sheet.nrows):
            rowvalue = sheet.row_values(j)
            single = OrderedDict()
            for i in range(len(rowvalue)):
                single[title[i]] = rowvalue[i]
            datalist.append(single)

        data = json.dumps(datalist)
  

        return data
    except Exception as Error:
        return Error