python openpyxl

标签：sheet openpyxl python cell sheet0 workbook col row

import openpyxl
from openpyxl.styles import Font, Border, Side, Alignment, Color,PatternFill,colors


def readExel(filename = "D:/test.xlsx"):
    inwb = openpyxl.load_workbook(filename)  # 读文件

    sheetnames = inwb.get_sheet_names()  # 获取读文件中所有的sheet，通过名字的方式
    ws = inwb.get_sheet_by_name(sheetnames[0])  # 获取第一个sheet内容

    # 获取sheet的最大行数和列数
    rows = ws.max_row
    cols = ws.max_column
    for r in range(1, rows):
        for c in range(1, cols):
            print(ws.cell(r, c).value)


def deleteExcelData(filename = "D:/test.xlsx"):
    workbook = openpyxl.load_workbook(filename)  # 读文件
    ws = workbook.active  # 获取当前活跃的worksheet对象（sheet表）
    #删除行
    ws.delete_rows(2)
    workbook.save(filename)

def writeExcel(saveExcel = "D:/test.xlsx"):
    workbook = openpyxl.Workbook()  # 打开一个将写的文件
    sheet0 = workbook.create_sheet(index=0)  # 在将写的文件创建sheet
    # fgColor   前景色
    # bgColor   后景色
    # 参数可选项
    patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical',
                   'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis',
                   'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp',
                   'lightHorizontal', 'darkTrellis', 'darkVertical'}
    # sheet1 = outwb.create_sheet(index=1)
    for row in range(1, 64):
        for col in range(1, 4):
            sheet0.cell(row, col).value = row * 2  # 写文件i
            # b:是否粗字体，i:是否斜字体
            # sheet0.cell(row, col).font = Font(name='宋体', size=12, color=Color(index=4), b=False, i=False)
            sheet0.cell(row, col).font = Font(name='宋体', size=12, color=Color(index=row), b=False, i=False)
            # 左右，上下对齐设置
            sheet0.cell(row, col).alignment= Alignment(horizontal='center',vertical='center')
            #设置背景色
            sheet0.cell(row, col).fill=PatternFill("solid", fgColor=colors.YELLOW)
            # sheet1.cell(row, col).value = row * 2
        print(row)

    workbook.save(saveExcel)  # 一定要记得保存
    workbook.close()

#设置单元格的高度和宽度
def setExceCellWidHeight(saveExcel = "D:/test.xlsx"):
    workbook = openpyxl.Workbook()  # 打开一个将写的文件
    sheet0 = workbook.create_sheet(index=0)  # 在将写的文件创建sheet
    #设置第一行高度为30
    row = sheet0.row_dimensions[1]
    row.height = 30

    #设置第一列宽带为50
    # 不能传1，2，3只能用 A，B，C
    # col = sheet0.column_dimensions[1]
    col = sheet0.column_dimensions['A']
    col.width = 50
    # sheet1 = outwb.create_sheet(index=1)
    for row in range(1, 10):
        for col in range(1, 4):
            sheet0.cell(row, col).value = row * 2  # 写文件
            # sheet1.cell(row, col).value = row * 2
    workbook.save(saveExcel)  # 一定要记得保存
    workbook.close()


if __name__=="__main__":
    writeExcel()
    # readExel()
    # deleteExcelData()
    # setExceCellWidHeight()

View Code

颜色

Color(index=0) # 根据索引进行填充
# 
Color(rgb='00000000') # 根据rgb值进行填充
# index 
COLOR_INDEX = (
    '00000000', '00FFFFFF', '00FF0000', '0000FF00', '000000FF', #0-4
    '00FFFF00', '00FF00FF', '0000FFFF', '00000000', '00FFFFFF', #5-9
    '00FF0000', '0000FF00', '000000FF', '00FFFF00', '00FF00FF', #10-14
    '0000FFFF', '00800000', '00008000', '00000080', '00808000', #15-19
    '00800080', '00008080', '00C0C0C0', '00808080', '009999FF', #20-24
    '00993366', '00FFFFCC', '00CCFFFF', '00660066', '00FF8080', #25-29
    '000066CC', '00CCCCFF', '00000080', '00FF00FF', '00FFFF00', #30-34
    '0000FFFF', '00800080', '00800000', '00008080', '000000FF', #35-39
    '0000CCFF', '00CCFFFF', '00CCFFCC', '00FFFF99', '0099CCFF', #40-44
    '00FF99CC', '00CC99FF', '00FFCC99', '003366FF', '0033CCCC', #45-49
    '0099CC00', '00FFCC00', '00FF9900', '00FF6600', '00666699', #50-54
    '00969696', '00003366', '00339966', '00003300', '00333300', #55-59
    '00993300', '00993366', '00333399', '00333333',  #60-63
)
BLACK = COLOR_INDEX[0]
WHITE = COLOR_INDEX[1]
RED = COLOR_INDEX[2]
DARKRED = COLOR_INDEX[8]
BLUE = COLOR_INDEX[4]
DARKBLUE = COLOR_INDEX[12]
GREEN = COLOR_INDEX[3]
DARKGREEN = COLOR_INDEX[9]
YELLOW = COLOR_INDEX[5]
DARKYELLOW = COLOR_INDEX[19]

字体

sheet0.cell(row, col).font = Font(name='宋体', size=12, color=Color(index=row), b=False, i=False)

# size   sz  字体大小
# b bold  是否粗体
# i italic  是否斜体
# name family  字体样式

边框

Side(style='thin',color=Color(index=0))

# style可选项
style = ('dashDot','dashDotDot', 'dashed','dotted',
'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
'mediumDashed', 'slantDashDot', 'thick', 'thin')
#  'medium' 中粗
#  'thin'  细
#  'thick'  粗
#  'dashed'  虚线
#  'dotted'  点线

填充

PatternFill(patternType='solid',fgColor=Color(), bgColor=Color())
# fgColor   前景色
# bgColor   后景色
# 参数可选项
patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', 
               'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', 
               'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', 
               'lightHorizontal', 'darkTrellis', 'darkVertical'}

ws.cell(3,3).fill = PatternFill()

对齐

Alignment(horizontal='fill',vertical='center')

# 参数可选项
horizontal = {'fill', 'distributed', 'centerContinuous', 'right',
              'justify', 'center', 'left', 'general'}

vertical = {'distributed', 'justify', 'center', 'bottom', 'top'}

ws.cell(3,3).alignment= Alignment()

 

标签：sheet,openpyxl,python,cell,sheet0,workbook,col,row
From： https://www.cnblogs.com/shaosks/p/17073394.html