Python third-party library OPENPYXL (2)

Python third-party library OPENPYXL (2)Simple to useWrite a workbook

>>> fromOpenpyxlImportWorkbook>>> fromOpenpyxl.compatImportRange>>> fromOpenpyxl.utilsImportGet_column_letter>>>>>> WB =Workbook ()>>>>>> Dest_filename ='empty_book.xlsx'>>>>>> ws1 =wb.active>>> Ws1.title ="Range Names">>>>>> forRowinchRange (1, 40):.. ws1.append (Range (600))>>>>>> ws2 = Wb.create_sheet (title="Pi")>>>>>> ws2['F5'] = 3.14>>>>>> WS3 = Wb.create_sheet (title="Data")>>> forRowinchRange (10, 20):...      forColinchRange (27, 54):...         _ = Ws3.cell (Column=col, Row=row, value="{0}". Format (Get_column_letter (col)))>>>Print(ws3['AA10'].value) AA>>> wb.save (filename = dest_filename)

View CodeTo read an existing workbook

 from Import Load_workbook ' empty_book.xlsx ' )>>> sheet_ranges = wb['range names']print( sheet_ranges['D18'].value)3

Note : There are several flags that can be used in the Loadworkbook

1.guess_types Enable or disable (default) type inference when reading a cell

2.data_only controls whether a cell with a formula has a formula (default) or the value of the last Excel read table

3.KEEP_VBA controls whether any Visual Basic elements are retained (default). If they are saved, they are still non-editable.

Note : OPENPYXL does not currently read all possible items in the Excel file, so if you open and save the same name, the image and chart will be lost from the existing file

Use number format

>>>Importdatetime>>> fromOpenpyxlImportWorkbook>>> WB =Workbook ()>>> ws =wb.active>>>#set date using Python datetime>>> ws['A1'] = Datetime.datetime (2010, 7, 21)>>>>>> ws['A1'].number_format'YYYY-MM-DD H:mm:ss'>>>#you can enable type inference in specific situations>>> wb.guess_types =True>>>#set percentages using string and% symbols>>> ws['B1'] ='3.14%'>>> wb.guess_types =False>>> ws['B1'].value0.031400000000000004>>>>>> ws['B1'].number_format'0%'

working with Formulas

 from Import Workbook>>> wb = Workbook ()>>> ws = wb.active#  Add a simple formula >>> ws["A1""=sum (1, 1)">>> Wb.save ("formula.xlsx")

Note : You must use the English name as a function, and the function arguments must be separated by commas, not other punctuation marks, such as semicolons

OPENPYXL never calculated a formula but can check the name of a formula

 from Import formulae " Hex2dec " inch formulaetrue

Merge/split cells

 >>> from  openpyxl.workbook import   Workbook  >>>>>> wb = Workbook ()  >>> ws = wb.active  >>>>>> Ws.merge_ Cells ( " a2:d2   " )  >>> ws.unmerge_cells ( " a2:d2   " )  >>>>>> #   equivalent  >> > Ws.merge_cells (start_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) 

inserting Images

 from Import Workbook  from Import Image>>>>>> wb = Workbook ()>>> ws = wb.active>>> ws[ ' A1 ' ' You should see three logos below '

# make a picture >>> img = image ('logo.png')

# Add worksheets and anchors next to cells ' A1 ' )>>> wb.save ('logo.xlsx')

Collapse Columns

Import OPENPYXL>>> wb = OPENPYXL. Workbook ()>>> ws = wb.create_sheet ()>>> ws.column_dimensions.group ('A  ','D', hidden=True)>>> wb.save ('  Group.xlsx')

working with Pandas and NumPyNumPy Support

OPENPYXL has provided support for numpy types of floating-point numbers, integers, and booleans. Use timestamp type pandas to support DateTimes

Working with Pandas Dataframes

openpyxl.utils.dataframe.dataframe_to_rows()函数提供了一个简单的方式来处理Pandas Dataframes

 from Import  == wb.active for in Dataframe_to_rows (DF, index=true, header=True):    Ws.append (R)

Although pandas itself supports conversion to Excel, this provides additional flexibility for client code, including the ability to transfer dataframes directly to a file.

Convert a dataframe to a worksheet that highlights headings and indexes

WB == wb.active for in Dataframe_to_rows (DF, index=true, header=True):    Ws.append (R) for in ws['A'] + ws[1]:    '  Pandas'wb.save ("pandas_openpyxl.xlsx")

Or, if you only want to convert the data you can use Write-only mode

 fromOpenpyxl.cell.cellImportWRITEONLYCELLWB= Workbook (write_only=True) WS=wb.create_sheet () cell=Writeonlycell (WS) Cell.style='Pandas' defformat_first_row (row, cell): forCinchRow:cell.value=CyieldCellRows=dataframe_to_rows (DF) First_row=Format_first_row (Next (rows), cell) ws.append (first_row) forRowinchRows:row=list (row) Cell.value=row[0] row[0]=cell ws.append (Row) Wb.save ("openpyxl_stream.xlsx")

View Code

This code is also applicable to standard workbooks

convert a worksheet to Dataframe

To convert a worksheet to Dataframe, you can use the Values property. This is easy if the worksheet does not have a title or index

DF = DataFrame (ws.values)

If a worksheet does have a title or index, such as created by pandas, you need to do more work

data == next (data) [1= for in = DataFrame (data, INDEX=IDX, Columns=cols)

Python third-party library OPENPYXL (2)