Python third-party library OPENPYXL (2)

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.