Repository: pandas-dev/pandas
Pull request: Append Mode for ExcelWriter with openpyxl
- [X] closes #3441
- [X] tests added / passed
- [X] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [X] whatsnew entry


Problem statement:
Allow ExcelWriter() to add sheets to existing workbook
The ability of ExcelWriter to save different dataframes to different worksheets is great for sharing those dfs with the python-deficient.  But this quickly leads to a need to _add_ worksheets to an existing workbook, not just creating one from scratch; something like:

```
df0=pd.DataFrame(np.arange(3))
df0.to_excel('foo.xlsx','Data 0')

df1=pd.DataFrame(np.arange(2))
df1.to_excel('foo.xlsx','Data 1')
```

The following little diff to io/parsers.py implements this behavior for *.xlsx files:

```
diff --git a/pandas/io/parsers.py b/pandas/io/parsers.py
index 89f892d..7f010ee 100644
--- a/pandas/io/parsers.py
+++ b/pandas/io/parsers.py
@@ -2099,12 +2099,19 @@ class ExcelWriter(object):
             self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
         else:
             from openpyxl.workbook import Workbook
-            self.book = Workbook()  # optimized_write=True)
-            # open pyxl 1.6.1 adds a dummy sheet remove it
-            if self.book.worksheets:
-                self.book.remove_sheet(self.book.worksheets[0])
+            from openpyxl.reader.excel import load_workbook
+
+            try:
+               self.book=load_workbook(filename = path)
+               self.sheets={wks.title:wks for wks in self.book.worksheets}
+            except InvalidFileException:
+                self.book = Workbook()  # optimized_write=True)
+                # open pyxl 1.6.1 adds a dummy sheet remove it
+                if self.book.worksheets:
+                    self.book.remove_sheet(self.book.worksheets[0])
+                self.sheets = {}
+
         self.path = path
-        self.sheets = {}
         self.cur_sheet = None
```

Doing this for *.xls files is a little harder.

Hints from issue discussion:
@jtratner is this still a bug/needed enhancement?

Because of how `to_excel` is set up, this would mean reading in and then writing the file each time (because `to_excel` with a path argument saves the file). The right way to do this is to use `ExcelWriter`:

``` python
import pandas as pd
writer = pd.ExcelWriter('foo.xlsx')
df.to_excel(writer, 'Data 0')
df.to_excel(writer, 'Data 1')
writer.save()
```

I could see (eventually) adding an option to ExcelWriter that doesn't overwrite the file. But, yet again, that may mean writing in the entire file first. I don't know.

I'm going to add something to the docs about this, maybe a test case with this, and I'll look into adding an option to read in the file, but it depends on how xlwt and openpyxl work.

@jtratner what about a context manager `get_excel`?

```
with get_excel('foo.xlsx') as e:
    df.to_excel(e,'Data 0)
    df.to_excel(e,'Data 1)
```

?

how about we just make ExcelWriter into a contextmanager instead?  it'll just call save at the end. Much simpler.

@ligon you can do this now this way:

``` python
with ExcelWriter('foo.xlsx') as writer:
    df.to_excel(writer, 'Data 0')
    df2.to_excel(writer, 'Data 1')
```

If you don't use the with statement, just have to call save() at the end.

Excellent.  And great that it has an **exit** method.

Thanks,
-Ethan Ligon

Ethan Ligon, Associate Professor
Agricultural & Resource Economics
University of California, Berkeley

I was extremely interesting by the request made by @ligon - but seems this is already there.
However using 0.12.0 pd version, when I am doing:
df = DataFrame([1,2,3])
df2 = DataFrame([5,5,6])
with ExcelWriter('foo.xlsx') as writer:
    df.to_excel(writer, 'Data 0')
    df2.to_excel(writer, 'Data 1')

Assumning foo.xlsx was containing a sheet named 'bar', basgot delete after the command run. While as per your comment, i was expecting to keep it in my foo excel file. Is that a bug?

is it hard to add sheets to an  existing excel file on the disk?
import pandas as pd 
import numpy as np
a=pd.DataFrame(np.random.random((3,1)))
excel_writer=pd.ExcelWriter('c:\excel.xlsx')
a.to_excel(excel_writer, 'a1')
excel_writer.save()

excel_writer=pd.ExcelWriter('c:\excel.xlsx')
a.to_excel(excel_writer, 'a2')
excel_writer.save()

here only sheet 'a2"  is save, but I like to save both 'a1' and 'a2'.

I know it is possible to add sheets to an existing workbook.

It's definitely possible to add sheets to an existing workbook, but it's
not necessarily easy to do it with pandas. I think you'd have to read the
workbook separately and then pass it into the ExcelWriter class... That
would be something we could consider supporting.

Commit message: Updated whatsnew

