Pyhton--csv/excel Data Persistence

One, CSV file Operation 1.1 read operation: Csv.read

import csvwith open("/路径/文件名.csv","r") as csvfile  #固定写法，使用open()方法，无需关闭file，‘r‘表示读操作    read=csv.reader(csvfile)                 #使用csv.reader()方法，读取文件，返回可迭代类型    for i in read:    

1.2 Write operation: Csv.writer

import csvwith open("/路径/文件名.csv","w") as csvfile    #‘w‘表示写操作，有则修改，无则新建    write=csv.writer(csvfile)    write.writerow(data)        #写入一行操作，data为可迭代类型，如果为字符串，则单个字符为一个元素    write.writerows(data)       #写入多行操作，data中一个元素为一行

1.3 Add Data: Csv.read--write

  Method 1:----------------------------------import CSV list1=[] with open (' Data.csv    ', ' R ') as Csvfile1: #首先---Read data read=csv.reader (CSVFILE1) for I in Read:list1.append (i)                          With open (' Data.csv ', ' W ') as Csvfile2: #然后----Read data before writing writer=csv.writer (Csvfile2) date=[list1[0], #在第五行开始添加数据 list1[1], list1[2], list1[3], [' Jac K ', ' 104 ']] writer.writerows (date) with open (' Data.csv ', ' R ') as Csvfile3: #输出添加后的数据 read2=csv.reader (CSVFILE3) for I in Read2:print (i)  

  Method 2: #读取文件 f = open ("./data.csv") #f是一个可迭代对象 result = [] #用于存放每一行的数据 for line in F:row = Line.split (",") #将每一行数据用 "," split, save to a list dat     A = [] for word in Row:data.append (Word.strip ()) # (Word.strip ()) removes extra space in the data result.append F.close () print (result) #当前文件夹下创建一个output. csv file where data is written to with open ("Output.csv", "W") as Csvfile:resul        T.append (["Jack", "104"]) for row in Result:csvFile.write (",". Join (Row) + "\ n") #将一个列表中的数据用 "," stitching into a string Csvfile.close ()  

Method 3: #使用DictReader方法读写 import csv def getData (path): #path读取文件的路径 result = [] with open (path) as File:reader = csv.                     Dictreader (file) for row in reader: # Row.keys () ["Name", "Stuno"] NewRow = {} # row.values () ["Zhangsan", "101"] for Key,value in Zip (Row.keys (), Row.values ()): #获取列名    , column value and go to Space newrow[key.strip ()] = Value.strip () result.append (newRow) return result data = GetData ("./data.csv") with open ("./output.csv", "W") as Csvfile:data = GetData ("./data.csv") dat A.append ({"Name": "Jack", "Stuno": "104"}) fields = ["Name", "Stuno"] #列标题 writer = CS                                V.dictwriter (Csvfile,fieldnames=fields) #用csv模块的dictwrite方法将字典写入到csv文件 Writer.writeheader () #将csv文件的第一行 (that is, the column name is written to a CSV file) for row in DaTA: #遍历字典写入到csv文件中 writer.writerow (Row) Csvfile.close () 

Operation of Excel 2.1: Use XLRD, XLWT (read-only, write-only) operation

XLRD Read file: Import xlrd myworkbook=xlrd.open_workbook ('/path/filename. xlsx ') #获取excel工作簿 Mysheets=mywo        Rkbook.sheets () #其次获取所有的工作表 mysheet1=myworkbook[0] #获取第一个工作表          Mysheet1=myworkbook.sheet_by_index (0) #同上 mysheet1=myworkbook.sheet_by_name (' Sheet1 ') #同上 nrows=mysheet1.nrows #获得所有的行数, int type ncols=mysheet1.ncols #获得所有的列数, int type M                Yrowvalue=mysheet1.row_value (i) #获得i列所有行的值 Mycell=mysheet1.cell (i,j) #获取i行, column J cell MyCell.Value #通过单元格获取值 mysheet1.cell_value (i,j) #直接获取表sheet1的i行, the value of column J MyS Heet1.cell_value (i,j) = "New_value" #直接赋值修改单元格的值: XLWT Write file: Import XLWT NEW_WK=XLWT. Workbook () #创建工作簿 new_sheet1=new_wk.add_sheet (' sheetname ') #创建名为sheetname的工作表 sheet.write ( I,j, ' content ') #在i行, column J Write content: ContenT,i,j starting from 0 new_wk.save ('/path/filename. xlsx ') #使用xlwt写入操作后, need to save 

2.2:xlutils combining XLRD (add data) operation

读、写文件：                   import xlrd    from xlutils.copy import copy    workBook=xlrd.open_workbook(‘/路径/文件名.xlsx‘)        #参考xlrd读操作    new_workBook=copy(workBook)          #使用xlutils中copy()方法复制一份工作簿    ws=new_workBook.get_sheet(index)     #get_sheet()方法，在仅导入xlrd时不可用，仅对使用copy()之后的工作簿可用    ws.write(i,j,‘content‘)              #在i行，j列写入内容：content，i，j从0开始，可覆盖    new_workBook.save(‘/路径/文件名.xlsx‘)

