CODEWITHSUNDEEP
pythonpython-3.xpandasopenpyxl
Yatin Pandit
Yatin Pandit

Reputation: 9

How to change the date format while writing it to excel file using openpyxl

I want to convert the date format from dd-mm-yy to mm-yy while writing it into excel file. I tried all the methods but to no success. I am trying to copy data from one excel file and paste it into another. But the date messes up everything.

This is my original Document. From where the code will copy the data: This is my original Document. From where the code will copy the data

This is how it gets displayed in destination excel file:
This is how it gets displayed in destination excel file.

I have used Openpyxl, Pandas for the same.

Upvotes: 0

Views: 10848

Answers (3)

Ricardo Martinez
Ricardo Martinez

Reputation: 21

If you have a variable with all the content you want to write, just use the datetime_format and NOT the date_format.

For instance, I am deleting a sheet called ValorLiq and then rewriting it. I have the content I want to write in myFILE. The commands are:

fn = 'C:/Users/whatever/yourspreadsheet.xlsx'
writer=pd.ExcelWriter(fn,mode='a',engine='openpyxl',datetime_format='DD/MM/YYYY')
idx = writer.book.sheetnames.index('ValorLiq')
writer.book.remove(writer.book.worksheets[idx])
myFILE.to_excel(writer, merge_cells = False, sheet_name='ValorLiq')
writer.save()

Upvotes: 2

Patrick Artner
Patrick Artner

Reputation: 51683

The problem is that you are still writing datetimes - you need to convert them to a string before writing (you loose all but what you have inside the string) or set the number format of your cell:

from openpyxl import Workbook                    # openpyxl  2.6.2
from openpyxl.utils import get_column_letter

import datetime

basedate = datetime.datetime.today() 

# create some demo dates, roughly -5 to +5 months
some_dates = [basedate + datetime.timedelta(days = i*30) for i in range(-5,6)]
print(some_dates)

# create a workbook
wb = Workbook() 
ws1 = wb.active
ws1.title = "dates"
ws1["A1"] = "Daaaaaaaaates"
# fill dates manually to enable cell formatting
for i, date in enumerate(some_dates,2):
    ws1[f"A{i}"] = date                # no format

    ws1[f"B{i}"] = date                # formatted to MM.YY
    cell = ws1.cell(column=2, row=i)   # get cell and change format
    cell.number_format = "MM.YY"       # use 'MM-YY' if you want a dash between month/year

    # uncomment if you want to store the stringified version directly
    # ws1[f"C{i}"] = date.strftime("%m.%y")

wb.save(filename = 'workbook.xlsx')

this gets you a printed list of 

[datetime.datetime(2019, 1, 9, 17, 1, 57, 329142), 
 datetime.datetime(2019, 2, 8, 17, 1, 57, 329142), 
 datetime.datetime(2019, 3, 10, 17, 1, 57, 329142), 
 datetime.datetime(2019, 4, 9, 17, 1, 57, 329142), 
 datetime.datetime(2019, 5, 9, 17, 1, 57, 329142), 
 datetime.datetime(2019, 6, 8, 17, 1, 57, 329142), 
 datetime.datetime(2019, 7, 8, 17, 1, 57, 329142), 
 datetime.datetime(2019, 8, 7, 17, 1, 57, 329142), 
 datetime.datetime(2019, 9, 6, 17, 1, 57, 329142), 
 datetime.datetime(2019, 10, 6, 17, 1, 57, 329142), 
 datetime.datetime(2019, 11, 5, 17, 1, 57, 329142)]

and an exported file (OpenOffice used to open) that looks like:

Exported xlss

HTH

Upvotes: 1

Abbas
Abbas

Reputation: 4069

To achieve what you want, a named style needs to be created and assigned for each cell. Here is a working example:

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import NamedStyle
# Create a dataframe having a column of dates
df = pd.DataFrame({'DATE':pd.date_range(start='2018-1-1', end='2019-1-1')})
df = df.assign(DATE2=df["DATE"])
df = df.assign(DATE3=df["DATE"])
# Create a excel work book
ewb = pd.ExcelWriter('test.xlsx', engine="openpyxl")
# Create a named style
nsmmyy=NamedStyle(name="cd1", number_format="MM-YY")
nsmmmyy=NamedStyle(name="cd2", number_format="MMM-YY")
nsbyy=NamedStyle(name="cd3", number_format="MMMM-YY")
# Write dataframe to workbook
df.to_excel(excel_writer=ewb, sheet_name="SHT1")
# Get the work book by name
ws = ewb.book["SHT1"]
# For each cell in the column set the named style
# Add +2 to the number of rows since excel index starts from 1
for i in range(1, len(df) + 2):
    # Pass column argument as 2, since column 1 will have dataframe index
    ws.cell(row=i, column=2).style = nsmmyy
    ws.cell(row=i, column=3).style = nsmmmyy
    ws.cell(row=i, column=4).style = nsbyy
# Save work book
ewb.save()

This is how the resultant excel sheet would look like:

enter image description here

Upvotes: 1

Related Questions