text: 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 witout 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)
  
  
  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)
  