Use xlswriter to name a file as current date and time

Question

I am trying to use xlsxwriter to create an excel file and name a file as the current date and time. For context, I want to add this into a web scraper, which is set to run at midday everyday and export it to Excel. I want the file names to correspond with the scrape time.

I have tried using the datetime function with no success:

import xlsxwriter
import datetime

# Create a workbook and add a worksheet.

todays_date = "'" + datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + '.xlsx' + "'"
workbook = xlsxwriter.Workbook(todays_date)
worksheet = workbook.add_worksheet()

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()

Does anyone know why this is not working or another alternative?

@Sandeep Hukku - edited code below:

import xlsxwriter
import datetime

# Create a workbook and add a worksheet.

# todays_date = "'" + datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + '.xlsx' + "'"
todays_date = str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") )+ '.xlsx'
workbook = xlsxwriter.Workbook(todays_date)
worksheet = workbook.add_worksheet()

# Some data we want to write to the worksheet.
expenses = (
    ['Rent', 1000],
    ['Gas',   100],
    ['Food',  300],
    ['Gym',    50],
)

# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0

# Iterate over the data and write it out row by row.
for item, cost in (expenses):
    worksheet.write(row, col,     item)
    worksheet.write(row, col + 1, cost)
    row += 1

# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()

@Snehal Parmar - second update:

import urllib import urllib.request from bs4 import BeautifulSoup import datetime import xlsxwriter

# Web scraping
def make_soup(url):
    the_page = urllib.request.urlopen(url)
    soup_data = BeautifulSoup(the_page, "html.parser")
    return soup_data


soup = make_soup('http://www.url.co.uk')


def getNames():
    for record in soup.findAll('tr'):
        for data in record.findAll('td'):
            for td_in_data in data.findAll('td', {"class": "propname"}):
                print(td_in_data.text)


def getRooms():
    for record in soup.findAll('tr'):
        for data in record.findAll('td'):
            for td_in_data in data.findAll('span', {"class": "beds"}):
                print(td_in_data.text)


def getRents():
    for record in soup.findAll('tr'):
        for data in record.findAll('td'):
            for td_in_data in data.findAll('td', {"class": "rentprice"}):
                print(td_in_data.text)


''' To do: get the scraped data to an Excel doc.'''

# Create a workbook and add a worksheet.


todays_date = str(datetime.datetime.now().strftime("%Y-%m-%d %H:%M") )+ '.xlsx'
todays_date = todays_date.replace(" ", "_").replace(":", "_")

workbook = xlsxwriter.Workbook(todays_date)
worksheet = workbook.add_worksheet()

# Data to Excel.
Excel_dump = (
    ['Name', getNames()],
    ['Rent',   getRents()],
    ['Rooms',  getRooms()]
)

# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0


# Iterate over the data and write it out row by row.
for item, cost in Excel_dump:
    worksheet.write()
    worksheet.write(col, row,     item)
    worksheet.write(col, row + 1)
    row += 1

Answer 1

I'm not sure but if you close the file after naming it and then again open in append mode and then add data into the file that might do the job. Sometimes create file is lazy operation, so what happens here is until nothing is written into the file its not create file and also operating system play important role too. Please and let me know, as its web scraper code I can't simulate here on my machine.

Got the issue: the problem is with the time part in the name of file, add the following :

todays_date = todays_date.replace(" ", "_").replace(":", "_")

or

todays_date = str(datetime.datetime.now().strftime("%Y-%m-%d_%H_%M") )+ '.xlsx'

I hope this will fix the issue.

Answer 2

You just need to write todays_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + '.xlsx' instead of todays_date = "'" + datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + '.xlsx' + "'"