[ADD] base_import: support for XLS and ODS files

* added intermediate ``_read_file`` step dispatching between CSV, ODS
  and XLS(X) parsing
  - primary dispatch on mime type, secondary on file extension:
    + OS may not provide a relevant mime type if no software locally
      installed for the filetype (e.g. Windows sends excel files as
      application/octet-stream if excel is not installed, and ODS files as
      zip if OpenOffice/LibreOffice isn't installed)
    + applications may re-register extensions to non-standard mimetypes
      breaking the dispatcher

    So if the mimetype is found trust it, otherwise try with the
    filename's extension (if any)
  - all readers skip lines with only empty cells in their output
  - ODS and XLS content are "CSVified", rows are converted to arrays of
    unicode strings
* UI altered to not assume CSV files everywhere, and avoid returning
  garbage preview data for non-CSV imports

Various:
* added a ``can_import`` utility function to tests.common, can be used
  to skip tests if an optional Python dependency is not installed (but
  one would like tests to run if the dependency is available)
* fixed datetime issue in ir_fields
* added converter for monetary fields (iso float)
* spreadsheet don't have integers, twiddling required to ensure integral
  values won't be serialized as floats (breaking conversion back to
  Python)
* improved some tests by asserting no error is generated (bonus: logs
  the error message if there is one, rather than just saying the result
  is blown)
* because some systems only provide elderly versions of
  XLRD (e.g. current debian stable provides 0.9.2 from April 2013)
  - keep using xldate_as_tuple instead of 0.9.3's xldate_as_datetime,
    workaround is simple
  - check for xlrd.xlsx in case of pre-0.8 XLRD

Authorship:
Ronak Baxi <rba@odoo.com>
Mohammed Shekha <msh@openerp.com>

Task 10792
Closes #7285

1 files changed, 110 insertions, 16 deletions

diff --git a/addons/base_import/models.py b/addons/base_import/models.py
index 784ae7f49f1..7463a02028e 100644
--- a/addons/base_import/models.py
+++ b/addons/base_import/models.py
@@ -1,7 +1,10 @@
 import csv
+import datetime
+import io
 import itertools
 import logging
 import operator
+import os
 
 try:
     from cStringIO import StringIO
@@ -12,10 +15,38 @@ import psycopg2
 
 from openerp.osv import orm, fields
 from openerp.tools.translate import _
+from openerp.tools import DEFAULT_SERVER_DATE_FORMAT, \
+    DEFAULT_SERVER_DATETIME_FORMAT
 
 FIELDS_RECURSION_LIMIT = 2
 ERROR_PREVIEW_BYTES = 200
 _logger = logging.getLogger(__name__)
+
+try:
+    import xlrd
+    try:
+        from xlrd import xlsx
+    except ImportError:
+        xlsx = None
+except ImportError:
+    xlrd = xlsx = None
+
+try:
+    import odf_ods_reader
+except ImportError:
+    odf_ods_reader = None
+
+FILE_TYPE_DICT = {
+    'text/csv': ('csv', True, None),
+    'application/vnd.ms-excel': ('xls', xlrd, 'xlrd'),
+    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet': ('xlsx', xlsx, 'xlrd >= 0.8'),
+    'application/vnd.oasis.opendocument.spreadsheet': ('ods', odf_ods_reader, 'odfpy')
+}
+EXTENSIONS = {
+    '.' + ext: handler
+    for mime, (ext, handler, req) in FILE_TYPE_DICT.iteritems()
+}
+
 class ir_import(orm.TransientModel):
     _name = 'base_import.import'
     # allow imports to survive for 12h in case user is slow
@@ -26,7 +57,7 @@ class ir_import(orm.TransientModel):
         'file': fields.binary(
             'File', help="File to check and/or import, raw binary (not base64)"),
         'file_name': fields.char('File Name'),
