openpyxl excel读写操作

#http://yumos.gitee.io/openpyxl3.0/index.html#benchmarks
#coding:utf-8
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
#写工作簿(workbook)
wb = Workbook()

dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
    ws1.append(range(40))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(1, 20):
    for col in range(1, 20):
        ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))

print(ws3['A10'].value)
wb.save(filename = dest_filename)

from openpyxl import load_workbook
#读取已存在的工作簿(workbook)
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_ranges = wb['range names']
print(sheet_ranges['D18'].value)

import datetime
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# set date using a Python datetime
ws['A1'] = datetime.datetime(2010, 7, 21)
#Using number formats
ws['A1'].number_format
print(ws['A1'].value)

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# 添加一个简单的函数
ws["A1"] = "=SUM(1, 1)"
wb.save("formula.xlsx")

from openpyxl.workbook import Workbook
wb = Workbook()
ws = wb.active
#合并/取消合并单元格
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
# 等效于以下方法
ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=2, end_column=4)

#插入图片
from openpyxl import Workbook
from openpyxl.drawing.image import Image
#import Pillow

# wb = Workbook()
# ws = wb.active
# ws['A1'] = 'You should see three logos below'
# # create an image
# img = Image('login.png')
# # add to worksheet and anchor next to cells
# ws.addimage(img, 'A1')
# wb.save('logo.xlsx')

#折叠(outline)
import openpyxl
wb = openpyxl.Workbook()
ws = wb.create_sheet()
ws.column_dimensions.group('A','D', hidden=True)
ws.row_dimensions.group(1,10, hidden=True)
#插入行
ws.insert_rows(7)
#删除行和列
#删除F到H（F:H）列:
ws.delete_cols(6, 3)
#移动所选区域的单元格
ws['E4']='E4'
ws['F10']='F10'
#单元格向上移动一行，向右移动两列
ws.move_range("E4:F10", rows=-1, cols=2)
wb.save('group.xlsx')

from openpyxl import Workbook
wb = Workbook(write_only = True)
ws = wb.create_sheet()
from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment
from openpyxl.styles import Font
cell = WriteOnlyCell(ws, value="hello world")
cell.font = Font(name='Courier', size=36)
cell.comment = Comment(text="A comment", author="Author's Name")
ws.append([cell, 3.14, None])
wb.save('write_only_file.xlsx')