Specify data format when saving Excel via Python
type
status
date
slug
summary
tags
category
icon
Hi~ In this article, we'll tackle a common pain point for data analysts:
As data analysts, we often receive Excel files from colleagues that contain color markings and special formatting. However, when we process the data with Python and export it back to Excel, it becomes a "plain text" file without any formatting. This not only affects the readability of the data but may also prevent colleagues from quickly identifying important information.
So, how can we preserve these beautiful formats when exporting Excel files using Python? This article will answer this question for you!
We'll address these specific issues together:
- How to use the super practical xlsxwriter library to save Excel files?
- How to save multiple dataframes (that's pandas DataFrames) to different worksheets?
- How to add beautiful formatting and styles to Excel?
- How to ensure that different data types such as datetime, integers, strings, and decimals display correctly when saved?
Question Description:
please imagine that you have the following 2 dataframes and you want to save them into one Excel file with different sheets and stylize the Excel formatting
Solution: Save Excel through xlsxwriter
1. import xlsxwriter package and create Excel file
use
Workbook to create a new Excel file.2. create two sheets
use .
add_worksheet to create a sheet because we have two dataframes that need to be saved, we have to create two sheets.3. set header format and save the header
first, we set header format and use
.write_row to save our header..write_row has four arguments, they are: worksheet.write_row(row, col, data, cell_format).4. create some format
format.set_num_format():This method is used to define the numerical format of a number in Excel. It controls whether a number is displayed as an integer, a floating point number, a date, a currency value, or some other user-defined format.The numerical format of a cell can be specified by using a format string or an index to one of Excel’s built-in formats, for more information you can check xlsxwriter-format
for example:
14 represents m/dd/yyyy, so format.set_num_format(14) = format.set_num_format('m/dd/yyyy')5. save the data into Excel with cell_format
the zero index row already saved our header, so we start from one index row(set the first argument to 1)
6. set column width
for a better display of the data, we can also use
.set_column to set the column width.7. finish
And that's it! Our Excel file is now created! Isn't it simple? Even if you're new to Python, following these steps will allow you to easily create Excel files with beautiful formatting and correct data types.
上一篇
Using Python to Retrieve Data Through Tableau REST-API
下一篇
Accessing Power BI Data with Python Using Power BI REST-API
- Author:Luca Liu
- URL:http://www.blog.luca-liu.com/article/how-to-specify-the-data-format-when-saving-excel-via-python
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
