Skip to content
Advertisement

Python WorkSheet creation with range list

I want to create a spreadsheet in below format. But unable to do it, Please help.

Every worksheet should have date which start from 25th and end at 24 without Saturday and Sunday.

def daterange(date1, date2):
    for n in range(int ((date2 - date1).days)+1):
        yield date1 + timedelta(n)

start_dt = date(2020,9,25)
end_dt = date(2020,10,24)

weekdays = [5,6]
for dt in daterange(start_dt, end_dt):
    if dt.weekday() not in weekdays:     # to print only the weekdates
        print(dt.strftime("%Y-%m-%d"))

Also want to add same date in each sheet cell with a table (unable to create a table yet).

import xlwt
import datetime

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet1')

date_format = xlwt.XFStyle()
date_format.num_format_str = 'dd/mm/yyyy'

worksheet.write(0, 0, datetime.datetime.now(), date_format)

workbook.save('date_format.xls')

enter image description here

Advertisement

Answer

I think this is what you are looking for:

import xlwt
import datetime

start = datetime.date(2020, 9, 25)
end = datetime.date(2020, 10, 24)
weekend=[5, 6]

def daterange(date1, date2):
    for n in range(int((date2 - date1).days) + 1):
        yield date1 + datetime.timedelta(n)

dates = [dt for dt in daterange(start, end) if dt.weekday() not in weekend]
workbook = xlwt.Workbook()
for date in dates:
    worksheet = workbook.add_sheet(f"{date.strftime('%d-%m-%Y')}") # the tab
    worksheet.write(0, 0, date.strftime('%d-%b-%Y')) # the cell
workbook.save('date_format.xls')

You had most of it done, all that was needed was to glue the 2 parts together.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement