Source code for pyexcel_webio

"""
    pyexcel.ext.webio
    ~~~~~~~~~~~~~~~~~~~

    A generic request and response interface for pyexcel web extensions

    :copyright: (c) 2015 by Onni Software Ltd.
    :license: New BSD License
"""
import pyexcel as pe
import sys
PY2 = sys.version_info[0] == 2
if PY2:
    from StringIO import StringIO as BytesIO
else:
    from io import BytesIO

    
FILE_TYPE_MIME_TABLE = {
    "csv": "text/csv",
    "tsv": "text/tab-separated-values",
    "csvz": "application/zip",
    "tsvz": "application/zip",
    "ods": "application/vnd.oasis.opendocument.spreadsheet",
    "xls": "application/vnd.ms-excel",
    "xlsx": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "xlsm": "application/vnd.ms-excel.sheet.macroenabled.12"
}


class ExcelInput(object):
    """A generic interface for an excel file to be converted

    The source could be from anywhere, memory or file system
    """
    def load_single_sheet(self, sheet_name=None, **keywords):
        """Abstract method
        
        :param form_field_name: the file field name in the html form for file upload
        :param sheet_name: For an excel book, there could be multiple sheets. If it is left
                         unspecified, the sheet at index 0 is loaded. For 'csv', 'tsv' file,
                         *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A sheet object
        """
        raise NotImplementedError("Please implement this function")

    def load_book(self, **keywords):
        """Abstract method
        
        :param form_field_name: the file field name in the html form for file upload
        :param keywords: additional key words
        :returns: A instance of :class:`Book`
        """
        raise NotImplementedError("Please implement this function")

    def get_sheet(self, sheet_name=None, **keywords):
        """
        Get a :class:`Sheet` instance from the file
        
        :param form_field_name: the file field name in the html form for file upload
        :param sheet_name: For an excel book, there could be multiple sheets. If it is left
                         unspecified, the sheet at index 0 is loaded. For 'csv', 'tsv' file,
                         *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A sheet object
        """
        return self.load_single_sheet(sheet_name=sheet_name, **keywords)
        
    def get_array(self, sheet_name=None, **keywords):
        """
        Get a list of lists from the file
        
        :param form_field_name: the file field name in the html form for file upload
        :param sheet_name: For an excel book, there could be multiple sheets. If it is left
                         unspecified, the sheet at index 0 is loaded. For 'csv', 'tsv' file,
                         *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A list of lists
        """
        sheet = self.get_sheet(sheet_name=sheet_name, **keywords)
        if sheet:
            return sheet.to_array()
        else:
            return None

    def get_dict(self, sheet_name=None, name_columns_by_row=0, **keywords):
        """Get a dictionary from the file
        
        :param form_field_name: the file field name in the html form for file upload
        :param sheet_name: For an excel book, there could be multiple sheets. If it is left
                         unspecified, the sheet at index 0 is loaded. For 'csv', 'tsv' file,
                         *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A dictionary
        """
        sheet = self.load_single_sheet(sheet_name=sheet_name, name_columns_by_row=name_columns_by_row, **keywords)
        if sheet:
            return sheet.to_dict()
        else:
            return None

    def get_records(self, sheet_name=None, name_columns_by_row=0, **keywords):
        """Get a list of records from the file
  
        :param form_field_name: the file field name in the html form for file upload
        :param sheet_name: For an excel book, there could be multiple sheets. If it is left
                         unspecified, the sheet at index 0 is loaded. For 'csv', 'tsv' file,
                         *sheet_name* should be None anyway.
        :param keywords: additional key words
        :returns: A list of records
        """
        sheet = self.load_single_sheet(sheet_name=sheet_name, name_columns_by_row=name_columns_by_row, **keywords)
        if sheet:
            return sheet.to_records()
        else:
            return None

    def save_to_database(
            self,
            session=None, table=None, initializer=None, mapdict=None, 
            sheet_name=None, name_columns_by_row=0, name_rows_by_column=-1,
            field_name=None, **keywords):
        sheet = self.load_single_sheet(
            field_name=field_name,
            sheet_name=sheet_name,
            name_columns_by_row=name_columns_by_row,
            name_rows_by_column=name_rows_by_column,
            **keywords)
        if sheet:
            sheet.save_to_database(session, table, initializer, mapdict)

    def get_book(self, **keywords):
        """Get a instance of :class:`Book` from the file

        :param form_field_name: the file field name in the html form for file upload
        :param keywords: additional key words
        :returns: A instance of :class:`Book`
        """
        return self.load_book(**keywords)

    def get_book_dict(self, **keywords):
        """Get a dictionary of two dimensional array from the file

        :param form_field_name: the file field name in the html form for file upload
        :param keywords: additional key words
        :returns: A dictionary of two dimensional arrays
        """
        book = self.load_book(**keywords)
        if book:
            return book.to_dict()
        else:
            return None

    def save_book_to_database(self, session=None, tables=None, initializers=None, mapdicts=None, **keywords):
        book = self.load_book(**keywords)
        if book:
            book.save_to_database(session, tables, initializers=initializers, mapdicts=mapdicts)


def dummy_func(content, content_type=None, status=200):
    return None


ExcelResponse = dummy_func


[docs]def make_response(pyexcel_instance, file_type, status=200, **keywords): """Make a http response from a pyexcel instance of :class:`~pyexcel.Sheet` or :class:`~pyexcel.Book` :param pyexcel_instance: pyexcel.Sheet or pyexcel.Book :param file_type: one of the following strings: * 'csv' * 'tsv' * 'csvz' * 'tsvz' * 'xls' * 'xlsx' * 'xlsm' * 'ods' :param status: unless a different status is to be returned. """ io = pe.get_io(file_type) pyexcel_instance.save_to_memory(file_type, io, **keywords) io.seek(0) return ExcelResponse(io.read(), content_type=FILE_TYPE_MIME_TABLE[file_type], status=status)
[docs]def make_response_from_array(array, file_type, status=200, **keywords): return make_response(pe.Sheet(array), file_type, status, **keywords)
[docs]def make_response_from_dict(adict, file_type, status=200, **keywords): return make_response(pe.load_from_dict(adict), file_type, status, **keywords)
[docs]def make_response_from_records(records, file_type, status=200, **keywords): return make_response(pe.load_from_records(records), file_type, status, **keywords)
[docs]def make_response_from_book_dict(adict, file_type, status=200, **keywords): return make_response(pe.Book(adict), file_type, status, **keywords)
[docs]def make_response_from_query_sets(query_sets, column_names, file_type, status=200, **keywords): sheet = pe.get_sheet(query_sets=query_sets, column_names=column_names) return make_response(sheet, file_type, status, **keywords)
[docs]def make_response_from_a_table(session, table, file_type, status=200, **keywords): sheet = pe.get_sheet(session=session, table=table, **keywords) return make_response(sheet, file_type, status, **keywords)
[docs]def make_response_from_tables(session, tables, file_type, status=200, **keywords): book = pe.get_book(session=session, tables=tables, **keywords) return make_response(book, file_type, status, **keywords)