Python operates on Excel's xlsx files

preface

Previously, xlrd/xlwt were used for reading and writing excel, but the disadvantage of these two libraries is that they only work well with xls, not xlsx. Since everyone is using the latest version of office and excel is xlsx, it is not appropriate to continue to use xlrd/xlwt. Fortunately, we can also use openpyxl to read and write xlsx files.

I'm not familiar with excel and I don't usually use it, so the processing of excel is very simple, just simple reading and writing. What is demonstrated here is also simple reading and writing operation. For specific advanced functions, you can refer to the link address after the text.

1: write one excel file as follows

 from openpyxl import Workbook
 from openpyxl.utils import get_column_letter
 
 #  Create in memory 1 a workbook Object, and it will at least be created 1 a  worksheet
 wb = Workbook()
 
 # Gets the currently active worksheet, The default is number one 1 a worksheet
 ws = wb.active
 
 # Set the value of the cell, A1 Is equal to the 6( The test shows openpyxl The rows and columns are numbered from 1 Start counting ) . B1 Is equal to the 7
 ws.cell(row=1, column=1).value = 6
 ws.cell("B1").value = 7
 
 # From the first 2 Line start, write 9 line 10 Column data whose value is the corresponding column ordinal number A , B , C , D...
 for row in range(2,11):
  for col in range (1,11):
   ws.cell(row=row, column=col).value = get_column_letter(col)
 
 # You can use append insert 1 Rows of data 
 ws.append([" I "," you "," she "])
 
 # save 
 wb.save(filename="/Users/budong/Desktop/a.xlsx")

2: read the excel just written as follows

 from openpyxl import load_workbook
 
 # Open the 1 a workbook
 wb = load_workbook(filename="/Users/budong/Desktop/a.xlsx")
 
 # Gets the currently active worksheet, The default is number one 1 a worksheet
 #ws = wb.active
 
 # You can also use the following method 
 
 # Get all the tables (worksheet) The name of the 
 sheets = wb.get_sheet_names()
 # The first 1 The name of the table 
 sheet_first = sheets[0]
 # Get specific worksheet
 ws = wb.get_sheet_by_name(sheet_first)

 # Gets all the rows and columns of the table, both of which are iterable 
 rows = ws.rows
 columns = ws.columns
 
 # Iterate over all rows 
 for row in rows:
  line = [col.value for col in row]
  print line
 
 # Read the value through the coordinates 
 print ws.cell('A1').value # A The column ,1 Said line 
 print ws.cell(row=1, column=1).value

conclusion