def _read_cell(element): cell_type = element.attrib.get(_tag(NS_OPENDOCUMENT_OFFICE, VALUE_TYPE)) value_token = ODS_VALUE_TOKEN.get(cell_type, 'value') if cell_type == 'string': cell = _read_text_cell(element) elif cell_type == 'currency': value = element.attrib.get(_tag(NS_OPENDOCUMENT_OFFICE, value_token)) currency = element.attrib.get(_tag(NS_OPENDOCUMENT_OFFICE, 'currency')) cell = Cell(value + ' ' + currency, type=CurrencyType()) elif cell_type is not None: value = element.attrib.get(_tag(NS_OPENDOCUMENT_OFFICE, value_token)) cell = Cell(value, type=ODS_TYPES.get(cell_type, StringType())) else: cell = Cell(EMPTY_CELL_VALUE, type=StringType()) return cell
def raw(self, sample=False): """ Iterate over all rows in this sheet. """ rows = ODS_ROW_MATCH.findall(self.sheet) for row in rows: row_data = [] block = "{0}{1}{2}".format(self.namespace_tags[0], row, self.namespace_tags[1]) partial = cStringIO.StringIO(block) for action, elem in etree.iterparse(partial, ('end', )): if elem.tag == '{urn:oasis:names:tc:opendocument:xmlns:table:1.0}table-cell': cell_type = elem.attrib.get( 'urn:oasis:names:tc:opendocument:xmlns:office:1.0:value-type' ) children = elem.getchildren() if children: c = Cell(children[0].text, type=ODS_TYPES.get(cell_type, StringType())) row_data.append(c) if not row_data: # ignore blank lines continue del partial yield row_data del rows
def __init__(self, value, column=None, type=None): if type is None: from messytables.types import StringType type = StringType() self.value = value self.column = column self.column_autogenerated = False self.type = type
def __init__(self, value=None, column=None, type=None, source=None): assert value is None assert isinstance(source, lxml.etree._Element) self._lxml = source if type is None: from messytables.types import StringType type = StringType() self.type = type self.column = column self.column_autogenerated = False
def _read_text_cell(element): children = element.getchildren() text_content = [] for child in children: if child.text: text_content.append(child.text) else: text_content.append(EMPTY_CELL_VALUE) if len(text_content) > 0: cell_value = '\n'.join(text_content) else: cell_value = EMPTY_CELL_VALUE return Cell(cell_value, type=StringType())
def from_xlrdcell(xlrd_cell, sheet, col, row): value = xlrd_cell.value cell_type = XLS_TYPES.get(xlrd_cell.ctype, StringType()) if cell_type == DateType(None): if value == 0: raise InvalidDateError year, month, day, hour, minute, second = \ xlrd.xldate_as_tuple(value, sheet.book.datemode) value = datetime(year, month, day, hour, minute, second) messy_cell = XLSCell(value, type=cell_type) messy_cell.sheet = sheet messy_cell.xlrd_cell = xlrd_cell messy_cell.xlrd_pos = (row, col ) # necessary for properties, note not (x,y) return messy_cell
def raw(self, sample=False): """ Iterate over all rows in this sheet. Types are automatically converted according to the excel data types specified, including conversion of excel dates, which are notoriously buggy. """ num_rows = self.sheet.get_highest_row() for i in xrange(min(self.window, num_rows) if sample else num_rows): row = [] for cell in self.sheet.rows[i]: value = cell.value if cell.is_date(): type = DateType(None) elif cell.data_type == 'n': type = IntegerType() else: type = StringType() row.append(Cell(value, type=type)) yield row
def __init__(self, pdftables_cell): self._cell = pdftables_cell if pdftables_cell.topleft: w, h = pdftables_cell.size self._properties = dict( colspan=w, rowspan=h, ) self.value = pdftables_cell.content else: self._properties = {} self.value = "" self.column = None self.column_autogenerated = False self.type = StringType()
def raw(self, sample=False): """ Iterate over all rows in this sheet. Types are automatically converted according to the excel data types specified, including conversion of excel dates, which are notoriously buggy. """ num_rows = self.sheet.nrows for i in xrange(min(self.window, num_rows) if sample else num_rows): row = [] for j, cell in enumerate(self.sheet.row(i)): value = cell.value type = XLS_TYPES.get(cell.ctype, StringType()) if type == DateType(None): if value == 0: raise ValueError('Invalid date at "%s":%d,%d' % (self.sheet.name, j + 1, i + 1)) year, month, day, hour, minute, second = \ xlrd.xldate_as_tuple(value, self.sheet.book.datemode) value = datetime(year, month, day, hour, minute, second) row.append(Cell(value, type=type)) yield row
from datetime import datetime import xlrd from messytables.core import RowSet, TableSet, Cell from messytables.types import StringType, IntegerType, \ DateType, FloatType XLS_TYPES = { 1: StringType(), # NB: Excel does not distinguish floats from integers so we use floats # We could try actual type detection between floats and ints later # or use the excel format string info - see # https://groups.google.com/forum/?fromgroups=#!topic/ # python-excel/cAQ1ndsCVxk 2: FloatType(), 3: DateType(None), # this is actually boolean but we do not have a boolean type yet 4: IntegerType() } class XLSTableSet(TableSet): """An excel workbook wrapper object. """ def __init__(self, fileobj=None, filename=None, window=None, encoding=None): '''Initilize the tableset.
def _map_openpyxl_type_to_messytable_type(type_str): type = OPENPYXL_TYPE_MAPPING.get(type_str, None) if type: return type return StringType()
The return value is a tuple of the offset of the header row and the names of the columns. """ rows = list(rows) modal = column_count_modal(rows) for i, row in enumerate(rows): length = len([c for c in row if c.value]) if length >= modal - tolerance: # TODO: use type guessing to check that this row has # strings and does not conform to the type schema of # the table. return i, [u'{}'.format(c.value) for c in row if c.value] return 0, [] OPENPYXL_TYPE_MAPPING = { TYPE_STRING: StringType(), TYPE_BOOL: BoolType(), TYPE_NUMERIC: IntegerType(), 'd': DateUtilType() } def _get_type_weight(type_str): if type_str == TYPE_STRING: return StringType.guessing_weight if type_str == TYPE_NUMERIC: return IntegerType.guessing_weight # Use "IntegerType" weight for even decimal + float if type_str == TYPE_BOOL: return BoolType.guessing_weight