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

Editor to share with you how to use openpyxl to deal with tabular data under Linux. I hope you will get something after reading this article. Let's discuss it together.

The openpyxl module is a Python library for reading and writing Excel 2010 documents. If you want to deal with earlier format Excel documents, you need to use additional libraries. Openpyxl is a more comprehensive tool that can read and modify Excel documents at the same time. Many other Excel-related projects basically only support reading or writing Excel.

Install it in Centos8 as follows:

[root@localhost ~] # yum-y install python3-openpyxl reads data from Excel table

If you want to read the Excel file, you must open the spreadsheet using the load_workbook () method. You can then use active to select the first available worksheet and select the cell by passing the row and column parameters using the cell () attribute. The value property returns the value of a specific cell. See the example below for a better understanding.

Note: the first row or column integer is 1, not 0.

The following data can be saved in the excel table file to do the following experiment, the file name is wb.xlsx:

NameCourseBranchSemesterAnkitB.TechCSE4RahulM.TechCSE2PriyaMBAHR3NikhilB.TechCSE4NishaB.TechBiotech5 [root@localhost data] # python3# Import openpyxl Library > import openpyxl# path variable specifies the location of the table file > path = "/ data/wb.xlsx" # load the table file > wb_obj = openpyxl.load_workbook (path) # activate the default worksheet > sheet_obj = wb_obj.active# Select the cell of the first row and the first column That is, A1 cell > cell_obj = sheet_obj.cell (row = 1, column = 1) # print cell location > print (cell_obj) 'Sheet1'.A1 > # print A1 cell value > print (cell_obj.value) Name reads data from multiple cells

There are two ways to read data from multiple cells.

* * method 1: * * We can use max_row and max_column to obtain the total number of rows and columns respectively. We can use these values in the for loop to get the values of the desired row or column or any cell as appropriate. Let's see how to get the values of the first column and the first row.

# use max_row to get data on how many rows a worksheet has > row = sheet_obj.max_row# uses max_column to get data on how many columns a worksheet has > column = number of rows printed by sheet_obj.max_column# > print ("Total Rows:", row) Total Rows: number of printed columns > print ("Total Cols:", column) Total Cols: print the first column data using for > for i in range (1) Row + 1):... Cell_obj = sheet_obj.cell (row = I, column = 1). Print (cell_obj.value)... NameAnkitRahulPriyaNikhilNisha# uses the for loop to print the second piece of data: > for i in range (1, column + 1):. Cell_obj = sheet_obj.cell (row = 3, column = I). Print (cell_obj.value, end = "")... Rahul M.Tech CSE 2

* * method 2: * * We can also use cell names to read from multiple cells. This can be thought of as a list slice of Python.

# Select the cells from A1 to B6 > cell_obj = sheet_obj ['A1 fantastically printed B6'] > # for fantasy print two columns of data > for cell1, cell2 in cell_obj:... Print (cell1.value, cell2.value)... Name CourseAnkit B.TechRahul M.TechPriya MBANikhil B.TechNisha B.Tech writes table data

First, let's create a new table, and then we write some data to the newly created file. You can create an empty table using the Workbook () method. Let's look at the following example.

[root@localhost data] # python3# import Workbook > > from openpyxl import Workbook > # create an empty workbook > workbook = Workbook () > # Save file > workbook.save (filename= "/ data/sample.xlsx") >

After creating an empty file, let's see how to add some data to it using Python. To add data first, we need to select the active worksheet, and then using the cell () method, we can select any specific cell by passing the row and column numbers as its parameters. We can also write with cell names.

# get active worksheet > sheet = workbook.active > > # add helo value for A1 > C1 = sheet.cell (row = 1, column = 1, value = 'Hello') # add world value for B1 > C2 = sheet.cell (row = 1, column = 2) Value = 'World') > # add Welcome value for A2 > C3 = sheet [' A2']. Value = 'Welcome'# add everyone value for B2 > C4 = sheet [' B2']. Value = 'Everyone' > # Save to file > workbook.save ("/ data/sample.xlsx")

Attach data to the table

In the above example, you will see that each time you try to write to the table, the existing data is overwritten and the file is saved as a new file. This happens because the Workbook () method always creates a new workbook file object. To write to an existing workbook, you must open the file using the load_workbook () method.

[root@localhost data] # python3 > > import openpyxl > > # load a file using load_workbook > wb = openpyxl.load_workbook ("/ data/sample.xlsx") > sheet = wb.active > c = sheet ['A3'] > c.value =' New Data' > wb.save ("/ data/sample.xlsx")

We can also use the append () method to append multiple data to the end of the worksheet.

# definition data > data = (. (1, 2, 3),... (4, 5, 5, 6). ) > # append content to the worksheet using the for loop > for row in data:... Sheet.append (row)... > > wb.save ('/ data/sample.xlsx')

Arithmetic Operation of Excel Table

You can perform arithmetic operations by typing formulas in specific cells of the spreadsheet. For example, if we want to sum, we use the = Sum () formula of the Excel file.

[root@localhost data] # python3 > import openpyxl > # create a new workbook > wb = openpyxl.Workbook () > sheet = wb.active > # write data in the cell > sheet ['A1'] = 200 > sheet [' A2'] = 300 > sheet ['A3'] = 400 > sheet [' A4'] = 500 > sheet ['A5'] = 6' summation in A7 cells > sheet ['A7'] =' = SUM (A1:A5)'> > # Save the file name as sum .xlsx > > wb.save ('/ data/sum.xlsx')

