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)