2.3: Use OPENPYXL action (write Excel artifact)

write a single data

读、写文件：    from openpyxl import Workbook    from openpyxl import load_workbook    from openpyxl.writer.excel import ExcelWriter    workBook=load_workbook(‘/路径/文件名.xlsx‘)    sheetNames=workBook.sheetnames                    #获取工作簿所有工作表名称，返回列表    sheet1=myWorkbook.get_sheet_by_name(‘Sheet1‘)     #获取myWorkbook的表单Sheet1    sheet1.cell(i,j).value                            #获取i行，j列单元格的值，注意：此时单元格索引有变，从1开始    sheet1[‘C3‘]=‘content‘                            #为C3单元格写入值，注意：此时单元格索引有变，分别从A，1开始     sheet1.cell(i,j).value  = ‘values‘

C. Python wrapper class to read and write Excel files

Here I have encapsulated a class that is easy to use later

 from OPENPYXL import Workbook #导入openpyxl模块的workbook模块 for writing. Xlsxfrom OPENPYXL Import load_workbookfrom openpyxl.writer.excel import Excelwriter wrapper class: Class Saveexceldata (object): # Defines the class ( For encapsulating functions for later use) def __init__ (self,datalist,sheettitle,filename): #定义构造函数分别传入数据, sheet title, save file name self.datalist=                  DataList self.sheettitle=sheettitle self.filename=filename def saveData (self): # define save Operation workbook = Workbook () # 1, create a new workbook, instantiate object sheet = workbook.active # 2, Activate a sheet sheet.title = self.sheettitle # 3, name the active worksheet for I in Self.datalist: # dataList incoming The Iterable container type that should be a iterable element sheet.append (i) workbook.save (self.filename)  

使用类：from day12_csv_xls.save_class_excel import SavaExcelData#     (上层文件夹)     （文件名）           （文件名里的类名）data = list(range(20))new_data = []for i in range(4):    new_data.append(data[len(data)//4*i:len(data)//4*(i+1)])    #写入数据顺序为 data[0:4],data[5:10],data[10:15],data[15:20]    print(len(new_data))new1 = SavaExcelData(new_data, ‘new_title‘, ‘new_file.xlsx‘)new1.saveData()

Iv. Comprehensive Exercises

Filter and sort rows in an Excel table, (sort each row according to the data in one of the columns), and write to the new form

Method 1:

Import Xlrdimport xlwtold_wbk = Xlrd.open_workbook ("rank.xlsx") rank = Old_wbk.sheets () [0]nrows = Rank.nrows #获取工作表数据的行数filterData = [] #保存筛选后的数据for row in range (nrows): Myrowvalue = Rank.row_valu ES (ROW) #从表中获取行数据 if row is 0 or myrowvalue[2]>5.0: #判断行数据 If it is the first row of the header or a data row with a points score greater than 5.0 is reserved Filterdata.appe nd (myrowvalue) headers = filterdata[0] #获取表头并写入到新的Excel表中new_wbk = XLWT. Workbook () #创建一个新的工作簿new_rank = New_wbk.add_sheet ("New_rank") #添加表单for col in range (len (headers)): New_ran                            K.write (0,col,headers[col]) del filterdata[0] #将表头从filterData中删除points = [] #将points从筛选后的数据中分离出来for item in FilterData:points.append (Item[2]) points.sort (reverse=true) #对分数进行排序resut            L = [] #存放最终排序后的结果 # Compare filtered data with sorted fractions, add to new list in order Inpoints:for row in filterdata:if row[2] = = point: Resutl.append (Row) print (RESUTL) for row in range (0,len (RESUTL)): For Col in range (len (headers)): New_rank.write (Row+1,col,resutl[row][col]) new_wbk.save ("New_rank . xls ")

Method 2--General Method

Import Xlrdimport Xlwtwb=xlrd.open_workbook ("./footage/two/rank.xlsx") mysheet=wb.sheet_by_index (0) nrows=mysheet.nrows #获取所有的行数 # Print (nrows) all_data=[]for I in Range (nrows): Row_values=mysheet.row_values (i) #将所有数据根据条件poi Nts>5 Shang Save # Print (Row_values[-1]) if i>0 and row_values[-1] > 5:all_data.append (row_values) Eli                        F i==0: #跳过表头 all_data.append (row_values) else:continue# print (All_data) #读取数据head =all_data[:1]data=all_data[1:]for i in range (len data): for J in Range (i): if Data[i                              ][-1]>DATA[J][-1]: #对某行的指定列value值进行对比 data[i],data[j]=data[j],data[i]print (data) #排序后wb =XLWT. Workbook () New_sheet=wb.add_sheet ("rank") for I in Range (Len (head)): New_sheet.write (0,i,head[i]) for J in Range (Len ( Data): for K in range (len (data[j)): New_sheet.write (j+1, K, data[j][k]) #写入新表单 (update) Wb.save ("Output.xls")

Pyhton--csv/excel Data Persistence