from datetime import date, datetime, timedelta
from functools import partial
from io import BytesIO
import os

import numpy as np
from numpy import nan
import pytest

from pandas.compat import PY36
import pandas.util._test_decorators as td

import pandas as pd
from pandas import DataFrame, Index, MultiIndex, get_option, set_option
import pandas.util.testing as tm
from pandas.util.testing import ensure_clean, makeCustomDataframe as mkdf

from pandas.io.excel import (
    ExcelFile, ExcelWriter, _OpenpyxlWriter, _XlsxWriter, _XlwtWriter,
    register_writer)


@td.skip_if_no('xlrd')
@pytest.mark.parametrize("ext", ['.xls', '.xlsx', '.xlsm'])
class TestRoundTrip:

    @td.skip_if_no("xlwt")
    @td.skip_if_no("openpyxl")
    @pytest.mark.parametrize("header,expected", [
        (None, DataFrame([np.nan] * 4)),
        (0, DataFrame({"Unnamed: 0": [np.nan] * 3}))
    ])
    def test_read_one_empty_col_no_header(self, ext, header, expected):
        # xref gh-12292
        filename = "no_header"
        df = pd.DataFrame(
            [["", 1, 100],
             ["", 2, 200],
             ["", 3, 300],
             ["", 4, 400]]
        )

        with ensure_clean(ext) as path:
            df.to_excel(path, filename, index=False, header=False)
            result = pd.read_excel(path, filename, usecols=[0], header=header)

        tm.assert_frame_equal(result, expected)

    @td.skip_if_no("xlwt")
    @td.skip_if_no("openpyxl")
    @pytest.mark.parametrize("header,expected", [
        (None, DataFrame([0] + [np.nan] * 4)),
        (0, DataFrame([np.nan] * 4))
    ])
    def test_read_one_empty_col_with_header(self, ext, header, expected):
        filename = "with_header"
        df = pd.DataFrame(
            [["", 1, 100],
             ["", 2, 200],
             ["", 3, 300],
             ["", 4, 400]]
        )

        with ensure_clean(ext) as path:
            df.to_excel(path, 'with_header', index=False, header=True)
            result = pd.read_excel(path, filename, usecols=[0], header=header)

        tm.assert_frame_equal(result, expected)

    @td.skip_if_no('openpyxl')
    @td.skip_if_no('xlwt')
    def test_set_column_names_in_parameter(self, ext):
        # GH 12870 : pass down column names associated with
        # keyword argument names
        refdf = pd.DataFrame([[1, 'foo'], [2, 'bar'],
                              [3, 'baz']], columns=['a', 'b'])

        with ensure_clean(ext) as pth:
            with ExcelWriter(pth) as writer:
                refdf.to_excel(writer, 'Data_no_head',
                               header=False, index=False)
                refdf.to_excel(writer, 'Data_with_head', index=False)

            refdf.columns = ['A', 'B']

            with ExcelFile(pth) as reader:
                xlsdf_no_head = pd.read_excel(reader, 'Data_no_head',
                                              header=None, names=['A', 'B'])
                xlsdf_with_head = pd.read_excel(
                    reader, 'Data_with_head', index_col=None, names=['A', 'B'])

            tm.assert_frame_equal(xlsdf_no_head, refdf)
            tm.assert_frame_equal(xlsdf_with_head, refdf)

    @td.skip_if_no("xlwt")
    @td.skip_if_no("openpyxl")
    def test_creating_and_reading_multiple_sheets(self, ext):
        # see gh-9450
        #
        # Test reading multiple sheets, from a runtime
        # created Excel file with multiple sheets.
        def tdf(col_sheet_name):
            d, i = [11, 22, 33], [1, 2, 3]
            return DataFrame(d, i, columns=[col_sheet_name])

        sheets = ["AAA", "BBB", "CCC"]

        dfs = [tdf(s) for s in sheets]
        dfs = dict(zip(sheets, dfs))

        with ensure_clean(ext) as pth:
            with ExcelWriter(pth) as ew:
                for sheetname, df in dfs.items():
                    df.to_excel(ew, sheetname)

            dfs_returned = pd.read_excel(pth, sheet_name=sheets, index_col=0)

            for s in sheets:
                tm.assert_frame_equal(dfs[s], dfs_returned[s])

    @td.skip_if_no("xlsxwriter")
    def test_read_excel_multiindex_empty_level(self, ext):
        # see gh-12453
        with ensure_clean(ext) as path:
            df = DataFrame({
                ("One", "x"): {0: 1},
                ("Two", "X"): {0: 3},
                ("Two", "Y"): {0: 7},
                ("Zero", ""): {0: 0}
            })

            expected = DataFrame({
                ("One", "x"): {0: 1},
                ("Two", "X"): {0: 3},
                ("Two", "Y"): {0: 7},
                ("Zero", "Unnamed: 4_level_1"): {0: 0}
            })

            df.to_excel(path)
            actual = pd.read_excel(path, header=[0, 1], index_col=0)
            tm.assert_frame_equal(actual, expected)

            df = pd.DataFrame({
                ("Beg", ""): {0: 0},
                ("Middle", "x"): {0: 1},
                ("Tail", "X"): {0: 3},
                ("Tail", "Y"): {0: 7}
            })

            expected = pd.DataFrame({
                ("Beg", "Unnamed: 1_level_1"): {0: 0},
                ("Middle", "x"): {0: 1},
                ("Tail", "X"): {0: 3},
                ("Tail", "Y"): {0: 7}
            })

            df.to_excel(path)
            actual = pd.read_excel(path, header=[0, 1], index_col=0)
            tm.assert_frame_equal(actual, expected)

    @td.skip_if_no("xlsxwriter")
    @pytest.mark.parametrize("c_idx_names", [True, False])
    @pytest.mark.parametrize("r_idx_names", [True, False])
    @pytest.mark.parametrize("c_idx_levels", [1, 3])
    @pytest.mark.parametrize("r_idx_levels", [1, 3])
    def test_excel_multindex_roundtrip(self, ext, c_idx_names, r_idx_names,
                                       c_idx_levels, r_idx_levels):
        # see gh-4679
        with ensure_clean(ext) as pth:
            if c_idx_levels == 1 and c_idx_names:
                pytest.skip("Column index name cannot be "
                            "serialized unless it's a MultiIndex")

            # Empty name case current read in as
            # unnamed levels, not Nones.
            check_names = r_idx_names or r_idx_levels <= 1

            df = mkdf(5, 5, c_idx_names, r_idx_names,
                      c_idx_levels, r_idx_levels)
            df.to_excel(pth)

            act = pd.read_excel(pth, index_col=list(range(r_idx_levels)),
                                header=list(range(c_idx_levels)))
            tm.assert_frame_equal(df, act, check_names=check_names)

            df.iloc[0, :] = np.nan
            df.to_excel(pth)

            act = pd.read_excel(pth, index_col=list(range(r_idx_levels)),
                                header=list(range(c_idx_levels)))
            tm.assert_frame_equal(df, act, check_names=check_names)

            df.iloc[-1, :] = np.nan
            df.to_excel(pth)
            act = pd.read_excel(pth, index_col=list(range(r_idx_levels)),
                                header=list(range(c_idx_levels)))
            tm.assert_frame_equal(df, act, check_names=check_names)

    @td.skip_if_no("xlwt")
    @td.skip_if_no("openpyxl")
    def test_read_excel_parse_dates(self, ext):
        # see gh-11544, gh-12051
        df = DataFrame(
            {"col": [1, 2, 3],
             "date_strings": pd.date_range("2012-01-01", periods=3)})
        df2 = df.copy()
        df2["date_strings"] = df2["date_strings"].dt.strftime("%m/%d/%Y")

        with ensure_clean(ext) as pth:
            df2.to_excel(pth)

            res = pd.read_excel(pth, index_col=0)
            tm.assert_frame_equal(df2, res)

            res = pd.read_excel(pth, parse_dates=["date_strings"], index_col=0)
            tm.assert_frame_equal(df, res)

            date_parser = lambda x: pd.datetime.strptime(x, "%m/%d/%Y")
            res = pd.read_excel(pth, parse_dates=["date_strings"],
                                date_parser=date_parser, index_col=0)
            tm.assert_frame_equal(df, res)


