-- coding: utf-8 --
"""
@Time ： 2023/6/16 17:37
@Auth ： Y
@file ：excel_tools.py
@IDE ：PyCharm
"""
import csv
import os
import openpyxl
from openpyxl import *
import xlrd
import xlwt
from openpyxl.styles import Alignment, Font
class Excel:
def init(self, file_name, sheet_name):
"""
初始化数据
:param file_name: 读取文件名字
:param sheet_name: 读取文件为表格时，sheet名字
"""
self.path = os.path.dirname(os.path.abspath(file_name))
self.path = os.path.abspath(file_name)
self.sheet_name = sheet_name
self.FILE_SUFFIX_XLS = ".xls"
self.FILE_SUFFIX_XLSX = ".xlsx"
self.FILE_SUFFIX_CSV = ".csv"
def read_excel(self) -> list:
"""
读取 Excel 中的数据(不包括第一行表头数据)
:return: table_data
"""
# 获取文件的后缀名
file_type = os.path.splitext(self.path)[-1]
table_data = []
# 判断文件的后缀名
try:
if file_type == self.FILE_SUFFIX_XLSX:
work_book = openpyxl.load_workbook(self.path, data_only=True)
sheet = work_book[self.sheet_name]
max_rows = sheet.max_row # 获取最大行
for i in range(max_rows - 1):
temp_list = []
for each in sheet.iter_cols(min_row=2):
temp_list.append(each[i].value)
table_data.append(temp_list)
return table_data
elif file_type == self.FILE_SUFFIX_XLS:
work_book = xlrd.open_workbook(self.path)
table = work_book.sheet_by_name(self.sheet_name)
rows = table.nrows
for i in range(1, rows):
table_data.append(table.row_values(i))
return table_data
elif file_type == self.FILE_SUFFIX_CSV:
with open(self.path) as f:
for row in csv.reader(f, skipinitialspace=True):
table_data.append(row)
return table_data
else:
print("未知的数据类型{}".format(file_type))
return table_data
except Exception as e:
print("读取失败:{}".format(e))
@staticmethod
def form_heard_style() -> any:
"""
写入.xls表格的头部样式
:return: heard_style
"""
heard_style = xlwt.XFStyle()
font = xlwt.Font()
font.name = '微软雅黑'
font.colour_index = 0x0A
font.height = 20 * 13
font.bold = False
font.underline = False
font.italic = False
style.font = font
alignment = xlwt.Alignment()
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.horz = 0x02
# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.vert = 0x01
heard_style.alignment = alignment
return heard_style
@staticmethod
def form_body_style() -> any:
"""
写入.xls表格的内容样式
:return: body_style
"""
body_style = xlwt.XFStyle()
font = xlwt.Font()
font.name = '微软雅黑'
font.colour_index = 0x0A
font.height = 20 * 11
alignment = xlwt.Alignment()
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.horz = 0x02
# 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)
alignment.vert = 0x01
body_style.alignment = alignment
return body_style
def write_excel(self, field_data, data) -> None:
"""
将数据写入Excel表
:param field_data: 表头数据（类型为list）
:param data: 表格内容数据（类型为二维数组）
:return:
"""
# 获取文件的后缀名
file_type = os.path.splitext(self.path)[-1]
if not os.path.exists(self.path):
# 实例化
work_book = Workbook() # 如果文件不存在，则创建文件
# 保存文件
work_book.save(self.path)
try:
# 判断表格格式
if file_type == self.FILE_SUFFIX_XLSX:
work_book = load_workbook(self.path) # 打开加载文件
# 读取表
work_sheet = work_book[self.sheet_name]
# 获取当前Sheet表格的最大行
row_max = work_sheet.max_row
# 写入表头数据
for i in field_data:
work_sheet.cell(row=row_max, column=field_data.index(i) + 1).value = i
# 设置单元格以及内容样式
letter = openpyxl.utils.get_column_letter(field_data.index(i) + 1)
work_sheet.column_dimensions[letter].width = 25
align = Alignment(horizontal='center', vertical='center')
work_sheet.cell(row=row_max, column=field_data.index(i) + 1).alignment = align
work_sheet.row_dimensions[row_max].height = 30
font = Font(name="微软雅黑", size=14, color='FF0000', bold=True, italic=False)
work_sheet.cell(row=row_max, column=field_data.index(i) + 1).font = font
# 写入表格内容
for i in range(len(data)):
row_max += 1
for j in field_data:
work_sheet.cell(row=row_max, column=field_data.index(j) + 1).value = data[i][
field_data.index(j)]
# 设置单元格以及内容样式
letter = openpyxl.utils.get_column_letter(field_data.index(j) + 1)
work_sheet.column_dimensions[letter].width = 25
align = Alignment(horizontal='center', vertical='center')
work_sheet.cell(row=row_max, column=field_data.index(j) + 1).alignment = align
work_sheet.row_dimensions[row_max].height = 20
work_book.save(self.path)
print("写入完成，一共写入{}条数据".format(len(data)))
elif file_type == self.FILE_SUFFIX_XLS:
# 读取表
work_book = xlwt.Workbook()
work_sheet = work_book.add_sheet(self.sheet_name)
for i in field_data:
tall_style = xlwt.easyxf('font:height 720')
first_row = work_sheet.row(0) # 获取sheet页的第一行
first_row.set_style(tall_style) # 给第一行设置tall_style样式，也就是行高
work_sheet.col(field_data.index(i)).width = 256 * 25
work_sheet.write(0, field_data.index(i), i, self.form_heard_style())
style1 = self.form_body_style()
for i in range(len(data)):
for j in field_data:
work_sheet.write(i + 1, field_data.index(j), data[i][field_data.index(j)], style1)
tall_style = xlwt.easyxf('font:height 400')
row = work_sheet.row(i + 1)
row.set_style(tall_style)
work_book.save(self.path)
print("写入完成，一共写入{}条数据".format(len(data)))
except Exception as e:
print("写入失败:{}".format(e))
Excel("test_data1.xlsx", "Sheet2").write_excel(student)
Excel("test_data.xls", "Sheet1").read_excel() | 
发表于 2023-7-25 10:02
|
发表于 2023-7-25 16:27
