“This is the first day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

Read the contents of the Excel table

Import Pandas as PD Pandas is an open source, BSD-licensed library that provides high-performance, easy-to-use data structures and data analysis tools. You can import data from a variety of file formats such as CSV, JSON, SQL, and Microsoft Excel.

Gets the contents of the specified workbook

Use read_excel to simply read some workbooks in the sheet by entering the path of excel and the name of the sheet. If you do not specify the workbook, only the contents of the first sheet are displayed by default

df = pd.read_excel("ww.xlsx", sheet_name=["Sheet1", "Sheet2"])
Copy the code

This is the output

View all workbooks

We can also view all workbooks from sheet_name by setting sheet_name to None

df = pd.read_excel("ww.xlsx", sheet_name=None)
Copy the code

When we want to see the names of all workbooks, we can do so by using df.keys()

Save the processed results to Excel

Xlsxwriter supports many excle functions. Xlsxwriter supports many excle functions. Perfect compatibility with Excel; Writing large files is fast and takes up very little memory. Reading or modifying existing Excel files is not supported

Create the table header we need

Title = [' class ', 'name ',' age ', 'gender ']Copy the code

Write the table header from cell A1

FileName = 'ok.xls' workbook = xw.workbook (fileName) # create workbook worksheet1 = workbook.add_worksheet(" class ") # create child table Worksheet1.activate () # Activate table worksheet1.write_row('A1', title)Copy the code

We can also change the header or cell style, such as color, font size, etc. The following code changes the background color to yellow

format = workbook.add_format()
format.set_pattern(1)
format.set_bg_color('yellow')
worksheet1.write_row('A1:D1', title, format)
Copy the code

Save to the table, we can use an excel_count variable to count, easy to remember the location of the store

Worksheet1. Write_row (' A '+ STR (excel_count), [' software class', 'zhang', '20', 'woman'])Copy the code

Don’t forget to close the table

workbook.close() 
Copy the code

About connecting to database storage

I used import mysql.connector to do the same thing with Pymysql. I had only connected to a database in a Java project before, so I needed to configure something and found it easy to connect to a database in Python

Conn = mysql.connector.connect(user='root', password='root', host='127.0.0.1', port=3306, Database ='test') # create cursor = conn.cursor() SQL =' create table........ 'cursor.execute(SQL) # close connection conn.mit () cursor.close()Copy the code

You create, you add, you delete, you modify, you check, you do the same thing and then you save the data in Excel, but whatever it is, you save it with %s, okay

INSERT INTO tablaName (' class ', 'name ',' age ', 'sex') VALUES (%s,%s,%s,%s). 'Zhang SAN ', '20',' female '])Copy the code