openpyxl

新建、保存、打开

from openpyxl import Workbook
path = "filename.xlsx"
ws = Workbook(path)
ws.save(path)

打开

from openpyxl import Workbook,load_workbook

path ='./dist/file/a.xlsx'
wb = load_workbook(path)
wb.save(path)

获取工作表的名称

wb.sheetnames();
//->['Sheet', '2e朋', '豺']
wb.worksheets
//->[<Worksheet "Sheet">, <Worksheet "2e朋">, <Worksheet "豺">]

获取一个表

sheet = wb.worksheets[0]
sheet = wb.worksheets['sheet1']

删除一个表

sheet = wb.worksheets['Sheet']
wb.remove(sheet)
wb.save(path)

创建一个表

wb.create_sheet('4月')
wb.save(path)

复制表

sheet_copy = wb.copy_worksheet(wb['4月'])
wb.save(path)

修改表名

sheet.title = '复制的4月'
wb.save(path)

获取单元格的值

sheet.cell(column=1,row=1).value

获取单元格区域

sheet['A1:C10']
#A列到C列
sheet['A:C']
#1-10行
sheet['1:10']

单元格写入数据

sheet.cell(row=9,column=8,value="写入的")

获取行总数，列总数

sheet.max_row
sheet.min_row
sheet.max_column
sheet.min_column

获取指定行、列区域

sheet.iter_rows(max_col=2,max_row=2,min_row=1,min_col=1)
sheet.iter_cols(max_col=2,max_row=2,min_row=1,min_col=1)

获取单元的值、坐标

sheet.cell(column=2,row=2).value
sheet.cell(column=2,row=2).coordinate

删除行、列

# 删除列
# idx从第几行开始，amount 删除多少个
sheet.delete_cols(idx=2,amount=5)
wb.save(path)
 
#删除行
sheet.delete_rows(idx=2,amount=5)
wb.save(path)

插件行、列

# 删除列
# idx从第几行开始，amount 删除多少个
sheet.insert_cols(idx=2,amount=5)
wb.save(path)
 
#删除行
sheet.insert_rows(idx=2,amount=5)
wb.save(path)

移动单元格

# rows和cols正数为向下或向右、负数为向左或向上
# 移动坐标，以A1为坐标，向右向下移动5格
sheet.move_range("A1:B2",rows=5,cols=5)

样式

字体

Font(name='Calibri', size=11, bold=False,italic=False,vertAlign=None, underline='none',strike=False, color='FF000000')

参数解读：

name：字体名称，注意中文字体前面加u
size：字号大小bold：True（加粗）/ False（不加粗）
italic：True（倾斜）/ False（不倾斜）vertAlign：'None'（默认）/ 'superscript'（上标）/ 'subscript'（下标）
underline：'None'（默认）/ 'single'（单下划线）/ 'double'（双下划线）/ 'singleAccounting'（会计用单下划线）/ 'doubleAccounting'（会计用双下划线）
strike：'True'（显示删除线）/ 'False'（不显示删除线）
color：字体的颜色RGB转HEX



sheet = wb.worksheets[0]
font = Font(name=u'微软雅黑', bold=True,size=72)
sheet['A2'].font =font

对齐

Alignment(horizontal='general',vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)

horizontal:
- 'general'(常规)
- 'justify'(两端对齐)
- 'right'(靠右)
- 'centerContinuous'(跨列居中)/ 'distributed'(分散对齐)
- 'fill'(填充)
- 'center'(居中)
- 'left'(靠左)
vertical:
- 'center'(垂直居中)
- 'top'(靠上)
- 'bottom'(靠下)
- 'justify'(两端对齐)
- 'distributed'(分散对齐)
text_rotation:指定文本旋转角度
wrap_text:是否自动换行
shrink_to_fit:是否缩小字体填充
indent:指定缩进

边框

Boder(left=side,right=side,top=side,bottom=side) Side(style=连线样式,color=边线颜色) Border(left=左边线样式,right=右连线样式,top=上边线样式,bottom=下边线样式)

style参数的种类: 'double, ‘mediumDashDotDot’, ‘slantDashDot’,‘dashDotDot’,‘dotted’,‘hair’, 'mediumDashed, ‘dashed’, ‘dashDot’, ‘thin’,‘mediumDashDot’,‘medium’, 'thick’

side = Side(style="double",color="ff0000")
sheet['A2'].border = Border(left=side)
wb.save(path)

填充

PatternFill(fill_type=None, start_color='FFFFFFFF', end_color='FF000000')

fill_type:
- 'None'(不填充)
- 'solid'(实心填充)
- 'darkGray'(75%灰色)
- 'mediumGray'(50%灰色)
- 'lightGray'(25%灰色)
- 'gray125'(12.5%灰色)
- 'gray0625'(6.25%灰色)
- 'darkHorizontal'(水平条纹)
- 'darkVertical'(垂直条纹)
- 'darkDown'(逆对角线条纹)
- 'darkUp'(对角线条纹)
- 'darkGrid'(对角线剖面线)
- 'darkTrellis'(粗对角线剖面线)
- 'lightHorizontal'(细水平条纹)
- 'lightVertical'(细垂直条纹)
- 'lightDown'(细逆对角线条纹)
- 'lightUp'(细对角线条纹)
- 'lightGrid'(细水平剖面线)
- 'lightTrellis'(细对角线剖面线)
start_color / fgColor:背景颜色 RGB转HEX
end_color / bgColor:图案颜色 RGB转HEX

合并、取消单元格

sheet.merge_cells("A3:A7")
sheet.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
sheet.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
wb.save(path)

行高和列宽

sheet.row_dimensions[1].height = 200
sheet.column_dimensions['B'].width = 100
wb.save(path)

行号与字母转换

from openpyxl import utils
utils.column_index_from_string('Z')
# 输出26
utils.get_column_letter(26)
# 输出 Z

添加批注

from openpyxl import comments
comment = comments.Comment('这里写批注','Amanda')
sheet['A2'].comment = comment;
wb.save(path)

冻结单元格

sheet.freeze_panes ='C3'
wb.save(path)

【Python】openpyxl--excel操作