文章目录

• 一、读取Excel
• • 1、载入Excel
  • 2、获取工作表名称
  • 3、获取工作表对象
  • 4、获取表格内容所在的范围
  • 5、获取单元格的具体内容
  • 6、获取单元格的所在行、列、坐标
  • 7、获取多个单元格的值
• 二、写入Excel
• • 1、写入单元格
  • 2、写入一行或者多行数据
  • 3、将公式写入单元格
  • 4、插入一行或多行
  • 5、插入一列或多列
  • 6、删除多行或多列
  • 7、移动范围数据
• 三、创建新的Excel表格
• 四、保存Excel
• 五、样式调整
• • 1、设置字体样式
  • 2、设置对齐样式
  • 3、设置边框样式
  • 4、设置单元格填充样式
  • 5、设置行高和列宽
  • 6、单元格合并与取消
• 六、单元格格式设置

一、读取Excel

1、载入Excel

from openpyxl import load_workbook
from openpyxl.styles import Font,Alignment,Side,Border,PatternFill

workbook = load_workbook(filename=r'C:\Users\liu\Desktop\报表汇总.xlsx')

2、获取工作表名称

name_lst = workbook.sheetnames

3、获取工作表对象

sheet = workbook['5、应收余额表']
# 只有一张工作表的时候
sheet = workbook.active

4、获取表格内容所在的范围

# 表格范围
print(sheet.dimensions)
# 最大行数
print(sheet.max_row)
# 最大列数
print(sheet.max_column)

5、获取单元格的具体内容

cell = sheet['A4'] # 指定坐标
cell1 = sheet.cell(row=4,column=2) # 指定行列
print(cell.value, cell1.value)

6、获取单元格的所在行、列、坐标

from openpyxl import utils

# 列文本转数值
a = utils.column_index_from_string('C') # 3
# 列数值转文本
b = utils.get_column_letter(2) # 'B'

print(cell.row, cell.column, cell.coordinate)

7、获取多个单元格的值

cells1 = sheet['A7:B10'] # 指定坐标范围的值
cells2 = sheet['A'] # 指定列的值
cells3 = sheet['A:B']
cells4 = sheet[7] # 指定行的值
cells5 = sheet[7:10]
cells6 = sheet.iter_rows(min_row=7,max_row=10,min_col=1,max_col=2)
cells7 = sheet.rows # 所有行
# 遍历多个单元格
for row in cells7: # 遍历行
    for cell in row: # 遍历列
        print(cell.value)

二、写入Excel

1、写入单元格

cell = sheet['A1']
cell.value = '单元格的值'

2、写入一行或者多行数据

# 末尾追加一行
lst = ['小明', 25, '四川']
sheet.append(lst)

# 末尾追加多行
data = [['小明1', 25, '四川1'],
        ['小明2', 26, '四川2'],
        ['小明3', 27, '四川3']]
for lst in data:
    sheet.append(lst)

3、将公式写入单元格

sheet['A11'] = '=AVERAGE(A1:A10)'

4、插入一行或多行

# 在第idx行上插入一行
sheet.insert_rows(idx=2)

# 在第idx行上插入多行(以插入4行为例)
sheet.insert_rows(idx=2, amount=4)

5、插入一列或多列

# 在第idx列左边插入一列
sheet.insert_cols(idx=2)

# 在第idx列左边插入多列(以插入4列为例)
sheet.insert_cols(idx=2, amount=4)

6、删除多行或多列

# 在第idx行开始删除多行(以删除4行为例)
sheet.delete_rows(idx=2, amount=4)

# 在第idx列开始删除多列(以删除4列为例)
sheet.delete_cols(idx=2, amount=4)

7、移动范围数据

# 数字为正是向下或向右，数字为负是向上或向左
sheet.move_range('C1:D2', rows=3, cols=2)

三、创建新的Excel表格

from openpyxl import Workbook

workbook = Workbook()

四、保存Excel

workbook.save(filename='Excel工作表1.xlsx')

五、样式调整

1、设置字体样式

cell = sheet['A1']
# 字体名称，大小，粗体，斜体，颜色
font = Font(name='宋体', size=11, bold=True, italic=True, color='FF0000')
cell.font = font

2、设置对齐样式

cell = sheet['A1']
# horizontal：水平对齐, vertical：垂直对齐, text_rotation：字体倾斜度, wrap_text：自动换行
alignment = Alignment(horizontal='center', vertical='center', text_rotation=45, wrap_text=True)
cell.alignment = alignment

• 水平对齐:distributed, justify, center, left, fill, centerContinuous, right, general
• 垂直对齐:bottom, distributed, justify, center, top

3、设置边框样式

cell = sheet['A1']
# style:边线样式，color：边线颜色
side = Side(style='thin', color='FF000000')  # 定义边框
# Border(上，左，右，下)，边线
border = Border(top=side, left=side, right=side, bottom=side)
cell.border = border

• 边线样式:double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

