def excel_to_csv(path):
wb = xlrd.open_workbook(path)
sh = wb.sheet_by_index(0)
csv_path = convert_path_to_csv(path)
csv_file = open(csv_path, 'wb')
wr = csv.writer(csv_file)
date_tuple = xlrd.xldate_as_tuple(sh.row_values(0)[-1], wb.datemode)
date = datetime(*date_tuple).strftime('%m/%d/%Y')
date_fields = [date for i in range(sh.nrows-1)]
date_fields = ['Date'] + date_fields
for rownum in xrange(sh.nrows):
if rownum == 0:
wr.writerow([date_fields[rownum]] + sh.row_values(rownum)[:-1] + ['Value'])
else:
wr.writerow([date_fields[rownum]] + sh.row_values(rownum))
csv_file.close()
python类xldate_as_tuple()的实例源码
def format_excelval(book, type, value, wanttupledate):
""" Clean up the incoming excel data """
## Data Type Codes:
## EMPTY 0
## TEXT 1 a Unicode string
## NUMBER 2 float
## DATE 3 float
## BOOLEAN 4 int; 1 means TRUE, 0 means FALSE
## ERROR 5
returnrow = []
if type == 2: # TEXT
if value == int(value): value = int(value)
elif type == 3: # NUMBER
datetuple = xlrd.xldate_as_tuple(value, book.datemode)
value = datetuple if wanttupledate else tupledate_to_isodate(datetuple)
elif type == 5: # ERROR
value = xlrd.error_text_from_code[value]
return value
#
# Save to CSV
#
def importData(fileName):
"""
Main entry point - opens import data Excel sheet (assumed to be first
sheet) and processes each record, one at a time.
"""
global importSheet, importRow, headers
book = xlrd.open_workbook(fileName)
importSheet = book.sheet_by_index(0)
print "importing from %s" % importSheet.name
# get cell value types from first-non header data row (2nd), since headers are all of type text
for colnum in range(0, importSheet.ncols):
headers.append((importSheet.cell(0, colnum).value, importSheet.cell(1, colnum).ctype))
configErrorReporting(headers)
for importRow in range(1, importSheet.nrows):
record = {}
for colnum in range(0, importSheet.ncols):
if headers[colnum][1] == xlrd.XL_CELL_DATE:
dateTuple = xlrd.xldate_as_tuple(importSheet.cell(rowx=importRow, colx=colnum).value, book.datemode)
date = datetime.date(dateTuple[0], dateTuple[1], dateTuple[2])
# required format for xsd:Date type, for web service call
record[headers[colnum][0]] = date.isoformat()
else:
value = importSheet.cell_value(rowx=importRow, colx=colnum)
if isinstance(value, basestring):
record[headers[colnum][0]] = value.strip()
else:
record[headers[colnum][0]] = value
#print "%s: %s" % (type(record[headers[colnum]]), record[headers[colnum]])
record = handleAccountContact(record)
book.unload_sheet(importSheet.name)
def convert_cell(self, cell, sheet):
value = cell.value
try:
if cell.ctype == 3:
if value == 0:
return None
year, month, day, hour, minute, second = \
xlrd.xldate_as_tuple(value, sheet.book.datemode)
if (year, month, day) == (0, 0, 0):
value = time(hour, minute, second)
return value.isoformat()
else:
value = datetime(year, month, day, hour, minute, second)
return value.isoformat()
except Exception:
pass
return stringify(value)
def get_row_data(bk, sh, rowx, colrange):
result = []
dmode = bk.datemode
ctys = sh.row_types(rowx)
cvals = sh.row_values(rowx)
for colx in colrange:
cty = ctys[colx]
cval = cvals[colx]
if bk.formatting_info:
cxfx = str(sh.cell_xf_index(rowx, colx))
else:
cxfx = ''
if cty == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cval, dmode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
cty = xlrd.XL_CELL_ERROR
elif cty == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval)
else:
showval = cval
result.append((colx, cty, showval, cxfx))
return result
def get_row_data(bk, sh, rowx, colrange):
result = []
dmode = bk.datemode
ctys = sh.row_types(rowx)
cvals = sh.row_values(rowx)
for colx in colrange:
cty = ctys[colx]
cval = cvals[colx]
if bk.formatting_info:
cxfx = str(sh.cell_xf_index(rowx, colx))
else:
cxfx = ''
if cty == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cval, dmode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
cty = xlrd.XL_CELL_ERROR
elif cty == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval)
else:
showval = cval
result.append((colx, cty, showval, cxfx))
return result
def get_row_data(bk, sh, rowx, colrange):
result = []
dmode = bk.datemode
ctys = sh.row_types(rowx)
cvals = sh.row_values(rowx)
for colx in colrange:
cty = ctys[colx]
cval = cvals[colx]
if bk.formatting_info:
cxfx = str(sh.cell_xf_index(rowx, colx))
else:
cxfx = ''
if cty == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cval, dmode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
cty = xlrd.XL_CELL_ERROR
elif cty == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval)
else:
showval = cval
result.append((colx, cty, showval, cxfx))
return result
def cell_display(cell, datemode=0, encoding='ascii'):
cty = cell.ctype
if cty == xlrd.XL_CELL_EMPTY:
return 'undefined'
if cty == xlrd.XL_CELL_BLANK:
return 'blank'
if cty == xlrd.XL_CELL_NUMBER:
return 'number (%.4f)' % cell.value
if cty == xlrd.XL_CELL_DATE:
try:
return "date (%04d-%02d-%02d %02d:%02d:%02d)" \
% xlrd.xldate_as_tuple(cell.value, datemode)
except xlrd.xldate.XLDateError:
return "date? (%.6f)" % cell.value
if cty == xlrd.XL_CELL_TEXT:
return "text (%s)" % cell.value.encode(encoding, 'replace')
if cty == xlrd.XL_CELL_ERROR:
if cell.value in xlrd.error_text_from_code:
return "error (%s)" % xlrd.error_text_from_code[cell.value]
return "unknown error code (%r)" % cell.value
if cty == xlrd.XL_CELL_BOOLEAN:
return "logical (%s)" % ['FALSE', 'TRUE'][cell.value]
raise Exception("Unknown Cell.ctype: %r" % cty)
def fucn_time(row_index):
col_index = 1
value = sheet.cell(row_index, col_index).value
while not value:
row_index -= 1
value = sheet.cell(row_index, col_index).value
if float(value) < 1:
value = xldate_as_tuple(value, 1)
time = str(value[3]) + "." + str(value[4])
else:
time = value
return time
def parse_date_value(self, value):
return xlrd.xldate_as_tuple(value, 0)
def _load_index_constituent(self, file_path, index_code):
self._create_constituent_table(index_code)
excel = xlrd.open_workbook(file_path)
sheet = excel.sheets()[0]
current_date = ''
current_constituent = []
# ?????????,????,??????????
row_indent = 1
if sheet.cell(sheet.nrows-row_indent, 0).value == u'?????????Choice??':
row_indent = 6
for row in range(1, sheet.nrows-row_indent):
# ??,???????????????
row_date = sheet.cell(row, 1).value
if isinstance(row_date, float):
row_date = datetime(*xlrd.xldate_as_tuple(row_date, excel.datemode)).strftime(STAND_DATE_FORMAT)
#?1????????,??????????????????
if row == 1:
current_date = row_date
# ??????,???????????????
# ???,????????????,???????????,????????,???????????????????
# if row_date != current_date or row == sheet.nrows-7:
if self._in_index_change_range(row_date, current_date) or row == sheet.nrows-7:
# sql = u'INSERT OR REPLACE INTO {} ({}, {}) VALUES ({}, "{}");'.format(
# IndexCollector._constituent_tablename(index_code), IndexConstituent.DATE_KEY, IndexConstituent.CONSTITUENTS_KEY, current_date, ','.join(current_constituent))
# self.db.execute(sql)
#???????,????date????????????,??????????
self.db.execute(u'INSERT OR REPLACE INTO '+ IndexCollector._constituent_tablename(index_code) +' VALUES (?, ?);', (current_date, ','.join(current_constituent)))
self.db.commit()
current_date = row_date
stock_code = sheet.cell(row, 2).value.split('.')[0]
# ????,?????????b?,?????????????b??????,?????????
if stock_code.startswith('2'):
continue
operation = sheet.cell(row, 4).value
if operation == u'??':
current_constituent.append(stock_code)
else:
current_constituent.remove(stock_code)
# ?????????,????????????,????????????
# ????????????????,??????????????
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
xlrdnameAPIdemo.py 文件源码
项目：InternationalizationScript-iOS
作者: alexfeng
项目源码
文件源码
阅读 17
收藏 0
点赞 0
评论 0
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
xlrdnameAPIdemo.py 文件源码
项目：InternationalizationScript-iOS
作者: alexfeng
项目源码
文件源码
阅读 20
收藏 0
点赞 0
评论 0
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError:
e1, e2 = sys.exc_info()[:2]
showval = "%s:%s" % (e1.__name__, e2)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def showable_cell_value(celltype, cellvalue, datemode):
if celltype == xlrd.XL_CELL_DATE:
try:
showval = xlrd.xldate_as_tuple(cellvalue, datemode)
except xlrd.XLDateError as e:
showval = "%s:%s" % (type(e).__name__, e)
elif celltype == xlrd.XL_CELL_ERROR:
showval = xlrd.error_text_from_code.get(
cellvalue, '<Unknown error code 0x%02x>' % cellvalue)
else:
showval = cellvalue
return showval
def make_excel_date_caster(file_name):
"""Make a date caster function that can convert dates from a particular workbook. This is required
because dates in Excel workbooks are stupid. """
from xlrd import open_workbook
wb = open_workbook(file_name)
datemode = wb.datemode
def excel_date(v):
from xlrd import xldate_as_tuple
import datetime
try:
year, month, day, hour, minute, second = xldate_as_tuple(float(v), datemode)
return datetime.date(year, month, day)
except ValueError:
# Could be actually a string, not a float. Because Excel dates are completely broken.
from dateutil import parser
try:
return parser.parse(v).date()
except ValueError:
return None
return excel_date
def cell_value(cell, datemode):
ctype = cell.ctype
value = cell.value
if ctype == xlrd.XL_CELL_ERROR:
raise ValueError(repr(cell), 'cell error')
elif ctype == xlrd.XL_CELL_BOOLEAN:
return str(value).upper()
elif ctype == xlrd.XL_CELL_NUMBER:
if value.is_integer():
value = int(value)
return str(value)
elif ctype == xlrd.XL_CELL_DATE:
value = xlrd.xldate_as_tuple(value, datemode)
if value[3:] == (0, 0, 0):
return datetime.date(*value[:3]).isoformat()
else:
return datetime.datetime(*value).isoformat()
elif ctype in (xlrd.XL_CELL_TEXT, xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK):
return value
raise ValueError(repr(cell), 'unknown cell type')
def excel2lines(file_name,sheet_indexes,header = False,annotation=False,date=False):
lines = []
num_annotators = False
workbook = xlrd.open_workbook(file_name)
#collect sheets
sheets = []
print(sheet_indexes)
for index in sheet_indexes:
print(index)
sheets.append(workbook.sheet_by_index(int(index)))
else:
sheets = workbook.sheets()
#for each sheet
for sheet in sheets:
sheetlines = []
if header:
first_row = 1
else:
first_row = 0
print("gen_functions",sheet.nrows)
last_row = sheet.nrows
#iterate the lines
for rownum in range(first_row,last_row):
values = []
#collect annotation values
if annotation:
for value in sheet.row_values(rownum):
if not type(value) == float:
value = value.strip()
try:
if float(value) in range(2):
values.append(float(value))
except ValueError:
continue
if num_annotators:
if len(values) != num_annotators:
print("number of annotation values on line",
rownum,"is not consistent; check the inputfile. Exiting...")
exit()
else:
num_annotators = len(values)
print(num_annotators, "annotators")
else:
rowvals = sheet.row_values(rownum)
if date:
try:
rowvals[date] = datetime.date(*xlrd.xldate_as_tuple(\
sheet.cell_value(rownum,date), workbook.datemode)[:3])
except:
continue
values = [unicode(x) for x in rowvals]
sheetlines.append(values)
#each sheet is a list of lists
lines.append(sheetlines)
return lines
def convert_excel_to_csv(input_excel_file,
output_csv_file_path=None, return_csv_file=False):
"""
Takes the excel file path as input and converts
into csv file and if we select return_csv_file as
True returns csv file else
returns csv file object
Arguments:
1. input_excel_file: It is a excel file path
which is to be converted into csv file
2. output_csv_file_path: If user gives the output csv path,
then creating csv file at that path else creating a csv file
in the directory from where he have given excel file.
3. return_csv_file: If the user selects return_csv_file as True,
returning the output csv file else returning the object.
Returns:
Returns the csv file path if user selects
return_csv_file as True else returns the object.
"""
try:
if output_csv_file_path is None:
if ".xlsx" in input_excel_file:
ret_csv_file = input_excel_file.replace(".xlsx", ".csv")
else:
ret_csv_file = input_excel_file.replace(".xls", ".csv")
else:
ret_csv_file = output_csv_file_path
wb = xlrd.open_workbook(input_excel_file)
sh = wb.sheet_by_index(0)
csv_file = open(ret_csv_file, 'wb+')
wr = csv.writer(csv_file, quoting=csv.QUOTE_ALL)
for rownum in xrange(sh.nrows):
row_val = sh.row_values(rownum)
for index, value in enumerate(row_val):
if sh.cell(rownum, index).ctype == 3:
year, month, day, hour, minute, sec = xlrd.xldate_as_tuple(
value, wb.datemode)
date_format = "%02d/%02d/%04d" % (month, day, year)
row_val[index] = date_format
wr.writerow(row_val)
if return_csv_file:
csv_file.close()
csv_file = ret_csv_file
else:
csv_file = csv_file
except Exception as exception:
print_exception(exception)
csv_file = None
return csv_file
def load_stock_history_quotation(self, stock_codes):
#????????
for stock_code in stock_codes:
self._create_stock_quotation_table(stock_code)
history_files = []
for root, _, files in os.walk('./stock_history/'):
for f in files:
if f.startswith(u'????'):
history_files.append(root + f)
#??????,????????
history_files.reverse()
for history_file in history_files:
excel = xlrd.open_workbook(history_file)
#???????
sheet = excel.sheets()[0]
# codes_line = sheet.row_values(2)
#??????,??????????,??????,?????
quotations = dict()
for col in range(1, sheet.ncols, 2):
code = sheet.cell(1, col).value.split('.')[0]
#????????choice?code???
ss = []
if code in stock_codes:
#???????????????????1?,2????,3???,???????????,?????,??????,???
#?????,????????,???????,?????date???,????????
for row in range(sheet.nrows-3, 3, -1):
#??????,?????
date_data = sheet.cell(row, 0).value
if isinstance(date_data, basestring) and len(date_data) == 0:
break
pb = sheet.cell(row, col).value
pe = sheet.cell(row, col+1).value
#pb,pe???,??????,?????????,?????????,?????,??????
#?????????,???????????????,?????,???????output??
if (isinstance(pb, basestring) and len(pb) == 0) or (isinstance(pe, basestring) and len(pe) == 0):
break
else:
date_tuple = xlrd.xldate_as_tuple(date_data, 0)
date = '{}-{:0>2}-{:0>2}'.format(date_tuple[0], date_tuple[1], date_tuple[2])
ss.append((date, safe_to_float(pe), safe_to_float(pb)))
else:
print 'code ' + code + " not in eastmoney"
if len(ss) > 0:
quotations[code] = ss
self._batch_update_stock_history_quotation(quotations)
print 'load history file finish ' + history_file
#??????
def parse_file(datafile=datafile):
workbook = xlrd.open_workbook(datafile)
sheet= workbook.sheet_by_index(0)
title_col = 1
type_col = 2
link_col = 3
date_col = 4
events = []
case = -1
agree = ''
data = [[sheet.cell_value(r,col) for col in range(sheet.ncols)] for r in range(sheet.nrows)]
for row in range(int(startRow)-1,sheet.nrows):
title = sheet.cell_value(row,1)
etype = sheet.cell_value(row,2)
link = sheet.cell_value(row,3)
date = datetime.datetime(*xlrd.xldate_as_tuple(sheet.cell_value(row,4),workbook.datemode))
dateString = date.strftime("%a, %d %b")
#date=sheet.cell_value(row,4)
dict = {'title':title,'type':etype,'link':link,'date':dateString}
events.append(dict)
print "New event appended"
print " Event name: %s \n Type : %s \t link : %s \n date : %r" %(dict['title'],dict['type'],dict['link'],dict['date'])
print "Do you want to view the linnk? y/n"
agree = raw_input("--> ")
if agree=='y'or agree=='Y':
if (not webbrowser.open(dict['link'],new=new)):
print "Failed to open"
case = int(raw_input("1 -- pass \t 2 -- reject \t 0 -- quit \n --> "))
if case==1:
pass
if case==2:
del events[-1]
print "Event removed"
if case==0:
break
print "Finished all the events"
print "Appending to the file"
file = open("data.txt",'w')
file.write(str(events))
file.close()
return events
