Python operations Excel (OPENPYXL)

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: OPENPYXL。 This is a very simple library that can be started in a few minutes. Installation is very simple, pip install OPENPYXL One step, I will mainly say the operation of Excel. Of course, the front of the nonsense so much, the students can almost guess, OPENPYXL can only manipulate xlsx files and cannot operate the XLS file. 1. Basic ConceptsIn Openpyxl, the main use of the three concepts: Workbooks,sheets,cells. Workbook is an Excel worksheet; sheet is a table page in a worksheet; The cell is a simple lattice. OPENPYXL 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

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 columnsFor IIn sheet["C"]:print (I.value, End=" ") # C1 c2 C3 C4 c5 c6 C7 C8 C9 C10 <-c All values in column     

3. Write to xlsxFirst Use

Import WORKBOOKWB = 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 IIn range: sheet["a%d"% (i+1)].value = i + 1    

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

 sheet[" e1"].value = " =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, here is still a little bit of a problem, that is, when I first use Load_workbook read an Excel, modified after save into the original file, will error: permissionerror: [Errno] Permission denied

I checked on the internet, did not find anything related to the introduction, many pages are said to save the time will overwrite the original file, but in fact it is not possible. I'm desperate, too! Really not, it can only be a roundabout solution: first save to another file name, and then delete the original file, and then change the new file to the original file name.

(later found that save is available, perhaps because I was the file was open, so it cannot be saved.) Also thanks to the 1 floor students reminded. ）

OPENPYXL also has a lot of fun features, but I did not introduce, interested students can explore for themselves. OPENPYXL'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.

Python operations Excel (OPENPYXL)