Adjust the rows and columns of a cell

The worksheet object has row_dimensions and column_dimensions properties that control the row height and column width. The row_dimensions and column_dimensions of the worksheet are dictionary-like values; row_dimensions contains RowDimension objects, and column_dimensions contains ColumnDimension objects. In row_dimensions, you can access one of the objects using a line number (in this case, 1 or 2). In column_dimensions, you can access one of the objects using the letters of the column (An or B in this case).

[root@localhost data] # python3# Import Module > import openpyxl > # create a new workbook > wb = openpyxl.Workbook () > sheet = wb.active > # write data to cell A1 > sheet.cell (row = 1, column = 1 Value = 'Hello') 'Sheet'.A1 > # writes data to B2 cell > sheet [' B2']. Value = 'Everyone' > # set the cell height of the first row to 45 > sheet.row_dimensions [1] .height = 45 > # set the cell width of column B to 70 > sheet.column_dimensions ['B']. Width = 70 > wb.save ('/ data/h_and_w.xlsx')

Merge cells

You can use the merge_cells () method to merge multiple cells into a single cell.

[root@localhost data] # python3 > import openpyxl > wb = openpyxl.Workbook () > sheet = wb.active# merges A2 to D4 cells and adds data > sheet.merge_cells ('A2purD4') > sheet [' A2']. Value = 'Twelve cells join together.' > # merge C6 into D6 cells and add data > sheet.merge_cells (' C6D6') > > sheet ['C6']. Value =' Two merge cells.' > wb.save ('/ data/merge_cells.xlsx')

Unmerge cells

To unmerge cells, use the unmerge_cells () method.

# Unmerge A2 to D4 cells > sheet.unmerge_cells ('A2wb.save D4') > # unmerge C6 to D6 cells > sheet.unmerge_cells (' C6purD6') > wb.save ('/ data/merge_cells.xlsx')

Set font styl

To customize the font style in the cell, the point is to import the Font () function from the openpyxl.styles module.

[root@localhost data] # python3 > import openpyxl# imports Font function from openpyxl.styles > from openpyxl.styles import Font > wb = openpyxl.Workbook () > sheet = wb.active > # add content for A1 cell and set font size to 24 > sheet ['A1']. Value =' Linuxprobe.com' > sheet ['A1']. Font = Font (size = 24) # B2 cell add content, set font size 24 Italics > sheet.cell (row = 2, column = 2, value = "Linuxprobe.com"). Font = Font (size = 24, italic = True) # C3 cell add content, set font size 24, bold > sheet.cell (row = 3, column = 3, value = "Linuxprobe.com"). Font = Font (size = 24, bold = True) # D4 cell add content, set font size 24 Set font style > sheet.cell (row = 4, column = 4, value = "Linuxprobe.com"). Font = Font (size = 24, name = 'Times New Roman') > wb.save (' / data/font_styles.xlsx')

Draw a chart

To draw a chart on an Excel table, you first create a chart object for a specific chart class (that is, BarChart, LineChart, and so on). After you create a chart object, insert data into it, and finally add the chart object to the worksheet.

[root@localhost data] # python3 > import openpyxl# Import Chart Library > > from openpyxl.chart import BarChart, Reference > wb = openpyxl.Workbook () > sheet = wb.active > # use a loop to attach content to the worksheet > for i in range (10):. Sheet.append ([I])... # create data for the chart > values = Reference (sheet, min_col=1,min_row=1,max_col=1) Max_row=10) # create chart instance > > chart = BarChart () # add data to bar chart > chart.add_data (values) # add chart title > chart.title = "BAR-CHART" # add X coordinate title > chart.x_axis.title = "X_AXIS" # add Y coordinate title > chart.y_axis.title = "Y_AXIS" > # add chart to E2 cell of worksheet > > sheet.add_chart (chart 'E2') > wb.save ("/ data/charts.xlsx")

The following example is to add a line chart:

[root@localhost data] # python3 > import openpyxl# Import Chart Library > > from openpyxl.chart import LineChart, Reference > wb = openpyxl.Workbook () > sheet = wb.active > # use a loop to attach content to the worksheet > for i in range (10):. Sheet.append ([I])... # create data for the chart > values = Reference (sheet, min_col=1,min_row=1,max_col=1) Max_row=10) # create chart instance > > chart = LineChart () # add data to discount chart > chart.add_data (values) # add chart title > chart.title = "LINE-CHART" # add X coordinate title > chart.x_axis.title = "X_AXIS" # add Y coordinate title > chart.y_axis.title = "Y_AXIS" > # add chart to E2 cell of worksheet > > sheet.add_chart (chart 'E2') > wb.save ("/ data/charts.xlsx")

Add Picture

To import images into our worksheet, we will use openpyxl.drawing.image.Image. This method is the PIL.Image method found in the pillow library. Therefore, the python3-pillow library must be installed to use this method.

[root@localhost data] # yum-y install python3-pillow [root@localhost data] # python3 > > import openpyxl# Import Image function > from openpyxl.drawing.image import Image > wb = openpyxl.Workbook () > sheet = wb.active > # add content to the first row of the worksheet > > sheet.append (["abc", 10, "def", 20]) # add picture > img = Image ("/ data/banner1.jpg") > sheet.add_image (img) After reading this article, 'A2') > wb.save ("/ data/images.xlsx") I believe you have a certain understanding of "how to use openpyxl to deal with tabular data under Linux". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.