Introduction to Excel read and write report statistics in Python

1. Environment preparation

• Python3.0 +.Download address
• Environment variable configuration, willPythonInstallation path Add value of system environment variablespath, as shown in the figure below

• Detecting environment variables,windowUsing shortcut keyswin+rThe inputcmdThen enterpythonThe environment configuration is correct. The following information is displayed

2. Dependencies

When you install Python, you will automatically install PIP. PIP is the engine for downloading dependencies. You can use the PIP install module name to download the required dependencies.

For example, when we learn to automate Excel with Python, we will need to refer to the operation documents such as OpenPyXL module,API and so on. The first step in development thinking is to reference this dependency

2.1 Dependent Download

1. incmdorvscodeEditor terminalpip install openpyxl, has just been installedPythonWhen the environment executes this command, there are two possible outcomes.

• An error message is displayed indicating that an upgrade is requiredpipVersion, the rootnodeThe difference is ifpipNot the latest version will not be able to pull the module file, so we need to upgradepipThe module
• ifpipWith the new version, the progress bar will load slowly or appear during the process when you pull the moduleTimeout This is because the module file is relatively large, and the default ispythonModule file of official website address, so we need transfer station (mirror)

2.2 Image Usage

See my first article, Getting Started with Python, for a more detailed description of mirroring and how to use it

3. excelOperation related

3.1 readexcelThe report data

So without further ado, go straight to the code. aboutopenpyxlThe operating documents of the You can refer to the official documentation when you encounter problems or have new ideas. Let’s say I need to readexcelThe following

