Python xlrd 通过日期转换将 Excel xlsx 解析为 csv

Question

我正在尝试将 Excel.xlsx 文件解析为 csv 文件。这是 Excel 文件:

Date         Person 1     Person 2  
02/03/2015   Bob          James A       
03/03/2015   Billy        Nic       
04/03/2015   Sally        Mark      
05/03/2015   Alan         James A       
06/03/2015  James W       James A

我的 Python 脚本:

import xlrd
import csv

book = xlrd.open_workbook('rota.xlsx')

sheet = book.sheet_by_name('Sheet1')

csvfile = open('output.csv', 'wb')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)

for rownum in range(sheet.nrows):
  wr.writerow(sheet.row_values(rownum))

csvfile.close()

但是它输出的日期是这样的:

Date,Person1,Person2
41884,Bob,James B
41885,Billy,Nic
41886,Sally,Mark
41887,Alan,James A
41888,James W,James A

我知道 xldate_as_tuple 函数或类似的东西可以将输出转换为有意义的值，但我不知道如何使用它。如有任何帮助，我将不胜感激。

Answer 1

这是一种可能的解决方案:

import xlrd
import csv
from datetime import datetime


book = xlrd.open_workbook('rota.xlsx')
sheet = book.sheet_by_name('Sheet1')
csvfile = open('output5.csv', 'wb')

wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)
wr.writerow(sheet.row_values(0))

for rownum in range(1,sheet.nrows):
  year, month, day, hour, minute, sec = xlrd.xldate_as_tuple(int(sheet.row_values(rownum)[0]), book.datemode)
  py_date = datetime(year, month, day, hour, minute)
  wr.writerow([py_date] + sheet.row_values(rownum)[1:])

csvfile.close()

输出:

"Date      "," Person 1","Person 2"
"2015-02-03 00:00:00"," Bob     ","James A "
"2015-03-03 00:00:00"," Billy   ","Nic     "
"2015-04-03 00:00:00"," Sally   ","Mark    "
"2015-05-03 00:00:00"," Alan    ","James A "
"2015-06-03 00:00:00","James W  ","James A "

版本 2:

代码:

#! /usr/bin/python

import xlrd
import csv
from datetime import datetime

book = xlrd.open_workbook('rota.xlsx')
sheet = book.sheet_by_name('Sheet1')
csvfile = open('output5.csv', 'wb')
wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)

for rownum in range(sheet.nrows):
    date = sheet.row_values(rownum)[0]
    if isinstance( date, float) or isinstance( date, int ):
        year, month, day, hour, minute, sec = xlrd.xldate_as_tuple(date, book.datemode)
        py_date = "%02d/%02d/%04d" % (month, day,year)
        wr.writerow([py_date] + sheet.row_values(rownum)[1:])
    else:
        wr.writerow(sheet.row_values(rownum))
csvfile.close()

输出:

"Date      "," Person 1","Person 2"
"02/03/2015"," Bob     ","James A "
"03/03/2015"," Billy   ","Nic     "
"04/03/2015"," Sally   ","Mark    "
"05/03/2015"," Alan    ","James A "
"06/03/2015","James W  ","James A "