from datetime import date, datetime, timedelta from functools import partial from io import BytesIO import os import numpy as np import pytest import pandas.util._test_decorators as td import pandas as pd from pandas import DataFrame, Index, MultiIndex, get_option, set_option import pandas._testing as tm from pandas.io.excel import ( ExcelFile, ExcelWriter, _OpenpyxlWriter, _XlsxWriter, _XlwtWriter, register_writer, ) @pytest.fixture def path(ext): """ Fixture to open file for use in each test case. """ with tm.ensure_clean(ext) as file_path: yield file_path @pytest.fixture def set_engine(engine, ext): """ Fixture to set engine 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. """ option_name = f"io.excel.{ext.strip('.')}.writer" prev_engine = get_option(option_name) set_option(option_name, engine) yield set_option(option_name, prev_engine) # Roll back option change @pytest.mark.parametrize( "ext", [ pytest.param(".xlsx", marks=[td.skip_if_no("openpyxl"), td.skip_if_no("xlrd")]), pytest.param(".xlsm", marks=[td.skip_if_no("openpyxl"), td.skip_if_no("xlrd")]), pytest.param(".xls", marks=[td.skip_if_no("xlwt"), td.skip_if_no("xlrd")]), pytest.param( ".xlsx", marks=[td.skip_if_no("xlsxwriter"), td.skip_if_no("xlrd")] ), pytest.param(".ods", marks=td.skip_if_no("odf")), ], ) class TestRoundTrip: @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 tm.ensure_clean(ext) as path: df.to_excel(path, filename, index=False, header=False) result = pd.read_excel( path, sheet_name=filename, usecols=[0], header=header ) tm.assert_frame_equal(result, expected) @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 tm.ensure_clean(ext) as path: df.to_excel(path, "with_header", index=False, header=True) result = pd.read_excel( path, sheet_name=filename, usecols=[0], header=header ) tm.assert_frame_equal(result, expected) 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 tm.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, sheet_name="Data_no_head", header=None, names=["A", "B"] ) xlsdf_with_head = pd.read_excel( reader, sheet_name="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) 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 tm.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]) def test_read_excel_multiindex_empty_level(self, ext): # see gh-12453 with tm.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) @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 tm.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 = tm.makeCustomDataframe( 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) 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 tm.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: 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) def test_multiindex_interval_datetimes(self, ext): # GH 30986 midx = pd.MultiIndex.from_arrays( [ range(4), pd.interval_range( start=pd.Timestamp("2020-01-01"), periods=4, freq="6M" ), ] ) df = pd.DataFrame(range(4), index=midx) with tm.ensure_clean(ext) as pth: df.to_excel(pth) result = pd.read_excel(pth, index_col=[0, 1]) expected = pd.DataFrame( range(4), pd.MultiIndex.from_arrays( [ range(4), [ "(2020-01-31, 2020-07-31]", "(2020-07-31, 2021-01-31]", "(2021-01-31, 2021-07-31]", "(2021-07-31, 2022-01-31]", ], ] ), ) tm.assert_frame_equal(result, expected) @pytest.mark.parametrize( "engine,ext", [ pytest.param( "openpyxl", ".xlsx", marks=[td.skip_if_no("openpyxl"), td.skip_if_no("xlrd")], ), pytest.param( "openpyxl", ".xlsm", marks=[td.skip_if_no("openpyxl"), td.skip_if_no("xlrd")], ), pytest.param( "xlwt", ".xls", marks=[td.skip_if_no("xlwt"), td.skip_if_no("xlrd")] ), pytest.param( "xlsxwriter", ".xlsx", marks=[td.skip_if_no("xlsxwriter"), td.skip_if_no("xlrd")], ), pytest.param("odf", ".ods", marks=td.skip_if_no("odf")), ], ) @pytest.mark.usefixtures("set_engine") class TestExcelWriter: def test_excel_sheet_size(self, path): # 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(path) with pytest.raises(ValueError, match=msg): col_df.to_excel(path) def test_excel_sheet_by_name_raise(self, path, engine): gt = DataFrame(np.random.randn(10, 2)) gt.to_excel(path) xl = ExcelFile(path) df = pd.read_excel(xl, sheet_name=0, index_col=0) tm.assert_frame_equal(gt, df) if engine == "odf": msg = "sheet 0 not found" with pytest.raises(ValueError, match=msg): pd.read_excel(xl, "0") else: import xlrd msg = "No sheet named <'0'>" with pytest.raises(xlrd.XLRDError, match=msg): pd.read_excel(xl, sheet_name="0") def test_excel_writer_context_manager(self, frame, path): with ExcelWriter(path) as writer: frame.to_excel(writer, "Data1") frame2 = frame.copy() frame2.columns = frame.columns[::-1] frame2.to_excel(writer, "Data2") with ExcelFile(path) as reader: found_df = pd.read_excel(reader, sheet_name="Data1", index_col=0) found_df2 = pd.read_excel(reader, sheet_name="Data2", index_col=0) tm.assert_frame_equal(found_df, frame) tm.assert_frame_equal(found_df2, frame2) def test_roundtrip(self, frame, path): frame = frame.copy() frame["A"][:5] = np.nan frame.to_excel(path, "test1") frame.to_excel(path, "test1", columns=["A", "B"]) frame.to_excel(path, "test1", header=False) frame.to_excel(path, "test1", index=False) # test roundtrip frame.to_excel(path, "test1") recons = pd.read_excel(path, sheet_name="test1", index_col=0) tm.assert_frame_equal(frame, recons) frame.to_excel(path, "test1", index=False) recons = pd.read_excel(path, sheet_name="test1", index_col=None) recons.index = frame.index tm.assert_frame_equal(frame, recons) frame.to_excel(path, "test1", na_rep="NA") recons = pd.read_excel(path, sheet_name="test1", index_col=0, na_values=["NA"]) tm.assert_frame_equal(frame, recons) # GH 3611 frame.to_excel(path, "test1", na_rep="88") recons = pd.read_excel(path, sheet_name="test1", index_col=0, na_values=["88"]) tm.assert_frame_equal(frame, recons) frame.to_excel(path, "test1", na_rep="88") recons = pd.read_excel( path, sheet_name="test1", index_col=0, na_values=[88, 88.0] ) tm.assert_frame_equal(frame, recons) # GH 6573 frame.to_excel(path, "Sheet1") recons = pd.read_excel(path, index_col=0) tm.assert_frame_equal(frame, recons) frame.to_excel(path, "0") recons = pd.read_excel(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(path) recons = pd.read_excel(path, index_col=0) tm.assert_frame_equal(s.to_frame(), recons) def test_mixed(self, frame, path): mixed_frame = frame.copy() mixed_frame["foo"] = "bar" mixed_frame.to_excel(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(mixed_frame, recons) def test_ts_frame(self, tsframe, path): df = tsframe # freq doesnt round-trip index = pd.DatetimeIndex(np.asarray(df.index), freq=None) df.index = index df.to_excel(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(df, recons) def test_basics_with_nan(self, frame, path): frame = frame.copy() frame["A"][:5] = np.nan frame.to_excel(path, "test1") frame.to_excel(path, "test1", columns=["A", "B"]) frame.to_excel(path, "test1", header=False) frame.to_excel(path, "test1", index=False) @pytest.mark.parametrize("np_type", [np.int8, np.int16, np.int32, np.int64]) def test_int_types(self, np_type, path): # 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(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) int_frame = df.astype(np.int64) tm.assert_frame_equal(int_frame, recons) recons2 = pd.read_excel(path, sheet_name="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( path, sheet_name="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, np_type, path): # Test np.float values read come back as float. df = DataFrame(np.random.random_sample(10), dtype=np_type) df.to_excel(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0).astype(np_type) tm.assert_frame_equal(df, recons) @pytest.mark.parametrize("np_type", [np.bool8, np.bool_]) def test_bool_types(self, np_type, path): # Test np.bool values read come back as float. df = DataFrame([1, 0, True, False], dtype=np_type) df.to_excel(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0).astype(np_type) tm.assert_frame_equal(df, recons) def test_inf_roundtrip(self, path): df = DataFrame([(1, np.inf), (2, 3), (5, -np.inf)]) df.to_excel(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(df, recons) def test_sheets(self, frame, tsframe, path): # freq doesnt round-trip index = pd.DatetimeIndex(np.asarray(tsframe.index), freq=None) tsframe.index = index frame = frame.copy() frame["A"][:5] = np.nan frame.to_excel(path, "test1") frame.to_excel(path, "test1", columns=["A", "B"]) frame.to_excel(path, "test1", header=False) frame.to_excel(path, "test1", index=False) # Test writing to separate sheets writer = ExcelWriter(path) frame.to_excel(writer, "test1") tsframe.to_excel(writer, "test2") writer.save() reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(frame, recons) recons = pd.read_excel(reader, sheet_name="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, frame, path): frame = frame.copy() frame["A"][:5] = np.nan frame.to_excel(path, "test1") frame.to_excel(path, "test1", columns=["A", "B"]) frame.to_excel(path, "test1", header=False) frame.to_excel(path, "test1", index=False) # column aliases col_aliases = Index(["AA", "X", "Y", "Z"]) frame.to_excel(path, "test1", header=col_aliases) reader = ExcelFile(path) rs = pd.read_excel(reader, sheet_name="test1", index_col=0) xp = frame.copy() xp.columns = col_aliases tm.assert_frame_equal(xp, rs) def test_roundtrip_indexlabels(self, merge_cells, frame, path): frame = frame.copy() frame["A"][:5] = np.nan frame.to_excel(path, "test1") frame.to_excel(path, "test1", columns=["A", "B"]) frame.to_excel(path, "test1", header=False) frame.to_excel(path, "test1", index=False) # test index_label df = DataFrame(np.random.randn(10, 2)) >= 0 df.to_excel(path, "test1", index_label=["test"], merge_cells=merge_cells) reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="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( path, "test1", index_label=["test", "dummy", "dummy2"], merge_cells=merge_cells, ) reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="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(path, "test1", index_label="test", merge_cells=merge_cells) reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0).astype(np.int64) df.index.names = ["test"] tm.assert_frame_equal(df, recons.astype(bool)) frame.to_excel( 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(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=[0, 1]) tm.assert_frame_equal(df, recons) def test_excel_roundtrip_indexname(self, merge_cells, path): df = DataFrame(np.random.randn(10, 4)) df.index.name = "foo" df.to_excel(path, merge_cells=merge_cells) xf = ExcelFile(path) result = pd.read_excel(xf, sheet_name=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, path): # datetime.date, not sure what to test here exactly # freq does not round-trip index = pd.DatetimeIndex(np.asarray(tsframe.index), freq=None) tsframe.index = index tsf = tsframe.copy() tsf.index = [x.date() for x in tsframe.index] tsf.to_excel(path, "test1", merge_cells=merge_cells) reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(tsframe, recons) def test_excel_date_datetime_format(self, engine, ext, path): # 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 tm.ensure_clean(ext) as filename2: writer1 = ExcelWriter(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(path) reader2 = ExcelFile(filename2) rs1 = pd.read_excel(reader1, sheet_name="test1", index_col=0) rs2 = pd.read_excel(reader2, sheet_name="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, path): # 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(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(expected, recons) def test_to_excel_interval_labels(self, path): # 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(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(expected, recons) def test_to_excel_timedelta(self, path): # 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(path, "test1") reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="test1", index_col=0) tm.assert_frame_equal(expected, recons) def test_to_excel_periodindex(self, tsframe, path): xp = tsframe.resample("M", kind="period").mean() xp.to_excel(path, "sht1") reader = ExcelFile(path) rs = pd.read_excel(reader, sheet_name="sht1", index_col=0) tm.assert_frame_equal(xp, rs.to_period("M")) def test_to_excel_multiindex(self, merge_cells, frame, path): 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(path, "test1", header=False) frame.to_excel(path, "test1", columns=["A", "B"]) # round trip frame.to_excel(path, "test1", merge_cells=merge_cells) reader = ExcelFile(path) df = pd.read_excel(reader, sheet_name="test1", index_col=[0, 1]) tm.assert_frame_equal(frame, df) # GH13511 def test_to_excel_multiindex_nan_label(self, merge_cells, path): 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(path, merge_cells=merge_cells) df1 = pd.read_excel(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, frame, path): 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(path, "test1", merge_cells=merge_cells) reader = ExcelFile(path) df = pd.read_excel(reader, sheet_name="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, tsframe, path): # 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(path, "test1", merge_cells=merge_cells) reader = ExcelFile(path) recons = pd.read_excel(reader, sheet_name="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, path): # 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(path, "test1", index=False) # Read it back in. reader = ExcelFile(path) frame3 = pd.read_excel(reader, sheet_name="test1") # Test that it is the same as the initial frame. tm.assert_frame_equal(frame1, frame3) def test_to_excel_float_format(self, path): df = DataFrame( [[0.123456, 0.234567, 0.567567], [12.32112, 123123.2, 321321.2]], index=["A", "B"], columns=["X", "Y", "Z"], ) df.to_excel(path, "test1", float_format="%.2f") reader = ExcelFile(path) result = pd.read_excel(reader, sheet_name="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, ext): # Avoid mixed inferred_type. df = DataFrame( [["\u0192", "\u0193", "\u0194"], ["\u0195", "\u0196", "\u0197"]], index=["A\u0192", "B"], columns=["X\u0193", "Y", "Z"], ) with tm.ensure_clean("__tmp_to_excel_float_format__." + ext) as filename: df.to_excel(filename, sheet_name="TestSheet", encoding="utf8") result = pd.read_excel(filename, sheet_name="TestSheet", index_col=0) tm.assert_frame_equal(result, df) def test_to_excel_unicode_filename(self, ext, path): with tm.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, sheet_name="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) # FIXME: dont leave commented-out # 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, c_idx_nlevels, r_idx_nlevels, use_headers, path ): def roundtrip(data, header=True, parser_hdr=0, index=True): data.to_excel(path, header=header, merge_cells=merge_cells, index=index) xf = ExcelFile(path) return pd.read_excel(xf, sheet_name=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 # ensure limited functionality in 0.10 # override of gh-2370 until sorted out in 0.11 df = tm.makeCustomDataframe( 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: msg = ( "Writing to Excel with MultiIndex columns and no index " "\\('index'=False\\) is not yet implemented." ) with pytest.raises(NotImplementedError, match=msg): 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, path): # see gh-5235 df = DataFrame([[1, 2, 3], [1, 2, 3], [1, 2, 3]], columns=["A", "B", "B"]) df.to_excel(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(path, sheet_name="test1", index_col=0) tm.assert_frame_equal(result, expected) # Explicitly, we pass in the parameter. result = pd.read_excel( path, sheet_name="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(path, "test1") result = pd.read_excel(path, sheet_name="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(path, "test1", index=False, header=False) result = pd.read_excel(path, sheet_name="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(path, sheet_name="test1", header=None, mangle_dupe_cols=False) def test_swapped_columns(self, path): # Test for issue #5427. write_frame = DataFrame({"A": [1, 1, 1], "B": [2, 2, 2]}) write_frame.to_excel(path, "test1", columns=["B", "A"]) read_frame = pd.read_excel(path, sheet_name="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, path): # see gh-10982 write_frame = DataFrame({"A": [1, 1, 1], "B": [2, 2, 2]}) with pytest.raises(KeyError, match="Not all names specified"): write_frame.to_excel(path, "test1", columns=["B", "C"]) with pytest.raises( KeyError, match="'passes columns are not ALL present dataframe'" ): write_frame.to_excel(path, "test1", columns=["C", "D"]) @pytest.mark.parametrize( "to_excel_index,read_excel_index_col", [ (True, 0), # Include index in write to file (False, None), # Dont include index in write to file ], ) def test_write_subset_columns(self, path, to_excel_index, read_excel_index_col): # GH 31677 write_frame = DataFrame({"A": [1, 1, 1], "B": [2, 2, 2], "C": [3, 3, 3]}) write_frame.to_excel( path, "col_subset_bug", columns=["A", "B"], index=to_excel_index ) expected = write_frame[["A", "B"]] read_frame = pd.read_excel( path, sheet_name="col_subset_bug", index_col=read_excel_index_col ) tm.assert_frame_equal(expected, read_frame) def test_comment_arg(self, path): # 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(path, "test_c") # Read file without comment arg. result1 = pd.read_excel(path, sheet_name="test_c", index_col=0) result1.iloc[1, 0] = None result1.iloc[1, 1] = None result1.iloc[2, 1] = None result2 = pd.read_excel(path, sheet_name="test_c", comment="#", index_col=0) tm.assert_frame_equal(result1, result2) def test_comment_default(self, path): # 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(path, "test_c") # Read file with default and explicit comment=None result1 = pd.read_excel(path, sheet_name="test_c") result2 = pd.read_excel(path, sheet_name="test_c", comment=None) tm.assert_frame_equal(result1, result2) def test_comment_used(self, path): # 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(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(path, sheet_name="test_c", comment="#", index_col=0) tm.assert_frame_equal(result, expected) def test_comment_empty_line(self, path): # 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(path, index=False) # Test that all-comment lines at EoF are ignored expected = DataFrame({"a": [1], "b": [2]}) result = pd.read_excel(path, comment="#") tm.assert_frame_equal(result, expected) def test_datetimes(self, path): # 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(path, "Sheet1") read_frame = pd.read_excel(path, sheet_name="Sheet1", header=0) tm.assert_series_equal(write_frame["A"], read_frame["A"]) def test_bytes_io(self, engine): # 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, path): # 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(path, "Sheet1") read = pd.read_excel(path, sheet_name="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_render_as_column_name(self, path): # see gh-34331 df = DataFrame({"render": [1, 2], "data": [3, 4]}) df.to_excel(path, "Sheet1") read = pd.read_excel(path, "Sheet1", index_col=0) expected = df tm.assert_frame_equal(read, expected) def test_true_and_false_value_options(self, path): # see gh-13347 df = pd.DataFrame([["foo", "bar"]], columns=["col1", "col2"]) expected = df.replace({"foo": True, "bar": False}) df.to_excel(path) read_frame = pd.read_excel( path, true_values=["foo"], false_values=["bar"], index_col=0 ) tm.assert_frame_equal(read_frame, expected) def test_freeze_panes(self, path): # see gh-15160 expected = DataFrame([[1, 2], [3, 4]], columns=["col1", "col2"]) expected.to_excel(path, "Sheet1", freeze_panes=(1, 1)) result = pd.read_excel(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=f"foo{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_localpath(writer, reader, path=f"foo{ext}") tm.assert_frame_equal(result, df) def test_merged_cell_custom_objects(self, merge_cells, path): # 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(path) result = pd.read_excel(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) @pytest.mark.parametrize("dtype", [None, object]) def test_raise_when_saving_timezones(self, dtype, tz_aware_fixture, path): # GH 27008, GH 7056 tz = tz_aware_fixture data = pd.Timestamp("2019", tz=tz) df = DataFrame([data], dtype=dtype) with pytest.raises(ValueError, match="Excel does not support"): df.to_excel(path) data = data.to_pydatetime() df = DataFrame([data], dtype=dtype) with pytest.raises(ValueError, match="Excel does not support"): df.to_excel(path) class TestExcelWriterEngineTests: @pytest.mark.parametrize( "klass,ext", [ pytest.param(_XlsxWriter, ".xlsx", marks=td.skip_if_no("xlsxwriter")), pytest.param(_OpenpyxlWriter, ".xlsx", marks=td.skip_if_no("openpyxl")), pytest.param(_XlwtWriter, ".xls", marks=td.skip_if_no("xlwt")), ], ) def test_ExcelWriter_dispatch(self, klass, ext): with tm.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("xlrd") @td.skip_if_no("openpyxl") 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)