Exemple #1
0
def test_pyexcel_issue_140():
    TestSheet1 = p.Sheet()
    TestSheet1[4, 4] = "4x4"
    TestSheet1[0, 0] = "0,0"
    expected = [
        ["0,0", "", "", "", ""],
        ["", "", "", "", ""],
        ["", "", "", "", ""],
        ["", "", "", "", ""],
        ["", "", "", "", "4x4"],
    ]
    eq_(expected, TestSheet1.to_array())
Exemple #2
0
 def test_data_frame(self):
     content = [["", "Column 1", "Column 2", "Column 3"],
                ["Row 1", 1, 2, 3], ["Row 2", 4, 5, 6], ["Row 3", 7, 8, 9]]
     s = pe.Sheet(content, name_rows_by_column=0, name_columns_by_row=0)
     content = dedent("""
         pyexcel sheet:
                  Column 1    Column 2    Column 3
         -----  ----------  ----------  ----------
         Row 1           1           2           3
         Row 2           4           5           6
         Row 3           7           8           9""").strip('\n')
     self.assertEqual(s.simple, content)
 def test_save_file_as_another_one(self):
     data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     testfile2 = "testfile2.csv"
     sheet.save_as(testfile)
     pe.save_as(file_name=testfile, dest_file_name=testfile2)
     sheet = pe.get_sheet(file_name=testfile2)
     sheet.format(int)
     eq_(sheet.to_array(), data)
     os.unlink(testfile)
     os.unlink(testfile2)
 def test_sheet_save_to_django_model_3(self):
     model=FakeDjangoModel()
     sheet = pe.Sheet(self.data)
     sheet.name_columns_by_row(0)
     def wrapper(row):
         row[0] = row[0] + 1
         return row
     sheet.save_to_django_model(model, initializer=wrapper)
     assert model.objects.objs == [
         {'Y': 2, 'X': 2, 'Z': 3},
         {'Y': 5, 'X': 5, 'Z': 6}
     ]
 def test_save_file_as_texttable(self):
     """
     test if _texttable can render generator or not
     """
     data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     testfile2 = "testfile2.texttable"
     sheet.save_as(testfile)
     pe.isave_as(file_name=testfile, dest_file_name=testfile2)
     os.unlink(testfile)
     os.unlink(testfile2)
Exemple #6
0
 def test_column_series(self):
     content = [["Column 1", "Column 2", "Column 3"], [1, 2, 3], [4, 5, 6],
                [7, 8, 9]]
     s = pe.Sheet(content, name_columns_by_row=0)
     content = dedent("""
         pyexcel sheet:
           Column 1    Column 2    Column 3
         ----------  ----------  ----------
                  1           2           3
                  4           5           6
                  7           8           9""").strip('\n')
     self.assertEqual(s.simple, content)
    def save_data(self, search_word):
        try:
            csv_data = self.get_detail_page(search_word)
            sheet = pyexcel.Sheet()
            for data in csv_data:
                sheet.row += pyexcel.get_sheet(adict=data, transpose_after=True)
            sheet.colnames = ['title', 'author', 'source', 'info', 'date', 'kws', 'cited', 'downed', 'abstract', 'fund', 'download']
            print(sheet)
            sheet.save_as(self.csvname)

        except Exception as e:
            print('404 error!%s' % e)
