"""
    pyexcel_xlsx
    ~~~~~~~~~~~~~~~~~~~

    The lower level xlsx file format handler using openpyxl

    :copyright: (c) 2015-2016 by Onni Software Ltd & its contributors
    :license: New BSD License
"""
import sys
from pyexcel_io.book import BookReader, BookWriter
from pyexcel_io.sheet import SheetReader, SheetWriter
import openpyxl

PY27_BELOW = sys.version_info[0] == 2 and sys.version_info[1] < 7
if PY27_BELOW:
    from ordereddict import OrderedDict
else:
    from collections import OrderedDict


COLUMNS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
COLUMN_LENGTH = 26


def get_columns(index):
    """
    Convert column index to column name
    """
    if index < COLUMN_LENGTH:
        return COLUMNS[index]
    else:
        return (get_columns(int(index // COLUMN_LENGTH) - 1) +
                COLUMNS[index % COLUMN_LENGTH])


class XLSXSheet(SheetReader):
    """
    xls sheet
    """
    @property
    def name(self):
        """sheet name"""
        return self.native_sheet.title

    def number_of_rows(self):
        """
        Number of rows in the xls sheet
        """
        return self.native_sheet.max_row

    def number_of_columns(self):
        """
        Number of columns in the xls sheet
        """
        return self.native_sheet.max_column

    def _cell_value(self, row, column):
        """
        Random access to the xls cells
        """
        actual_row = row + 1
        cell_location = "%s%d" % (get_columns(column), actual_row)
        return self.native_sheet.cell(cell_location).value


class XLSXBook(BookReader):
    """
    XLSBook reader

    It reads xls, xlsm, xlsx work book
    """
    def open(self, file_name, **keywords):
        BookReader.open(self, file_name, **keywords)
        self._get_params()
        self._load_from_file()

    def open_stream(self, file_stream, **keywords):
        BookReader.open_stream(self, file_stream, **keywords)
        self._get_params()
        self._load_from_memory()

    def read_sheet_by_name(self, sheet_name):
        sheet = self.native_book.get_sheet_by_name(sheet_name)
        if sheet is None:
            raise ValueError("%s cannot be found" % sheet_name)
        else:
            return self.read_sheet(sheet)

    def read_sheet_by_index(self, sheet_index):
        names = self.native_book.sheetnames
        length = len(names)
        if sheet_index < length:
            return self.read_sheet_by_name(names[sheet_index])
        else:
            raise IndexError("Index %d of out bound %d" % (
                sheet_index,
                length))

    def read_all(self):
        result = OrderedDict()
        for sheet in self.native_book:
            if self.skip_hidden_sheets and sheet.sheet_state == 'hidden':
                continue
            data_dict = self.read_sheet(sheet)
            result.update(data_dict)
        return result

    def read_sheet(self, native_sheet):
        sheet = XLSXSheet(native_sheet, **self.keywords)
        return {sheet.name: sheet.to_array()}

    def _load_from_memory(self):
        self.native_book = openpyxl.load_workbook(filename=self.file_stream,
                                                  data_only=True)

    def _load_from_file(self):
        self.native_book = openpyxl.load_workbook(filename=self.file_name,
                                                  data_only=True)

    def _get_params(self):
        self.skip_hidden_sheets = self.keywords.get('skip_hidden_sheets', True)


class XLSXSheetWriter(SheetWriter):
    """
    xls, xlsx and xlsm sheet writer
    """
    def set_sheet_name(self, name):
        self.native_sheet.title = name
        self.current_row = 1

    def write_row(self, array):
        """
        write a row into the file
        """
        for i in range(0, len(array)):
            cell_location = "%s%d" % (get_columns(i), self.current_row)
            self.native_sheet.cell(cell_location).value = array[i]
        self.current_row += 1


class XLSXWriter(BookWriter):
    """
    xls, xlsx and xlsm writer
    """
    def __init__(self):
        BookWriter.__init__(self)
        self.current_sheet = 0
        self.native_book = None

    def open(self, file_name, **keywords):
        BookWriter.open(self, file_name, **keywords)
        self.native_book = openpyxl.Workbook()

    def create_sheet(self, name):
        if self.current_sheet == 0:
            self.current_sheet = 1
            return XLSXSheetWriter(self.native_book,
                                   self.native_book.active, name)
        else:
            return XLSXSheetWriter(self.native_book,
                                   self.native_book.create_sheet(), name)

    def close(self):
        """
        This call actually save the file
        """
        self.native_book.save(filename=self.file_alike_object)

_XLSX_MIME = (
    "application/" +
    "vnd.openxmlformats-officedocument.spreadsheetml.sheet")

_xlsx_registry = {
    "file_type": "xlsx",
    "reader": XLSXBook,
    "writer": XLSXWriter,
    "stream_type": "binary",
    "mime_type": _XLSX_MIME,
    "library": "pyexcel-xlsx"
}

_xlsm_registry = {
    "file_type": "xlsm",
    "reader": XLSXBook,
    "writer": XLSXWriter,
    "stream_type": "binary",
    "mime_type": "application/vnd.ms-excel.sheet.macroenabled.12",
    "library": "pyexcel-xlsx"
}

exports = (_xlsx_registry, _xlsm_registry)

@@ -3761,207 +3761,39 @@
     
-for i in range(0, len(array)):%0A            cell_location = %22%25s%25d%22 %25 (get_columns(i), self.current_row)%0A            self.native_sheet.cell(cell_location).value = array%5Bi%5D%0A        self.current_row += 1
+self.native_sheet.append(array)
 %0A%0A%0Ac
@@ -4130,16 +4130,31 @@
 orkbook(
+write_only=True
 )%0A%0A    d
@@ -4186,216 +4186,8 @@
 e):%0A
-        if self.current_sheet == 0:%0A            self.current_sheet = 1%0A            return XLSXSheetWriter(self.native_book,%0A                                   self.native_book.active, name)%0A        else:%0A    
     
@@ -4231,20 +4231,16 @@
 e_book,%0A
-    
         

resolve #11 use write only flag to run optimized writing