Summary of several methods for accessing xls/xlsx files in Python

This article mainly introduces the Python learning _ several access Xls/xlsx file method Summary, has a certain reference value, now share to everyone, the need for friends can refer to

You want to save some parameters dynamically while the deep learning program is running.

Save into Excel file for easy viewing, we looked at several methods, do a test. Because I usually do not use Excel, simple access to data is enough.

xlwt/xlrd Inventory Excel file : (if there are characters in the stored data, then there is a slight change in the wording)

Import xlwt  workbook = xlwt. Workbook (encoding= ' utf-8 ') Booksheet = Workbook.add_sheet (' Sheet 1 ', cell_overwrite_ok=true) #存第一行cell () and cell ( ) Booksheet.write (0,0,34) booksheet.write (0,1,38) #存第二行cell (2,1) and cell (2,2) booksheet.write (1,0,36) Booksheet.write ( 1,1,39) #存一行数据 rowdata = [43,56] for I in range (len (rowdata)):  booksheet.write (2,i,rowdata[i]) workbook.save (' Test_ Xlwt.xls ')

Read Excel file: (same for numeric type data)

Import Xlrdworkbook = Xlrd.open_workbook (' D:\\py_exercise\\test_xlwt.xls ') print (Workbook.sheet_names ())  # See all Sheetbooksheet = Workbook.sheet_by_index (0) #用索引取第一个sheetbooksheet = workbook.sheet_by_name (' Sheet 1 ') # or read cell data with the name sheet# Cell_11 = Booksheet.cell_value (0,0) cell_21 = Booksheet.cell_value (1,0) #读一行数据row_3 = Booksheet.row_ VALUES (2) print (Cell_11, cell_21, Row_3) >>>34.0 36.0 [43.0, 56.0]

OPENPYXL Inventory Excel file:

From OPENPYXL import Workbook Workbook = Workbook () Booksheet = Workbook.active #获取当前活跃的sheet, the default is the first sheet# to save the first row of cell cells ( (Booksheet.cell). Value = 6 #这个方法索引从1开始booksheet. Cell ("B1"). Value = 7# save one row of data booksheet.append ([11,87]) Workbook.save ("Test_openpyxl.xlsx")

Read Excel file:

From OPENPYXL import Load_workbook workbook = Load_workbook (' d:\\py_exercise\\test_openpyxl.xlsx ') #booksheet = Workbook.active #获取当前活跃的sheet, the default is the first sheetsheets = Workbook.get_sheet_names () #从名称获取sheetbooksheet = workbook.get_ Sheet_by_name (sheets[0]) rows = Booksheet.rowscolumns = booksheet.columns# iteration All rows for row in rows:line = [Col.value for col In row] #通过坐标读取值cell_11 = Booksheet.cell (' A1 '). Valuecell_11 = Booksheet.cell (row=1, column=1). Value

The principle is actually the same, there are some differences in the wording.

In fact, if the storage format is not required, I think it is very good to save a CSV file:

Import pandas as PD  Csv_mat = Np.empty ((0,2), float) Csv_mat = Np.append (Csv_mat, [[43,55]], axis=0) Csv_mat = Np.appen D (Csv_mat, [[65,67]], axis=0) CSV_PD = PD. DataFrame (Csv_mat) csv_pd.to_csv ("Test_pd.csv", sep= ', ', Header=false, Index=false)

Because it's very simple to read:

Import pandas as PD  filename = "d:\\py_exercise\\test_pd.csv" csv_data = pd.read_csv (filename, header=none) csv_data = Np.array (Csv_data, Dtype=float)