Learn Excel Read-write, convert an Excel file content to dict for subsequent processing, write dict to Excel
#coding = ' Utf-8 'Import TimeImportXlrd,xlsxwriterstart=Time.clock ()defread_excel (file):"""read in Excel file: Rtype:object:p Aram File:: Return: Data Object""" Try: Data=xlrd.open_workbook (file)returnDataexceptException as err:Print(ERR)defwrite_excel (file):"""read in Excel file: Rtype:object:p Aram File:: Return: Data Object""" Try: Data=xlsxwrite.workbook (file)returnDataexceptException as err:Print(ERR)defexcel_to_dict (read_excel_file,excel_title):""": Rtype:object:return dict"""Dict2={} data=read_excel (read_excel_file) Table= Data.sheet_by_name (U'Bas_info') forIinchRange (1, table.nrows): Row_content=table.row_values (i, 0, table.ncols) dict1=dict (Zip (Excel_title, row_content)) Dict2[i]=Dict1returnDict2defDict_to_excel (dict_content,excel_title):"""Write dictionary to Excel: Type Dict_content:object dict excel_title column header"""dict_ing=dict_content Excel_init_file=Xlsxwriter. Workbook (out_excel_file) Table= Excel_init_file.add_worksheet ('Bas_info') Title_bold= Excel_init_file.add_format ({'Bold': True,'Border': 2,'Bg_color':'Blue'}) Border= Excel_init_file.add_format ({'Border': 1}) forI,jinchEnumerate (Excel_title): Table.set_column (I,i,len (j)+1) table.write_string (0,i,j,title_bold) forKvinchDict_content.items (): forIinchRange (len (v)): J=V.get (Excel_title[i]) table.write_string (k,i,j,border) table.set_column (1,1,16) Table.set_column (0,0,16)
excel_init_file.close ()
if __name__=='__main__':
Read_excel_file= R'D:\baspools_read.xlsx'
Out_excel_file= R'D:\baspools_out.xlsx'
Excel_title= ['Basname','Basip',' Location','baslocation','Port','Basprovider','portal_version','Timeout','Retry','Auth_type']
Dict_content=Excel_to_dict (Read_excel_file,excel_title) #excel to Dict
Dict_to_excel (dict_content,excel_title) #dict to Excel write to Out_excel_file
End=Time.clock ()
Print("read:%f s"% (End-start))
Python Excel read-write and dict conversion