Exemple #8
0
 def test_get_sheet_from_file(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     sheet.save_as(testfile)
     sheet = pe.get_sheet(file_name=testfile)
     assert sheet.to_array() == data
     os.unlink(testfile)
Exemple #9
0
def getPlotCSV():
    print("new request to build CSV file")
    data = json.loads(str(request.data))
    sheet = pe.Sheet(data)
    io = StringIO.StringIO()
    sheet.save_to_memory("csv", io)

    output = make_response(io.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=export.csv"
    output.headers["Content-type"] = "text/csv"
    #return send_file("Adjacency.csv", as_attachment=True, mimetype='application/vnd.ms-excel')
    return output
Exemple #10
0
 def test_row_value_filter(self):
     data = [
         ['a', 'b', 'c', 'd', 'e'],
         [1, 2, 3, 4, 1]
     ]
     sheet = pe.Sheet(data)
     sheet.filter(pe.ColumnValueFilter(lambda column: column[1] == 1).invert())
     expected = [
         ['a', 'e'],
         [1, 1]
     ]
     assert sheet.to_array() == expected
Exemple #11
0
 def test_row_series(self):
     content = [["Row 1", 1, 2, 3], ["Row 2", 4, 5, 6], ["Row 3", 7, 8, 9]]
     s = pe.Sheet(content, name_rows_by_column=0)
     content = dedent("""
         Sheet Name: pyexcel
         +-------+---+---+---+
         | Row 1 | 1 | 2 | 3 |
         +-------+---+---+---+
         | Row 2 | 4 | 5 | 6 |
         +-------+---+---+---+
         | Row 3 | 7 | 8 | 9 |
         +-------+---+---+---+""").strip('\n')
     assert str(s) == content
Exemple #12
0
 def test_normal_usage(self):
     content = [[1, 2, 3], [4, 588, 6], [7, 8, 999]]
     s = pe.Sheet(content)
     content = dedent("""
        Sheet Name: pyexcel
        +---+-----+-----+
        | 1 | 2   | 3   |
        +---+-----+-----+
        | 4 | 588 | 6   |
        +---+-----+-----+
        | 7 | 8   | 999 |
        +---+-----+-----+""").strip('\n')
     assert str(s) == content
Exemple #13
0
 def test_normal_usage2(self):
     data = [
         # 0 1  2  3  4 5   6
         [1, 2, 3, 4, 5, 6, 7],  #  0
         [21, 22, 23, 24, 25, 26, 27],
         [31, 32, 33, 34, 35, 36, 37],
         [41, 42, 43, 44, 45, 46, 47],
         [51, 52, 53, 54, 55, 56, 57]  # 4
     ]
     s = pe.Sheet(data)
     s.add_filter(pe.filters.RegionFilter(slice(1, 4, 1), slice(1, 5, 1)))
     expected = [[22, 23, 24, 25], [32, 33, 34, 35], [42, 43, 44, 45]]
     assert s.to_array() == expected
Exemple #14
0
 def test_normal_usage(self):
     content = [[1, 2, 3], [4, 588, 6], [7, 8, 999]]
     s = pe.Sheet(content)
     # note, plus the trailing '\n'
     # due to tabluate produces the extra new line
     content = dedent("""
         pyexcel sheet:
         -  ---  ---
         1    2    3
         4  588    6
         7    8  999
         -  ---  ---""").strip('\n')
     self.assertEqual(s.simple, content)
Exemple #15
0
    def test_save_an_array7(self):
        data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
        sheet = pe.Sheet(data)
        sheet.name_columns_by_row(0)

        def make_signature(row):
            return Signature(X=row["X"], Y=row["Y"], Z=row["Z"])

        sheet.save_to_database(self.session,
                               Signature,
                               initializer=make_signature)
        result = pe.get_dict(session=self.session, table=Signature)
        assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
Exemple #16
0
 def test_region(self):
     data = [
         # 0 1  2  3  4 5   6
         [1, 2, 3, 4, 5, 6, 7],  #  0
         [21, 22, 23, 24, 25, 26, 27],
         [31, 32, 33, 34, 35, 36, 37],
         [41, 42, 43, 44, 45, 46, 47],
         [51, 52, 53, 54, 55, 56, 57]  # 4
     ]
     s = pe.Sheet(data)
     data = s.region([1, 1], [4, 5])
     expected = [[22, 23, 24, 25], [32, 33, 34, 35], [42, 43, 44, 45]]
     assert data == expected
Exemple #17
0
 def test_normal_usage(self):
     content = [[1, 2, 3], [4, 588, 6], [7, 8, 999]]
     s = pe.Sheet(content)
     content = dedent("""
        pyexcel sheet:
        +---+-----+-----+
        | 1 | 2   | 3   |
        +---+-----+-----+
        | 4 | 588 | 6   |
        +---+-----+-----+
        | 7 | 8   | 999 |
        +---+-----+-----+""").strip("\n")
     self.assertEqual(str(s), content)
Exemple #18
0
 def test_save_as_and_append_colnames(self):
     data = [[1, 2, 3], [4, 5, 6]]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     testfile2 = "testfile.xls"
     sheet.save_as(testfile)
     pe.save_as(
         file_name=testfile,
         dest_file_name=testfile2,
         colnames=["X", "Y", "Z"],
     )
     array = pe.get_array(file_name=testfile2)
     eq_(array, [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]])
Exemple #19
0
 def test_row_series(self):
     content = [["Row 1", 1, 2, 3], ["Row 2", 4, 5, 6], ["Row 3", 7, 8, 9]]
     s = pe.Sheet(content, name_rows_by_column=0)
     content = dedent("""
         pyexcel sheet:
         +-------+---+---+---+
         | Row 1 | 1 | 2 | 3 |
         +-------+---+---+---+
         | Row 2 | 4 | 5 | 6 |
         +-------+---+---+---+
         | Row 3 | 7 | 8 | 9 |
         +-------+---+---+---+""").strip("\n")
     self.assertEqual(str(s), content)
Exemple #20
0
def usufyToTextExport(d, fPath=None):
    """
    Workaround to export to a .txt file or to show the information.

    Args:
    -----
        d: Data to export.
        fPath: File path for the output file. If None was provided, it will
            assume that it has to print it.

    Returns:
    --------
        unicode: It sometimes returns a unicode representation of the Sheet
            received.
    """
    # Manual check...
    if d == []:
        return "+------------------+\n| No data found... |\n+------------------+"

    import pyexcel as pe
    import pyexcel.ext.text as text

    if fPath == None:
        isTerminal = True
    else:
        isTerminal = False

    try:
        oldData = get_data(fPath)
    except:
        # No information has been recovered
        oldData = {"OSRFramework": []}

    # Generating the new tabular data
    tabularData = _generateTabularData(d, {"OSRFramework": [[]]},
                                       True,
                                       canUnicode=False)

    # The tabular data contains a dict representing the whole book and we need only the sheet!!
    sheet = pe.Sheet(tabularData["OSRFramework"])
    sheet.name = "Profiles recovered (" + getCurrentStrDatetime() + ")."
    # Defining the headers
    sheet.name_columns_by_row(0)
    text.TABLEFMT = "grid"

    try:
        with open(fPath, "w") as oF:
            oF.write(str(sheet))
    except Exception as e:
        # If a fPath was not provided... We will only print the info:
        return unicode(sheet)
 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]
