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

Most people do not understand the knowledge points of this article "Excel how to use python to achieve office automation", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how Excel uses python to achieve office automation" article.

Prepare for

Using Python to manipulate Excel files, the common ways are as follows:

Xlrd / xlwt

Openpyxl

Pandas

Xlsxwriter

Xlwings

Pywin32

Xlrd and xlwt are the two dependent libraries that manipulate Excel files most frequently.

Among them

Xlrd is responsible for reading Excel files, and xlwt can write data to Excel files

We install these two dependent libraries

# install dependent library pip3 install xlrd pip3 install xlwtxlrd to read Excel

Open a local Excel file using open_workbook (filepath) in xlrd

Import xlrd# opens the file and returns a workbook object wb = xlrd.open_workbook (file_path)

The nsheets property of the workbook object gets the number of Sheet, and the sheet_names () method returns a list of all Sheet names

# Statistics the number of sheet sheets_num, sheets_names = wb.nsheets, wb.sheet_names () print ('sheet total:', sheets_num) print ('sheet name is:', sheets_names)

There are two ways to filter out a Sheet in a workbook, which are:

By Sheet name

Use the location index, starting at 0

# get a sheet# get sheet = wb.sheet_by_index (0) # sheet = wb.sheet_by_name ('first Sheet') print (sheet) by name or index

Each sheet object can obtain the Sheet name, number of rows, and number of columns using name, nrows, and ncols.

In addition,

Row_values (index) and col_values (index) are used to get a list of data for a row or column, respectively.

# get the number of rows and columns contained in a sheet, sheet_name, sheet_row_count, sheet_column_count = sheet.name, sheet.nrows, sheet.ncolsprint ('current sheet name is:', sheet_name, ", there are:, sheet_row_count," rows There are: ", sheet_column_count," column ") # get a row of data separately, the index starts at 0 # for example: get the second row of data row_datas = sheet.row_values (1) print ('the second row of data is:', row_datas) # get a column of data separately, the index starts at 0 # for example: get the second column of data column_datas = sheet.col_values (1) print ('the second column of data is:', column_datas)

Cells can be obtained through row index, column index, and call cell (row_index,column_index) function.

It should be noted that both row and column indexes start at 0, that is, 0 represents the first row

In xlrd, there are 6 data types of cells, and the corresponding relationship with the ctype attribute is as follows:

0-Null (empty)

1-string (string)

2-numeric (number)

3-date (date)

4-boolean (Boolean)

5-error (error)

# get the data of a cell # for example: get the data of the cell in row 2, column 1, one_cell = sheet.cell (1, 0) # the value of the cell cell_value = one_cell.valueprint ("the value of the cell is:", cell_value) # cell data type cell_type = one_cell.print ("cell data type is:", cell_type)

Finally, if you want to get the data in all the cells of the current Sheet, you can do so by iterating through all the rows and columns

# get the values of all cells print ('all the data in the table are as follows:') for rin range (sheet.nrows): for i in range (sheet.ncols): print (sheet.cell (r, I) .value) xlwt writes to Excel

If you want to write data to Excel, xlwt is very convenient.

First, create a workbook object using the Workbook () method of xlwt

Then, add the Sheet using the add_sheet (sheetname) method of the workbook object

Import xlwtsheetname = 'first Sheet'# create a workbook object wb = xlwt.Workbook () # add Sheet by sheet name sheet = wb.add_sheet (sheetname)

Then, through the write () method of the sheet object, the data is written to the corresponding cell according to the row index and column index.

# write data to Sheet # 3 parameters are: row index (starting from 0), column index (starting from 0), cell value # first row first column Write a data # write title for index, title in enumerate (self.titles): sheet.write (0, index, title) # write values for index_row, row_values in enumerate (self.values): for index_column, column_value in enumerate (row_values): sheet.write (index_row + 1, index_column, column_value)

It should be noted that finally, the workbook's save (filepath) must be called to generate the Excel file locally.

# Save the file # finally save the file for wb.save (filepath) advanced usage

Next, let's talk about a few common advanced uses.

1. Get all visible Sheet

When reading Sheet data, it is often necessary to filter hidden Sheet

When the value of the visibility property of the sheet object is 0, the Sheet is displayed in the workbook; otherwise, it is hidden

Def get_all_visiable_sheets (self, wb): "get all visible sheet: param wb:: return:"return list (filter (lambda item: item.visibility = = 0, wb.sheets () # 1, get all visible sheetsheet_visiable = self.get_all_visiable_sheets (wb) print ('all visible sheet includes:', sheet_visiable)

2. Get Sheet visible rows or columns

In a Sheet, there may be some rows and columns hidden.

Def get_all_visiable_rows (self, sheet): "" get the visible line in a sheet: param sheet:: return: "result = [index for index in range (sheet.nrows) if sheet.rowinfo_ map [index] .hidden = = 0] return resultdef get_all_visiable_columns (self, sheet):"get a sheet Visible column: param sheet:: return: "result = [index for index in range (sheet.ncols) if sheet.colinfo_ map [index]. Hidden = = 0] return result

3. Get the cell style

Take getting the cell font color and background as an example

Def get_cell_bg_color (self, wb, sheet, row_index, col_index): "get the background color of a cell: param wb:: param sheet:: param row_index:: param col_index:: return:" xfx = sheet.cell_xf_index (row_index) Col_index) xf = wb.xf_ list [xfx] # font color font_color = wb.font_ list [xf.font _ index] .background _ index # background color bg_color = xf.background.pattern_colour_index return font_color, bg_color

It should be noted that using xlrd to read the cell style, you need to explicitly define formatting_info = True when you open the workbook, otherwise an exception will be thrown

# Note: formatting_info=True must be set to get the attribute wb = xlrd.open_workbook (file_path, formatting_info=True) sheet = wb.sheet_by_index (0).

With xlrd and xlwt, you can basically accomplish most of the work. For some complex functions, such as replication, segmentation, filtering, etc., you can use xlutils as a dependent library.

It is important to point out that this combination is not very compatible with xlsx; if you need to manipulate xlsx files, you need to convert to xls before doing so.

The above is about the content of this article on "how Excel uses python to achieve office automation". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more related knowledge, please pay attention to the industry information channel.

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.