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/
两个很好的学习网站