4、设置单元格填充样式

from openpyxl.styles import PatternFill,GradientFill

# 单色填充
cell = sheet['A1']
pattern_fill = PatternFill(fill_type='solid', fgColor='99ccff')
cell.fill = pattern_fill

# 渐变填充
cell2 = sheet['B2']
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff', '000000'))
cell2.fill = gradient_fill

5、设置行高和列宽

# 行高
sheet.row_dimensions[1].height = 50
# 列宽
sheet.column_dimensions['C'].width = 20

6、单元格合并与取消

# 合并
sheet.merge_cells('A1:B2') 
sheet.merge_cells(start_row=1, start_column=3, 
                  end_row=2, end_column=4)

# 取消合并
sheet.unmerge_cells('A1:B2') 
sheet.unmerge_cells(start_row=1, start_column=3, 
                    end_row=2, end_column=4)

六、单元格格式设置

cell = sheet['A1']
cell.number_format = 'General' # 常规

0: 'General',
1: '0',
2: '0.00',
3: '#,##0',
4: '#,##0.00',
5: '"$"#,##0_);("$"#,##0)',
6: '"$"#,##0_);[Red]("$"#,##0)',
7: '"$"#,##0.00_);("$"#,##0.00)',
8: '"$"#,##0.00_);[Red]("$"#,##0.00)',
9: '0%',
10: '0.00%',
11: '0.00E+00',
12: '# ?/?',
13: '# ??/??',
14: 'mm-dd-yy',
15: 'd-mmm-yy',
16: 'd-mmm',
17: 'mmm-yy',
18: 'h:mm AM/PM',
19: 'h:mm:ss AM/PM',
20: 'h:mm',
21: 'h:mm:ss',
22: 'm/d/yy h:mm',
37: '#,##0_);(#,##0)',
38: '#,##0_);[Red](#,##0)',
39: '#,##0.00_);(#,##0.00)',
40: '#,##0.00_);[Red](#,##0.00)',
41: r'_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)',
42: r'_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_)',
43: r'_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)',
44: r'_("$"* #,##0.00_)_("$"* \(#,##0.00\)_("$"* "-"??_)_(@_)',
45: 'mm:ss',
46: '[h]:mm:ss',
47: 'mmss.0',
48: '##0.0E+0',
49: '@', 
BUILTIN_FORMATS_MAX_SIZE = 164
BUILTIN_FORMATS_REVERSE = dict(
        [(value, key) for key, value in BUILTIN_FORMATS.items()])

FORMAT_GENERAL = BUILTIN_FORMATS[0]
FORMAT_TEXT = BUILTIN_FORMATS[49]
FORMAT_NUMBER = BUILTIN_FORMATS[1]
FORMAT_NUMBER_00 = BUILTIN_FORMATS[2]
FORMAT_NUMBER_COMMA_SEPARATED1 = BUILTIN_FORMATS[4]
FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-'
FORMAT_PERCENTAGE = BUILTIN_FORMATS[9]
FORMAT_PERCENTAGE_00 = BUILTIN_FORMATS[10]
FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'
FORMAT_DATE_YYMMDD = 'yy-mm-dd'
FORMAT_DATE_DDMMYY = 'dd/mm/yy'
FORMAT_DATE_DMYSLASH = 'd/m/y'
FORMAT_DATE_DMYMINUS = 'd-m-y'
FORMAT_DATE_DMMINUS = 'd-m'
FORMAT_DATE_MYMINUS = 'm-y'
FORMAT_DATE_XLSX14 = BUILTIN_FORMATS[14]
FORMAT_DATE_XLSX15 = BUILTIN_FORMATS[15]
FORMAT_DATE_XLSX16 = BUILTIN_FORMATS[16]
FORMAT_DATE_XLSX17 = BUILTIN_FORMATS[17]
FORMAT_DATE_XLSX22 = BUILTIN_FORMATS[22]
FORMAT_DATE_DATETIME = 'yyyy-mm-dd h:mm:ss'
FORMAT_DATE_TIME1 = BUILTIN_FORMATS[18]
FORMAT_DATE_TIME2 = BUILTIN_FORMATS[19]
FORMAT_DATE_TIME3 = BUILTIN_FORMATS[20]
FORMAT_DATE_TIME4 = BUILTIN_FORMATS[21]
FORMAT_DATE_TIME5 = BUILTIN_FORMATS[45]
FORMAT_DATE_TIME6 = BUILTIN_FORMATS[21]
FORMAT_DATE_TIME7 = 'i:s.S'
FORMAT_DATE_TIME8 = 'h:mm:ss@'
FORMAT_DATE_TIMEDELTA = '[hh]:mm:ss'
FORMAT_DATE_YYMMDDSLASH = 'yy/mm/dd@'
FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-'
FORMAT_CURRENCY_USD = '$#,##0_-'
FORMAT_CURRENCY_EUR_SIMPLE = '[$EUR ]#,##0.00_-'