Convert Excel to CSV - Properly Convert Date Field

问题:

This question already has an answer here:

• Python xlrd read as string 3 answers

So, I now have my Excel2CSV function working, but encountered another problem wherein the Date Fields in my Excel File ('Date Opened', 'Date Closed') which are formatted as Date in Excel are being written as an integer value when converted to the CSV (ex. 5/1/1995 converts to 34820).

I'd like it to just write those dates out as plain text (ie. 5/1/1995 -or- May 1, 1995, or something along those lines. Something human readable.)

def Excel2CSV(ExcelFile, SheetName, CSVFile):
     import xlrd
     import csv
     print "Acquiring " + ExcelFile + "..."
     workbook = xlrd.open_workbook(ExcelFile)
     print "Locating " + SheetName + " Worksheet..."
     worksheet = workbook.sheet_by_name(SheetName)
     print "Creating " + CSVFile + "..."
     csvfile = open(CSVFile, 'wb')
     print "Preparing to write CSV file..."
     wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
     print "Writing CSV..."
     for rownum in xrange(worksheet.nrows):
         wr.writerow(
             list(x.encode('utf-8') if type(x) == type(u'') else x
                  for x in worksheet.row_values(rownum)))
     print "Closing CSV File..."
     csvfile.close()
     print "CSV successfully written."
     return CSVFile

I'm not sure how to capture the date fields by name or value and then convert the values to plain text properly. Any help is appreciated.

回答1:

To convert from the date number to a Python datetime use the following formula:

dt = datetime.datetime(1899, 12, 30) + datetime.timedelta(days=num)

Once you've done that you can convert it to the default format with str(dt) or specify your own format with dt.strftime(format). See the datetime documentation