Python使用openpyxl操作Excel文件
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, fills, colors, Side
class excel_openpyxl:
def __init__(self, file_name, sheet_index):
self.file_name = file_name
self.sheet_index = sheet_index
def create_excel_file(self):
try:
self.work_book = Workbook()
self.work_book.save(self.file_name)
except Exception as error:
print("Function create_excel_file error:", error)
def load_work_book(self):
try:
self.work_book = load_workbook(self.file_name)
self.work_sheet = self.work_book.worksheets[self.sheet_index]
except Exception as error:
print("Function load_work_book error=", error)
def append_excel_row(self, rows_contents):
try:
for i in range(len(rows_contents)):
self.work_sheet.append(rows_contents[i])
self.work_book.save(self.file_name)
except Exception as error:
print("Function append_excel_row error=", error)
def get_cell_data_by_coord(self, coord):
try:
cell_value = self.work_sheet[coord].value
return cell_value
except Exception as error:
print("Function get_cell_data_by_coord error", error)
def get_cell_data_by_num(self, row_num, col_num):
try:
cell = self.work_sheet.cell(row_num, col_num)
return cell.value
except Exception as error:
print("Function get_cell_data_by_num error=", error)
def get_cells_data(self, coord):
try:
cells = self.work_sheet[coord]
return cells
except Exception as error:
print("Function get_cells_data error=", error)
def get_all_rows_data(self):
try:
cells = self.work_sheet.rows
return cells
except Exception as error:
print("Function get_all_rows_data error=", error)
def get_all_cols_data(self):
try:
cells = self.work_sheet.columns
return cells
except Exception as error:
print("Function get_all_cols_data error=", error)
def get_iter_rows_data(self, min_row, max_row, min_col, max_col):
try:
cells = self.work_sheet.iter_rows(min_row, max_row, min_col, max_col)
return cells
except Exception as error:
print("Function get_iter_rows_data error=", error)
def get_iter_cols_data(self, min_col, max_col, min_row, max_row):
try:
cells = self.work_sheet.iter_cols(min_col, max_col, min_row, max_row)
return cells
except Exception as error:
print("Function get_iter_cols_data error=", error)
def modify_cell_data(self, coord, content):
try:
cell = self.work_sheet[coord]
cell.value = content
self.work_book.save(self.file_name)
except Exception as error:
print("Function modify_cell_data error=", error)
def insert_rows(self, row_index, amount):
try:
self.work_sheet.insert_rows(row_index, amount)
self.work_book.save(self.file_name)
except Exception as error:
print("Function insert_rows error=", error)
def insert_cols(self, col_index, amount):
try:
self.work_sheet.insert_cols(col_index, amount)
self.work_book.save(self.file_name)
except Exception as error:
print("Function insert_rows error=", error)
def delete_rows(self, row_index, amount):
try:
self.work_sheet.delete_rows(row_index, amount)
self.work_book.save(self.file_name)
except Exception as error:
print("Function insert_rows error=", error)
def delete_cols(self, col_index, amount):
try:
self.work_sheet.delete_cols(col_index, amount)
self.work_book.save(self.file_name)
except Exception as error:
print("Function insert_rows error=", error)
def create_sheet(self, sheet_name):
try:
self.work_book.create_sheet(sheet_name)
self.work_book.save(self.file_name)
except Exception as error:
print("Function create_sheet error=", error)
def remove_sheet(self, sheet_name):
try:
sheet = self.work_book[sheet_name]
self.work_book.remove(sheet)
self.work_book.save(self.file_name)
except Exception as error:
print("Function remove_sheet error=", error)
def set_font(self, sheet_index):
try:
sheet = self.work_book.worksheets[sheet_index]
sheet.column_dimensions["A"].width = 20
sheet.column_dimensions["B"].width = 20
sheet.column_dimensions["C"].width = 20
font = Font("微软雅黑", size=20, color=colors.BLACK, bold=False)
fill = PatternFill(
fill_type="solid", start_color="CDCDCD", end_color="CDCDCD"
)
alignment = Alignment(horizontal="center", vertical="center", indent=0)
bd = Border(
left=Side(border_style="thin", color=colors.BLACK),
right=Side(border_style="thin", color=colors.BLACK),
top=Side(border_style="thin", color=colors.BLACK),
bottom=Side(border_style="thin", color=colors.BLACK),
outline=Side(border_style="thin", color=colors.BLACK),
vertical=Side(border_style="thin", color=colors.BLACK),
horizontal=Side(border_style="thin", color=colors.BLACK),
)
for irow, row in enumerate(sheet.rows, start=1):
font = font
fill = fill
alignment = alignment
border = bd
for cell in row:
cell.font = font
cell.fill = fill
cell.alignment = alignment
cell.border = bd
header_font = Font("宋体", size=12, color=colors.BLUE, bold=True)
sheet["A1"].font = header_font
sheet["B1"].font = header_font
sheet["C1"].font = header_font
self.work_book.save(self.file_name)
except Exception as error:
print("Function set_font error=", error)
file_name = "openpyxl.xlsx"
rows_content = [["header1", "header2", "header3"], ["content1", "content2", "content3"]]
myExcel = excel_openpyxl(file_name, 0)
myExcel.create_excel_file()
myExcel.load_work_book()
myExcel.append_excel_row(rows_content)
cells = myExcel.get_cells_data("A1:C2")
for i in cells:
for j in i:
print(j.value)
cells = myExcel.get_cells_data("2")
for i in cells:
print(i.value)
cells = myExcel.get_cells_data("A")
for i in cells:
print(i.value)
cells = myExcel.get_cells_data("1:3")
for i in cells:
for j in i:
print(j.value)
cells = myExcel.get_cells_data("A:B")
for i in cells:
for j in i:
print(j.value)
myExcel.insert_rows(2, 2)
cells = myExcel.get_all_rows_data()
for i in cells:
for j in i:
print(j.value)
myExcel.insert_cols(2, 2)
cells = myExcel.get_all_cols_data()
for i in cells:
for j in i:
print(j.value)
myExcel.delete_rows(2, 2)
cells = myExcel.get_iter_rows_data(1, 2, 1, 3)
for i in cells:
for j in i:
print(j.value)
myExcel.delete_cols(2, 2)
cells = myExcel.get_iter_cols_data(1, 3, 1, 2)
for i in cells:
for j in i:
print(j.value)
print(myExcel.get_cell_data_by_coord("B2"))
myExcel.modify_cell_data("B2", "NewContent")
print(myExcel.get_cell_data_by_coord("B2"))
print(myExcel.work_book.sheetnames)
myExcel.create_sheet("New_Sheet")
print(myExcel.work_book.sheetnames)
myExcel.remove_sheet("New_Sheet")
print(myExcel.work_book.sheetnames)
myExcel.set_font(0)