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

This article mainly explains "how to read Excel files from Python". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how Python reads Excel files.

Prepare for

First, we need to install the dependency package

# install dependency package pip3 install openpyxl to read data

Use load_workbook (filepath) in openpyxl to load a local Excel file and return a workbook object

Import openpyxl# loads the local Excel file wb = openpyxl.load_workbook (file_path)

Using the workbook object, you can get all the Sheet names and Sheet lists

Def get_all_sheet_names (wb): "" get the names of all sheet: param wb:: return: "# sheet name list sheet_names = wb.sheetnames return sheet_namesdef get_all_sheet (wb):" get all sheet: param wb:: return: "# sheet name list sheet_names = get_all _ sheet_names (wb) # all sheet sheets = [] for sheet_name in sheet_names: sheet = wb [sheet _ name] sheets.append (sheet) return sheets

The workbook object provides an active property to quickly get the currently selected Sheet

Def get_current_sheet (wb): "gets the currently selected sheet. The default is the last sheet: param wb:: return:" # currently selected sheet current_sheet = wb.active return current_sheet

Alternatively, you can get a specific Sheet object through the Sheet name

Def get_sheet_by_name (wb, sheet_name): "" use sheetname to find a sheet: param wb:: param sheet_name:: return: "sheet_names = get_all_sheet_names (wb) if sheet_name in sheet_names: result = wb [sheet _ name] else: result = None return result

Use sheet.max_row and sheet.max_column to get the number of rows and columns in the current Sheet.

Def get_row_and_column_num (sheet): "" get the number of rows and columns of sheet: param sheet:: return: "" # rows row_count = sheet.max_row # columns column_count = sheet.max_column return row_count, column_count# rows and columns row_count, column_count = get_row_and_column_num (sheet) print ('rows and columns are:' Row_count, column_count)

Openpyxl provides two ways to locate a cell, namely:

Digital index, starting with 1

Digital index: row digital index, column digital index

For example: row_index=1,column_index=1

A string index consisting of rows and columns

String index: column consists of letters + row index

For example: A1 corresponds to the cells of the first row and the first column

In addition, openpyxl.utils provides a method to facilitate the conversion of column indexes between the two

From openpyxl.utils import get_column_letter, column_index_from_stringdef column_num_to_str (num): "" Excel index column from numeric to alphabetic: param num:: return: "return get_column_letter (num) def column_str_to_num (str):" Excel index column From letter to number: param str:: return: "return column_index_from_string (str)

Cell acquisition can also be obtained through the above two indexing methods.

Def get_cell (sheet, row_index, column_index): "get cell: param sheet:: param row_index:: param column_index:: return:" # openpyxl index counts from 1, which is different from xlrd # get a cell (choose one of two) # for example: get the data of A1 cell That is, the data of the first row and column # cell_one = sheet ['A1'] cell_one = sheet.cell (row=row_index, column=column_index) return cell_one

In the daily processing of Excel data, it may be necessary to determine the cell data type, but openpyxl does not provide a ready-made method

Here, we can get the value through the value property of the cell object, and then use the isinstance method to determine the data type

Def get_cell_value_and_type (cell): "get the content and data type of a cell: param cell:: return:" # the value of the cell cell_value = cell.value # the cell type cell_type = get_cell_value_type (cell_value) return cell_value Cell_typedef get_cell_value_type (cell_value): "" get data type: param cell_value:: return: "" # where # 0: empty # 1: number # 2: string # 3: date # 4: other if not cell_value: cell_type = 0 elif isinstance (cell_value, int) or isinstance (cell_value Float): cell_type = 1 elif isinstance (cell_value, str): cell_type = 2 elif isinstance (cell_value, datetime.datetime): cell_type = 3 else: cell_type = 4 return cell_type

To get the data of a row [column] separately, you can use the following ways:

Def get_row_cells_by_index (sheet, row_index): "" get the cell of a row through the row index: param row_index:: return: "# Note: the first column starts from 1 row_cells = sheetrow [index] return row_cellsdef get_column_cells_by_index (sheet, column_index):"through the column index Get the cell number of a column and convert it to the letter column_index_str = column_num_to_str (column_index) # get the data of a column column_cells = sheet [column _ index_str] return column_cells

It should be noted that to get data for a row, you need to pass in a numeric index, while for column data, you must pass in a string index.

Similar to Python list range values, openpyxl also supports the use of: symbols to get data rows [columns] within a certain range

Def get_rows_by_range (sheet, row_index_start, row_index_end): "to select a row range by range, for example: select all data from rows 2 to 4. The return value is a tuple: param sheet:: param row_index_start:: param row_index_end:: return: "" rows_range = sheet [row _ index_start:row_index_end] return rows_rangedef get_columns_by_range (sheet, column_index_start, column_index_end): "to select a column range by range, for example: select all the data from columns 2 to 4. The returned value is tuple: param sheet:: param column_index_start:: param column_index_end:: return: "columns_range = sheet [column _ num_to_str (column_index_start): column_num_to_str (column_index_end)] return columns_range write data

To write data to the Excel table

First, create an Excel workbook object using openpyxl.Workbook ()

Next, create a new Sheet using the create_sheet () of the workbook object

# create an Excel workbook # Note: every time a new Excel file is created, a worksheet named [Sheet] is generated by default, Sheetwb = openpyxl.Workbook () # create a new sheet By default, it is inserted into the tail # new_sheet = wb.create_sheet ('new Sheet') # you can also specify the insertion position through the second parameter: index # for example: insert to the beginning new_sheet = wb.create_sheet (' new Sheet', 0)

The default created Sheet is inserted into the last location, and the second parameter specifies the location where the Sheet is inserted.

The background color of Sheet tags can also be modified. Use sheet_properties.tabColor to specify RGB color values.

