openpyxl样式详解17

pip install openpyxl

""" 1.警告：openpyxl只可以操作xlsx格式的文件，如果想操作xls请使用xlrd 这边 索引是从1开始的

更离谱的是 创建sheet页面的索引竟然是从0开始的

# 然后还有一点，就是读取值的时候  如果是公式会读出来None，这会在windows打开文件，再关闭就可以了，；linux不知道怎么解决

""" from openpyxl import load_workbook

xls_file_src = "xls_demo.xls" xlsx_file_src = "xlsx_demo.xlsx"

#############获取sheet名字############### def get_sheet_names(): wb = load_workbook(xlsx_file_src) sheet_names = wb.sheetnames print(sheet_names)

############最大值############### def get_max_index(): wb = load_workbook(xlsx_file_src) sheeet_names = wb.sheetnames for sheet_name in sheeet_names: sheet_obj = wb.get_sheet_by_name(sheet_name) max_rows = sheet_obj.max_row max_cols = sheet_obj.max_column print(f"sheet_name = {sheet_name} max_rows={max_rows} max_cols={max_cols}")

############获取所有的值############### def get_cell_value(): wb = load_workbook(xlsx_file_src) sheeet_names = wb.sheetnames for sheet_name in sheeet_names: sheet_obj = wb.get_sheet_by_name(sheet_name) max_rows = sheet_obj.max_row max_cols = sheet_obj.max_column for row_index in range(1,max_rows+1): for col_index in range(1,max_cols+1): value = sheet_obj.cell(row_index,col_index).value print(value)

############用window包 打开xlsx文件 def win32_open_file(file_src): # python -m pip install pypiwin32 import os from win32com import client as wc xl = wc.DispatchEx("Excel.Application") ab_file_src = os.path.abspath(xlsx_file_src) wb = xl.workbooks.open(ab_file_src) xl.Visible = False wb.Save() xl.Quit()

############获取公式计算以后的值###############

def get_cell_value(): win32_open_file(file_src=xlsx_file_src) wb = load_workbook(xlsx_file_src,data_only=True) sheeet_names = wb.sheetnames for sheet_name in sheeet_names: sheet_obj = wb.get_sheet_by_name(sheet_name) max_rows = sheet_obj.max_row max_cols = sheet_obj.max_column for row_index in range(1,max_rows+1): for col_index in range(1,max_cols+1): value = sheet_obj.cell(row_index,col_index).value print(value)

#######设置样式########### from openpyxl.styles import Border, Side, Font, Alignment def _set_cell_style(cell): cell.font = Font(name='微软雅黑', size=11, color="000000") cell.alignment = Alignment(horizontal="center", vertical="center") cell.border = Border(right=Side( border_style="thin", color="000000")) return cell

###########设置样式########### def set_style(): wb = load_workbook(xlsx_file_src) sheeet_names = wb.sheetnames for sheet_name in sheeet_names: sheet_obj = wb.get_sheet_by_name(sheet_name) max_rows = sheet_obj.max_row max_cols = sheet_obj.max_column for row_index in range(1,max_rows+1): for col_index in range(1,max_cols+1): cell = sheet_obj.cell(row_index,col_index) value = cell.value

from openpyxl.styles import Border, Side, Font, Alignment
            cell.font = Font(name='微软雅黑', size=23, color="000000")
            cell.alignment = Alignment(horizontal="center", vertical="center")
            cell.border = Border(right=Side(
                border_style="thin", color="000000"))
wb.save(xlsx_file_src)

############创建一个新的sheet################ def new_sheet(): wb = load_workbook(xlsx_file_src)

sheet_obj = wb.create_sheet(title="新的sheet Name",index=0)
wb.save(xlsx_file_src)

样式 ############创建一个测试字体################ wb = load_workbook(xlsx_file_src) sheet_obj = wb.create_sheet(title="测试样式",index=0)

测试字体

cell = sheet_obj.cell(1,1) cell.value = "字体" cell.font = Font(name="微软雅黑",size=20,color="FFDAB9")

测试背景色

cell = sheet_obj.cell(1,2) cell.value = "背景色" cell.fill = PatternFill(start_color="FFDAB9",fill_type="solid")

长宽

width = 20 height = 50 cell = sheet_obj.cell(1,3) cell.value = f"底{width} 高{height}" sheet_obj.row_dimensions[1].height=height sheet_obj.column_dimensions["C"].width=width

测试字体位置

cell = sheet_obj.cell(1,4) sheet_obj.column_dimensions["D"].width=width cell.value = "对齐方式" cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)

border_style_list = [ "dashDot", "dashDotDot", "dashed", "dotted", "double", "hair", "medium", "mediumDashDot", "mediumDashDotDot", "mediumDashed", "slantDashDot", "thick", "thin", ] for row_index in range(1,len(border_style_list)+1): sheet_obj.row_dimensions[row_index].height = height cell = sheet_obj.cell(row_index, 5) cell.value = "边框" cell.border = Border(left=Side(border_style=border_style_list[row_index-1], color='000000'), # right=Side(border_style='mediumDashed', color='000000'), # top=Side(border_style='double', color='000000'), # bottom=Side(border_style='dashed', color='000000') )

wb.save(xlsx_file_src)