一 安装
pip install openpyxl
二 使用
1. 创建 一个工作簿
- #!/usr/bin/python
- from openpyxl import Workbook
- mywb=Workbook()
- mywb.save('first_excl.xlsx')
2. 编辑工作簿 ,创建一个工作表 ,并添加数据
- #!/usr/bin/python
- from openpyxl import Workbook
- from openpyxl.comments import Comment
- if __name__ == '__main__':
- wb = Workbook()
- ws = wb.active
- for i in range(10):
- for j in range(10):
- ws.cell(row=i+1,column=j+1).value=i+j
- wb.save('test.xlsx')
3. 添加单个数据与修改数据
- #!/usr/bin/python
- from openpyxl import Workbook
- wb = Workbook()
- # grab the active worksheet
- ws = wb.active
- # Data can be assigned directly to cells, add single data
- ws['A1'] = 42
- # Rows can also be appended, append is add data line by line
- ws.append([1, 2, 3])
- # Python types will automatically be converted,changed A2's data
- import datetime
- ws['A2'] = datetime.datetime.now()
- # Save the file
- wb.save("sample.xlsx")
4. 操作工作簿中的多张表
- #!/usr/bin/python
- from openpyxl import Workbook
- from openpyxl.compat import range
- from openpyxl.utils import get_column_letter
- wb = Workbook()
- dest_filename = 'empty_book.xlsx'
- ws1 = wb.active
- ws1.title = "range names"
- for row in range(1, 40):
- ws1.append(range(600))
- ws2 = wb.create_sheet(title="Pi")
- ws2['F5'] = 3.14
- ws3 = wb.create_sheet(title="Data")
- for row in range(10, 20):
- for col in range(27, 54):
- # ws3.cell(column=col, row=row).value = "aaaaa"
- _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
- print(ws3['AA10'].value)
- wb.save(filename = dest_filename)
5. 在工作簿中添加批注
#!/usr/bin/python from openpyxl import Workbook from openpyxl.comments import Comment if __name__ == '__main__': wb = Workbook() ws = wb.active comment = Comment('hello comment', 'wyang') ws['A1'].comment = comment wb.save('test.xlsx') 注:openpyxl默认的批注框大小为:
- width = '108pt'
- height = '59.25pt'
Linux路径 : %PYTHON_HOME%\Lib\site-packages\openpyxl\comments\shape_writer.py
windows路径: %PYTHON_HOME%/lib/python3.6/site-packages/openpyxl/comments/shape_writer.py
找到_shape_factory()函数,修改第一行代码中.format()中的height和width即可:
- style = ("position:absolute; margin-left:59.25pt;"
- "margin-top:1.5pt;width:{width};height:{height};"
- "z-index:1;visibility:hidden").format(height = "59.25pt",
- width = "108pt")
6. 合并单元格
- #!/usr/bin/python
- from openpyxl.workbook import Workbook
- wb = Workbook()
- ws = wb.active
- ws.merge_cells('A2:D2')
- # or equivalently
- #ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
- wb.save('merge.xlsx')
- #!/usr/bin/python
- import openpyxl
- wb=openpyxl.Workbook()
- sheet=wb.get_active_sheet()
- sheet.merge_cells('A1:D3')
- sheet['A1']='Twelve cells merged together.'
- sheet.merge_cells('C5:D5')
- sheet['C5']='Two merged cells'
- wb.save('merged.xlsx')
取消合并
- #!/usr/bin/python
- from openpyxl import load_workbook
- wb = load_workbook('merge.xlsx')
- ws = wb.active
- ws.unmerge_cells('A2:D2')
- # or equivalently
- #ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
- wb.save('unmerge.xlsx')
- #!/usr/bin/python
- import openpyxl
- wb=openpyxl.load_workbook('merged.xlsx')
- sheet=wb.get_active_sheet()
- sheet.unmerge_cells('A1:D3')
- sheet.unmerge_cells('C5:D5')
- wb.save('merged.xlsx')
7. 在excel中画图 ,Creating a chart
条形图:openpyxl.charts.BarChart()
折线图:openpyxl.charts.LineChart()
散点图:openpyxl.charts.ScatterChart()
饼图:openpyxl.charts.PieChart()
- #!/usr/bin/python
- from openpyxl import Workbook
- wb = Workbook()
- ws = wb.active
- for i in range(10):
- ws.append([i])
- from openpyxl.chart import BarChart, Reference, Series
- values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
- chart = BarChart()
- chart.add_data(values)
- ws.add_chart(chart, "E15")
- wb.save("SampleChart.xlsx")