class _WriterBase:

    @pytest.fixture(autouse=True)
    def set_engine_and_path(self, request, engine, ext):
        """Fixture to set engine and open file for use in each test case

        Rather than requiring `engine=...` to be provided explicitly as an
        argument in each test, this fixture sets a global option to dictate
        which engine should be used to write Excel files. After executing
        the test it rolls back said change to the global option.

        It also uses a context manager to open a temporary excel file for
        the function to write to, accessible via `self.path`

        Notes
        -----
        This fixture will run as part of each test method defined in the
        class and any subclasses, on account of the `autouse=True`
        argument
        """
        option_name = 'io.excel.{ext}.writer'.format(ext=ext.strip('.'))
        prev_engine = get_option(option_name)
        set_option(option_name, engine)
        with ensure_clean(ext) as path:
            self.path = path
            yield
        set_option(option_name, prev_engine)  # Roll back option change


@pytest.mark.parametrize("engine,ext", [
    pytest.param('openpyxl', '.xlsx', marks=pytest.mark.skipif(
        not td.safe_import('openpyxl'), reason='No openpyxl')),
    pytest.param('openpyxl', '.xlsm', marks=pytest.mark.skipif(
        not td.safe_import('openpyxl'), reason='No openpyxl')),
    pytest.param('xlwt', '.xls', marks=pytest.mark.skipif(
        not td.safe_import('xlwt'), reason='No xlwt')),
    pytest.param('xlsxwriter', '.xlsx', marks=pytest.mark.skipif(
        not td.safe_import('xlsxwriter'), reason='No xlsxwriter'))
])
class TestExcelWriter(_WriterBase):
    # Base class for test cases to run with different Excel writers.

    def test_excel_sheet_size(self, engine, ext):

        # GH 26080
        breaking_row_count = 2**20 + 1
        breaking_col_count = 2**14 + 1
        # purposely using two arrays to prevent memory issues while testing
        row_arr = np.zeros(shape=(breaking_row_count, 1))
        col_arr = np.zeros(shape=(1, breaking_col_count))
        row_df = pd.DataFrame(row_arr)
        col_df = pd.DataFrame(col_arr)

        msg = "sheet is too large"
        with pytest.raises(ValueError, match=msg):
            row_df.to_excel(self.path)

        with pytest.raises(ValueError, match=msg):
            col_df.to_excel(self.path)

    def test_excel_sheet_by_name_raise(self, engine, ext):
        import xlrd

        gt = DataFrame(np.random.randn(10, 2))
        gt.to_excel(self.path)

        xl = ExcelFile(self.path)
        df = pd.read_excel(xl, 0, index_col=0)

        tm.assert_frame_equal(gt, df)

        with pytest.raises(xlrd.XLRDError):
            pd.read_excel(xl, "0")

    def test_excel_writer_context_manager(self, frame, engine, ext):
        with ExcelWriter(self.path) as writer:
            frame.to_excel(writer, "Data1")
            frame2 = frame.copy()
            frame2.columns = frame.columns[::-1]
            frame2.to_excel(writer, "Data2")

        with ExcelFile(self.path) as reader:
            found_df = pd.read_excel(reader, "Data1", index_col=0)
            found_df2 = pd.read_excel(reader, "Data2", index_col=0)

            tm.assert_frame_equal(found_df, frame)
            tm.assert_frame_equal(found_df2, frame2)

    def test_roundtrip(self, engine, ext, frame):
        frame = frame.copy()
        frame['A'][:5] = nan

        frame.to_excel(self.path, 'test1')
        frame.to_excel(self.path, 'test1', columns=['A', 'B'])
        frame.to_excel(self.path, 'test1', header=False)
        frame.to_excel(self.path, 'test1', index=False)

        # test roundtrip
        frame.to_excel(self.path, 'test1')
        recons = pd.read_excel(self.path, 'test1', index_col=0)
        tm.assert_frame_equal(frame, recons)

        frame.to_excel(self.path, 'test1', index=False)
        recons = pd.read_excel(self.path, 'test1', index_col=None)
        recons.index = frame.index
        tm.assert_frame_equal(frame, recons)

        frame.to_excel(self.path, 'test1', na_rep='NA')
        recons = pd.read_excel(
            self.path, 'test1', index_col=0, na_values=['NA'])
        tm.assert_frame_equal(frame, recons)

        # GH 3611
        frame.to_excel(self.path, 'test1', na_rep='88')
        recons = pd.read_excel(
            self.path, 'test1', index_col=0, na_values=['88'])
        tm.assert_frame_equal(frame, recons)

        frame.to_excel(self.path, 'test1', na_rep='88')
        recons = pd.read_excel(
            self.path, 'test1', index_col=0, na_values=[88, 88.0])
        tm.assert_frame_equal(frame, recons)

        # GH 6573
        frame.to_excel(self.path, 'Sheet1')
        recons = pd.read_excel(self.path, index_col=0)
        tm.assert_frame_equal(frame, recons)

        frame.to_excel(self.path, '0')
        recons = pd.read_excel(self.path, index_col=0)
        tm.assert_frame_equal(frame, recons)

        # GH 8825 Pandas Series should provide to_excel method
        s = frame["A"]
        s.to_excel(self.path)
        recons = pd.read_excel(self.path, index_col=0)
        tm.assert_frame_equal(s.to_frame(), recons)

    def test_mixed(self, engine, ext, frame):
        mixed_frame = frame.copy()
        mixed_frame['foo'] = 'bar'

        mixed_frame.to_excel(self.path, 'test1')
        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, 'test1', index_col=0)
        tm.assert_frame_equal(mixed_frame, recons)

    def test_ts_frame(self, tsframe, engine, ext):
        df = tsframe

        df.to_excel(self.path, "test1")
        reader = ExcelFile(self.path)

        recons = pd.read_excel(reader, "test1", index_col=0)
        tm.assert_frame_equal(df, recons)

    def test_basics_with_nan(self, engine, ext, frame):
        frame = frame.copy()
        frame['A'][:5] = nan
        frame.to_excel(self.path, 'test1')
        frame.to_excel(self.path, 'test1', columns=['A', 'B'])
        frame.to_excel(self.path, 'test1', header=False)
        frame.to_excel(self.path, 'test1', index=False)

    @pytest.mark.parametrize("np_type", [
        np.int8, np.int16, np.int32, np.int64])
    def test_int_types(self, engine, ext, np_type):
        # Test np.int values read come back as int
        # (rather than float which is Excel's format).
        df = DataFrame(np.random.randint(-10, 10, size=(10, 2)),
                       dtype=np_type)
        df.to_excel(self.path, "test1")

        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, "test1", index_col=0)

        int_frame = df.astype(np.int64)
        tm.assert_frame_equal(int_frame, recons)

        recons2 = pd.read_excel(self.path, "test1", index_col=0)
        tm.assert_frame_equal(int_frame, recons2)

        # Test with convert_float=False comes back as float.
        float_frame = df.astype(float)
        recons = pd.read_excel(self.path, "test1",
                               convert_float=False, index_col=0)
        tm.assert_frame_equal(recons, float_frame,
                              check_index_type=False,
                              check_column_type=False)

    @pytest.mark.parametrize("np_type", [
        np.float16, np.float32, np.float64])
    def test_float_types(self, engine, ext, np_type):
        # Test np.float values read come back as float.
        df = DataFrame(np.random.random_sample(10), dtype=np_type)
        df.to_excel(self.path, "test1")

        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, "test1", index_col=0).astype(np_type)

        tm.assert_frame_equal(df, recons, check_dtype=False)

    @pytest.mark.parametrize("np_type", [np.bool8, np.bool_])
    def test_bool_types(self, engine, ext, np_type):
        # Test np.bool values read come back as float.
        df = (DataFrame([1, 0, True, False], dtype=np_type))
        df.to_excel(self.path, "test1")

        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, "test1", index_col=0).astype(np_type)

        tm.assert_frame_equal(df, recons)

    def test_inf_roundtrip(self, engine, ext):
        df = DataFrame([(1, np.inf), (2, 3), (5, -np.inf)])
        df.to_excel(self.path, "test1")

        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, "test1", index_col=0)

        tm.assert_frame_equal(df, recons)

    def test_sheets(self, engine, ext, frame, tsframe):
        frame = frame.copy()
        frame['A'][:5] = nan

        frame.to_excel(self.path, 'test1')
        frame.to_excel(self.path, 'test1', columns=['A', 'B'])
        frame.to_excel(self.path, 'test1', header=False)
        frame.to_excel(self.path, 'test1', index=False)

        # Test writing to separate sheets
        writer = ExcelWriter(self.path)
        frame.to_excel(writer, 'test1')
        tsframe.to_excel(writer, 'test2')
        writer.save()
        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, 'test1', index_col=0)
        tm.assert_frame_equal(frame, recons)
        recons = pd.read_excel(reader, 'test2', index_col=0)
        tm.assert_frame_equal(tsframe, recons)
        assert 2 == len(reader.sheet_names)
        assert 'test1' == reader.sheet_names[0]
        assert 'test2' == reader.sheet_names[1]

    def test_colaliases(self, engine, ext, frame):
        frame = frame.copy()
        frame['A'][:5] = nan

        frame.to_excel(self.path, 'test1')
        frame.to_excel(self.path, 'test1', columns=['A', 'B'])
        frame.to_excel(self.path, 'test1', header=False)
        frame.to_excel(self.path, 'test1', index=False)

        # column aliases
        col_aliases = Index(['AA', 'X', 'Y', 'Z'])
        frame.to_excel(self.path, 'test1', header=col_aliases)
        reader = ExcelFile(self.path)
        rs = pd.read_excel(reader, 'test1', index_col=0)
        xp = frame.copy()
        xp.columns = col_aliases
        tm.assert_frame_equal(xp, rs)

    def test_roundtrip_indexlabels(self, merge_cells, engine, ext, frame):
        frame = frame.copy()
        frame['A'][:5] = nan

        frame.to_excel(self.path, 'test1')
        frame.to_excel(self.path, 'test1', columns=['A', 'B'])
        frame.to_excel(self.path, 'test1', header=False)
        frame.to_excel(self.path, 'test1', index=False)

        # test index_label
        df = (DataFrame(np.random.randn(10, 2)) >= 0)
        df.to_excel(self.path, 'test1',
                    index_label=['test'],
                    merge_cells=merge_cells)
        reader = ExcelFile(self.path)
        recons = pd.read_excel(
            reader, 'test1', index_col=0).astype(np.int64)
        df.index.names = ['test']
        assert df.index.names == recons.index.names

        df = (DataFrame(np.random.randn(10, 2)) >= 0)
        df.to_excel(self.path,
                    'test1',
                    index_label=['test', 'dummy', 'dummy2'],
                    merge_cells=merge_cells)
        reader = ExcelFile(self.path)
        recons = pd.read_excel(
            reader, 'test1', index_col=0).astype(np.int64)
        df.index.names = ['test']
        assert df.index.names == recons.index.names

        df = (DataFrame(np.random.randn(10, 2)) >= 0)
        df.to_excel(self.path,
                    'test1',
                    index_label='test',
                    merge_cells=merge_cells)
        reader = ExcelFile(self.path)
        recons = pd.read_excel(
            reader, 'test1', index_col=0).astype(np.int64)
        df.index.names = ['test']
        tm.assert_frame_equal(df, recons.astype(bool))

        frame.to_excel(self.path,
                       'test1',
                       columns=['A', 'B', 'C', 'D'],
                       index=False, merge_cells=merge_cells)
        # take 'A' and 'B' as indexes (same row as cols 'C', 'D')
        df = frame.copy()
        df = df.set_index(['A', 'B'])

        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, 'test1', index_col=[0, 1])
        tm.assert_frame_equal(df, recons, check_less_precise=True)

    def test_excel_roundtrip_indexname(self, merge_cells, engine, ext):
        df = DataFrame(np.random.randn(10, 4))
        df.index.name = 'foo'

        df.to_excel(self.path, merge_cells=merge_cells)

        xf = ExcelFile(self.path)
        result = pd.read_excel(xf, xf.sheet_names[0], index_col=0)

        tm.assert_frame_equal(result, df)
        assert result.index.name == 'foo'

    def test_excel_roundtrip_datetime(self, merge_cells, tsframe, engine, ext):
        # datetime.date, not sure what to test here exactly
        tsf = tsframe.copy()

        tsf.index = [x.date() for x in tsframe.index]
        tsf.to_excel(self.path, "test1", merge_cells=merge_cells)

        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, "test1", index_col=0)

        tm.assert_frame_equal(tsframe, recons)

    def test_excel_date_datetime_format(self, engine, ext):
        # see gh-4133
        #
        # Excel output format strings
        df = DataFrame([[date(2014, 1, 31),
                         date(1999, 9, 24)],
                        [datetime(1998, 5, 26, 23, 33, 4),
                         datetime(2014, 2, 28, 13, 5, 13)]],
                       index=["DATE", "DATETIME"], columns=["X", "Y"])
        df_expected = DataFrame([[datetime(2014, 1, 31),
                                  datetime(1999, 9, 24)],
                                 [datetime(1998, 5, 26, 23, 33, 4),
                                  datetime(2014, 2, 28, 13, 5, 13)]],
                                index=["DATE", "DATETIME"], columns=["X", "Y"])

        with ensure_clean(ext) as filename2:
            writer1 = ExcelWriter(self.path)
            writer2 = ExcelWriter(filename2,
                                  date_format="DD.MM.YYYY",
                                  datetime_format="DD.MM.YYYY HH-MM-SS")

            df.to_excel(writer1, "test1")
            df.to_excel(writer2, "test1")

            writer1.close()
            writer2.close()

            reader1 = ExcelFile(self.path)
            reader2 = ExcelFile(filename2)

            rs1 = pd.read_excel(reader1, "test1", index_col=0)
            rs2 = pd.read_excel(reader2, "test1", index_col=0)

            tm.assert_frame_equal(rs1, rs2)

            # Since the reader returns a datetime object for dates,
            # we need to use df_expected to check the result.
            tm.assert_frame_equal(rs2, df_expected)

    def test_to_excel_interval_no_labels(self, engine, ext):
        # see gh-19242
        #
        # Test writing Interval without labels.
        df = DataFrame(np.random.randint(-10, 10, size=(20, 1)),
                       dtype=np.int64)
        expected = df.copy()

        df["new"] = pd.cut(df[0], 10)
        expected["new"] = pd.cut(expected[0], 10).astype(str)

        df.to_excel(self.path, "test1")
        reader = ExcelFile(self.path)

        recons = pd.read_excel(reader, "test1", index_col=0)
        tm.assert_frame_equal(expected, recons)

    def test_to_excel_interval_labels(self, engine, ext):
        # see gh-19242
        #
        # Test writing Interval with labels.
        df = DataFrame(np.random.randint(-10, 10, size=(20, 1)),
                       dtype=np.int64)
        expected = df.copy()
        intervals = pd.cut(df[0], 10, labels=["A", "B", "C", "D", "E",
                                              "F", "G", "H", "I", "J"])
        df["new"] = intervals
        expected["new"] = pd.Series(list(intervals))

        df.to_excel(self.path, "test1")
        reader = ExcelFile(self.path)

        recons = pd.read_excel(reader, "test1", index_col=0)
        tm.assert_frame_equal(expected, recons)

    def test_to_excel_timedelta(self, engine, ext):
        # see gh-19242, gh-9155
        #
        # Test writing timedelta to xls.
        df = DataFrame(np.random.randint(-10, 10, size=(20, 1)),
                       columns=["A"], dtype=np.int64)
        expected = df.copy()

        df["new"] = df["A"].apply(lambda x: timedelta(seconds=x))
        expected["new"] = expected["A"].apply(
            lambda x: timedelta(seconds=x).total_seconds() / float(86400))

        df.to_excel(self.path, "test1")
        reader = ExcelFile(self.path)

        recons = pd.read_excel(reader, "test1", index_col=0)
        tm.assert_frame_equal(expected, recons)

    def test_to_excel_periodindex(self, engine, ext, tsframe):
        xp = tsframe.resample('M', kind='period').mean()

        xp.to_excel(self.path, 'sht1')

        reader = ExcelFile(self.path)
        rs = pd.read_excel(reader, 'sht1', index_col=0)
        tm.assert_frame_equal(xp, rs.to_period('M'))

    def test_to_excel_multiindex(self, merge_cells, engine, ext, frame):
        arrays = np.arange(len(frame.index) * 2).reshape(2, -1)
        new_index = MultiIndex.from_arrays(arrays,
                                           names=['first', 'second'])
        frame.index = new_index

        frame.to_excel(self.path, 'test1', header=False)
        frame.to_excel(self.path, 'test1', columns=['A', 'B'])

        # round trip
        frame.to_excel(self.path, 'test1', merge_cells=merge_cells)
        reader = ExcelFile(self.path)
        df = pd.read_excel(reader, 'test1', index_col=[0, 1])
        tm.assert_frame_equal(frame, df)

    # GH13511
    def test_to_excel_multiindex_nan_label(self, merge_cells, engine, ext):
        df = pd.DataFrame({'A': [None, 2, 3],
                           'B': [10, 20, 30],
                           'C': np.random.sample(3)})
        df = df.set_index(['A', 'B'])

        df.to_excel(self.path, merge_cells=merge_cells)
        df1 = pd.read_excel(self.path, index_col=[0, 1])
        tm.assert_frame_equal(df, df1)

    # Test for Issue 11328. If column indices are integers, make
    # sure they are handled correctly for either setting of
    # merge_cells
    def test_to_excel_multiindex_cols(self, merge_cells, engine, ext, frame):
        arrays = np.arange(len(frame.index) * 2).reshape(2, -1)
        new_index = MultiIndex.from_arrays(arrays,
                                           names=['first', 'second'])
        frame.index = new_index

        new_cols_index = MultiIndex.from_tuples([(40, 1), (40, 2),
                                                 (50, 1), (50, 2)])
        frame.columns = new_cols_index
        header = [0, 1]
        if not merge_cells:
            header = 0

        # round trip
        frame.to_excel(self.path, 'test1', merge_cells=merge_cells)
        reader = ExcelFile(self.path)
        df = pd.read_excel(reader, 'test1', header=header, index_col=[0, 1])
        if not merge_cells:
            fm = frame.columns.format(sparsify=False,
                                      adjoin=False, names=False)
            frame.columns = [".".join(map(str, q)) for q in zip(*fm)]
        tm.assert_frame_equal(frame, df)

    def test_to_excel_multiindex_dates(
            self, merge_cells, engine, ext, tsframe):
        # try multiindex with dates
        new_index = [tsframe.index, np.arange(len(tsframe.index))]
        tsframe.index = MultiIndex.from_arrays(new_index)

        tsframe.index.names = ['time', 'foo']
        tsframe.to_excel(self.path, 'test1', merge_cells=merge_cells)
        reader = ExcelFile(self.path)
        recons = pd.read_excel(reader, 'test1', index_col=[0, 1])

        tm.assert_frame_equal(tsframe, recons)
        assert recons.index.names == ('time', 'foo')

    def test_to_excel_multiindex_no_write_index(self, engine, ext):
        # Test writing and re-reading a MI without the index. GH 5616.

        # Initial non-MI frame.
        frame1 = DataFrame({'a': [10, 20], 'b': [30, 40], 'c': [50, 60]})

        # Add a MI.
        frame2 = frame1.copy()
        multi_index = MultiIndex.from_tuples([(70, 80), (90, 100)])
        frame2.index = multi_index

        # Write out to Excel without the index.
        frame2.to_excel(self.path, 'test1', index=False)

        # Read it back in.
        reader = ExcelFile(self.path)
        frame3 = pd.read_excel(reader, 'test1')

        # Test that it is the same as the initial frame.
        tm.assert_frame_equal(frame1, frame3)

    def test_to_excel_float_format(self, engine, ext):
        df = DataFrame([[0.123456, 0.234567, 0.567567],
                        [12.32112, 123123.2, 321321.2]],
                       index=["A", "B"], columns=["X", "Y", "Z"])
        df.to_excel(self.path, "test1", float_format="%.2f")

        reader = ExcelFile(self.path)
        result = pd.read_excel(reader, "test1", index_col=0)

        expected = DataFrame([[0.12, 0.23, 0.57],
                              [12.32, 123123.20, 321321.20]],
                             index=["A", "B"], columns=["X", "Y", "Z"])
        tm.assert_frame_equal(result, expected)

    def test_to_excel_output_encoding(self, engine, ext):
        # Avoid mixed inferred_type.
        df = DataFrame([["\u0192", "\u0193", "\u0194"],
                        ["\u0195", "\u0196", "\u0197"]],
                       index=["A\u0192", "B"],
                       columns=["X\u0193", "Y", "Z"])

        with ensure_clean("__tmp_to_excel_float_format__." + ext) as filename:
            df.to_excel(filename, sheet_name="TestSheet", encoding="utf8")
            result = pd.read_excel(filename, "TestSheet",
                                   encoding="utf8", index_col=0)
            tm.assert_frame_equal(result, df)

    def test_to_excel_unicode_filename(self, engine, ext):
        with ensure_clean("\u0192u." + ext) as filename:
            try:
                f = open(filename, "wb")
            except UnicodeEncodeError:
                pytest.skip("No unicode file names on this system")
            else:
                f.close()

            df = DataFrame([[0.123456, 0.234567, 0.567567],
                            [12.32112, 123123.2, 321321.2]],
                           index=["A", "B"], columns=["X", "Y", "Z"])
            df.to_excel(filename, "test1", float_format="%.2f")

            reader = ExcelFile(filename)
            result = pd.read_excel(reader, "test1", index_col=0)

            expected = DataFrame([[0.12, 0.23, 0.57],
                                  [12.32, 123123.20, 321321.20]],
                                 index=["A", "B"], columns=["X", "Y", "Z"])
            tm.assert_frame_equal(result, expected)

    # def test_to_excel_header_styling_xls(self, engine, ext):

    #     import StringIO
    #     s = StringIO(
    #     """Date,ticker,type,value
    #     2001-01-01,x,close,12.2
    #     2001-01-01,x,open ,12.1
    #     2001-01-01,y,close,12.2
    #     2001-01-01,y,open ,12.1
    #     2001-02-01,x,close,12.2
    #     2001-02-01,x,open ,12.1
    #     2001-02-01,y,close,12.2
    #     2001-02-01,y,open ,12.1
    #     2001-03-01,x,close,12.2
    #     2001-03-01,x,open ,12.1
    #     2001-03-01,y,close,12.2
    #     2001-03-01,y,open ,12.1""")
    #     df = read_csv(s, parse_dates=["Date"])
    #     pdf = df.pivot_table(values="value", rows=["ticker"],
    #                                          cols=["Date", "type"])

    #     try:
    #         import xlwt
    #         import xlrd
    #     except ImportError:
    #         pytest.skip

    #     filename = '__tmp_to_excel_header_styling_xls__.xls'
    #     pdf.to_excel(filename, 'test1')

    #     wbk = xlrd.open_workbook(filename,
    #                              formatting_info=True)
    #     assert ["test1"] == wbk.sheet_names()
    #     ws = wbk.sheet_by_name('test1')
    #     assert [(0, 1, 5, 7), (0, 1, 3, 5), (0, 1, 1, 3)] == ws.merged_cells
    #     for i in range(0, 2):
    #         for j in range(0, 7):
    #             xfx = ws.cell_xf_index(0, 0)
    #             cell_xf = wbk.xf_list[xfx]
    #             font = wbk.font_list
    #             assert 1 == font[cell_xf.font_index].bold
    #             assert 1 == cell_xf.border.top_line_style
    #             assert 1 == cell_xf.border.right_line_style
    #             assert 1 == cell_xf.border.bottom_line_style
    #             assert 1 == cell_xf.border.left_line_style
    #             assert 2 == cell_xf.alignment.hor_align
    #     os.remove(filename)
    # def test_to_excel_header_styling_xlsx(self, engine, ext):
    #     import StringIO
    #     s = StringIO(
    #     """Date,ticker,type,value
    #     2001-01-01,x,close,12.2
    #     2001-01-01,x,open ,12.1
    #     2001-01-01,y,close,12.2
    #     2001-01-01,y,open ,12.1
    #     2001-02-01,x,close,12.2
    #     2001-02-01,x,open ,12.1
    #     2001-02-01,y,close,12.2
    #     2001-02-01,y,open ,12.1
    #     2001-03-01,x,close,12.2
    #     2001-03-01,x,open ,12.1
    #     2001-03-01,y,close,12.2
    #     2001-03-01,y,open ,12.1""")
    #     df = read_csv(s, parse_dates=["Date"])
    #     pdf = df.pivot_table(values="value", rows=["ticker"],
    #                                          cols=["Date", "type"])
    #     try:
    #         import openpyxl
    #         from openpyxl.cell import get_column_letter
    #     except ImportError:
    #         pytest.skip
    #     if openpyxl.__version__ < '1.6.1':
    #         pytest.skip
    #     # test xlsx_styling
    #     filename = '__tmp_to_excel_header_styling_xlsx__.xlsx'
    #     pdf.to_excel(filename, 'test1')
    #     wbk = openpyxl.load_workbook(filename)
    #     assert ["test1"] == wbk.get_sheet_names()
    #     ws = wbk.get_sheet_by_name('test1')
    #     xlsaddrs = ["%s2" % chr(i) for i in range(ord('A'), ord('H'))]
    #     xlsaddrs += ["A%s" % i for i in range(1, 6)]
    #     xlsaddrs += ["B1", "D1", "F1"]
    #     for xlsaddr in xlsaddrs:
    #         cell = ws.cell(xlsaddr)
    #         assert cell.style.font.bold
    #         assert (openpyxl.style.Border.BORDER_THIN ==
    #                 cell.style.borders.top.border_style)
    #         assert (openpyxl.style.Border.BORDER_THIN ==
    #                 cell.style.borders.right.border_style)
    #         assert (openpyxl.style.Border.BORDER_THIN ==
    #                 cell.style.borders.bottom.border_style)
    #         assert (openpyxl.style.Border.BORDER_THIN ==
    #                 cell.style.borders.left.border_style)
    #         assert (openpyxl.style.Alignment.HORIZONTAL_CENTER ==
    #                 cell.style.alignment.horizontal)
    #     mergedcells_addrs = ["C1", "E1", "G1"]
    #     for maddr in mergedcells_addrs:
    #         assert ws.cell(maddr).merged
    #     os.remove(filename)

    @pytest.mark.parametrize("use_headers", [True, False])
    @pytest.mark.parametrize("r_idx_nlevels", [1, 2, 3])
    @pytest.mark.parametrize("c_idx_nlevels", [1, 2, 3])
    def test_excel_010_hemstring(self, merge_cells, engine, ext,
                                 c_idx_nlevels, r_idx_nlevels, use_headers):

        def roundtrip(data, header=True, parser_hdr=0, index=True):
            data.to_excel(self.path, header=header,
                          merge_cells=merge_cells, index=index)

            xf = ExcelFile(self.path)
            return pd.read_excel(xf, xf.sheet_names[0], header=parser_hdr)

        # Basic test.
        parser_header = 0 if use_headers else None
        res = roundtrip(DataFrame([0]), use_headers, parser_header)

        assert res.shape == (1, 2)
        assert res.iloc[0, 0] is not np.nan

        # More complex tests with multi-index.
        nrows = 5
        ncols = 3

        from pandas.util.testing import makeCustomDataframe as mkdf
        # ensure limited functionality in 0.10
        # override of gh-2370 until sorted out in 0.11

        df = mkdf(nrows, ncols, r_idx_nlevels=r_idx_nlevels,
                  c_idx_nlevels=c_idx_nlevels)

        # This if will be removed once multi-column Excel writing
        # is implemented. For now fixing gh-9794.
        if c_idx_nlevels > 1:
            with pytest.raises(NotImplementedError):
                roundtrip(df, use_headers, index=False)
        else:
            res = roundtrip(df, use_headers)

            if use_headers:
                assert res.shape == (nrows, ncols + r_idx_nlevels)
            else:
                # First row taken as columns.
                assert res.shape == (nrows - 1, ncols + r_idx_nlevels)

            # No NaNs.
            for r in range(len(res.index)):
                for c in range(len(res.columns)):
                    assert res.iloc[r, c] is not np.nan

    def test_duplicated_columns(self, engine, ext):
        # see gh-5235
        df = DataFrame([[1, 2, 3], [1, 2, 3], [1, 2, 3]],
                       columns=["A", "B", "B"])
        df.to_excel(self.path, "test1")
        expected = DataFrame([[1, 2, 3], [1, 2, 3], [1, 2, 3]],
                             columns=["A", "B", "B.1"])

        # By default, we mangle.
        result = pd.read_excel(self.path, "test1", index_col=0)
        tm.assert_frame_equal(result, expected)

        # Explicitly, we pass in the parameter.
        result = pd.read_excel(self.path, "test1", index_col=0,
                               mangle_dupe_cols=True)
        tm.assert_frame_equal(result, expected)

        # see gh-11007, gh-10970
        df = DataFrame([[1, 2, 3, 4], [5, 6, 7, 8]],
                       columns=["A", "B", "A", "B"])
        df.to_excel(self.path, "test1")

        result = pd.read_excel(self.path, "test1", index_col=0)
        expected = DataFrame([[1, 2, 3, 4], [5, 6, 7, 8]],
                             columns=["A", "B", "A.1", "B.1"])
        tm.assert_frame_equal(result, expected)

        # see gh-10982
        df.to_excel(self.path, "test1", index=False, header=False)
        result = pd.read_excel(self.path, "test1", header=None)

        expected = DataFrame([[1, 2, 3, 4], [5, 6, 7, 8]])
        tm.assert_frame_equal(result, expected)

        msg = "Setting mangle_dupe_cols=False is not supported yet"
        with pytest.raises(ValueError, match=msg):
            pd.read_excel(
                self.path, "test1", header=None, mangle_dupe_cols=False)

    def test_swapped_columns(self, engine, ext):
        # Test for issue #5427.
        write_frame = DataFrame({'A': [1, 1, 1],
                                 'B': [2, 2, 2]})
        write_frame.to_excel(self.path, 'test1', columns=['B', 'A'])

        read_frame = pd.read_excel(self.path, 'test1', header=0)

        tm.assert_series_equal(write_frame['A'], read_frame['A'])
        tm.assert_series_equal(write_frame['B'], read_frame['B'])

    def test_invalid_columns(self, engine, ext):
        # see gh-10982
        write_frame = DataFrame({"A": [1, 1, 1],
                                 "B": [2, 2, 2]})

        with tm.assert_produces_warning(FutureWarning,
                                        check_stacklevel=False):
            write_frame.to_excel(self.path, "test1", columns=["B", "C"])

        expected = write_frame.reindex(columns=["B", "C"])
        read_frame = pd.read_excel(self.path, "test1", index_col=0)
        tm.assert_frame_equal(expected, read_frame)

        with pytest.raises(KeyError):
            write_frame.to_excel(self.path, "test1", columns=["C", "D"])

    def test_comment_arg(self, engine, ext):
        # see gh-18735
        #
        # Test the comment argument functionality to pd.read_excel.

        # Create file to read in.
        df = DataFrame({"A": ["one", "#one", "one"],
                        "B": ["two", "two", "#two"]})
        df.to_excel(self.path, "test_c")

        # Read file without comment arg.
        result1 = pd.read_excel(self.path, "test_c", index_col=0)

        result1.iloc[1, 0] = None
        result1.iloc[1, 1] = None
        result1.iloc[2, 1] = None

        result2 = pd.read_excel(self.path, "test_c", comment="#", index_col=0)
        tm.assert_frame_equal(result1, result2)

    def test_comment_default(self, engine, ext):
        # Re issue #18735
        # Test the comment argument default to pd.read_excel

        # Create file to read in
        df = DataFrame({'A': ['one', '#one', 'one'],
                        'B': ['two', 'two', '#two']})
        df.to_excel(self.path, 'test_c')

        # Read file with default and explicit comment=None
        result1 = pd.read_excel(self.path, 'test_c')
        result2 = pd.read_excel(self.path, 'test_c', comment=None)
        tm.assert_frame_equal(result1, result2)

    def test_comment_used(self, engine, ext):
        # see gh-18735
        #
        # Test the comment argument is working as expected when used.

        # Create file to read in.
        df = DataFrame({"A": ["one", "#one", "one"],
                        "B": ["two", "two", "#two"]})
        df.to_excel(self.path, "test_c")

        # Test read_frame_comment against manually produced expected output.
        expected = DataFrame({"A": ["one", None, "one"],
                              "B": ["two", None, None]})
        result = pd.read_excel(self.path, "test_c", comment="#", index_col=0)
        tm.assert_frame_equal(result, expected)

    def test_comment_empty_line(self, engine, ext):
        # Re issue #18735
        # Test that pd.read_excel ignores commented lines at the end of file

        df = DataFrame({'a': ['1', '#2'], 'b': ['2', '3']})
        df.to_excel(self.path, index=False)

        # Test that all-comment lines at EoF are ignored
        expected = DataFrame({'a': [1], 'b': [2]})
        result = pd.read_excel(self.path, comment='#')
        tm.assert_frame_equal(result, expected)

    def test_datetimes(self, engine, ext):

        # Test writing and reading datetimes. For issue #9139. (xref #9185)
        datetimes = [datetime(2013, 1, 13, 1, 2, 3),
                     datetime(2013, 1, 13, 2, 45, 56),
                     datetime(2013, 1, 13, 4, 29, 49),
                     datetime(2013, 1, 13, 6, 13, 42),
                     datetime(2013, 1, 13, 7, 57, 35),
                     datetime(2013, 1, 13, 9, 41, 28),
                     datetime(2013, 1, 13, 11, 25, 21),
                     datetime(2013, 1, 13, 13, 9, 14),
                     datetime(2013, 1, 13, 14, 53, 7),
                     datetime(2013, 1, 13, 16, 37, 0),
                     datetime(2013, 1, 13, 18, 20, 52)]

        write_frame = DataFrame({'A': datetimes})
        write_frame.to_excel(self.path, 'Sheet1')
        read_frame = pd.read_excel(self.path, 'Sheet1', header=0)

        tm.assert_series_equal(write_frame['A'], read_frame['A'])

    def test_bytes_io(self, engine, ext):
        # see gh-7074
        bio = BytesIO()
        df = DataFrame(np.random.randn(10, 2))

        # Pass engine explicitly, as there is no file path to infer from.
        writer = ExcelWriter(bio, engine=engine)
        df.to_excel(writer)
        writer.save()

        bio.seek(0)
        reread_df = pd.read_excel(bio, index_col=0)
        tm.assert_frame_equal(df, reread_df)

    def test_write_lists_dict(self, engine, ext):
        # see gh-8188.
        df = DataFrame({"mixed": ["a", ["b", "c"], {"d": "e", "f": 2}],
                        "numeric": [1, 2, 3.0],
                        "str": ["apple", "banana", "cherry"]})
        df.to_excel(self.path, "Sheet1")
        read = pd.read_excel(self.path, "Sheet1", header=0, index_col=0)

        expected = df.copy()
        expected.mixed = expected.mixed.apply(str)
        expected.numeric = expected.numeric.astype("int64")

        tm.assert_frame_equal(read, expected)

    def test_true_and_false_value_options(self, engine, ext):
        # see gh-13347
        df = pd.DataFrame([["foo", "bar"]], columns=["col1", "col2"])
        expected = df.replace({"foo": True, "bar": False})

        df.to_excel(self.path)
        read_frame = pd.read_excel(self.path, true_values=["foo"],
                                   false_values=["bar"], index_col=0)
        tm.assert_frame_equal(read_frame, expected)

    def test_freeze_panes(self, engine, ext):
        # see gh-15160
        expected = DataFrame([[1, 2], [3, 4]], columns=["col1", "col2"])
        expected.to_excel(self.path, "Sheet1", freeze_panes=(1, 1))

        result = pd.read_excel(self.path, index_col=0)
        tm.assert_frame_equal(result, expected)

    def test_path_path_lib(self, engine, ext):
        df = tm.makeDataFrame()
        writer = partial(df.to_excel, engine=engine)

        reader = partial(pd.read_excel, index_col=0)
        result = tm.round_trip_pathlib(writer, reader,
                                       path="foo.{ext}".format(ext=ext))
        tm.assert_frame_equal(result, df)

    def test_path_local_path(self, engine, ext):
        df = tm.makeDataFrame()
        writer = partial(df.to_excel, engine=engine)

        reader = partial(pd.read_excel, index_col=0)
        result = tm.round_trip_pathlib(writer, reader,
                                       path="foo.{ext}".format(ext=ext))
        tm.assert_frame_equal(result, df)

    def test_merged_cell_custom_objects(self, engine, merge_cells, ext):
        # see GH-27006
        mi = MultiIndex.from_tuples([(pd.Period('2018'), pd.Period('2018Q1')),
                                     (pd.Period('2018'), pd.Period('2018Q2'))])
        expected = DataFrame(np.ones((2, 2)), columns=mi)
        expected.to_excel(self.path)
        result = pd.read_excel(self.path, header=[0, 1],
                               index_col=0, convert_float=False)
        # need to convert PeriodIndexes to standard Indexes for assert equal
        expected.columns.set_levels([[str(i) for i in mi.levels[0]],
                                     [str(i) for i in mi.levels[1]]],
                                    level=[0, 1],
                                    inplace=True)
        expected.index = expected.index.astype(np.float64)
        tm.assert_frame_equal(expected, result)


