<> preface

Used Pandas and openpyxl Ku's classmates know , The two libraries are complementary .Pandas Absolutely Python Medium processing Excel Fastest , Best Library , But use
openpyxl Some of the advantages of are the ease of using styles , Custom spreadsheets such as conditional formatting .

If you want to use it easily Pandas handle Excel data , And want to Excel Add some styles to the spreadsheet , What should I do ?

But guess what , You don't have to worry about choosing .

in fact ,openpyxl Support data from Pandas DataFrame Convert to Workbook , Or vice versa , take openpyxl Convert workbook to Pandas

<>DataFrame Transfer to Workbook

Let's create one first DataFrame:
import pandas as pd data = { " full name ": [" Zhang San ", " Li Si "], " Gender ": [" male ", " female "], " Age ": [15,
25], } df = pd.DataFrame(data) df
give the result as follows :

If you want to set the header to red font , And centered , How should I set it ?
from openpyxl import Workbook from openpyxl.utils.dataframe import
dataframe_to_rowsfrom openpyxl.styles import Font from openpyxl.styles import
Alignment wb= Workbook() ws = wb.active for row in dataframe_to_rows(df, index=
False, header=True): ws.append(row) font = Font(name=" Microsoft YaHei ",size=10, bold=True,
italic=False,color="FF0000") alignment = Alignment(horizontal="center",vertical=
"center") for i in range(1,df.shape[1]+1): cell = ws.cell(row=1, column=i) print
(cell.value) cell.font = font cell.alignment = alignment wb.save("pandas.xlsx")
give the result as follows :

<> Workbook to DataFrame

If there is such a data , We want to convert it to DataFrame, What should I do ?

In fact, this is a little superfluous , We use it directly pandas After reading , Finished processing data , Isn't it OK to carry out style design ? Why do you have to use it at the beginning openpyxl Read the workbook ?

ha-ha , But since openpyxl This method is provided in , Let's take a look .
import pandas as pd from openpyxl import load_workbook workbook = load_workbook
(filename="df_to_openpyxl.xlsx") sheet = workbook.active values = sheet.values
df= pd.DataFrame(values) df
give the result as follows :

©2019-2020 Toolsou All rights reserved,
java Comparing attribute values between two objects utilize Python handle Excel data ——xlrd,xlwt library Bidirectional linked list Why? Python Not a future oriented programming language ?Python【 Assignment statement 】 Special lecture , You can't just a=b ah ! Suggest mastering ! utilize Python handle Excel data ——pandas library see SQL-SERVER Data volume and occupied space of database and each table PID The algorithm finally figured out the principle , It was so simple web Two front-end practical games ( Attached source code ) Beginners learn Python Be sure to know what his basic algorithms are ? What is the function ?