Powerful tool for reading and writing Excel with Python: PYOPENXL

Recently saw several groups of people asked XLWT, wlrd question, how to say, if it is office2007 just out, we use xlsx file with not accustomed to, also can understand, this is 10 years passed, even if not evolved to office2016, Still in the use of office2003 a little justified it. Someone can use xlsx to save as XLS Ah! --deliberately do more than a few steps, the purpose? For compatibility? Compatible with the old office2003? Moreover, since all use Python to operate Excel, but also to manually save the file, this is God horse idea? So, I still think, should abandon XLS and transform xlsx. That's why this article--XLWT, WLRD can only read and write XLS files, and cannot manipulate xlsx files. Solution: PYOPENXL。 This is a very simple library that can be started in a few minutes. Installation is very simple, pip install PYOPENXL One step, I will mainly say the operation of Excel. Of course, the front of the nonsense so much, the students can almost guess, PYOPENXL can only manipulate xlsx files and cannot operate the XLS file. 1. Basic ConceptsIn Pyopenxl, the main use of the three concepts: Workbooks,sheets,cells.workbook is an Excel worksheet; sheet is a table page in a worksheet; cell is a simple grid .。 PYOPENXL is around these three concepts, whether read or write is "kick": Open workbook, locate sheet, operate the cell. The following readings and writes describe several common methods separately. 2. Read xlsxIn order to do the experiment, I prepared an Excel document in advance, there are sheet1,sheet2,sheet3 these three pages, in the Sheet3 filled in the following: first with

 from Import Load_workbook

Introduce libraries.

WB = Load_workbook ("template.xlsx")

Opens a xlsx file.

Print (wb.sheetnames)    # [' Sheet1 ', ' Sheet2 ', ' Sheet3 ']

You can see which sheet pages are in the open Excel table.

Sheet = wb.get_sheet_by_name ("Sheet3")

Reading to the specified sheet page, sheet becomes magical and the desired content is here. Like what:

Print(sheet["C"])#(<cell sheet3.c1>, <cell sheet3.c2>, <cell sheet3.c3>, <cell sheet3.c4>, <Cell Sheet3.C5 , <cell Sheet3.c6>, <cell sheet3.c7>, <cell sheet3.c8>, <cell Sheet3.c9>, <Cell sheet3.c10>) <-column CPrint(sheet["4"])#(<cell sheet3.a4>, <cell sheet3.b4>, <cell sheet3.c4>, <cell sheet3.d4>, <Cell Sheet3.E4 >) <-Line 4thPrint(sheet["C4"].value)#C4 <-The value of the C4 latticePrint(Sheet.max_row)#<-Maximum number of rowsPrint(Sheet.max_column)#5 <-Maximum number of columns forIinchsheet["C"]:  Print(I.value, end=" ")#C1 c2 c3 c4 c5 c6 C7 C8 C9 C10 <-c All values in the column

3. Write to xlsxFirst Use

 from Import  = Workbook ()

Create a worksheet, and then

Sheet = wb.active

Locate the active sheet page. The default sheet page for an empty Excel table is called sheet, and if you want to change the name, you can assign a value directly to the Title property.

" New Shit "

This attribute is readable and writable. Of course, this is only for the current active page, other pages, you can use Create_sheet and remove_sheet to add and delete. It's easier to write content to the sheet page, just as it reads above.

sheet['C3'Hello world! '  for  in range:  sheet["a%d" % (i+1)].value = i + 1

We can also perform fancy operations, such as writing formulas:

sheet["E1" "=sum (a:a)"

Finally remember to save

Wb.save (' save a new excel.xlsx')

After you can open to see if there is wood to feel fried chicken simple? Beating is so powerful. In fact, there are a lot of fun features, but I did not introduce, interested students can explore for themselves. PYOPENXL's official website has a very complete library of the use of the method, although it is in English, but very good read (address: http://openpyxl.readthedocs.io/en/default/). In addition there is a crooked nut to write the tutorial (https://automatetheboringstuff.com/chapter12/), also written very comprehensive, you can see.

Powerful tool for reading and writing Excel with Python: PYOPENXL