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 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 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 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))