• python3 excel基本操作及格式设置


    #encoding=utf-8
    '''
    excel基本操作整理
    '''
    #openpyxl 版本2.5.4
    from openpyxl import *
    import datetime as dt
    from datetime import datetime as dtt
    
    #新建create_sheet、copy_worksheet
    
    wb=Workbook()
    wb.create_sheet('新创建sheet')
    
    ws=wb.active
    print(ws.title)
    ws.title='测试用例'
    
    ws1=wb.copy_worksheet(ws)
    ws1.title='测试步骤'
    print(ws1.title)
    ws1.sheet_properties.tabColor='FF3030'  #设置sheet标签颜色
    
    ws2=wb.copy_worksheet(ws1)
    ws2.title='测试数据'
    
    #新建sheet,设置了位置2
    wb.create_sheet('第三个位置sheet',2)
    
    #sheet写入数据
    
    ws['A2']='大家好'
    ws['B4']=dtt.now()
    ws.append(['nihao','wohao','heihei123',4,5,6])
    
    wb.save('f:\pylianxi\test\test.xlsx')
    
    
    #加载已存在的excel
    
    wb=load_workbook('f:\pylianxi\test\test.xlsx')
    
    print('load active_sheet:',wb.active.title)
    print(wb.sheetnames)
    print('测试数据的标题:',wb['测试数据'].title)
    
    for i in wb['测试用例'].rows:
        for j in i:
            print(j.value,end=' ')
        print()
    
    print(type(wb))
    print(type(wb['测试用例']))
    for sheet in wb:
        print('**',sheet.title)
    
    #使用cell读写单元格
    ws=wb['测试用例']
    
    #写入
    ws.cell(row=2,column=1).value='大家真的好吗'  #行号和列号从1开始
    ws.cell(row=5,column=3,value='设么东东')
    
    wb.save('f:\pylianxi\test\test.xlsx')
    
    #
    print('ws.cell(row=2,column=1).value:',ws.cell(row=2,column=1).value)
    print('ws.cell(row=5,column=3).value:',ws.cell(5,3).value)
    
    #小练习
    
    for i in range(1,4):
        for j in range(1,4):
            ws.cell(i,j).value=i*j
    
    wb.save('f:\pylianxi\test\test.xlsx')
    
    #可以设置默认打开的sheet页
    #wb.active=1 默认是0
    
    #注意 cell的 行列 是从1,1开始的,不是0,0,rows、columns的迭代器都是从0开始的
    
    print('#'*20)
    #取某一列
    for cell in ws["C"]:
        print (cell.value)
    
    #取多列的值
    for i in ws['A':'C']:
    #for column in ws["A:C"]:
        for j in i:
            print(j.value)
    
    #按列取
    print('*'*20)
    for i in ws[1:3]:
        for j in i:
            print(j.value)
    
    print('*'*20)
    #指定区域范围取值
    for i in ws.iter_rows(min_row=1,max_row=2,min_col=1,max_col=2):
        for j in i:
            print(j.value)
    
    print('*'*20)
    print(list(ws.rows)[1:]) #从第2行开始取值
    for i in ws.rows:
        print(i)
        for j in i:
            print(j.value)
    
    print('*'*20)
    #按列取值
    for i in ws.columns:
        for j in i:
            print(j.value,end=' ')
        print()
    
    #获取最大行列,最小行列
    print(ws.max_row,ws.min_row,ws.max_column,ws.min_column)
    
    #
    print(ws.rows) #行生成器
    print(list(ws.rows)[0])#第一行
    print(list(ws.rows)[0][0])#第一个单元格
    print(list(ws.rows)[0][0].value)#第一个单元格的值
    
    print(ws.columns) #列生成器
    print(list(ws.columns)[0])#第一列
    print(list(ws.columns)[0][0])#列第一个单元格
    print(list(ws.columns)[0][0].value)#列第一个单元格的值
    
    
    #单元格格式 number_format
    
    import datetime
    wb = load_workbook('e:\sample.xlsx')
    
    ws=wb.active
    wb.guess_types = True
    
    ws["A1"]=datetime.datetime(2010, 7, 21)
    print (ws["A1"].number_format)
    
    ws["A2"]="12%"
    print (ws["A2"].number_format)
    
    ws["A3"]= 1.1
    print (ws["A3"].number_format)
    
    ws["A4"]= "中国"
    print (ws["A4"].number_format)
    # Save the file
    wb.save("e:\sample.xlsx")
    
    #guess_types  猜测单元格的类型
    
    wb=load_workbook('e:\sample.xlsx')
    wb.guess_types=True  #可以猜测 存储的某字段值 是什么类型
    
    ws=wb.active
    
    ws['D2']='12%'  #如果是True 则会按0.12处理,否则会按str处理
    print(ws['D2'].value)
    print(type(ws['D2'].value))
    wb.save('e:\sample.xlsx')
    
    
    #单元格输入公式,打开excel会自动计算
    
    ws["A4"] = "=SUM(1, 1)"
    ws["A5"] = "=SUM(A1:A3)"
    
    print (ws["A4"].value)  #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值
    print (ws["A5"].value)  #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值
    
    #合并单元格(2种方式)
    #ws.merge_cells('A2:D2')  #合并完了之后取值,按A2取值,B2、C2、D2取不到值
    #ws.unmerge_cells('A2:D2')
    
    print("ws['A2'].value:",ws['A2'].value)
    
    
    #合并单元格区域
    ws.merge_cells(start_row=1,start_column=1,end_row=2,end_column=4)
    ws.unmerge_cells(start_row=1,start_column=1,end_row=2,end_column=4)
    
    #单元格插入图片
    from openpyxl.drawing.image import Image
    
    img = Image('e:\Snap1.jpg')
    #ws.add_image(img, 'A15')
    wb.save('e:\sample.xlsx')
    
    #设置单元格字体
    from openpyxl.styles import colors
    from openpyxl.styles import Font
    
    ft=Font(name="楷体",color=colors.BLUE,size=20,bold=True,underline="single",family=1) 
    
    ws['A1'].font=ft
    ws['A1']='你好吗哈哈'
    wb.save('e:\sample.xlsx')
    
    
    #比较全面的设置单元格格式
    
    from openpyxl import Workbook
    from openpyxl.styles import Font
    from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill
    from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
    
    wb = Workbook()
    ws = wb.active
    
    ft = Font(name=u'微软雅黑',
        size=11,
        bold=False,
        italic=False,
        vertAlign=None,
        underline='none',
        strike=False,
        color='FF000000')
    
    #ft=Font(bold=True, size=20,color= "ff0100")
    
    #fill = PatternFill(fill_type="solid",start_color='FFEEFFFF',end_color='FF001100')
    fill = PatternFill("solid", fgColor="DDDDDD")
    
    #边框可以选择的值为:'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin']
    #diagonal 表示对角线
    bd = Border(left=Side(border_style="thin",
                  color='FF001000'),
        right=Side(border_style="thin",
                   color='FF110000'),
        top=Side(border_style="thin",
                 color='FF110000'),
        bottom=Side(border_style="thin",
                    color='FF110000'),
        diagonal=Side(border_style=None,
                      color='FF000000'),
        diagonal_direction=0,
        outline=Side(border_style=None,
                     color='FF000000'),
        vertical=Side(border_style=None,
                      color='FF000000'),
        horizontal=Side(border_style=None,
                       color='FF110000')
                    )
    #bdline = Side(style='thick', color="000000")
    #bd = Border(left=bdline, top=bdline, right=bdline, bottom=bdline)
    
    alignment=Alignment(horizontal='general',
            vertical='bottom',
            text_rotation=0,
            wrap_text=False,
            shrink_to_fit=False,
            indent=0)
    
    number_format = 'General'
    
    protection = Protection(locked=True,hidden=False)
    
    ws["B5"].font = ft
    ws["B5"].fill =fill
    ws["B5"].border = bd
    ws["B5"].alignment = alignment
    ws["B5"].number_format = number_format
    
    ws["B5"].value ="glory road"
    
    # Save the file
    wb.save("e:\sample.xlsx")
  • 相关阅读:
    Nginx使用
    nginx常见配置详解
    配置yum源
    nginx常见使用方式和日志功能
    SpringCloud学习篇《一》
    myeclipse的各种背景:黑色,护眼,欢迎围观
    java基础二 <流程控制语句, 方法,数组,java内存结构> 未完待续...
    fastjson解析超长json串以及转成list,map等方法实例
    Linux下权限的修改-JDK的配置-文件的常见操作
    java面试基础大全,绝对经典<126-170><转>
  • 原文地址:https://www.cnblogs.com/xiaoxiao075/p/10858449.html
Copyright © 2020-2023  润新知