# Introduce the required dependencies
import openpyxl
Use the same directory as the Python file, or use the unique path, need to pay attention to the drive letter
wb = openpyxl.load_workbook('helloword.xlsx')
Select * from 'sheet' where 'sheet' = 'sheet' and 'sheet' = 'sheet'
names = wb.sheetnames
# wb.get_sheet_by_name(name) is discarded. Use WB [name] to obtain the specified worksheet
sheet = wb[names[0]]
# Get the maximum number of rows
maxRow = sheet.max_row
# Obtain the maximum number of columns
maxColumn = sheet.max_column
# Get the current active table
current_sheet = wb.active
Get the name of the current active table
current_name = sheet.title
Print the sheet array
print(names)
---------------------------------------------------------------------------------
PS D:\python-workplace> & D:/python/python.exe d:/python-workplace/excel-demo1.py
['Sheet1'.'test'.'Active page'] -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -# Retrieve the specified sheet object
sh1 = wb['Sheet1']
Read the value of the specified cell
cell_v = sh1['A1'].value
# Determine data by rows and columns
cell_RL = sh1.cell(row=1,column=2).value
print("Read the specified cell value: {}" .format(cell_v)
print("Read specified data through rows and columns :{}".format(cell_RL)) --------------------------------------------------------------------------------- PS D:\python-workplace> & D:/python/python.exe D:/ python-workplace/excel-demo1.py Pie reading specify the data from the ranks: sold -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -Retrieve data for the specified class and row
col_range = ws['A:B']
row_range = ws['1']

# Read by column (column by column)
for col in col_range:
    for cell in col:
        print(cell.value) --------------------------------------------------------------------------------- PS D:\python-workplace> &  D:/python/python.exe d:/python-workplace/excel-demo1.py Pie apple balana Pumkin orange sold50
30
10
10
---------------------------------------------------------------------------------

# Read by line (line by line)
for row in row_range:
    for cell in row:
        print(cell.value) --------------------------------------------------------------------------------- PS D:\python-workplace> &  D:/python/python.exe d:/python-workplace/excel-demo1.py Pie sold apple50
balana
30
Pumkin
10
orange
10
---------------------------------------------------------------------------------

# Merge top
for row in ws.iter_rows(min_row=1,max_row=5,max_col=2) :for cell in row:
        print(cell.value) --------------------------------------------------------------------------------- PS D:\python-workplace> &  D:/python/python.exe d:/python-workplace/excel-demo1.py Pie sold apple50
balana
30
Pumkin
10
orange
10
---------------------------------------------------------------------------------
Copy the code

3.2 Writing Data toexcel

import openpyxl
from openpyxl.utils import get_column_letter

""" wb = openPyxl.workbook () sheet = wb.active print(sheet.title) Print (wb.get_sheet_names()) # sheet_names() #wb.save("excel_writer.xlsx") # create sheet_names in the specified order wb.create_sheet(index=0,title='First Sheet') wb.create_sheet(index=1,title='Second Sheet') print(wb.get_sheet_names()) "" "


# Write data to excel units
wb = openpyxl.Workbook()
sheet = wb.active

# Specifies the cell column padding value
sheet['A1'] = "hello python 2021"
print(sheet['A1'].value)
# Row data filling
ws = wb.create_sheet('range rows')
for i in range(1.39):
    ws.append(range(17))

ws1 = wb.create_sheet('List')
rows = [
    ['Number'.'Batch 1'.'Batch2'],
    [2.100.500],
    [3.200.11],
    [4.12.777],
    [5.22.Awesome!],
    [6.543.544]]for row in rows:
   ws1.append(row) 

ws2 = wb.create_sheet("Date")
# Fill values in rows and columns
for row in range(5.30) :for col in range(10.45):
        ws2.cell(column=col,row=row,value=get_column_letter(col))

print(ws2['AA10'])
# Save to disk
wb.save('excel_writer.xlsx')
# read sheet
sts = wb.get_sheet_names()
print(sts)

sh1 = wb.get_sheet_by_name('Sheet')
print(sh1['A1'].value)

sh2 = wb.get_sheet_by_name('range rows')
print(sh2.rows)




Copy the code

3.3 excelFont, color, width, height, row, column, simple chart and so on

import openpyxl
from openpyxl.styles import Font
from openpyxl.styles import colors


# Excel operations related to font style size and so on

wb = openpyxl.Workbook()

# the font associated
ws = wb.active
ws.title='Font'
Set font to 24px italic
italic24Font = Font(size=24,italic=True)
ws['B3'].font = italic24Font
ws['B3'] = '24 px itailc '
# Set Times New Roman to red
boldRedFont = Font(name='Times New Roman',bold=True,color=colors.COLOR_INDEX[2])
ws['A1'].font = boldRedFont
ws['A1'] = 'bold red font'

Sum of Formula and so on
ws = wb.create_sheet('Formula')
ws['A1'] = 200
ws['A2'] =300
ws['A3'] ='=SUM(A1:A2)'

Set the height and width of a row or cell
ws = wb.create_sheet('dimensions')
ws['A1'] =  'Tall row'
ws.row_dimensions[1].height = 70
ws['B2'] = 'Width column'
ws.column_dimensions['B'].Width = 20

# Cell merge
ws = wb.create_sheet('megred')
ws.merge_cells('A1:D3')
ws['A1'] = 'Twelve cells megred together'


# Split cells
ws = wb.copy_worksheet(wb['megred'])
ws.title ='unmegerd'
ws.unmerge_cells('A1:D3')


The icon operation draws an icon
from openpyxl.chart import(
    AreaChart,
    Reference,
    Series,
    PieChart,
)
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import (
    Paragraph, 
    ParagraphProperties, 
    CharacterProperties
)
ws = wb.create_sheet('AreaChart')


data = [
    ['Pie'.'sold'],
    ['apple'.50],
    ['balana'.30],
    ['Pumkin'.10],
    ['orange'.10]]for row in data:
    ws.append(row)
# Create line chart instance
pie = AreaChart()
# Set title
pie.title='pie sold chart'
# Statistical graph style to participate in the official website
pie.style =13
# x Week title
pie.x_axis.title ='Test'
# Y-axis heading
pie.y_axis.title ='Percentage'

# Create statistics
cats = Reference(ws,min_col=1,min_row=2,max_row=6)
# Add data
datas = Reference(ws,min_col=2,min_row=1,max_row=5)
Insert data and read headers from the table
pie.add_data(datas,titles_from_data=True)
pie.set_categories(cats)
Insert the specified position
ws.add_chart(pie,"A10")


p = PieChart()
p.title='Fruit Sales Statistics'
p.add_data(datas,titles_from_data=True)
p.set_categories(cats)
p.style =10 
p.height =10
# Get the data instance
s1 = p.series[0]
Get the data and set the percentage
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = True     # Tag display
s1.dLbls.showVal = False     # Number display
s1.dLbls.showPercent = True     # Percentage display
axis = CharacterProperties(sz=1200)     # Font size in chart *100
s1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])
ws.add_chart(p,'J10')
wb.save('excel_style.xlsx')

Copy the code