How Python operates Excel
Editor to share with you how to operate Python Excel, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!
Python operation Excel
Common tools
Data processing is a major application scenario of Python, and Excel is the most popular data processing software at present. Therefore, when using Python for data processing, it is easy to deal with Excel. Thanks to the hard work of our predecessors, Python has many ready-made wheels for dealing with Excel, such as xlrd & xlwt & xlutils, XlsxWriter, OpenPyXL, and the open interface of Microsoft Excel can be directly called on the Windows platform, which are more commonly used tools.
Introduction to xlrd & xlwt & xlutils
Xlrd&xlwt&xlutils is made up of the following three libraries:
Xlrd: for reading Excel files
Xlwt: for writing to Excel files
Xlutils: utilities for manipulating Excel files, such as copying, splitting, filtering, etc.
Install pip install xlrdpip install xlwtpip install xlutils import import xlrdimport xlwtimport xlutils read excel#-read excel----data= xlrd.open_workbook (excel_file) read sheet#-read sheet----# get sheet table = data.sheets () [0] # get sheet table = data.sheet_by_index (0) # get sheet table = data.sheet_by_ by name Name ("sheet") # returns the names of all sheet in book names = data.sheet_names () # pass in the index or sheet name to check whether a sheet has been imported table.sheet_loaded ("sheet") table.sheet_loaded (0) # sheet name table.name# sheet columns number of table.ncols# sheet rows table.nrows reads sheet rows # returns a list of all cell objects in the rowx row table.row (rowx) # obtained Take the type of the first cell in the rowx line # 0. Empty (empty), 1 string (text), 2 number, 3 date, 4 boolean, 5 error 6 blank (blank table) table.row (rowx) [0] .ctype # gets the value of the first cell in the rowx row table.row (rowx) [0] .value # returns a list of all cell objects in the rowx row table.row_slice (self, rowx, start_colx=0, end_colx=None) # returns a list of table.row_types (rowx, start_colx=0) consisting of the data types of all cells in the rowx row End_colx=None) # returns a list of all the cells in the rowx row table.row_values (rowx, start_colx=0, end_colx=None) # returns the effective cell length of the rowx row table.row_len (rowx) reads the column of sheet # returns the list of all cell objects in the colx column table.col (colx, start_rowx=0, end_rowx=None) # returns the list of all cell objects in the colx column table.col_slice (colx) Start_rowx=0, end_rowx=None) # returns a list of data types of all cells in the colx column table.col_types (colx, start_rowx=0, end_rowx=None) # returns a list of data of all cells in the colx column table.col_values (colx, start_rowx=0, end_rowx=None) reads sheet cells # returns the cell object cell = table.cell (rowx,colx) # cell data type # 0. Empty (empty), 1 string (text), 2 number, 3 date, 4 boolean, 5 error 6 blank (blank table) cell.ctype# cell value cell.value# returns the data type in the cell table.cell_type (rowx,colx) # returns the data in the cell table.cell_value (rowx,colx) # yet does not understand table.cell_xf_index (rowx,colx) write excel# use xlutils to convert objects read by xlrd into xlwt operable objects Table is the above xlrd read tableworkbook = xlutils.copy (table) # or if you just want to create an empty table workbook = xlwt.Workbook (encoding = 'utf-8') # create a sheetworksheet = workbook.add_sheet (' sheet') # get an existing sheetworksheet = workbook.get_sheet ('sheet') # write a value In parentheses are the number of rows, columns, and content worksheet.write (row, column, respectively) "memeda") workbook.save ('memeda.xls') write sample workbook = xlwt.Workbook (encoding =' utf-8') style = xlwt.XFStyle () font = xlwt.Font () # create font font.name = 'Arial'font.bold = True # boldface font.underline = True # underline font.italic = True # italic font.colour_index = 2 # Red style.font = fontworksheet.write (row, column, "memeda") Style) workbook.save ('memeda.xls') output multiple color fonts import xlwtworkbook = xlwt.Workbook (encoding='utf-8') def get_style (I): style = xlwt.XFStyle () font = xlwt.Font () # create fonts font.colour_index = I style.font = fontreturn stylesheet = workbook.add_sheet ("memeda") for i in range (0,100): sheet.write (I, 0, "memeda" Get_style (I)) workbook.save ('memeda.xls') these are all the contents of the article "how Python operates Excel" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