class TestExcelWriterEngineTests:

    @pytest.mark.parametrize('klass,ext', [
        pytest.param(_XlsxWriter, '.xlsx', marks=pytest.mark.skipif(
            not td.safe_import('xlsxwriter'), reason='No xlsxwriter')),
        pytest.param(_OpenpyxlWriter, '.xlsx', marks=pytest.mark.skipif(
            not td.safe_import('openpyxl'), reason='No openpyxl')),
        pytest.param(_XlwtWriter, '.xls', marks=pytest.mark.skipif(
            not td.safe_import('xlwt'), reason='No xlwt'))
    ])
    def test_ExcelWriter_dispatch(self, klass, ext):
        with ensure_clean(ext) as path:
            writer = ExcelWriter(path)
            if ext == '.xlsx' and td.safe_import('xlsxwriter'):
                # xlsxwriter has preference over openpyxl if both installed
                assert isinstance(writer, _XlsxWriter)
            else:
                assert isinstance(writer, klass)

    def test_ExcelWriter_dispatch_raises(self):
        with pytest.raises(ValueError, match='No engine'):
            ExcelWriter('nothing')

    def test_register_writer(self):
        # some awkward mocking to test out dispatch and such actually works
        called_save = []
        called_write_cells = []

        class DummyClass(ExcelWriter):
            called_save = False
            called_write_cells = False
            supported_extensions = ['xlsx', 'xls']
            engine = 'dummy'

            def save(self):
                called_save.append(True)

            def write_cells(self, *args, **kwargs):
                called_write_cells.append(True)

        def check_called(func):
            func()
            assert len(called_save) >= 1
            assert len(called_write_cells) >= 1
            del called_save[:]
            del called_write_cells[:]

        with pd.option_context('io.excel.xlsx.writer', 'dummy'):
            register_writer(DummyClass)
            writer = ExcelWriter('something.xlsx')
            assert isinstance(writer, DummyClass)
            df = tm.makeCustomDataframe(1, 1)
            check_called(lambda: df.to_excel('something.xlsx'))
            check_called(
                lambda: df.to_excel(
                    'something.xls', engine='dummy'))


@td.skip_if_no('openpyxl')
@pytest.mark.skipif(not PY36, reason='requires fspath')
class TestFSPath:

    def test_excelfile_fspath(self):
        with tm.ensure_clean('foo.xlsx') as path:
            df = DataFrame({"A": [1, 2]})
            df.to_excel(path)
            xl = ExcelFile(path)
            result = os.fspath(xl)
            assert result == path

    def test_excelwriter_fspath(self):
        with tm.ensure_clean('foo.xlsx') as path:
            writer = ExcelWriter(path)
            assert os.fspath(writer) == str(path)