-        'file_type': fields.char('File Type'),
+        'file_type': fields.char(string='File Type'),
     }
 
     def get_fields(self, cr, uid, model, context=None,
@@ -126,6 +157,69 @@ class ir_import(orm.TransientModel):
         # TODO: cache on model?
         return fields
 
+    def _read_file(self, file_type, record, options):
+        (file_extension, handler, req) = FILE_TYPE_DICT.get(file_type, (None, None, None))
+        if handler:
+            return getattr(self, '_read_' + file_extension)(record, options)
+        # fallback on file extensions as mime types can be unreliable (e.g.
+        # software setting incorrect mime types, or non-installed software
+        # leading to browser not sending mime types)
+        if record.file_name:
+            p, ext = os.path.splitext(record.file_name)
+            if ext and EXTENSIONS.get(ext):
+                return getattr(self, '_read_' + ext[1:])(record, options)
+
+        if req:
+            raise ImportError(_("Unable to load \"{extension}\" file: requires Python module \"{modname}\"").format(extension=file_extension, modname=req))
+        raise ValueError(_("Unsupported file format \"{}\", import only supports CSV, ODS, XLS and XLSX").format(file_type))
+
+    def _read_xls(self, record, options):
+        book = xlrd.open_workbook(file_contents=record.file)
+        sheet = book.sheet_by_index(0)
+        # emulate Sheet.get_rows for pre-0.9.4
+        for row in itertools.imap(sheet.row, range(sheet.nrows)):
+            values = []
+            for cell in row:
+                if cell.ctype is xlrd.XL_CELL_NUMBER:
+                    is_float = cell.value % 1 != 0.0
+                    values.append(
+                        unicode(cell.value)
+                        if is_float
+                        else unicode(int(cell.value))
+                    )
+                elif cell.ctype is xlrd.XL_CELL_DATE:
+                    is_datetime = cell.value % 1 != 0.0
+                    # emulate xldate_as_datetime for pre-0.9.3
+                    dt = datetime.datetime(*xlrd.xldate.xldate_as_tuple(
+                        cell.value, book.datemode))
+                    values.append(
+                        dt.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
+                        if is_datetime
+                        else dt.strftime(DEFAULT_SERVER_DATE_FORMAT)
+                    )
+                elif cell.ctype is xlrd.XL_CELL_BOOLEAN:
+                    values.append(u'True' if cell.value else u'False')
+                elif cell.ctype is xlrd.XL_CELL_ERROR:
+                    raise ValueError(
+                        _("Error cell found while reading XLS/XLSX file: %s") %
+                        xlrd.error_text_from_code.get(
+                            cell.value, "unknown error code %s" % cell.value)
+                    )
+                else:
+                    values.append(cell.value)
+            if any(x for x in values if x.strip()):
+                yield values
+    _read_xlsx = _read_xls
+
+    def _read_ods(self, record, options):
+        doc = odf_ods_reader.ODSReader(file=io.BytesIO(record.file))
+
+        return (
+            row
+            for row in doc.getFirstSheet()
+            if any(x for x in row if x.strip())
+        )
+
     def _read_csv(self, record, options):
         """ Returns a CSV-parsed iterator of all empty lines in the file
 
@@ -137,15 +231,13 @@ class ir_import(orm.TransientModel):
             quotechar=str(options['quoting']),
             delimiter=str(options['separator']))
 
-        def nonempty(row):
-            return any(x for x in row if x.strip())
-
-        csv_nonempty = itertools.ifilter(nonempty, csv_iterator)
         # TODO: guess encoding with chardet? Or https://github.com/aadsm/jschardet
         encoding = options.get('encoding', 'utf-8')
-        return itertools.imap(
-            lambda row: [item.decode(encoding) for item in row],
-            csv_nonempty)
+        return (
+            [item.decode(encoding) for item in row]
+            for row in csv_iterator
+            if any(x for x in row if x.strip())
+        )
 
     def _match_header(self, header, fields, options):
         """ Attempts to match a given header to a field of the
@@ -211,10 +303,10 @@ class ir_import(orm.TransientModel):
             return None, None
 
         headers = next(rows)
-        return headers, dict(
-            (index, [field['name'] for field in self._match_header(header, fields, options)] or None)
+        return headers, {
+            index: [field['name'] for field in self._match_header(header, fields, options)] or None
             for index, header in enumerate(headers)
-        )
+        }
 
     def parse_preview(self, cr, uid, id, options, count=10, context=None):
         """ Generates a preview of the uploaded files, and performs
@@ -236,7 +328,7 @@ class ir_import(orm.TransientModel):
         fields = self.get_fields(cr, uid, record.res_model, context=context)
 
         try:
-            rows = self._read_csv(record, options)
+            rows = self._read_file(record.file_type, record, options)
 
             headers, matches = self._match_headers(rows, fields, options)
             # Match should have consumed the first row (iif headers), get
@@ -253,15 +345,17 @@ class ir_import(orm.TransientModel):
             # Due to lazy generators, UnicodeDecodeError (for
             # instance) may only be raised when serializing the
             # preview to a list in the return.
-            _logger.debug("Error during CSV parsing preview", exc_info=True)
+            _logger.debug("Error during parsing preview", exc_info=True)
+            preview = None
+            if record.file_type == 'text/csv':
+                preview = record.file[:ERROR_PREVIEW_BYTES].decode('iso-8859-1')
             return {
                 'error': str(e),
                 # iso-8859-1 ensures decoding will always succeed,
                 # even if it yields non-printable characters. This is
                 # in case of UnicodeDecodeError (or csv.Error
                 # compounded with UnicodeDecodeError)
-                'preview': record.file[:ERROR_PREVIEW_BYTES]
-                                .decode( 'iso-8859-1'),
+                'preview': preview,
             }
 
     def _convert_import_data(self, record, fields, options, context=None):
@@ -287,7 +381,7 @@ class ir_import(orm.TransientModel):
         # Get only list of actually imported fields
         import_fields = filter(None, fields)
 
-        rows_to_import = self._read_csv(record, options)
+        rows_to_import = self._read_file(record.file_type, record, options)
         if options.get('headers'):
             rows_to_import = itertools.islice(
                 rows_to_import, 1, None)