Shulou(Shulou.com)06/03 Report--

This article mainly introduces "how Python reads and writes Excel data". In daily operation, I believe many people have doubts about how Python reads and writes Excel data. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how Python reads and writes Excel data". Next, please follow the editor to study!

Using python to operate Excel requires two libraries, xlrd and xlwt, to read and write excel data in python. Installation and import import are required before use.

1.Python reads excel data

To read excel data using Python, you first need to use xlrd.open_workbook (file name) to open the Excel file. The default is rb.

Then you can get Excel file information and read excel data through the methods in the xlrd library object

Import xlrdfrom pprint import pprintstaff_excel = xlrd.open_workbook ('. / staff.xlsx') # get the name of the sheet workbook print (staff_excel.sheet_names ()) # get the corresponding workbook by name sheet = staff_excel.sheet_by_name ('employee basic Information') # display the number of rows in the table And number of columns print (sheet.nrows) print (sheet.ncols) # read the contents of all cell in the second line print (sheet.row_values (2)) # get the second line The value of column 0: print (sheet.cell (2Power0) .value) print (sheet.cell_value (2Magin0)) data_type = sheet.cell (2Magin2) .ctypeprint (data_type) if data_type is 3: # returns a tuple # ret = xlrd.xldate_as_tuple (sheet.cell_value (1Mague 2), staff_excel.datemode) # converts the time in the excel table to the time ret in python (sheet.cell_value (1Power2) Staff_excel.datemode) print (ret.strftime ('% Ymuri% mmure% d'))

The row_values (I) and col_values (I) methods can get information about the specified number of rows or columns, where I is counted from 0, and both methods return the list object

The cell_value (I, j) method can read cell data. I is the number of rows, j is the number of columns, and the number of rows and columns are counted from 0.

In excel, 0 means empty,1, string,2 means number,3, date,4 means boolean,5, error.

two。 Convert Excel data to json and write to a file

First, you need to open the excel file, then get the corresponding sheet by name, and then you can start to manipulate the excel table.

First create an empty list and get the first row of the excel table as the key value of the dictionary

Then create a dictionary object in the local variable (each new loop, the dictionary object needs to be empty), take the value through two layers of loop (outer loop control row, inner loop control column), assign the value to the dictionary object, and add the dictionary object to the defined empty list at the end of each loop.

To write data to a file, you can use the with context manager to serialize the custom list of previously stored data through the json.dumps () method, and then write to the file. To output real Chinese, you need to specify the parameter ensure_ascii=False.

Json_list = [] keys = sheet.row_values (0) print (keys) for index_r in range (1meme sheet.nrows): # [1meme2] # this dictionary must be a local variable line = {} for index_c in range (sheet.ncols): # [0,3] # get the type cell_type = sheet.nrows (index_r Index_c) .ctype # get the value cell_value = sheet.cell (index_r, index_c). Value # if it is a time type if cell_type is 3: cell_value = xlrd.xldate_as_datetime (cell_value Staff_excel.datemode) .strftime ('% Ymuri% MMI% d') line[ line [index _ c]] = cell_value else: json_list.append (line) pprint (json_list, indent=4) with open ('staff.json',' axioms,) as f: f.write (json.dumps (index, ensure_ascii=False)) 3. Rewrite the json file to Excel# to create an Excel object file new_staff = xlwt.Workbook () staff_sheet = new_staff.add_sheet ('xkd employee information') with open ('staff.json') as f: # return a list data = json.load (f) # get the first line item = data [0] in Excel # get the value column_values = [] for item in data: column_ in Excel Values.append (item.values ()) # write to the first line for I Key in enumerate (item.keys ()): print (key) staff_sheet.write (0, I, label=key) # write the other lines for iMagazine column in enumerate (column_values): for j column columnist value in enumerate (column): staff_sheet.write (iMag1, j, label=column_value) new_staff.save ('. / new_staff.xls') so far The study on "how Python reads and writes Excel data" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.