to_excel(): DataFrame to Excel

Create Excel file using Pandas DataFrame .
import pandas as pd 
df = pd.DataFrame(data=my_dict) # DataFrame from dicitionary
df.to_excel('D:\my_file.xlsx') # create Excel file 
to_excel(): Data from Pandas DataFrame to Excel file #B02


index: bool, default=True

By default pandas will add one index column. We can remove index by using option index=False

Storing Path

We can keep in D drive ( root )
Inside data directory

na_rep Blank data

How to handle if data is blank, we can use na_rep='*'


List of columns to use while creating Excel file. Use only id and name columns here
df2.to_excel('C:\\data\\student2.xlsx', columns=['id','name'])


Aliases for the column names we can use. WE created a list with our headers and then used the same.


Default value is 0, this is the top row.


Default value is 0, this is the left most column.
startrow and startcol in to_excel()


We can use 'openpyxl' or 'xlsxwriter'


A tuple , one-based bottommost row and rightmost column that is to be frozen.


Formatting the floating point numbers. float_format="%.2f" will update the value 0.1234 to 0.12

Storing different worksheets

Excel has worksheets to store data in different sheets.
When we create a new excel file the data stored in Sheet1. We can create different Sheets and store data in different worksheets.

By using sheet_name we can store in worksheet with specific name sheet_name='my_Sheet_1'
This will store our data in my_sheet_1 worksheet of file name my_file.xlsx at root of D drive.

multiple worksheets

We will use one object of ExcelWriter to create multiple worksheets in a excel file.
import pandas as pd 
df = pd.DataFrame(data=my_dict)
df2 = df.copy() # copy of df

with pd.ExcelWriter('D:my_file.xlsx') as my_excel_obj: #Object created
Above code will create excel file with two worksheets. Here the new file name is my_file.xlsx with two worksheets.

Appending worksheets

We will add two more worksheets to the existing files by opening the file in append mode.

Note that we are using the same my_file.xlsx file created in above code.

We will be using mode='a' and engine='openpyxl' while creating the ExcelWriter object.
import pandas as pd 
df = pd.DataFrame(data=my_dict)
df2 = df.copy() # copy of df

with pd.ExcelWriter('D:my_file.xlsx',mode='a',engine='openpyxl') as my_excel_obj:
This code will add two more worksheets my_Sheet_3 and my_Sheet_4 to existing workbook my_file.xlsx .

While executing the above code, you may get error saying Append mode is not supported with xlsxwriter. To solve this issue use engine='openpyxl' while creating the ExcelWriter object.

Data from MySQL table

We can read data from MySQL table and then store them in excel file.
import mysql.connector
import pandas as pd 
my_connect = mysql.connector.connect(
####### end of connection ####
sql="SELECT * FROM student "
df = pd.read_sql(sql,my_connect )

In above code we have first connected to MySQL database and then collected the records of student table by using read_sql() to a DataFrame. Finally we saved them in an excel file using to_excel().

Using SQLAlchemy MySQL connection

Read more on MySQL with SQLAlchemy connection. Below code will create student.xlsx file in the same directory, you can add path ( as above ) if you want the file to be created at different location.
import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
sql="SELECT * FROM student "
df = pd.read_sql(sql,my_conn)
df.to_excel('D:\\my_data\\student.xlsx') # Add the path
We can collect data of different classes from our student table and store them in one excel file by keeping in different worksheets. So all students of class Four will be in one worksheet named as Four and similarly another worksheet for class Three students. You can extend this to other classes also.
import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
sql="SELECT * FROM student WHERE class='Three'"
sql2="SELECT * FROM student WHERE class='Four'"
df=pd.read_sql(sql,my_conn)   # class Three students 
df2=pd.read_sql(sql2,my_conn) # class Four students
with pd.ExcelWriter('D:\\my_data\\student.xlsx',engine='openpyxl') as my_obj:
More about xlsxwriter

Storing part of the data

We can filter the DataFrame and then save the rows in xlsx file. For this we will use our test.csv file as we have more rows.
Now let us store only two columns, class and name
import pandas as pd 
df = pd.DataFrame(data=df)
We can use various other filters to manage the data and store in CSV file. You can rad more on filters sections.

Inserting DataFrame to google sheets

Python Google sheets
We can add our DataFrame to google sheets by using pygsheets library and google drive API.
Pandas DataFrame to Google sheet by set_dataframe


Data input and output from Pandas DataFrame Sample student DataFrame DataFrame to SQlite table at Colab platform using to_sql()
SQLite table to DataFrame at Colab platform using read_sql()
Pandas read_csv to_csv read_excel to_string()

Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here

    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 All rights reserved worldwide Privacy Policy Disclaimer