from openpyxl import Workbook '''写excel 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 #创建sheet 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='{0}'.format(get_column_letter(col))) print(ws3['AA10'].value) wb.save(filename = dest_filename) ''' ''' #读excel from openpyxl import load_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) a = ws['A1'].number_format print(a) #猜类型 wb.guess_types = True # set percentage using a string followed by the percent sign ws['B1'] = '3.14%' wb.guess_types = False print(ws['B1'].value) print(ws['B1'].number_format) ''' ''' #使用公式 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('E3:F3') ws.merge_cells('A2:D2') ws.unmerge_cells('A2:D2') #或者 ws.merge_cells(start_columnt_row=2, start_column=1, end_row=4, end_column=4) ws.unmerge_cells(start_row = 2, start_column = 1, end_row = 4, end_column = 4) ''' ''' #插入图像 from openpyxl import Workbook from openpyxl.drawing.image import Image wb = Workbook() ws = wb.active ws['A1'] = 'You shoud see three logos below' #create on image img = Image('logo.png') #add to worksheet and anchor next to cells ws.add_image(img, 'A3') wb.save('logo.xlsx') ''' #折叠大纲 import openpyxl wb = openpyxl.Workbook() ws = wb.create_sheet() ws.column_dimensions.group('A', 'D', hidden=True) wb.save('group.xlsx')