Patch to review:
diff --git a/doc/source/whatsnew/v0.23.1.txt b/doc/source/whatsnew/v0.23.1.txt
index 35484e34ee9eb..2b2ea5c7dad44 100644
--- a/doc/source/whatsnew/v0.23.1.txt
+++ b/doc/source/whatsnew/v0.23.1.txt
@@ -16,6 +16,7 @@ New features
 ~~~~~~~~~~~~
 
 - :meth:`Index.droplevel` is now implemented also for flat indexes, for compatibility with MultiIndex (:issue:`21115`)
+- ``ExcelWriter`` now accepts ``mode`` as a keyword argument, enabling append to existing workbooks when using the ``openpyxl`` engine (:issue:`3441`)
 
 
 .. _whatsnew_0231.deprecations:
diff --git a/pandas/io/excel.py b/pandas/io/excel.py
index 5608c29637447..04105212c800d 100644
--- a/pandas/io/excel.py
+++ b/pandas/io/excel.py
@@ -804,6 +804,8 @@ class ExcelWriter(object):
     datetime_format : string, default None
         Format string for datetime objects written into Excel files
         (e.g. 'YYYY-MM-DD HH:MM:SS')
+    mode : {'w' or 'a'}, default 'w'
+        File mode to use (write or append).
 
     Notes
     -----
@@ -897,7 +899,8 @@ def save(self):
         pass
 
     def __init__(self, path, engine=None,
-                 date_format=None, datetime_format=None, **engine_kwargs):
+                 date_format=None, datetime_format=None, mode='w',
+                 **engine_kwargs):
         # validate that this engine can handle the extension
         if isinstance(path, string_types):
             ext = os.path.splitext(path)[-1]
@@ -919,6 +922,8 @@ def __init__(self, path, engine=None,
         else:
             self.datetime_format = datetime_format
 
+        self.mode = mode
+
     def __fspath__(self):
         return _stringify_path(self.path)
 
@@ -993,23 +998,28 @@ class _OpenpyxlWriter(ExcelWriter):
     engine = 'openpyxl'
     supported_extensions = ('.xlsx', '.xlsm')
 
-    def __init__(self, path, engine=None, **engine_kwargs):
+    def __init__(self, path, engine=None, mode='w', **engine_kwargs):
         # Use the openpyxl module as the Excel writer.
         from openpyxl.workbook import Workbook
 
-        super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs)
+        super(_OpenpyxlWriter, self).__init__(path, mode=mode, **engine_kwargs)
 
-        # Create workbook object with default optimized_write=True.
-        self.book = Workbook()
+        if self.mode == 'a':  # Load from existing workbook
+            from openpyxl import load_workbook
+            book = load_workbook(self.path)
+            self.book = book
+        else:
+            # Create workbook object with default optimized_write=True.
+            self.book = Workbook()
 
-        # Openpyxl 1.6.1 adds a dummy sheet. We remove it.
-        if self.book.worksheets:
-            try:
-                self.book.remove(self.book.worksheets[0])
-            except AttributeError:
+            # Openpyxl 1.6.1 adds a dummy sheet. We remove it.
+            if self.book.worksheets:
+                try:
+                    self.book.remove(self.book.worksheets[0])
+                except AttributeError:
 
