openpyxl详解

# 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)