openpyxl exercise
1 from openpyxl.workbook import Workbook
from openpyxl.utils import get_column_letter 2 def write_a_workbook(): 3 wb = Workbook() 4 5 dest_filename = 'empty_book.xlsx' 6 7 ws1 = wb.active 8 ws1.title = "range names" 9 10 for row in range(1,40): 11 ws1.append(range(600)) 12 13 ws2 = wb.create_sheet(title = 'Pi') 14 ws2['F5'] = 3.14 15 16 ws3 = wb.create_sheet(title='Data') 17 for row in range(10,20): 18 for col in range(27,54): 19 _= ws3.cell(column=col,row=row,value="{0}".format(get_column_letter(col))) 20 print(ws3['AA10'].value) 21 22 wb.save(filename = dest_filename) 23 from openpyxl import load_workbook 24 def read_an_existing_workbook(): 25 wb = load_workbook(filename = 'empty_book.xlsx') 26 for sheetnames in wb.get_sheet_names(): 27 if(sheetnames == 'range names'): 28 ws = wb[sheetnames] 29 ws['A1'] = 100 30 ws.cell(row=1,column=2,value=200) 31 print sheetnames 32 wb.save(filename = 'empty_book.xlsx') 33 import datetime 34 def using_number_formats(): 35 wb = Workbook() 36 ws=wb.active 37 wb.guess_types = True 38 ws['A1'] = datetime.datetime(2018,1,17) 39 40 print ws['A1'].value 41 print ws['A1'].number_format 42 #wb.guess_types = True 43 44 ws['B1'] = '3.14%' 45 #wb.guess_types = False 46 print ws['B1'].value 47 print ws['B1'].number_format 48 49 wb.save(filename = 'using number formats.xlsx') 50 def using_formulae(): 51 wb = Workbook() 52 ws = wb.active 53 ws['A2'] = 10 54 ws.cell(row=3,column=1,value=20) 55 ws['A1'] = "=SUM(A2+A3)" 56 wb.save(filename = 'formula.xlsx') 57 def Merge_Unmerge_cells(): 58 wb = Workbook() 59 #wb = load_workbook(filename = 'merge unmerge cells.xlsx') 60 ws = wb.active 61 #ws.merge_cells(start_row=2,start_column=2,end_row=5,end_column=5) 62 ws.merge_cells('A2:D8') 63 #ws2 = wb.copy_worksheet(ws) 64 #ws2.title = 'ws2' 65 #ws2.unmerge_cells('A2:D2') 66 67 #ws3 = wb.copy_worksheet(ws) 68 #ws3.title = 'merge' 69 #ws3.merge_cells(start_row=1,start_column=1,end_row=3,end_column=4) 70 71 #ws4 = wb.copy_worksheet(ws3) 72 #ws4.title = 'unmerge' 73 #ws4.cell(row=1,column=1,value=100) 74 #ws4.unmerge_cells(start_row=1,start_column=1,end_row=2,end_column=4) 75 76 wb.save(filename = 'merge unmerge cells.xlsx') 77 78 from openpyxl.drawing.image import Image 79 def Inserting_an_image(): 80 #ImportError: You must install PIL to fetch image objects 81 # download Pillow, download path:https://pypi.python.org/pypi/Pillow/5.0.0#downloads 82 # exe file, double click install 83 wb = Workbook() 84 ws = wb.active 85 ws['A1'] = 'You should see three logos below' 86 87 img = Image('logo.png') 88 ws.add_image(img,'A1') 89 90 ws1 = wb.copy_worksheet(ws) # image can not be copied 91 #ws1.add_image(img,'D4') # lead to image can not display in sheet1 92 ws1.title = 'ws1' 93 wb.save('logo.xlsx') 94 95 def Fold_columns(): 96 wb = Workbook() 97 ws = wb.active 98 ws.column_dimensions.group('B','E',hidden=True) 99 100 wb.save('group.xlsx') 101 if __name__ == "__main__": 102 #write_a_workbook() 103 #read_an_existing_workbook() 104 #using_number_formats() 105 #using_formulae() 106 #Merge_Unmerge_cells() 107 #Inserting_an_image() 108 Fold_columns()
https://openpyxl.readthedocs.io/en/latest/tutorial.html
https://automatetheboringstuff.com/chapter12/
两个很好的学习网站
