Python3 manipulating Excel

Let's start by explaining

When dealing with large files, OPENPYXL performance is not as good as XLRD,XLWT. So you can read the time use XLRD, write the time with OPENPYXL.

Today, a usage scenario is encountered: Excel holds a list of use cases, including headers, which need to be read out. Cells are stored in dictionaries and other types.

Implementation See Code:

The general idea is to first get the table header as a list 1:list1.

The subsequent rows of data are then fetched: LISTN, which is combined into a dictionary using Dict (Zip (LIST1,LISTN).

Finally, save the dictionary as a list.

#FileName:Excel.py#Author:adil#DATETIME:2017/12/10 13:08#Software:pycharmImportxlrdImportOS fromOpenpyxl.reader.excelImportLoad_workbook fromWm_apiImportReadconfig as RCRc=RC. Readconfig ()classExcel (object):" "define an Excel class" "    def __init__(self):'Initializing basic information'Self.path=Rc.path Self.excelpath= Os.path.join (Self.path,'Casedata')    defReadexcel (self,excelname,sheetname):'Read Excel'Self.excelname=Os.path.join (self.excelpath,excelname) self. Rb=Xlrd.open_workbook (self.excelname) self. Rs=Self . Rb.sheet_by_name (SheetName)#Get row Countrows =Self . Rs.nrows#define a dict to hold a single use case        #self.titledict = Dict.fromkeys (self. Rs.row_values (0))        #the first row of the table header is saved as list. Self.titlelist =Self . Rs.row_values (0)#define a list to store all use casesSelf.caselist = []         forRinchRange (1, rows): Rowvalues=Self . Rs.row_values (R)#print (R)            #Print (self. Rs.row_values (R))            #Self.caseinfo = Dict.fromkeys (self. Rs.row_values (0), self. Rs.row_values (R))            #print (self.caseinfo)            #combine lists into dictionaries this is a way to convert a list to a dictionary. Self.casedict =dict (Zip (self.titlelist,rowvalues))#The following is the conversion of dictionaries to lists,            #Print (list (self.casedict))            #print (Self.caseDict.values ())            #print (self.casedict)            #stitch the dictionary back into a list. self.caseList.append (self.casedict)#print (self.caselist)        #back to Caselist        returnself.caselistif __name__=='__main__': Excel=Excel () excel.readexcel ('apiinfo.xlsx','Login')

Note: The data types that are read in this way are str. Includes the dictionary in the table. Read is also str.

If you use this dictionary directly, you will get an error, as follows:

Raise Jsondecodeerror ("expecting value"from 1 column 1 (char 0)

The processing method is as follows:

The Special Function eval () is used here.

Casedata = casedict['Casedata'] Caserun= casedict['Caserun']            Print(URL1)ifCaserun = ='Y':                ifmethod = ='Post':                    Print(casedict['Casename'])                    Print(Casedata)Print(Type (casedata)) Casedata=eval (casedata)Print(Type (casedata))#The following is the result of the above three prints, which looks the same, but the type is different. So we need to switch to Dict                    #{' username ': ' xzyc001 ', ' Password ': ' 111111 '}                    #< class ' str ' >                    #                     #< class ' Dict ' >

Here's a way to use eval.

Original address: https://www.cnblogs.com/liu-shuai/p/6098246.html

Eval

Function: evaluates the string str as a valid expression and returns the result of the calculation.

Syntax: eval (source[, globals[, locals]), value

Parameters:

Source: The code object returned by a Python expression or function compile ()

Globals: Optional. It must be dictionary.

Locals: Optional. Arbitrary map Object

Examples show:

you can convert list,tuple,dict and string to each other. #################################################string conversion to list>>>a ="[ [up], [3,4], [5,6], [7,8], [9,0]]">>>type (a)<type'Str'>>>> B =Eval (a)>>>Printb[[1, 2], [3, 4], [5, 6], [7, 8], [9, 0]]>>>type (b)<type'List'>#################################################convert strings into dictionaries>>> A ="{1: ' A ', 2: ' B '}">>>type (a)<type'Str'>>>> B =Eval (a)>>>Printb{1:'a', 2:'b'}>>>type (b)<type'Dict'>#################################################string Conversion Narimoto Group>>> A ="([up], [3,4], [5,6], [7,8], (9,0))">>>type (a)<type'Str'>>>> B =Eval (a)>>>PrintB ([1, 2], [3, 4], [5, 6], [7, 8], (9, 0))>>>type (b)<type'tuple'>

Python3 manipulating Excel