Exemple #22
0
 def test_issue_03(self):
     file_prefix = "issue_03_test"
     csv_file = "%s.csv" % file_prefix
     xls_file = "%s.xls" % file_prefix
     my_sheet_name = "mysheetname"
     data = [[1,1]]
     sheet = pe.Sheet(data, name=my_sheet_name)
     sheet.save_as(csv_file)
     assert(os.path.exists(csv_file))
     sheet.save_as(xls_file)
     book = pe.load_book(xls_file)
     assert book.sheet_names()[0] == my_sheet_name
     os.unlink(csv_file)
     os.unlink(xls_file)
Exemple #23
0
 def test_row_value_filter(self):
     data = [
         ['a', 1, 2, 3, 4, 1],
         ['b', 2, 3, 4, 5, 7],
         ['c', 3, 4, 5, 6, 7]
     ]
     sheet = pe.Sheet(data, name_rows_by_column=0)
     sheet.filter(pe.NamedColumnValueFilter(lambda column: column['a'] == 3).invert())
     expected = [
         ['a', 3],
         ['b', 4],
         ['c', 5]
     ]
     assert sheet.to_array() == expected
Exemple #24
0
 def setUp(self):
     file_type = "xlsx"
     io = BytesIO()
     self.content = [
         ["X", "Y", "Z"],
         [1, 11, 12],
         [2, 21, 22],
         [3, 31, 32],
         [4, 41, 42],
         [5, 51, 52]
     ]
     sheet = pe.Sheet(pe.transpose(self.content), name_rows_by_column=0)
     sheet.save_to_memory(file_type, io)
     self.test_tuple = (file_type, io.getvalue())
