def field_mapping():
return {
'0': ('vessel_name', may_strip),
'1': ('flag', may_strip),
'2': ('gross_tonnage', lambda x: try_apply(x, float, int)),
'3': ('vessel_type', may_strip),
'4': ('shipping_agent', may_strip),
'5':
ignore_key('ignore transit'),
'6':
ignore_key('from country'),
'7': ('arrival', lambda x: xldate_as_datetime(x, 0)
if isinstance(x, float) else None),
'8':
ignore_key('next_zone, bacause ceyhan is not up to date'),
'9': ('departure', lambda x: xldate_as_datetime(x, 0)
if isinstance(x, float) else None),
'10': ('evacuation_type', None),
'11': ('evacuation_tonnage', lambda x: try_apply(x, float, int)),
'12':
ignore_key('discharge at port'),
'13': ('loading_type', None),
'14': ('loading_tonnage', lambda x: try_apply(x, float, int)),
'15':
ignore_key('loading port'),
# meta fields
'port_name': ('port_name', None),
'provider_name': ('provider_name', None),
'reported_date': ('reported_date', None),
}
def edit_file(filename, base_id=[]):
font0 = xlwt.Font()
font0.name = 'Times New Roman'
font0.colour_index = 2 # 红色
font0.bold = True
style0 = xlwt.XFStyle()
style0.font = font0
style1 = xlwt.XFStyle()
style1.num_format_str = 'YYYY/MM/DD' # 对日期格式的处理
rb = open_workbook(filename)
wb = copy(rb)
ws = wb.get_sheet(0)
# table = rb.get_sheet()[0] #这个方法好像过时了，这里会报错
table = rb.sheets()[0]
for row_number in range(table.nrows):
if row_number:
if table.row_values(row_number)[0] in base_id:
print xldate.xldate_as_datetime(table.row_values(row_number)[1], 0)
ws.write(row_number, 0, table.row_values(row_number)[0], style0) # 这个地方需要改一个颜色
ws.write(row_number, 1, xldate.xldate_as_datetime(table.row_values(row_number)[1], 0),style1) # 这个地方需要改一个颜色
wb.save(filename)
# wb.save('b' + filename)# 可以把文件保存为另外的名字，原文件不会改变
print 'ok'
def parse_movimenti_carta(conto_id, sheet):
movimenti = []
cursor = mysql.connection.cursor()
for rowindex in range(1, sheet.nrows):
check_val = sheet.cell_value(rowindex, 3)
if not check_val:
break
movimento = Movimento()
try:
movimento.date = xldate_as_datetime(sheet.cell_value(rowindex, 0),
datemode=0)
except:
date_str = sheet.cell_value(rowindex, 0)
movimento.date = datetime.strptime(date_str, '%d/%m/%Y')
try:
movimento.data_contabile = xldate_as_datetime(sheet.cell_value(
rowindex, 1),
datemode=0)
except:
date_str = sheet.cell_value(rowindex, 1)
movimento.data_contabile = datetime.strptime(date_str, '%d/%m/%Y')
movimento.type = 'PAGAMENTO CARTA DI CREDITO'
movimento.description = sheet.cell_value(rowindex, 2)
movimento.amount = sheet.cell_value(rowindex, 4) * -1
movimento.compute_hash()
cursor.execute('select id from movimenti where row_hash = %s',
[movimento.row_hash])
rec = cursor.fetchone()
if not rec:
movimento.categoria_id, movimento.sottocategoria_id = assign_category(
movimento)
cursor.execute(
"""
INSERT INTO movimenti (tipo,
descrizione,
data_movimento,
importo,
row_hash,
categoria_id,
sottocategoria_id,
conto_id)
VALUES (%s, %s, %s, %s, %s, %s,%s, %s)
""", [
movimento.type, movimento.description, movimento.date,
movimento.amount, movimento.row_hash,
movimento.categoria_id, movimento.sottocategoria_id,
conto_id
])
movimento.id = cursor.lastrowid
movimenti.append(movimento)
else:
print('Movimento già caricato')
mysql.connection.commit()
cursor.close()
return movimenti
def _parse_cell(cell_contents, cell_typ):
"""
converts the contents of the cell into a pandas appropriate object
"""
if cell_typ == XL_CELL_DATE:
# Use the newer xlrd datetime handling.
try:
cell_contents_orgin = cell_contents
cell_contents = \
xldate.xldate_as_datetime(cell_contents,
epoch1904)
# distinguish between dates and time in mysql
try:
cell_contents_tuple = xldate.xldate_as_tuple(cell_contents_orgin, epoch1904)
if cell_contents_tuple[3:6] == (0, 0, 0):
cell_contents = cell_contents.date()
except:
cell_contents = \
xldate.xldate_as_datetime(cell_contents_orgin,
epoch1904)
except OverflowError:
return cell_contents
# Excel doesn't distinguish between dates and time,
# so we treat dates on the epoch as times only.
# Also, Excel supports 1900 and 1904 epochs.
year = (cell_contents.timetuple())[0:3]
if (not epoch1904 and year == (1899, 12, 31)) or (
epoch1904 and year == (1904, 1, 1)
):
cell_contents = time(
cell_contents.hour,
cell_contents.minute,
cell_contents.second,
cell_contents.microsecond,
)
elif cell_typ == XL_CELL_ERROR:
cell_contents = np.nan
elif cell_typ == XL_CELL_BOOLEAN:
cell_contents = bool(cell_contents)
elif convert_float and cell_typ == XL_CELL_NUMBER:
# GH5394 - Excel 'numbers' are always floats
# it's a minimal perf hit and less surprising
val = int(cell_contents)
if val == cell_contents:
cell_contents = val
return cell_contents
def read_line(ws, row, fields):
"""
Read the trade information from a line.
"""
line_info = {}
column = 0
for fld in fields:
logger.debug('read_line(): row={0}, column={1}'.format(row, column))
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld == 'Item No.':
cell_value = int(cell_value)
if fld == 'Security Code':
if isinstance(cell_value, float):
cell_value = str(int(cell_value))
if not is_valid_isin(cell_value):
cell_value = map_to_isin(cell_value)
if fld in ['Trade Date', 'Value Date']:
cell_value = xldate_as_datetime(cell_value, 0)
line_info[fld] = cell_value
column = column + 1
return line_info
def _parse_cell(cell_contents, cell_typ):
if cell_typ == XL_CELL_DATE:
try:
cell_contents = xldate.xldate_as_datetime(
cell_contents, epoch1904)
except OverflowError:
return cell_contents
year = (cell_contents.timetuple())[0:3]
if (not epoch1904 and year == (1899, 12, 31)) or (
epoch1904 and year == (1904, 1, 1)):
cell_contents = time(
cell_contents.hour,
cell_contents.minute,
cell_contents.second,
cell_contents.microsecond,
)
elif cell_typ == XL_CELL_ERROR:
cell_contents = np.nan
elif cell_typ == XL_CELL_BOOLEAN:
cell_contents = bool(cell_contents)
elif convert_float and cell_typ == XL_CELL_NUMBER:
val = int(cell_contents)
if val == cell_contents:
cell_contents = val
return cell_contents
def test_dates_only_1900_epoch(self):
"""
Test the _xldate_to_datetime() function for dates in the Excel
standard 1900 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's day 0 in the 1900 epoch is 1 day before 1900.
("1899-12-31", 0),
# Excel's day 1 in the 1900 epoch.
("1900-01-01", 1),
# Date/time before the false Excel 1900 leapday.
("1900-02-28", 59),
# Date/time after the false Excel 1900 leapday.
("1900-03-01", 61),
# Random date/times in Excel's 0-9999.9999+ range.
("1902-09-27", 1001),
("1999-12-31", 36525),
("2000-01-01", 36526),
("4000-12-31", 767376),
("4321-01-01", 884254),
("9999-01-01", 2958101),
# End of Excel's date range.
("9999-12-31", 2958465),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%d")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
def test_times_only(self):
"""
Test the _xldate_to_datetime() function for times only, i.e, the
fractional part of the Excel date when the serial date is 0.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file. The 1899-12-31 date is Excel's day 0.
excel_dates = [
# Random times in Excel's 0-0.9999+ range for 1 day.
('1899-12-31T00:00:00.000', 0),
('1899-12-31T00:15:20.213', 1.0650613425925924E-2),
('1899-12-31T02:24:37.095', 0.10042934027777778),
('1899-12-31T04:56:35.792', 0.2059698148148148),
('1899-12-31T07:31:20.407', 0.31343063657407405),
('1899-12-31T09:37:23.945', 0.40097158564814817),
('1899-12-31T12:09:48.602', 0.50681252314814818),
('1899-12-31T14:37:57.451', 0.60969271990740748),
('1899-12-31T17:04:02.415', 0.71113906250000003),
('1899-12-31T19:14:24.673', 0.80167445601851861),
('1899-12-31T21:39:05.944', 0.90215212962962965),
('1899-12-31T23:17:12.632', 0.97028509259259266),
('1899-12-31T23:59:59.999', 0.99999998842592586),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
def read_excel():
# 打开文件
book = xlrd.open_workbook('demo.xlsx')
# 获取所有的sheet
print(book.sheet_names())
sheet2_name = book.sheet_names()[1]
# 根据sheet索引或者名称获取内容
sheet2 = book.sheet_by_index(1)
sheet2 = book.sheet_by_name('Sheet2')
# sheet的名称，行数，列数
print(sheet2.name, sheet2.nrows, sheet2.ncols)
# 获取整行和整列的值
rows = sheet2.row_values(3)
cols = sheet2.col_values(2)
print(rows)
print(cols)
# 获取单元格内容
print(sheet2.cell(1, 0).value)
print(sheet2.cell_value(1, 0))
print(sheet2.row(1)[0].value)
# ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
# 获取单元格内容的数据类型
print(sheet2.cell(2, 2).ctype)
print(sheet2.cell(2, 1).ctype)
print(sheet2.cell(2, 0).ctype)
print(sheet2.cell(2, 4).ctype)
print(sheet2.cell(2, 2).ctype)
date_value = xlrd.xldate_as_tuple(sheet2.cell_value(2, 2), book.datemode)
date1 = xldate_as_datetime(sheet2.cell_value(2, 2), book.datemode)
print(date1)
print(date_value)
for i in range(sheet2.nrows):
r = sheet2.col_values(4)[i]
print(r)
def test_dates_only_1904_epoch(self):
"""
Test the _xldate_to_datetime() function for dates in the Excel
Mac/1904 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's day 0 in the 1904 epoch.
('1904-01-01', 0),
# Random date/times in Excel's 0-9999.9999+ range.
('1904-01-31', 30),
('1904-08-31', 243),
('1999-02-28', 34757),
('1999-12-31', 35063),
('2000-01-01', 35064),
('2400-12-31', 181526),
('4000-01-01', 765549),
('9999-01-01', 2956639),
# End of Excel's date range.
('9999-12-31', 2957003),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%d")
got = xldate.xldate_as_datetime(excel_date[1], is_1904)
self.assertEqual(got, exp)
def CreateLists(book,model):
k = 0
br = []
wb=open_workbook(book[0])
sh=wb.sheet_by_name(book[1])
for rows_ in sh.col_values(4):
if model in rows_:
try: ## 判断有无链接
link=sh.hyperlink_map.get((k,0))
lists_link=link.url_or_path
lists_value=sh.cell(k,0).value
column_1=lists_link
except:
column_1=sh.cell(k,0).value
try:
date=xldate.xldate_as_datetime(sh.cell(k,1).value, 0) ## 单元格日期
da=arrow.get(date)
except:
da='1999/9/9' ## 错误日期填充
column_2=da.format('YYYY-M-D')
column_3=sh.cell(k,2).value
column_4=sh.cell(k,3).value
column_5=sh.cell(k,4).value
column_6=sh.cell(k,5).value
column_7=sh.cell(k,6).value
arr = [column_1,column_2,column_3,column_4,column_5,column_6,column_7]
br.append(arr)
k+=1
return br
def loadxlsx(self):
self.filename = QtWidgets.QFileDialog.getOpenFileName(self, "Datei wählen")
self.statusbar.showMessage("Loading: " + self.filename[0])
self.show()
book = open_workbook(self.filename[0])
sheet = book.sheet_by_index(0)
item_model = QtGui.QStandardItemModel(self.accountSelector)
filter_model = QtGui.QStandardItemModel(self.filterSelector)
filter_model.appendRow(QtGui.QStandardItem("Basic cumulative"))
filter_model.appendRow(QtGui.QStandardItem("Basic category shares income"))
filter_model.appendRow(QtGui.QStandardItem("Basic category shares expenditures"))
for row_index in range(1, sheet.nrows):
dateinfo = sheet.cell_value(row_index, 2)
if isinstance(dateinfo, float):
dateinfo = xldate.xldate_as_datetime(sheet.cell_value(row_index, 2),0)
dateinfo = dateinfo.strftime("%d.%m.%Y")
self.data_list.append((sheet.cell_value(row_index, 0),sheet.cell_value(row_index, 1), dateinfo,sheet.cell_value(row_index, 3),sheet.cell_value(row_index, 4),sheet.cell_value(row_index, 5),sheet.cell_value(row_index, 6),sheet.cell_value(row_index, 7),sheet.cell_value(row_index, 8),sheet.cell_value(row_index, 9),sheet.cell_value(row_index, 10)))
if(sheet.cell_value(row_index, 9)not in self.account_list):
self.account_list.append(sheet.cell_value(row_index, 9))
item_model.appendRow(QtGui.QStandardItem(sheet.cell_value(row_index, 9)))
self.data_list.sort(key=lambda tup: datetime.strptime(tup[2],"%d.%m.%Y"))
table_model = TableModel(self, self.data_list, ['Transaktions_ID','geprüft','Datum','Beschreibung','Wert','Kategorie1','Kategorie2','Kategorie3','Laufend','Konto','Soll/Haben'])
self.filterSelector.setModel(filter_model)
self.accountSelector.setModel(item_model)
self.transactionList.setModel(table_model)
self.refreshKPI()
self.statusbar.showMessage("Data loaded!")
self.show()
def test_dates_and_times_1900_epoch(self):
"""
Test the _xldate_to_datetime() function for dates and times in
the Excel standard 1900 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's 0.0 date in the 1900 epoch is 1 day before 1900.
("1899-12-31T00:00:00.000", 0),
# Date/time before the false Excel 1900 leapday.
("1900-02-28T02:11:11.986", 59.09111094906),
# Date/time after the false Excel 1900 leapday.
("1900-03-01T05:46:44.068", 61.24078782403),
# Random date/times in Excel's 0-9999.9999+ range.
("1982-08-25T00:15:20.213", 30188.010650613425),
("2065-04-19T00:16:48.290", 60376.011670023145),
("3222-06-11T03:08:08.251", 483014.13065105322),
("4379-08-03T06:14:48.580", 905652.26028449077),
("5949-12-30T12:59:54.263", 1479232.5416002662),
# End of Excel's date range.
("9999-12-31T23:59:59.000", 2958465.999988426),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
def format(self, col, cell):
"""Format a value with the pattern set for column.
If no format pattern is set for column, value is returned
unchanged.
Args:
col (int): Column number
cell (xlrd.sheet.Cell): Excel cell
Returns:
str: Formatted value
"""
pat = self.get(col)
log('col=%r, pat=%r, cell=%r', col, pat, cell)
if not pat or cell.ctype in (TYPE_BOOLEAN, TYPE_ERROR, TYPE_EMPTY):
return self._format_default(cell)
if cell.ctype == TYPE_DATE:
dt = xldate_as_datetime(cell.value, self.datemode)
formatted = dt.strftime(pat)
else:
try:
formatted = pat % cell.value
except Exception: # Try new-style formatting
try:
formatted = pat.format(cell.value)
except Exception:
formatted = cell.value
# log('pat=%r, %r --> %r', pat, cell.value, formatted)
return formatted
def convert_next(self):
'''
Returns a generator that yields converted rows. The conversion is
from the related xls_file to the CSV row format expected by
contracts.loaders.region_10.Region10Loader
'''
heading_indices = self.get_heading_indices_map()
datemode = self.book.datemode # necessary for Excel date parsing
sheet = self.book.sheet_by_index(self.sheet_index)
# skip the heading row, process the rest
for rx in range(1, sheet.nrows):
row = [None] * len(self.xl_heading_to_csv_idx_map) # init row
for heading, xl_idx in heading_indices.items():
# item_val = cval(xl_idx)
cell = sheet.cell(rx, xl_idx)
cell_type = cell.ctype
cell_value = cell.value
csv_col_idx = self.xl_heading_to_csv_idx_map[heading]
if cell_type is XL_CELL_DATE:
# convert to mm/dd/YYYY string
date = xldate_as_datetime(cell_value, datemode)
cell_value = date.strftime('%m/%d/%Y')
# Save the string value into the expected CSV col
# index of the row
row[csv_col_idx] = str(cell_value)
yield row
def _format_default(self, cell):
"""Return cell value with default formatting.
Args:
cell (xlrd.sheet.Cell): Excel cell
Returns:
str: Formatted cell value
"""
if cell.ctype == TYPE_BOOLEAN:
if cell.value:
return 'yes'
else:
return 'no'
if cell.ctype == TYPE_ERROR:
return '<error>'
if cell.ctype == TYPE_EMPTY:
return ''
if cell.ctype == TYPE_DATE:
dt = xldate_as_datetime(cell.value, self.datemode)
return dt.strftime(DATE_FORMAT)
return cell.value
def format(self, col, cell):
"""Format a value with the pattern set for column.
If no format pattern is set for column, value is returned
unchanged.
Args:
col (int): Column number
cell (xlrd.sheet.Cell): Excel cell
Returns:
str: Formatted value
"""
pat = self.get(col)
log('col=%r, pat=%r, cell=%r', col, pat, cell)
if not pat or cell.ctype in (TYPE_BOOLEAN, TYPE_ERROR, TYPE_EMPTY):
return self._format_default(cell)
if cell.ctype == TYPE_DATE:
dt = xldate_as_datetime(cell.value, self.datemode)
formatted = dt.strftime(pat)
else:
try:
formatted = pat % cell.value
except Exception: # Try new-style formatting
try:
formatted = pat.format(cell.value)
except Exception:
formatted = cell.value
# log('pat=%r, %r --> %r', pat, cell.value, formatted)
return formatted
def test_times_only(self):
"""
Test the _xldate_to_datetime() function for times only, i.e, the
fractional part of the Excel date when the serial date is 0.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file. The 1899-12-31 date is Excel's day 0.
excel_dates = [
# Random times in Excel's 0-0.9999+ range for 1 day.
('1899-12-31T00:00:00.000', 0),
('1899-12-31T00:15:20.213', 1.0650613425925924E-2),
('1899-12-31T02:24:37.095', 0.10042934027777778),
('1899-12-31T04:56:35.792', 0.2059698148148148),
('1899-12-31T07:31:20.407', 0.31343063657407405),
('1899-12-31T09:37:23.945', 0.40097158564814817),
('1899-12-31T12:09:48.602', 0.50681252314814818),
('1899-12-31T14:37:57.451', 0.60969271990740748),
('1899-12-31T17:04:02.415', 0.71113906250000003),
('1899-12-31T19:14:24.673', 0.80167445601851861),
('1899-12-31T21:39:05.944', 0.90215212962962965),
('1899-12-31T23:17:12.632', 0.97028509259259266),
('1899-12-31T23:59:59.999', 0.99999998842592586),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
def parse(context, data):
res = context.http.rehash(data)
xls = xlrd.open_workbook(res.file_path)
ws = xls.sheet_by_index(0)
header = [slugify(h, sep='_') for h in ws.row_values(0)]
assert XLS_COLUMNS == header
batch = []
for r in range(1, ws.nrows):
row = ws.row(r)
row = dict(zip(header, row))
for head, cell in row.items():
if cell.ctype == 1:
row[head] = cell.value
elif cell.ctype == 2:
row[head] = str(int(cell.value))
elif cell.ctype == 3:
date = xldate_as_datetime(cell.value, xls.datemode)
row[head] = date.strftime('%m/%d/%y')
elif cell.ctype == 0:
row[head] = None
if row.get('name_type') == 'Primary Name':
batch = [row]
elif row.get('name_type') == 'aka':
batch.append(row)
context.emit(data={'rows': batch})
def read_line_position(ws, row, fields):
"""
Read a line in the unmatched position file, return a position record.
"""
line_info = {}
column = 0
for fld in fields:
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld == 'Portfolio' and isinstance(cell_value, float):
cell_value = str(int(cell_value))
if fld in ['Interest Start Day', 'Maturity']:
cell_value = convert_datetime_to_string(
xldate_as_datetime(cell_value, 0))
line_info[fld] = cell_value
column = column + 1
# end of for loop
return line_info
def parse(context, data):
emitter = EntityEmitter(context)
references = defaultdict(list)
with context.http.rehash(data) as res:
xls = xlrd.open_workbook(res.file_path)
ws = xls.sheet_by_index(0)
headers = [slugify(h, sep="_") for h in ws.row_values(0)]
for r in range(1, ws.nrows):
row = ws.row(r)
row = dict(zip(headers, row))
for header, cell in row.items():
if cell.ctype == 2:
row[header] = str(int(cell.value))
elif cell.ctype == 3:
date = xldate_as_datetime(cell.value, xls.datemode)
row[header] = date.isoformat()
elif cell.ctype == 0:
row[header] = None
row[header] = cell.value
reference = clean_reference(row.get("reference"))
references[reference].append(row)
for ref, rows in references.items():
parse_reference(emitter, ref, rows)
emitter.finalize()
def datetimeindex_to_xldates(datetimeindex):
"""
Convert a datetime index to a numpy array of Excel numerical date format.
"""
timedeltas = datetimeindex - xldate_as_datetime(4000, 0)
xldates = timedeltas.total_seconds() / (3600 * 24) + 4000
return xldates.values
def test_dates_only_1900_epoch(self):
"""
Test the _xldate_to_datetime() function for dates in the Excel
standard 1900 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's day 0 in the 1900 epoch is 1 day before 1900.
("1899-12-31", 0),
# Excel's day 1 in the 1900 epoch.
("1900-01-01", 1),
# Date/time before the false Excel 1900 leapday.
("1900-02-28", 59),
# Date/time after the false Excel 1900 leapday.
("1900-03-01", 61),
# Random date/times in Excel's 0-9999.9999+ range.
("1902-09-27", 1001),
("1999-12-31", 36525),
("2000-01-01", 36526),
("4000-12-31", 767376),
("4321-01-01", 884254),
("9999-01-01", 2958101),
# End of Excel's date range.
("9999-12-31", 2958465),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%d")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
def test_dates_only_1904_epoch(self):
"""
Test the _xldate_to_datetime() function for dates in the Excel
Mac/1904 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's day 0 in the 1904 epoch.
('1904-01-01', 0),
# Random date/times in Excel's 0-9999.9999+ range.
('1904-01-31', 30),
('1904-08-31', 243),
('1999-02-28', 34757),
('1999-12-31', 35063),
('2000-01-01', 35064),
('2400-12-31', 181526),
('4000-01-01', 765549),
('9999-01-01', 2956639),
# End of Excel's date range.
('9999-12-31', 2957003),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%d")
got = xldate.xldate_as_datetime(excel_date[1], is_1904)
self.assertEqual(got, exp)
def get_value(self, item, **kwargs):
"""Handle different value types for XLS.
:param item: xlrd cell object
:returns: items value with dates parsed properly
"""
if item.ctype == XL_CELL_DATE:
try:
return xldate.xldate_as_datetime(item.value, self._workbook.datemode)
except XLDateAmbiguous:
raise Exception('Date fields are not in a format that SEED can interpret. '
'A possible solution is to save as a CSV file and reimport.')
if item.ctype == XL_CELL_NUMBER:
if item.value % 1 == 0: # integers
return int(item.value)
else:
return item.value
if isinstance(item.value, unicode):
return unicodedata.normalize('NFKD', item.value).encode(
'ascii', 'ignore'
)
return item.value
def dollar_ils_rate_parse_from_excel_file(file):
book = open_workbook(file)
sheet = book.sheet_by_index(0)
dic = dict()
for row in range(0, sheet.nrows):
rate_cell = sheet.cell(row, BANK_OF_ISRAEL_RATE_COL)
date_cell = sheet.cell(row, BANK_OF_ISRAEL_DATE_COL)
try:
rate = float(rate_cell.value)
except Exception as e:
# If exception converting the rate column to float -
# we haven't reached the actual data yet. Go to next row
continue
if date_cell.ctype == XL_CELL_TEXT:
date_str = date_cell.value
datetime_obj = datetime.datetime.strptime(date_str, '%d/%m/%Y')
elif date_cell.ctype == XL_CELL_DATE:
datetime_obj = xldate.xldate_as_datetime(date_cell.value,
book.datemode)
else:
raise Exception('Unknown cell type when parsing exchanges')
dic[datetime_obj] = rate
return dic
def parse_quarter_date(cls, value) -> datetime:
v = xldate_as_datetime(value, 0)
if not v or not isinstance(v, datetime):
raise ValueError("Invalid CPI quarter")
return v
def get_value(self, item, **kwargs):
"""Handle different value types for XLS.
:param item: xlrd cell object
:returns: items value with dates parsed properly
"""
if item.ctype == XL_CELL_DATE:
try:
date = xldate.xldate_as_datetime(item.value,
self._workbook.datemode)
return date.strftime("%Y-%m-%d %H:%M:%S")
except XLDateAmbiguous:
raise Exception(
'Date fields are not in a format that SEED can interpret. '
'A possible solution is to save as a CSV file and reimport.'
)
if item.ctype == XL_CELL_NUMBER:
if item.value % 1 == 0: # integers
return int(item.value)
else:
return item.value
if isinstance(item.value, basestring):
return unidecode(item.value)
return item.value
def _format_default(self, cell):
"""Return cell value with default formatting.
Args:
cell (xlrd.sheet.Cell): Excel cell
Returns:
str: Formatted cell value
"""
if cell.ctype == TYPE_BOOLEAN:
if cell.value:
return 'yes'
else:
return 'no'
if cell.ctype == TYPE_ERROR:
return '<error>'
if cell.ctype == TYPE_EMPTY:
return ''
if cell.ctype == TYPE_DATE:
dt = xldate_as_datetime(cell.value, self.datemode)
return dt.strftime(DATE_FORMAT)
return cell.value
def datetime(*args,**kwargs):
tzinfo=kwargs.get('tzinfo',LOCAL)
if len(args)==1:
d=args[0]
if isinstance(d,(dt.datetime,dt.time)):
'''
如果是datetime或time类型，检查是否有tzinfo信息，
如无，则设为LOCAL，否则直接返回
'''
if not d.tzinfo:
d=d.replace(tzinfo=tzinfo)
return d
elif isinstance(d,str):
'''将字符串转换为datetime类型'''
args=[int(x) for x in re.findall('\d+',d)]
return dt.datetime(*args,tzinfo=tzinfo)
elif isinstance(d,(int,float)):
'''将整数或浮点数转换成日期类型
如果小于100000，则按Excel的格式转换；
否则按unix timestamp 来转换'''
from xlrd.xldate import xldate_as_datetime
if d<100000:
dd=xldate_as_datetime(d,None).replace(tzinfo=tzinfo)
else:
dd=dt.datetime.fromtimestamp(d,tzinfo)
return dd
else:
kwargs['tzinfo']=tzinfo
return dt.datetime(*args,**kwargs)
def update_cash_data(cash_accounts):
"""
Update certain cash fields to other format.
Local CCY: change to standard representation such as USD, HKD, etc.
As Of: change to python datetime format.
"""
logger.debug('update_cash_data(): start')
c_map = {
'US DOLLAR': 'USD',
'CHINESE RENMINBI YUAN': 'CNH' # star helios uses CNH not CNY
}
for account in filter(lambda acc: acc['Local CCY'] != '', cash_accounts):
logger.debug('update_cash_data(): {0}, amount {1}'.\
format(account['Local CCY'], account['Position Accounting Market Value (Local CCY)']))
account['As Of'] = xldate.xldate_as_datetime(account['As Of'],
get_datemode())
try:
account['Local CCY'] = c_map[account['Local CCY']]
except KeyError:
logger.error(
'update_cash_data(): failed to map {0} to standard representation'
.format(account['Local CCY']))
raise
return cash_accounts
def _parse_cell(cell_contents, cell_typ):
"""converts the contents of the cell into a pandas
appropriate object"""
if cell_typ == XL_CELL_DATE:
# Use the newer xlrd datetime handling.
try:
cell_contents = xldate.xldate_as_datetime(
cell_contents, epoch1904)
except OverflowError:
return cell_contents
# Excel doesn't distinguish between dates and time,
# so we treat dates on the epoch as times only.
# Also, Excel supports 1900 and 1904 epochs.
year = (cell_contents.timetuple())[0:3]
if ((not epoch1904 and year == (1899, 12, 31)) or
(epoch1904 and year == (1904, 1, 1))):
cell_contents = time(cell_contents.hour,
cell_contents.minute,
cell_contents.second,
cell_contents.microsecond)
elif cell_typ == XL_CELL_ERROR:
cell_contents = np.nan
elif cell_typ == XL_CELL_BOOLEAN:
cell_contents = bool(cell_contents)
elif convert_float and cell_typ == XL_CELL_NUMBER:
# GH5394 - Excel 'numbers' are always floats
# it's a minimal perf hit and less surprising
val = int(cell_contents)
if val == cell_contents:
cell_contents = val
return cell_contents
def get_value(self, item, **kwargs):
"""Handle different value types for XLS.
:param item: xlrd cell object
:returns: items value with dates parsed properly
"""
if item.ctype == XL_CELL_DATE:
try:
return xldate.xldate_as_datetime(item.value,
self._workbook.datemode)
except XLDateAmbiguous:
raise Exception(
'Date fields are not in a format that SEED can interpret. '
'A possible solution is to save as a CSV file and reimport.'
)
if item.ctype == XL_CELL_NUMBER:
if item.value % 1 == 0: # integers
return int(item.value)
else:
return item.value
if isinstance(item.value, unicode):
return unicodedata.normalize('NFKD',
item.value).encode('ascii', 'ignore')
return item.value
def open_file(self):
name = QtGui.QFileDialog.getOpenFileName(self, "File Open")
print(name)
wb = open_workbook(name, encoding_override = "cp1251")
# ws = wb.sheet_by_name('Лист1')
ws = wb.sheet_by_index(0)
if ws.row(0)[1].value == '': i = 2
else: i = 1
names = []
for row_index in range(i, ws.nrows):
names.append((ws.row(row_index)[12].value,))
names = list(set(names))
#выводим на экран
#for item in lis:
#my_list.insert(END, item)
con = sqlite3.connect('ebank.db')
with con:
cursor = con.cursor()
cursor.execute('pragma foreign_keys=ON')
cursor.execute('pragma foreign_keys')
cursor.executemany('INSERT INTO people(name) values(?)', names)
rows = []
for row_index in range(i, ws.nrows):
name = ws.row(row_index)[12].value
a = list(x.value for x in ws.row(row_index) if x.value != name)
a[3] = xldate.xldate_as_datetime(a[3], wb.datemode).date()
a[4] = xldate.xldate_as_datetime(a[4], wb.datemode).date()
a[8] = xldate.xldate_as_datetime(a[8], wb.datemode).date()
a[9] = xldate.xldate_as_datetime(a[9], wb.datemode).date()
a = tuple(a)
a = (names.index((name,))+1,) + a#сложение кортежей
rows.append(a)
cursor.execute('pragma foreign_keys=ON')
cursor.execute('pragma foreign_keys')
operation = cursor.executemany('INSERT INTO contract(man_id, summa, numer_dog, days, nachalo, okonchanie, rate, summa_k_uplate, tek_period, tek_nachalo, tek_okonchanie, summa_za_period, vsego_za_period, word_doc) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)', rows)
con.commit()
con.close()
print("База создана")
def getExcelTime(v,datemode):
try:
return xldate_as_datetime(v,datemode).time()
# Not a valid excel time format
except:
v = v.replace(";",":")
return datetime.strptime(v, "%H:%M").time()
def read_line(ws, row, fields):
"""
Read a line, store as trade information. Note, it only read lines whose
transaction type is one of the following:
1. CSA: transferred in (from accounts not under FT)
2. CSW: transferred out (to accounts not under FT)
3. IATSW: transferred out (internal accounts)
4. IATSA: transferred in (internal accounts)
5. CALLED: called by issuer
6. TNDRL: buy back by issuer
If not, then it returns None.
"""
trade_info = {}
column = 0
for fld in fields:
logger.debug('read_line(): row={0}, column={1}'.format(row, column))
cell_value = ws.cell_value(row, column)
if isinstance(cell_value, str):
cell_value = cell_value.strip()
if fld == 'ACCT_ACNO':
cell_value = str(int(cell_value))
if fld in ['SCTYID_SMSEQ', 'SCTYID_SEDOL', 'SCTYID_CUSIP'
] and isinstance(cell_value, float):
cell_value = str(int(cell_value))
if fld in ['TRDDATE', 'STLDATE', 'ENTRDATE']:
# some FT files uses traditional excel date, some uses
# a float number to represent date.
if is_htm_portfolio(trade_info['ACCT_ACNO']):
cell_value = xldate_as_datetime(cell_value, get_datemode())
else:
cell_value = convert_float_to_datetime(cell_value)
if fld in ['QTY', 'GROSSBAS', 'PRINB', 'RGLBVBAS', 'RGLCCYCLS', \
'ACCRBAS', 'TRNBVBAS', 'GROSSLCL', 'FXRATE', 'TRADEPRC'] \
and isinstance(cell_value, str) and cell_value.strip() == '':
cell_value = 0.0
check_field_type(fld, cell_value)
trade_info[fld] = cell_value
column = column + 1
try:
if not trade_info['TRANTYP'] in ['IATSW', 'IATSA', 'CSA', 'CSW', \
'CALLED', 'TNDRL']:
trade_info = None
break
except KeyError:
pass
# end of for loop
return trade_info
def convert_excel_cell(book: Book, cell: Cell) -> Optional[str]:
if cell.ctype == 2:
return str(int(cell.value))
elif cell.ctype in (0, 5, 6):
return None
if cell.ctype == 3:
return xldate_as_datetime(cell.value, book.datemode)
else:
return cell.value
def get_xldates(self):
"""
Return a numpy array containing the Excel numerical dates
corresponding to the dates of the dataset.
"""
print('Converting datetimes to xldates...', end=' ')
timedeltas = self.data.index - xldate_as_datetime(4000, 0)
xldates = timedeltas.total_seconds() / (3600 * 24) + 4000
print('done')
return xldates.values
def specify_date_data(day_ago):
for each_day in range(0,int(day_ago)+1):
fwant_day = datetime.datetime.now() - datetime.timedelta(days=each_day)
mwant_day = fwant_day.strftime('%Y-%m-%d')
for each_line_num in range(1,table.nrows):
each_line = table.row_values(each_line_num)
# date_time = datetime.xldate_as_tuple(each_line[1],0)
date_time = xldate.xldate_as_datetime(each_line[1],0)
if str(mwant_day) in str(date_time):
print 'Date: %s %s:%s %s:%s' %(str(mwant_day), table.cell(0, 2).value, each_line[2], table.cell(0, 3).value, each_line[3])
def _convert_xlrd_date(raw_date_float):
"""Convert xlrd date float to iso format date.
Args:
raw_date_float (float):
Returns:
str:
"""
return xldate_as_datetime(raw_date_float, datemode=0).isoformat()
def input_users():
write_byte = open_workbook('用户表.xlsx')
try:
sheet_inf = write_byte.sheet_by_name('用户信息')
print('reading Sheet1...')
except:
print(' \'Sheet1\' changed its name.')
return None
rows = sheet_inf.nrows
cols = sheet_inf.ncols
print('rows {rows},cols {cols}.'.format(rows=rows, cols=cols))
cell_values = sheet_inf.cell_value(1, 1)
print('cell\'s values : {0}.'.format(cell_values))
data_list = []
for i in range(2, rows):
row_data = sheet_inf.row_values(i)
data_list.append(row_data)
# print('data of list : {}'.format(data_list))
for key in data_list:
if key[6] == '男':
key[6] = int(1)
elif key[6] == '女':
key[6] = int(2)
d = xldate_as_datetime(key[7], 0)
print('dddd :{}, type {}:'.format(d, type(d)))
print('d.year :{}, type:{}'.format(d.year, type(d.year)))
d2 = datetime.datetime.strftime(d, '%Y:%m:%d')
print('+++++{} , type :{}'.format(d2, type(d2)))
'''try:
user = Examinee(studycard=str(int(key[1])),
password=str(int(key[2])),
name=key[4],
gender=key[6],
birthday='1999-9-1') # xldate_as_datetime(key[7], 0)
print('1 ok.')
except:
print('user wrong.')
try:
db.session.add(user)
except:
print('session add wrong.')
try:
db.session.commit()
print('2 ok.')
except:
print('session commit wrong.')
# db.session.rollback()'''
'''userData = {}
def convert_dates(row, date_fields, date_mode):
"""Rename the row keys according to the mapping.
"""
for date_field in date_fields:
try:
row[date_field] = \
xldate_as_datetime(float(row[date_field]), date_mode).date()
except ValueError:
warning('Could not convert excel date = %s', row[date_field])
row[date_field] = None
return row
def version_date(self, v):
"""Deal with wacky version dates, like: 14APR13:10:31:45"""
from dateutil import parser
from xlrd.xldate import xldate_as_datetime
try:
v = parser.parse("{}-{}-{}".format(v[0:2],v[2:5],v[5:7])).date()
except:
v = xldate_as_datetime(v, 0).date()
return v
def specify_date_data(day_ago):
for each_day in range(0, int(day_ago) + 1):
fwant_day = datetime.datetime.now() - datetime.timedelta(days=each_day)
mwant_day = fwant_day.strftime('%Y-%m-%d')
for each_line_num in range(1, table.nrows):
each_line = table.row_values(each_line_num)
# date_time = datetime.xldate_as_tuple(each_line[1],0)
date_time = xldate.xldate_as_datetime(each_line[1], 0)
if str(mwant_day) in str(date_time):
print 'Date: %s %s:%s %s:%s' % (
str(mwant_day), table.cell(0, 2).value, each_line[2],
table.cell(0, 3).value, each_line[3])
def read_date(ws, row, column):
"""
Find the date of valuation period.
"""
datemode = get_datemode()
cell_value = ws.cell_value(row, column)
# print(row, column, cell_value)
try:
return xldate_as_datetime(cell_value, datemode)
except:
logger.error('read_date(): failed to convert value:{0} to date'.
format(cell_value))
raise
def validate_and_convert_field_value(field, cell_value):
"""
Validate the field value read is of proper type, if yes then convert it
to a proper value if necessary (e.g., an empty field to zero). If no,
then raise an exception.
"""
field_value = cell_value
# if the following field value type is not string, then
# something must be wrong
if field in ['is_listed', 'listed_location', 'currency'] \
and not isinstance(cell_value, str):
logger.error('validate_and_convert_field_value(): field {0} should be a string: {1}'.
format(field, cell_value))
raise ValueError('bad field type: not a string')
elif field in ['fx_on_trade_day', 'coupon_rate', 'average_cost', \
'amortized_cost', 'price', 'book_cost', 'interest_bought', \
'amortized_value', 'market_value', 'accrued_interest', \
'amortized_gain_loss', 'market_gain_loss', 'fx_gain_loss'] \
and not isinstance(cell_value, float):
logger.error('validate_and_convert_field_value(): field {0} should be a float: {1}'.
format(field, cell_value))
raise ValueError('bad field type: not a float')
elif field in ['par_amount', 'number_of_shares']:
if isinstance(cell_value, float):
# OK, no change
pass
elif isinstance(cell_value, str) and str.strip(cell_value) == '':
# treat an empty holding as zero
field_value = 0
else:
logger.error('validate_and_convert_field_value(): field {0} should be a \
float or empty string: {1}'.format(field, cell_value))
raise ValueError('bad field type: not a float or empty string')
# convert float to python datetime object when necessary
if field in ['coupon_start_date', 'maturity_date', 'last_trade_date']:
try:
datemode = get_datemode()
field_value = xldate_as_datetime(cell_value, datemode)
except:
logger.warning('validate_and_convert_field_value(): convert {0} to excel date failed, value = {1}'.
format(field, cell_value))
# treat it as a "dd/mm/yyyy" string and try again
field_value = convert_date_string(cell_value)
return field_value
def read_expense_item(ws, row, fields, expenses):
"""
Read the row and try to create an expense item based on content
in the row. If successful, append it to the expenses list,
if not, an InvalidExpenseItem exception is thrown.
If amount of the expense is 0, then no expense item is created.
"""
exp_item = {} # the expense item
column = -1
for fld in fields:
column = column + 1
if fld == 'empty_field':
continue
cell_value = ws.cell_value(row, column)
if fld in ['value_date', 'amount', 'fx_rate', 'hkd_equivalent']:
if not isinstance(cell_value, float):
logger.warning('read_expense_item(): field {0} is not float, value = {1}'.
format(fld, cell_value))
raise InvalidExpenseItem('invalid field type')
if fld == 'value_date':
datemode = get_datemode()
exp_item[fld] = xldate_as_datetime(cell_value, datemode)
elif fld == 'amount' and cell_value == 0: # stop reading
logger.warning('read_expense_item(): amount is 0 at row {0}'.
format(row))
return
else:
exp_item[fld] = cell_value
elif fld in ['description', 'currency']:
if not isinstance(cell_value, str):
logger.error('read_expense_item(): field {0} is not string, value = {1}'.
format(fld, cell_value))
raise InvalidExpenseItem('invalid field type')
exp_item[fld] = str.strip(cell_value)
else: # unexpected field type
logger.error('read_expense_item(): field {0} is unexpected'.format(fld))
raise InvalidExpenseItem('unexpected field type')
# end of for loop
expenses.append(exp_item)
def __call__(self, row):
""" give a row index return a dict value
:param row: row index tell which row now process
:return: dict value
"""
row_dict = {}
for index, key in enumerate(self.keys):
cell = self.sheet.row(row)[self.col+index]
if cell.ctype is XL_CELL_DATE:
row_dict[key] = xldate_as_datetime(cell.value, self.date_mode).strftime('%Y/%m/%d')
else:
row_dict[key] = str(cell.value).strip()
if self._check_state(row_dict):
return
return row_dict
def convert_next(self):
'''
Returns a generator that yields converted rows. The conversion is
from the related xls_file to the CSV row format expected by
contracts.loaders.region_10.Region10Loader
'''
heading_indices = self.get_heading_indices_map()
datemode = self.book.datemode # necessary for Excel date parsing
sheet = self.book.sheet_by_index(self.sheet_index)
# skip the heading row, process the rest
for rx in range(1, sheet.nrows):
row: List[Optional[str]] = \
[None] * len(self.xl_heading_to_csv_idx_map) # init row
for heading, xl_idx in heading_indices.items():
# item_val = cval(xl_idx)
cell = sheet.cell(rx, xl_idx)
cell_type = cell.ctype
cell_value = cell.value
csv_col_idx = self.xl_heading_to_csv_idx_map[heading]
if cell_type is XL_CELL_DATE:
# convert to mm/dd/YYYY string
date = xldate_as_datetime(cell_value, datemode)
cell_value = date.strftime('%m/%d/%Y')
# Save the string value into the expected CSV col
# index of the row
row[csv_col_idx] = str(cell_value)
yield row
def _parse_excel(self, sheetname=0, header=0, skiprows=None, skip_footer=0,
index_col=None, has_index_names=None, parse_cols=None,
parse_dates=False, date_parser=None, na_values=None,
thousands=None, chunksize=None, convert_float=True,
**kwds):
import xlrd
from xlrd import (xldate, XL_CELL_DATE,
XL_CELL_ERROR, XL_CELL_BOOLEAN,
XL_CELL_NUMBER)
epoch1904 = self.book.datemode
# xlrd >= 0.9.3 can return datetime objects directly.
if LooseVersion(xlrd.__VERSION__) >= LooseVersion("0.9.3"):
xlrd_0_9_3 = True
else:
xlrd_0_9_3 = False
if isinstance(sheetname, compat.string_types):
sheet = self.book.sheet_by_name(sheetname)
else: # assume an integer if not a string
sheet = self.book.sheet_by_index(sheetname)
data = []
should_parse = {}
for i in range(sheet.nrows):
row = []
for j, (value, typ) in enumerate(zip(sheet.row_values(i),
sheet.row_types(i))):
if parse_cols is not None and j not in should_parse:
should_parse[j] = self._should_parse(j, parse_cols)
if parse_cols is None or should_parse[j]:
if typ == XL_CELL_DATE:
if xlrd_0_9_3:
# Use the newer xlrd datetime handling.
value = xldate.xldate_as_datetime(value, epoch1904)
# Excel doesn't distinguish between dates and time,
# so we treat dates on the epoch as times only.
# Also, Excel supports 1900 and 1904 epochs.
year = (value.timetuple())[0:3]
if ((not epoch1904 and year == (1899, 12, 31))
or (epoch1904 and year == (1904, 1, 1))):
value = datetime.time(value.hour,
value.minute,
value.second,
value.microsecond)
else:
# Use the xlrd <= 0.9.2 date handling.
dt = xldate.xldate_as_tuple(value, epoch1904)
if dt[0] < datetime.MINYEAR:
value = datetime.time(*dt[3:])
else:
value = datetime.datetime(*dt)
elif typ == XL_CELL_ERROR:
value = np.nan
elif typ == XL_CELL_BOOLEAN:
value = bool(value)
elif convert_float and typ == XL_CELL_NUMBER:
# GH5394 - Excel 'numbers' are always floats
# it's a minimal perf hit and less suprising
val = int(value)
if val == value:
value = val
row.append(value)
data.append(row)
if header is not None:
data[header] = _trim_excel_header(data[header])
parser = TextParser(data, header=header, index_col=index_col,
has_index_names=has_index_names,
na_values=na_values,
thousands=thousands,
parse_dates=parse_dates,
date_parser=date_parser,
skiprows=skiprows,
skip_footer=skip_footer,
chunksize=chunksize,
**kwds)
return parser.read()
def format(self, v):
try:
return xldate_as_datetime(v, self.wb.datemode).date()
except:
return None
def main():
#===============================================================================
global inputdir, codedir, outputdir, CGMSdir, ECMWFdir, optimidir,\
EUROSTATdir, custom_yns
#-------------------------------------------------------------------------------
# ================================= USER INPUT =================================
# read the settings from the rc file
rcdict = rc.read('settings.rc')
#===============================================================================
#-------------------------------------------------------------------------------
# extract the needed information from the rc file
sites = [s.strip(' ') for s in rcdict['sites'].split(',')]
crops = [s.strip(' ') for s in rcdict['crops'].split(',')]
crop_nos = [int(s.strip(' ')) for s in rcdict['crop_nos'].split(',')]
years = [int(s.strip(' ')) for s in rcdict['years'].split(',')]
# optimization settings
force_optimization = str_to_bool(rcdict['force_optimization'])
selec_method = rcdict['selec_method']
ncells = int(rcdict['ncells'])
nsoils = int(rcdict['nsoils'])
weather = rcdict['weather']
# directory paths
outputdir = rcdict['outputdir']
inputdir = rcdict['inputdir']
codedir = rcdict['codedir']
CGMSdir = os.path.join(inputdir, 'CGMS')
ECMWFdir = os.path.join(inputdir, 'ECMWF')
EUROSTATdir = os.path.join(inputdir, 'EUROSTATobs')
#-------------------------------------------------------------------------------
# get the list of NUTS 2 region names associated to the list of FluxNet sites
from WOF_00_retrieve_input_data import open_csv
sitdict = open_csv(inputdir, 'sites_info2.csv', convert_to_float=False)
NUTS_reg = sitdict['NUTS_reg']
#custom_yieldnsow =
#-------------------------------------------------------------------------------
# get local yield and sowing date information
import xlrd
from xlrd.xldate import xldate_as_datetime
xl_workbook=xlrd.open_workbook(os.path.join(inputdir,'site_yields.xlsx'))
sheet_names = xl_workbook.sheet_names()
xl_sheet = xl_workbook.sheet_by_name(sheet_names[0])
xl_sites = xl_sheet.col(0)
xl_years = xl_sheet.col(1)
xl_crops = xl_sheet.col(2)
xl_yield = xl_sheet.col(3)
xl_sowda = xl_sheet.col(9)
datemode = xl_workbook.datemode
custom_yns = []
for si,ye,cr,so,yi in zip(xl_sites[1:38], xl_years[1:38], xl_crops[1:38],
xl_sowda[1:38], xl_yield[1:38]):
sit = str(si.value)
yea = int(ye.value)
cro = int(cr.value)
if int(so.value) != -9999: sow = xldate_as_datetime(so.value, datemode)
else: sow = np.nan
if int(yi.value) != -9999.: yie = yi.value
else: yie = np.nan
custom_yns += [(sit, yea, cro, sow, yie)]
for row in custom_yns: print row
#-------------------------------------------------------------------------------
# optimize fgap at the location / year / crops specified by user
for s,site in enumerate(sites):
for c,crop_name in enumerate(crops):
crop_no = crop_nos[c]
for year in years:
# create output folder if it doesn't already exists
optimidir = os.path.join(outputdir,'fgap/%i/c%i/'%(year,crop_no))
if not os.path.exists(optimidir):
print 'creating new directory %s'%optimidir
os.makedirs(optimidir)
# we try to optimize fgap for the NUTS 2, 1, 0 regions
for NUTS_level in range(3):
NUTS_no = NUTS_reg[s][0:4-NUTS_level]
print '\n', site, NUTS_no, year, crop_name
# OPTIMIZATION OF FGAP:
yldgapf = optimize_fgap(site, crop_no, crop_name, year, NUTS_no,
selec_method, ncells, nsoils,
weather, force_optimization)
count = cur.execute('select * from powerConsume_detail')
# print 'there has %s rows record' % count
result = cur.fetchone()
# print result
# !!!!!!!重要!!!!!!!!!
# !!!!!切换城市!!!!!!!
cityname = 'shanwei'
# cityname = 'shenzhen'
wb = open_workbook('../' + cityname + '.xlsx')
table = wb.sheets()[0]
nrows = table.nrows
for row in range(1, nrows):
row_value = table.row_values(row)
dt = xldate.xldate_as_datetime(row_value[0], 0)
dt_formate = dt.strftime('%Y-%m-%d %H:%M:%S')
t_formate = dt.strftime('%H:%M:%S')
year = dt.year
month = dt.month
day = dt.day
daytype = dt.weekday()
time = dt.time()
power = row_value[1]
# print dt,daytype,power,dt_formate,t_formate
cur.execute("insert into powerConsume_detail(cityName,datetime,daytype,time,powerConsume,year,month,day) values('%s','%s','%s','%s',%f,%i,%i,%i)" % (cityname, dt_formate, daytype, t_formate, power, year, month, day))
# print "insert into sz_history(datetime,daytype,time,power_consume) values('%s',%i,'%s',%d)" % (dt_formate,daytype,t_formate,power)
# time = xldate_as_tuple(table.cell(row,0).value, 0)
# print 'ID: %s info %s' % result
conn.commit()
cur.close()
"""
from openpyxl import load_workbook
from xlrd.xldate import xldate_as_datetime
__version__ = 0.31
file_to_generate = 'data_and_index.py'
wb = load_workbook(filename='battery.xlsx')
sheet = wb['Sheet1']
header = '#!/usr/bin/env python' + '\n' + \
'# -*- coding: utf-8 -*-' + '\n' + \
'\"\"\"' + '\n' + \
'Author: longqi' + '\n' + \
'Data source version: ' + str(xldate_as_datetime(sheet['P2'].value, 0)) + '\n' + \
'Generator version: ' + str(__version__) + '\n\n' + \
'This file is generated automatically, DO NOT MODIFY !!!' + '\n' + \
'\"\"\"' + '\n' + \
'from collections import UserDict' + '\n\n'
header_group = 'Group1 = 1' + '\n' + \
'Group2 = 2' + '\n' + \
'Group3 = 3' + '\n' + \
'Group4 = 4' + '\n' + \
'Group5 = 5' + '\n' + \
'Group6 = 6' + '\n' + \
'Group7 = 7' + '\n' + \
'Group8 = 8' + '\n' + \
'\n\n'
file_to_generate = "data_and_index.py"
wb = load_workbook(filename="BIC.xlsx")
sheet = wb["Sheet1"]
header = (
"#!/usr/bin/env python"
+ "\n"
+ "# -*- coding: utf-8 -*-"
+ "\n"
+ '"""'
+ "\n"
+ "Author: longqi"
+ "\n"
+ "Data source version: "
+ str(xldate_as_datetime(sheet["K2"].value, 0))
+ "\n"
+ "Generator version: "
+ str(__version__)
+ "\n\n"
+ "This file is generated automatically, DO NOT MODIFY !!!"
+ "\n"
+ '"""'
+ "\n"
+ "from collections import UserDict"
+ "\n\n"
)
header_group = (
"Group1 = 1"
+ "\n"
def parse_minutegram(msheet, csheet, sw, user):
work = Work()
if msheet.cell(0,7).value == '':
e = IntegrityError()
e.__cause__="El trabajo no tiene numero"
raise e
else:
work.number = msheet.cell(0, 7).value
if column_value_search(1, 'DESCRIPCION TP:', msheet):
drow = column_value_search(1, 'DESCRIPCION TP:', msheet)
else:
e = IntegrityError()
e.__cause__="El documento no tiene seccion DESCRIPCION TP"
raise e
if column_value_search(1, 'JUSTIFICACION: ', msheet):
jrow = column_value_search(1, 'JUSTIFICACION: ', msheet)
else:
e = IntegrityError()
e.__cause__="El documento no tiene seccion JUSTIFICACION"
raise e
if column_value_search(1, 'OBSERVACIONES:', msheet):
orow = column_value_search(1, 'OBSERVACIONES:', msheet)
else:
e = IntegrityError()
e.__cause__="El documento no tiene seccion OBSERVACIONES"
raise e
if column_value_search(1, 'PLAN DE TRABAJO (MINUTOGRAMA):', msheet):
wprow = column_value_search(1, 'PLAN DE TRABAJO (MINUTOGRAMA):', msheet)
else:
e = IntegrityError()
e.__cause__="El documento no tiene seccion PLAN DE TRABAJO"
raise e
if column_value_search(1, 'PLAN DE CONTINGENCIA / ROLLBACK:', msheet):
cprow = column_value_search(1, 'PLAN DE CONTINGENCIA / ROLLBACK:', msheet)
else:
e = IntegrityError()
e.__cause__="El documento no tiene seccion PLAN DE CONTINGENCIA / ROLLBACK"
raise e
#este bloque de codigo asigna los datos extraidos del formulario al work creado
work.ticketArea = sw.ticketArea
work.department = sw.department
work.municipality = sw.municipality
work.impact = sw.impact
work.ticketCause = sw.ticketCause
work.initialDate = sw.initialDate
work.finalDate = sw.finalDate
work.outboundDate = sw.outboundDate
work.createdDate = datetime.date.today()
work.affectTime = sw.affectTime
work.rollbackTime = sw.rollbackTime
now = timezone.make_aware(datetime.datetime.now(), timezone.get_default_timezone())
#Si el tiempo dado para la causa esta en horas se entiende que debe pasarse a areas internas y nunca externas
if sw.ticketCause.timeLapseType == Cause.HOURS and sw.ticketArea.type == Area.INTERN:
if now + datetime.timedelta(days=1, hours=sw.ticketCause.internTimeLapse) <= sw.initialDate:
work.limitResponseDate = now + datetime.timedelta(days=1, hours=sw.ticketCause.internTimeLapse)
else:
e = IntegrityError()
e.__cause__="El tiempo maximo de respuesta de los clientes es mas tarde que la fecha de inicio del trabajo"
raise e
elif sw.ticketCause.timeLapseType == Cause.HOURS and sw.ticketArea.type == Area.EXTERN:
e = IntegrityError()
e.__cause__="La Causa del ticket no puede asignarse a un area externa"
raise e
elif sw.ticketCause.timeLapseType == Cause.DAYS and sw.ticketArea.type == Area.INTERN:
if now + datetime.timedelta(days=1+sw.ticketCause.internTimeLapse) <= sw.initialDate:
work.limitResponseDate = now + datetime.timedelta(days=1+sw.ticketCause.internTimeLapse)
else:
e = IntegrityError()
e.__cause__="El tiempo maximo de respuesta de los clientes es mas tarde que la fecha de inicio del trabajo"
raise e
elif sw.ticketCause.timeLapseType == Cause.DAYS and sw.ticketArea.type == Area.INTERN:
if now + datetime.timedelta(days=1+sw.ticketCause.externTimeLapse) <= sw.initialDate:
work.limitResponseDate = now + datetime.timedelta(days=1+sw.ticketCause.externTimeLapse)
else:
e = IntegrityError()
e.__cause__="El tiempo maximo de respuesta de los clientes es mas tarde que la fecha de inicio del trabajo"
raise e
#se asigna el usuario loggeado al trabajo
if user:
work.userCreator = user
#-------------------------------------------------------------------------------
work.description = msheet.cell(drow+1, 1).value
work.justification = msheet.cell(jrow+1, 1).value
work.observations = msheet.cell(orow+1, 1).value
try:
group = WorkGroup.objects.get(number = work.number)
for w in group.work_set.all():
w.state = Work.CANCELED
for acc in w.acceptance_set.all():
acc.valid = False
acc.save()
w.save()
work.group = group
work.programmed = Work.REPROGRAMMED
except:
group = WorkGroup()
group.number = work.number
group.save()
work.group = group
work.save()
#loads work plans
for i in range(wprow+2,cprow):
if check_line(i, 2, 6, msheet):
wp = WorkPlan()
wp.work=work
wp.initialDate = xldate_as_datetime(msheet.cell(i, 2).value, 0)
wp.finalDate = xldate_as_datetime(msheet.cell(i, 3).value, 0)
wp.affectation = datetime.time(*(xldate_as_tuple(msheet.cell(i, 4).value, 0))[3:])
wp.activity = msheet.cell(i, 5).value
wp.save()
else:
e = IntegrityError()
e.__cause__="Alguno de los planes de trabajo tiene un campo vacio"
raise e
#loads contingency plans
for i in range(cprow+2, drow-1):
if check_line(i, 2, 6, msheet):
cp = ContingencyPlan()
cp.work=work
cp.initialDate = xldate_as_datetime(msheet.cell(i, 2).value, 0)
cp.finalDate = xldate_as_datetime(msheet.cell(i, 3).value, 0)
cp.affectation = datetime.time(*(xldate_as_tuple(msheet.cell(i, 4).value, 0))[3:])
cp.activity = msheet.cell(i, 5).value
cp.save()
else:
e = IntegrityError()
e.__cause__="Alguno de los planes de contingencia tiene un campo vacio"
raise e
parse_corp_clients(csheet, work)
def excel_dt_1904(v):
"""Convert a float that representes a date in an excel file into a datetime. The float
is assumed to have a basis of 1904"""
from xlrd.xldate import xldate_as_datetime
return xldate_as_datetime(v, 0)
def _parse_cell(xcell, epoch1904=False):
"""
Parse a xl-xcell.
:param xlrd.Cell xcell: an excel xcell
:type xcell: xlrd.sheet.Cell
:param epoch1904:
Which date system was in force when this file was last saved.
False => 1900 system (the Excel for Windows default).
True => 1904 system (the Excel for Macintosh default).
:type epoch1904: bool
:return: formatted xcell value
:rtype:
int, float, datetime.datetime, bool, None, str, datetime.time,
float('nan')
Examples::
>>> import xlrd
>>> from xlrd.sheet import Cell
>>> _parse_cell(Cell(xlrd.XL_CELL_NUMBER, 1.2))
1.2
>>> _parse_cell(Cell(xlrd.XL_CELL_DATE, 1.2))
datetime.datetime(1900, 1, 1, 4, 48)
>>> _parse_cell(Cell(xlrd.XL_CELL_TEXT, 'hi'))
'hi'
"""
ctype = xcell.ctype
cvalue = xcell.value
if ctype == XL_CELL_NUMBER:
# GH5394 - Excel 'numbers' are always floats
# it's a minimal perf hit and less suprising
cint = int(cvalue)
if cint == cvalue:
return cint
return cvalue
elif ctype in (XL_CELL_EMPTY, XL_CELL_BLANK):
return None # RECT-LOOP NEVER USE THIS
elif ctype == XL_CELL_TEXT:
return cvalue
elif ctype == XL_CELL_BOOLEAN:
return bool(cvalue)
elif ctype == XL_CELL_DATE: # modified from Pandas library
if _xlrd_0_9_3:
# Use the newer xlrd datetime handling.
d = xldate.xldate_as_datetime(cvalue, epoch1904)
# Excel doesn't distinguish between dates and time, so we treat
# dates on the epoch as times only. Also, Excel supports 1900 and
# 1904 epochs.
epoch = (1904, 1, 1) if epoch1904 else (1899, 12, 31)
if (d.timetuple())[0:3] == epoch:
d = datetime.time(d.hour, d.minute, d.second, d.microsecond)
else:
# Use the xlrd <= 0.9.2 date handling.
d = xldate.xldate_as_tuple(xcell.value, epoch1904)
if d[0] < datetime.MINYEAR: # time
d = datetime.time(*d[3:])
else: # date
d = datetime.datetime(*d)
return d
elif ctype == XL_CELL_ERROR:
return float('nan')
raise ValueError('Invalid XL-cell type(%s) for value(%s)!' %
(xcell.ctype, xcell.value))
def test_dates_and_times_1900_epoch(self):
"""
Test the _xldate_to_datetime() function for dates and times in
the Excel standard 1900 epoch.
"""
# Test Excel dates strings and corresponding serial date numbers taken
# from an Excel file.
excel_dates = [
# Excel's 0.0 date in the 1900 epoch is 1 day before 1900.
("1899-12-31T00:00:00.000", 0),
# Date/time before the false Excel 1900 leapday.
("1900-02-28T02:11:11.986", 59.09111094906),
# Date/time after the false Excel 1900 leapday.
("1900-03-01T05:46:44.068", 61.24078782403),
# Random date/times in Excel's 0-9999.9999+ range.
("1982-08-25T00:15:20.213", 30188.010650613425),
("2065-04-19T00:16:48.290", 60376.011670023145),
("3222-06-11T03:08:08.251", 483014.13065105322),
("4379-08-03T06:14:48.580", 905652.26028449077),
("5949-12-30T12:59:54.263", 1479232.5416002662),
# End of Excel's date range.
("9999-12-31T23:59:59.000", 2958465.999988426),
]
# Convert the Excel date strings to datetime objects and compare
# against the dateitme return value of xldate.xldate_as_datetime().
for excel_date in excel_dates:
exp = datetime.strptime(excel_date[0], "%Y-%m-%dT%H:%M:%S.%f")
got = xldate.xldate_as_datetime(excel_date[1], not_1904)
self.assertEqual(got, exp)
