def Open(self):
     try:
         if not os.path.isfile(self.v_filename):
             raise Spartacus.Utils.Exception('File {0} does not exist or is not a file.'.format(self.v_filename))
         if self.v_extension == 'csv':
             self.v_file = open(self.v_filename, encoding=self.v_encoding)
             v_sample = self.v_file.read(1024)
             self.v_file.seek(0)
             v_sniffer = csv.Sniffer()
             if not v_sniffer.has_header(v_sample):
                 raise Spartacus.Utils.Exception('CSV file {0} does not have a header.'.format(self.v_filename))
             v_dialect = v_sniffer.sniff(v_sample)
             if self.v_delimiter is not None:
                 v_dialect.delimiter = self.v_delimiter
             self.v_object = csv.DictReader(self.v_file, self.v_header, None, None, v_dialect)
             self.v_open = True
         elif self.v_extension == 'xlsx':
             self.v_object = openpyxl.load_workbook(self.v_filename, read_only=True)
             self.v_open = True
         elif self.v_extension == 'xls':
             v_tmp_file = tempfile.NamedTemporaryFile(suffix='.xlsx')
             v_tmp_file.file.close()
             pyexcel.save_book_as(file_name=self.v_filename, dest_file_name=v_tmp_file.name)
             self.v_object = openpyxl.load_workbook(v_tmp_file.name, read_only=True)
             self.v_open = True
         else:
             raise Spartacus.Utils.Exception('File extension "{0}" not supported.'.format(self.v_extension))
     except Spartacus.Utils.Exception as exc:
         raise exc
     except Exception as exc:
         raise Spartacus.Utils.Exception(str(exc))

 def setUp(self):
     self.testfile = "multiple1.xls"
     self.content = OrderedDict()
     self.content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
     self.content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
     self.content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
     pe.save_book_as(dest_file_name=self.testfile, bookdict=self.content)

 def test_save_book_as_to_database(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     data1 = [
         ["A", "B", "C"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     sheet_dict = {
         Signature.__tablename__: data,
         Signature2.__tablename__: data1
     }
     pe.save_book_as(bookdict=sheet_dict,
                     dest_session=self.session,
                     dest_tables=[Signature, Signature2])
     result = pe.get_dict(session=self.session, table=Signature)
     assert result == {
         "X": [1, 4],
         "Y": [2, 5],
         "Z": [3, 6]
     }
     result = pe.get_dict(session=self.session, table=Signature2)
     assert result == {
         "A": [1, 4],
         "B": [2, 5],
         "C": [3, 6]
     }

def diff(source_file_type, file_type, source, dest):
    """
    Find difference in plain data format

    Both files are transcoded into a file stream of a common file type.
    Then a diff operation is run on them.

    \b
    SOURCE: a file name or '-'. '-' tells the command to use stdin
    DEST: a file name
    """
    params = {}
    if source == '-':
        params['file_content'] = get_input_content(source_file_type)
        params['file_type'] = source_file_type
    else:
        params['file_name'] = source
    params['dest_file_type'] = file_type
    source_lines = pe.save_book_as(**params)
    dest_lines = pe.save_book_as(file_name=dest,
                                 dest_file_type=file_type)
    result = unified_diff(source_lines.getvalue().splitlines(),
                          dest_lines.getvalue().splitlines(),
                          source, dest)
    has_difference = 0
    for difference in result:
        has_difference = 1
        click.echo(difference)
    sys.exit(has_difference)

    def test_dict(self):
        adict = {
            'sheet 1': [[1,2],[3,4]],
            'sheet 2': [[5,6],[7,8]]
        }
        pe.save_book_as(bookdict=adict, dest_file_name=self.testfile)

        self._check_test_file('dict')

 def setUp(self):
     self.testfile4 = "multiple_sheets.xls"
     self.content4 = {
         "Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]],
         "Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]],
         "Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]
     }
     pe.save_book_as(dest_file_name=self.testfile4,
                     bookdict=self.content4)

 def test_dict(self):
     adict = {
         'sheet 1': [[1,2],[3,4]],
         'sheet 2': [[5,6],[7,8]]
     }
     pe.save_book_as(bookdict=adict, dest_file_name=self.testfile)
     with open(self.testfile, "r") as f:
         written_content = json.load(f)
         assert written_content == adict

    def test_no_title_multiple_sheets(self):
        adict = {
            'sheet 1': [[1,2],[3,4]],
            'sheet 2': [[5,6],[7,8]]
        }
        pe.save_book_as(bookdict=adict, dest_file_name=self.testfile,
                        dest_write_title=False)

        self._check_test_file('no_title_multiple_sheets')

 def test_save_book_as_file_from_sql(self):
     test_file = "book_from_sql.xls"
     pe.save_book_as(out_file=test_file, session=Session(), tables=[Signature, Signature2])
     book_dict = pe.get_book_dict(file_name=test_file)
     expected = OrderedDict()
     expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
     expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
     assert book_dict == expected
     os.unlink(test_file)

    def _write_test_file(self, filename, content):
        """
        Make a test file as:

        1,1,1,1
        2,2,2,2
        3,3,3,3
        """
        pe.save_book_as(dest_file_name=filename, bookdict=content)

    def _write_test_file(self, file):
        """
        Make a test file as:

        1,1,1,1
        2,2,2,2
        3,3,3,3
        """
        self.rows = 3
        pyexcel.save_book_as(bookdict=self.content,dest_file_name=file)

 def test_save_book_as_file_from_sql(self):
     test_file="book_from_sql.xls"
     pe.save_book_as(out_file=test_file,
                     session=Session(),
                     tables=[Signature, Signature2])
     book_dict = pe.get_book_dict(file_name=test_file)
     expected = OrderedDict()
     expected.update({'signature': [['X', 'Y', 'Z'], [1, 2, 3], [4, 5, 6]]})
     expected.update({'signature2': [['A', 'B', 'C'], [1, 2, 3], [4, 5, 6]]})
     assert book_dict == expected
     os.unlink(test_file)

 def save_book_to_database(self, models=None, initializers=None,
                           mapdicts=None, batch_size=None,
                           **keywords):
     """
     Save data from a book to a nominated django models
     """
     params = self.get_params(**keywords)
     params['dest_models'] = models
     params['dest_initializers']=initializers
     params['dest_mapdicts'] = mapdicts
     params['dest_batch_size'] = batch_size
     pe.save_book_as(**params)

 def test_dict(self):
     adict = {
         'sheet 1': [[1,2],[3,4]],
         'sheet 2': [[5,6],[7,8]]
     }
     pe.save_book_as(bookdict=adict, dest_file_name=self.testfile)
     f = open(self.testfile, "r")
     written_content = f.read()
     f.close()
     content = dedent("""
         {"sheet 1": [[1, 2], [3, 4]], "sheet 2": [[5, 6], [7, 8]]}""").strip('\n')
     assert written_content == content

def test_write_texttable_book():
    content = {"Sheet": [[1,2]]}
    test_file = "test.texttable"
    expected = dedent("""
    Sheet:
    +---+---+
    | 1 | 2 |
    +---+---+""").strip('\n')
    pe.save_book_as(bookdict=content, dest_file_name=test_file)
    with open(test_file, 'r') as f:
        written = f.read()
        eq_(written, expected)
    os.unlink(test_file)

 def test_save_book_to_memory_from_sql(self):
     test_file = pe.save_book_as(dest_file_type="xls", session=Session(), tables=[Signature, Signature2])
     book_dict = pe.get_book_dict(file_content=test_file.getvalue(), file_type="xls")
     expected = OrderedDict()
     expected.update({"signature": [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]})
     expected.update({"signature2": [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]})
     assert book_dict == expected

    def setUp(self):
        """
        Make a test csv file as:

        1,2,3,4
        5,6,7,8
        9,10,11,12
        """
        self.testfile = "testcsv.xls"
        self.content = {
            "Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]],
            "Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]],
            "Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]
        }
        pe.save_book_as(bookdict=self.content,
                        dest_file_name=self.testfile)

 def test_book_file(self):
     data = {
         "Category":[
             ["id", "name"],
             [1, "News"],
             [2, "Sports"]
         ],
         "Post":[
             ["id", "title", "body", "pub_date", "category"],
             [1, "Title A", "formal", datetime(2015,1,20,23,28,29), "News"],
             [2, "Title B", "informal", datetime(2015,1,20,23,28,30), "Sports"]
         ]
     }
     for upload_file_type in ['xls']:
         with app.app_context():
             db.drop_all()
             db.create_all()
         print("Uploading %s" % upload_file_type)
         file_name = "test.%s" % upload_file_type
         io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type)
         response = self.app.post('/upload/all',
                                  buffered=True,
                                  data={"file": (io, file_name)},
                                  content_type="multipart/form-data")
         ret = pe.get_book_dict(file_type="xls", file_content=response.data)
         assert data['Category'] == ret['category']
         sheet = pe.Sheet(data['Post'], name_columns_by_row=0)
         sheet.column.format("pub_date", lambda d: d.isoformat())
         sheet2 = pe.Sheet(ret['post'], name_columns_by_row=0)
         for key in sheet.colnames:
             if key == "category":
                 continue
             assert sheet.column[key] == sheet2.column[key]
         assert sheet2.column['category_id'] == [1, 2]

 def test_book_file(self):
     data = {
         "Category": [["id", "name"], [1, "News"], [2, "Sports"]],
         "Post": [
             ["id", "title", "body", "pub_date", "category"],
             [1, "Title A", "formal", datetime(2015, 1, 20, 23, 28, 29), "News"],
             [2, "Title B", "informal", datetime(2015, 1, 20, 23, 28, 30), "Sports"],
         ],
     }
     for upload_file_type in ["xls"]:
         print("Uploading %s" % upload_file_type)
         file_name = "test.%s" % upload_file_type
         io = pe.save_book_as(bookdict=data, dest_file_type=upload_file_type)
         if not PY2:
             if isinstance(io, BytesIO):
                 content = io.getvalue()
             else:
                 content = io.getvalue().encode("utf-8")
         else:
             content = io.getvalue()
         response = self.app.post("/upload/all", upload_files=[("file", file_name, content)])
         ret = pe.get_book_dict(file_type="xls", file_content=response.body)
         assert data["Category"] == ret["category"]
         sheet = pe.Sheet(data["Post"], name_columns_by_row=0)
         sheet.column.format("pub_date", lambda d: d.isoformat())
         sheet2 = pe.Sheet(ret["post"], name_columns_by_row=0)
         for key in sheet.colnames:
             if key == "category":
                 continue
             assert sheet.column[key] == sheet2.column[key]
         assert sheet2.column["category_id"] == [1, 2]

 def test_get_book_from_file_stream(self):
     content = OrderedDict()
     content.update({"Sheet1": [[1, 1, 1, 1], [2, 2, 2, 2], [3, 3, 3, 3]]})
     content.update({"Sheet2": [[4, 4, 4, 4], [5, 5, 5, 5], [6, 6, 6, 6]]})
     content.update({"Sheet3": [[u'X', u'Y', u'Z'], [1, 4, 7], [2, 5, 8], [3, 6, 9]]})
     io = pe.save_book_as(dest_file_type="xls", bookdict=content)
     book2 = pe.get_book(file_stream=io, file_type="xls")
     assert book2.to_dict() == content