Exemple #25
0
 def test_empty_series_reader(self):
     # debug this further
     s = p.Sheet()  # seriesreader is gone since v0.0.7
     assert s.name == "pyexcel sheet"
     test_data = [
         [1, 2, 3],
         [4, 5, 6],
         ["Column 1", "Column 2", "Column 3"],
     ]
     s.column += p.internal.sheets.transpose(test_data)
     actual = s.array
     assert test_data == actual
     s.name_columns_by_row(2)
     assert s.colnames == test_data[2]
Exemple #26
0
 def test_irregular_usage(self):
     """textable doesn't like empty string """
     content = [[1, 2, 3], [4, 588, 6], [7, 8]]  # one empty string
     s = pe.Sheet(content)
     content = dedent("""
        pyexcel sheet:
        +---+-----+---+
        | 1 | 2   | 3 |
        +---+-----+---+
        | 4 | 588 | 6 |
        +---+-----+---+
        | 7 | 8   |   |
        +---+-----+---+""").strip("\n")
     self.assertEqual(str(s), content)
Exemple #27
0
 def test_row_value_filter(self):
     data = [
         ['a', 'b', 'c', 'd', 'e'],
         [1, 2, 3, 4, 1],
         [2, 3, 4, 5, 7],
         [3, 4, 5, 6, 7]
     ]
     sheet = pe.Sheet(data, name_columns_by_row=0)
     sheet.filter(pe.NamedRowValueFilter(lambda row: row['a'] == 3).invert())
     expected = [
         ['a', 'b', 'c', 'd', 'e'],
         [3, 4, 5, 6, 7]
     ]
     assert sheet.to_array() == expected
Exemple #28
0
 def test_get_records_from_file(self):
     data = [
         ["X", "Y", "Z"],
         [1, 2, 3],
         [4, 5, 6]
     ]
     sheet = pe.Sheet(data)
     testfile = "testfile.xls"
     sheet.save_as(testfile)
     result = pe.iget_records(file_name=testfile)
     eq_(list(result), [
         {"X": 1, "Y": 2, "Z": 3},
         {"X": 4, "Y": 5, "Z": 6}
     ])
     os.unlink(testfile)
Exemple #29
0
def json_to_csv(res):

    d = []
    l = len(res["results"])
    d.append((res["results"][0]["_source"]).keys())
    for i in range(l):
        d.append((res["results"][i]["_source"]).values())

    sheet = pe.Sheet(d)
    io = StringIO.StringIO()
    sheet.save_to_memory("csv", io)
    output = make_response(io.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=convert.csv"
    output.headers["Content-type"] = "text/csv"
    return output
Exemple #30
0
 def export_ods(self) -> None:
     print(f'Ukládám výsledky do ODS ...')
     # save main values
     table = self.export_table_data()
     sheet = pyexcel.Sheet(name='stats', sheet=table, name_columns_by_row=0)
     sheet.save_as(self.args.output)
     # add formulas (pyexcel can't do those, use ezodf instead)
     doc = ezodf.opendoc(self.args.output)
     doc.backup = False
     sheet = doc.sheets[0]
     sheet.append_rows(2)
     lastrow = sheet.nrows() - 1
     sheet[lastrow, 2].formula = '=SUBTOTAL(9;C:C)'
     sheet[lastrow, 3].formula = '=SUBTOTAL(9;D:D)'
     sheet[lastrow, 4].formula = '=SUBTOTAL(9;E:E)'
     doc.save()