美文网首页
python的openpyxl的使用笔记

python的openpyxl的使用笔记

作者: 8251d0cd9fde | 来源:发表于2019-02-25 11:25 被阅读61次

    OpenPyXL文稿
    http://openpyxl.readthedocs.io/en/stable/

    openpyxl模块介绍

    openpyxl模块是一个读写Excel 2010文档的Python库,如果要处理更早格式的Excel文档,需要用到额外的库,openpyxl是一个比较综合的工具,能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。

    安装openpyxl模块

    openpyxl是一个开源项目,这里使用如下命令安装openpyxl模块

    pip3 install openpyxl
    

    一.作成Excel文件

    1.作成空的excel

    import openpyxl
    
    if __name__ == '__main__':
        
        #作成一个新的空的Excel
        wb = openpyxl.Workbook()
    
        # 保存
        wb.save('example.xlsx')
    

    2.变更默认sheet页的名称

    import openpyxl
    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook
        #当前打开的sheet页 wb.active
        ws = wb.active
    
        # 更改默认名称Sheet`  
        ws.title = "WorkSheetTitle"
    
        # 保存
        wb.save('example.xlsx')
    

    3.作成含多个sheet页的Excel

    import openpyxl
    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook()
        #当前打开的sheet页 wb.active
        ws = wb.active
    
        # #更改默认名称Sheet`
        ws.title = "WorkSheetTitle"
    
        # 定义第二个sheet页
        ws2 = wb.create_sheet("NewWorkSheet2")
    
        # 定义第三个sheet页
        # `0` 的设定 会将该sheet页 置于wb最前面
        ws3 = wb.create_sheet("NewWorkSheet3", 0)
    
        # 保存
        wb.save('example.xlsx')
    

    4.sheet页WS的tab颜色变更

    import openpyxl
    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook()
        ws = wb.active
    
         # 更改默认名称Sheet`
        ws.title = "WorkSheetTitle"
    
        # WS的tab颜色设定
        ws.sheet_properties.tabColor = "1072BA"
    
        # 保存
        wb.save('example.xlsx')
    

    二.单元格赋值

    1.指定坐标

    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook()
        ws = wb.active
    
        # 更改默认名称Sheet`
        ws.title = "WorkSheetTitle"
    
        # 给单元格赋值
        ws["A1"] = "HOGE"
        ws["B1"] = "FUGA"
    
        # 保存
        wb.save('example.xlsx')
    

    2.指定行列

    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook()
        ws = wb.active
    
        # 更改默认名称Sheet`
        ws.title = "WorkSheetTitle"
    
        # 指定行列给单元格赋值
        ws.cell(row=4, column=2, value=10)
    
        # 保存
        wb.save('example.xlsx')
    

    3.指定范围

    import openpyxl
    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook()
        ws = wb.active
    
         # 更改默认名称Sheet`
        ws.title = "worksheettitle"
    
        # 指定行列给单元格赋值
        v = 0
        for i in range(1,10):
            for n in range(1,10):
                ws.cell(row=i, column=n, value=v)
                v += 1
    
        # 保存
        wb.save('example.xlsx')
    

    4.一次输出行

    import openpyxl
    
    # column名
    column_title = ["FirstName", "LastName"]
    
    if __name__ == '__main__':
        """
        CELL放入值
        """
        wb = openpyxl.Workbook()
        ws = wb.active
    
        # 更改默认名称Sheet`
        ws.title = "worksheettitle"
    
        # column名和値顺序放入单元格中
        rows = [
            column_title,
            ["Tarou", "Tanaka"],
            ["Tarou", "Suzuki"],
            ["Tarou", "Uchiayama"],
        ]
        for row in rows:
            ws.append(row)
    
        # 保存
        wb.save('example.xlsx')
    
    一次输出行运行结果

    5.单元格内换行

    import openpyxl
    
    if __name__ == '__main__':
        """
        单元格内换行
        """
        wb = openpyxl.Workbook()
        ws = wb.active
    
        # 更改默认名称Sheet`
        ws.title = "WorkSheetTitle"
    
        # 单元格内换行
        ws['A1'] = "A\nB\nC"
        ws['A1'].alignment = openpyxl.styles.Alignment(wrapText=True)
    
        # 保存
        wb.save('example.xlsx')
    
    单元格内换行运行结果图

    三.设置单元格的style

    1.style文稿

    https://openpyxl.readthedocs.io/en/default/styles.html#cell-styles-and-named-styles

    2.设置字体font

    import openpyxl
    
    if __name__ == '__main__':
        """
        设置字体font
        """
        wb = openpyxl.Workbook()
        ws = wb.active
    
         # 更改默认名称Sheet`
        ws.title = "worksheettitle"
    
        # 设置font
        font = openpyxl.styles.Font(
            name = "宋体",
            size = 15,
        )
        a1 = ws["A1"]
        a1.font = font
        a1.value = "TEST"
    
        # 保存
        wb.save('example.xlsx')
    

    3.单元格边框border

    import openpyxl
    from openpyxl.styles import Border, Side
    
    if __name__ == '__main__':
        """
        设置单元格style
        """
        wb = openpyxl.Workbook()
        ws = wb.active
    
        # 更改默认名称Sheet`
        ws.title = "worksheettitle"
    
        # 设置单元格border的style
        border = Border(
            left=Side(
                border_style="thin",
                color="FF0000"
            ),
            right=Side(
                border_style="thin",
                color="FF0000"
            ),
            top=Side(
                border_style="thin",
                color="FF0000"
            ),
            bottom=Side(
                border_style="thin",
                color="FF0000"
    
            )
        )
        b2 = ws["B2"]
        b2.border = border
        b2.value = "TEST"
    
        # 保存
        wb.save('example.xlsx')
    
    单元格边框style 运行结果图

    4.合并单元格

    import openpyxl
    
    if __name__ == '__main__':
       
        wb = openpyxl.Workbook()
        ws = wb.active
    
        ws.title = "worksheettitle"
    
        # 合并单元格
        ws.merge_cells("A1:E1")
        ws["A1"] = "HOGE"
    
        # 保存
        wb.save('example.xlsx')
    
    合并单元格 运行效果图

    5.单元格填充颜色

    import openpyxl
    from openpyxl.styles import PatternFill
    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook()
        ws = wb.active
    
        ws.title = "worksheettitle"
    
        # 单元格填充颜色
        fill = PatternFill(fill_type='solid',
                           fgColor='FFFF0000')
        b2 = ws["B2"]
        b2.fill = fill
        b2.value = "TEST"
    
        # 保存
        wb.save('example.xlsx')
    
    单元格填充颜色 效果图

    四.hyperlink超链接

    import openpyxl
    
    if __name__ == '__main__':
        
        wb = openpyxl.Workbook()
        ws = wb.active
    
        ws.title = "worksheettitle"
    
        # 作成第二个sheet页 名称胃example
        ws2 = wb.create_sheet("example")
    
        # 设置超链接 到“example”sheet页 鼠标定格在A5单元格
        ws["A1"] = "Link"
        ws["A1"].hyperlink = "example.xlsx#example!A5"
    
        # 保存
        wb.save('example.xlsx')
    

    五.读Excel文档

    from openpyxl import Workbook, load_workbook
    
    wb = load_workbook('./example.xlsx')
    ws = wb.active
    
    for row in ws:
        for cell in row:
            print(cell)
    

    相关文章

      网友评论

          本文标题:python的openpyxl的使用笔记

          本文链接:https://www.haomeiwen.com/subject/hsdwyqtx.html