-                # compat
-                self.book.remove_sheet(self.book.worksheets[0])
+                    # compat
+                    self.book.remove_sheet(self.book.worksheets[0])
 
     def save(self):
         """
@@ -1443,11 +1453,16 @@ class _XlwtWriter(ExcelWriter):
     engine = 'xlwt'
     supported_extensions = ('.xls',)
 
-    def __init__(self, path, engine=None, encoding=None, **engine_kwargs):
+    def __init__(self, path, engine=None, encoding=None, mode='w',
+                 **engine_kwargs):
         # Use the xlwt module as the Excel writer.
         import xlwt
         engine_kwargs['engine'] = engine
-        super(_XlwtWriter, self).__init__(path, **engine_kwargs)
+
+        if mode == 'a':
+            raise ValueError('Append mode is not supported with xlwt!')
+
+        super(_XlwtWriter, self).__init__(path, mode=mode, **engine_kwargs)
 
         if encoding is None:
             encoding = 'ascii'
@@ -1713,13 +1728,18 @@ class _XlsxWriter(ExcelWriter):
     supported_extensions = ('.xlsx',)
 
     def __init__(self, path, engine=None,
-                 date_format=None, datetime_format=None, **engine_kwargs):
+                 date_format=None, datetime_format=None, mode='w',
+                 **engine_kwargs):
         # Use the xlsxwriter module as the Excel writer.
         import xlsxwriter
 
+        if mode == 'a':
+            raise ValueError('Append mode is not supported with xlsxwriter!')
+
         super(_XlsxWriter, self).__init__(path, engine=engine,
                                           date_format=date_format,
                                           datetime_format=datetime_format,
+                                          mode=mode,
                                           **engine_kwargs)
 
         self.book = xlsxwriter.Workbook(path, **engine_kwargs)
diff --git a/pandas/tests/io/test_excel.py b/pandas/tests/io/test_excel.py
index 05423474f330a..0c0594f23f40b 100644
--- a/pandas/tests/io/test_excel.py
+++ b/pandas/tests/io/test_excel.py
@@ -2006,6 +2006,32 @@ def test_write_cells_merge_styled(self, merge_cells, ext, engine):
             assert xcell_b1.font == openpyxl_sty_merged
             assert xcell_a2.font == openpyxl_sty_merged
 
+    @pytest.mark.parametrize("mode,expected", [
+        ('w', ['baz']), ('a', ['foo', 'bar', 'baz'])])
+    def test_write_append_mode(self, merge_cells, ext, engine, mode, expected):
+        import openpyxl
+        df = DataFrame([1], columns=['baz'])
+
+        with ensure_clean(ext) as f:
+            wb = openpyxl.Workbook()
+            wb.worksheets[0].title = 'foo'
+            wb.worksheets[0]['A1'].value = 'foo'
+            wb.create_sheet('bar')
+            wb.worksheets[1]['A1'].value = 'bar'
+            wb.save(f)
+            wb.close()
+
+            writer = ExcelWriter(f, engine=engine, mode=mode)
+            df.to_excel(writer, sheet_name='baz', index=False)
+            writer.save()
+
+            wb2 = openpyxl.load_workbook(f)
+            result = [wb.title for wb in wb2.worksheets]
+            assert result == expected
+
+            for index, cell_value in enumerate(expected):
+                assert wb2.worksheets[index]['A1'].value == cell_value
+
 
 @td.skip_if_no('xlwt')
 @pytest.mark.parametrize("merge_cells,ext,engine", [
@@ -2060,6 +2086,13 @@ def test_to_excel_styleconverter(self, merge_cells, ext, engine):
         assert xlwt.Alignment.HORZ_CENTER == xls_style.alignment.horz
         assert xlwt.Alignment.VERT_TOP == xls_style.alignment.vert
 
+    def test_write_append_mode_raises(self, merge_cells, ext, engine):
+        msg = "Append mode is not supported with xlwt!"
+
+        with ensure_clean(ext) as f:
+            with tm.assert_raises_regex(ValueError, msg):
+                ExcelWriter(f, engine=engine, mode='a')
+
 
 @td.skip_if_no('xlsxwriter')
 @pytest.mark.parametrize("merge_cells,ext,engine", [
@@ -2111,6 +2144,13 @@ def test_column_format(self, merge_cells, ext, engine):
 
             assert read_num_format == num_format
 
+    def test_write_append_mode_raises(self, merge_cells, ext, engine):
+        msg = "Append mode is not supported with xlsxwriter!"
+
+        with ensure_clean(ext) as f:
+            with tm.assert_raises_regex(ValueError, msg):
+                ExcelWriter(f, engine=engine, mode='a')
+
 
 class TestExcelWriterEngineTests(object):
 


Code review comments:
diff_hunk: @@ -993,23 +998,28 @@ class _OpenpyxlWriter(ExcelWriter):
     engine = 'openpyxl'
     supported_extensions = ('.xlsx', '.xlsm')
 
-    def __init__(self, path, engine=None, **engine_kwargs):
+    def __init__(self, path, engine=None, mode='w', **engine_kwargs):
         # Use the openpyxl module as the Excel writer.
         from openpyxl.workbook import Workbook
 
-        super(_OpenpyxlWriter, self).__init__(path, **engine_kwargs)
+        super(_OpenpyxlWriter, self).__init__(path, mode=mode, **engine_kwargs)
 
-        # Create workbook object with default optimized_write=True.
-        self.book = Workbook()
+        if self.mode == 'a':  # Load from existing workbook
+            from openpyxl import load_workbook
+            book = load_workbook(self.path)
+            self.book = book
+        else:
+            # Create workbook object with default optimized_write=True.
+            self.book = Workbook()
 
-        # Openpyxl 1.6.1 adds a dummy sheet. We remove it.
-        if self.book.worksheets:
-            try:
-                self.book.remove(self.book.worksheets[0])
-            except AttributeError:
+            # Openpyxl 1.6.1 adds a dummy sheet. We remove it.
path: pandas/io/excel.py
text: @user1:
you can remove this comment (and maybe don't need this anymore), 1.6.1 is lower than supported version

@author:
Unless I'm overlooking it I don't see an explicit min version. Happy to add one - what would that be?