How to Change Date Format in Excel using Openpyxl
When working with date data in Excel using the openpyxl
library, you may need to change the date format to a specific format before writing it to an Excel file. In this tutorial, we will guide you through the process of changing the date format and writing it to an Excel file using openpyxl
.
Step 1: Import the required libraries
To begin, we need to import the necessary libraries: pandas
, openpyxl
, and openpyxl.styles
.
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import NamedStyle
Step 2: Create a DataFrame with date column
Next, we'll create a DataFrame with a column of dates. In this example, we'll generate a range of dates from '2018-1-1' to '2019-1-1'.
df = pd.DataFrame({'DATE': pd.date_range(start='2018-1-1', end='2019-1-1')})
Step 3: Assign the date column to multiple columns in the DataFrame
To demonstrate different date formats, we'll assign the date column to multiple new columns in the DataFrame.
df = df.assign(DATE2=df["DATE"])
df = df.assign(DATE3=df["DATE"])
Step 4: Create an Excel workbook and named styles
We'll create an Excel workbook using pd.ExcelWriter
and specify the engine as "openpyxl". Additionally, we'll define three named styles with different date formats.
ewb = pd.ExcelWriter('test.xlsx', engine="openpyxl")
nsmmyy = NamedStyle(name="cd1", number_format="MM-YY")
nsmmmyy = NamedStyle(name="cd2", number_format="MMM-YY")
nsbyy = NamedStyle(name="cd3", number_format="MMMM-YY")
Step 5: Write the DataFrame to the workbook
We'll write the DataFrame to the Excel workbook using the to_excel
method of the Excel writer.
df.to_excel(excel_writer=ewb, sheet_name="SHT1")
Step 6: Apply named styles to the date columns
To change the date format in Excel, we'll iterate over each cell in the date columns and apply the appropriate named style.
ws = ewb.book["SHT1"]
for i in range(1, len(df) + 2):
ws.cell(row=i, column=2).style = nsmmyy
ws.cell(row=i, column=3).style = nsmmmyy
ws.cell(row=i, column=4).style = nsbyy
Step 7: Save the workbook
Finally, we'll save the workbook using the save
method of the Excel writer.
ewb.save()
Top comments (0)