For example, to set the background color of a Sheet to red, you only need to query the corresponding Sheet, and then specify the color value FF0000.

Def set_sheet_bg_color (sheet, rgb_value): "" set the color of the Sheet label: param rgb_value:: return: "" # set the color of the button at the bottom of the Sheet (RRGGBB) sheet.sheet_properties.tabColor = rgb_value # set the background color of the Sheet (red) set_sheet_bg_color (new_sheet, 'FF0000')

Openpyxl supports row-column numeric index and string index to write data to cells in these two ways.

Def write_value_to_cell_with_num (sheet, row_index, column_index, value): "" write data by row index and column index: param shell:: param row_index: row index: param column_index: column index: param value:: return: "" # choose one of the two sheet.cell (row=row_index, column=column_index, value=value) # shell.cell (row=row_index Column=column_index) .value = valuedef write_value_to_cell_with_index_str (sheet, index_str, value): "by alphabetical position Write data to the corresponding cell: param shell:: param index_str: the corresponding cell location: param value:: return: "" sheet [index _ str] = value

Inserting a picture into a cell is also easy. The add_image () method provided by openpyxl

There are two parameters, namely: picture object and cell string index

For ease of use, we can convert the column index and encapsulate it into two methods for inserting pictures

From openpyxl.drawing.image import Imagedef insert_img_to_cell_with_num (sheet, image_path, row_index, column_index): "" insert a picture into the cell: param sheet:: param image_path:: param row_index:: param column_index:: return: "" # through row index and column index Get the alphabetic index index_str = column_num_to_str (column_index) + str (row_index) insert_img_to_cell_with_str (sheet, image_path, index_str) def insert_img_to_cell_with_str (sheet, image_path) Index_str): "" insert a picture into the cell: param sheet:: param image_path:: param index_str:: return: "sheet.add_image ((image_path), index_str)

Finally, call the save () method of the workbook object to actually write the data to the Excel file

# Note: you must write it before you can actually save it to the file wb.template = Falsewb.save ('new.xlsx') to modify the data

Modification data includes: modification of cell data, modification of cell style

For the modification of cell data, you only need to read the workbook object, query the Sheet object to be operated, then call the above method to modify the cell data, and finally call the save () function to save the overlay.

Def modify_excel (self File_path): "" modify the data in the local Excel file: param file_path:: return: "" # read the local Excel file wb = openpyxl.load_workbook (file_path) # read a certain sheet sheet = wb ['first Sheet'] print (sheet) # modify the data write_value_to_cell_with_num of a cell directly (sheet, 1,1) 'name 1') # Save and overwrite wb.save (file_path)

Cell styles include: font style, cell background style, border style, alignment, etc.

Take common font styles and alignment as examples

First, use the Font class in openpyxl to create an object that specifies the font name, font size, bold, italic, color, underline, and so on

From openpyxl.styles import Font# font format # specifies font type, size, bold, color, etc. Font0 = Font (name='Calibri', size=20, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF00FF00')

Next, build an Alignment object that specifies the alignment of the cell

From openpyxl.styles import Font,Alignment# cell alignment alignment0 = Alignment (horizontal='center', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)

Finally, use the font/alignment property of the cell object to set the font style and alignment

# set property style (font, alignment) sheet ['A1']. Font = font0sheet [' A1']. Alignment = alignment0 advanced usage

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

1. Get visible and hidden Sheet

By judging the value of the sheet_state attribute of the Sheet object, you can determine whether the current Sheet is displayed or hidden

When the value is visible, the Sheet is displayed

When the value is hidden, the Sheet is hidden

Def get_all_visiable_sheets (wb): "get all visible sheet: param wb:: return:" return [sheet for sheet in get_all_sheet (wb) if sheet.sheet_state = = 'visible'] def get_all_hidden_sheets (wb): "get all hidden sheet: param wb:: return:"in the workbook "return [sheet for sheet in get_all_sheet (wb) if sheet.sheet_state = = 'hidden']

2. Get hidden / displayed row index list and column index list

Limited by space, here is an example of getting an index list of all displayed / hidden rows

Traverses the row_dimensions attribute value of the Sheet object, and determines whether the current row is hidden or displayed by judging the hidden value of the row attribute.

Def get_all_rows_index (sheet, hidden_or_visiable): "get all hidden / displayed rows: param hidden_or_visiable: True: hidden False: show: param sheet:: return: "" # iterate through rows # Hidden Index hidden_indexs = [] # all Hidden Row Index for row_index RowDimension in sheet.row_dimensions.items (): if rowDimension.hidden: hidden_indexs.append (row_index) # all displayed row indexes visiable_indexs = [index + 1 for index in range (get_row_and_column_num (sheet) [0]) if index + 1 not in hidden_indexs] # hidden or displayed row index list return hidden_indexs if hidden_or_visiable else visiable_indexs

3. Get the cell font color and cell background color

The font.color.rgb and fill.fgColor.rgb attribute values of the cell object represent the font color value and the cell background color, respectively.

Def get_cell_font_color (sheet, row_index, column_index): "" get the color of the cell font: param sheet:: param row_index: row index: param column_index: column index: return: "cell_color = sheet.cell (row_index, column_index). Font.color if cell_color: return sheet.cell (row_index Column_index) .font.color.rgb else: # Color does not exist Maybe the cell has no data return Nonedef get_cell_bg_color (sheet, row_index, column_index): "" get the background color of the cell: param sheet:: param row_index: row index: param column_index: column index: return: "return sheet.cell (row_index, column_index) .fill.fgColor.rgb

It can be found that compared with xlrd/xlwt, openpyxl provides a large number of practical API, is more powerful, and perfectly supports xlsx!

At this point, I believe you have a deeper understanding of "how to read Excel files from Python". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.