<p dir="auto">When using pd.ExcelWriter, it appears that calling the save method prevents you from subsequently creating new sheets. E.g. see the following test script</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import pandas as pd
import numpy as np
import os


if __name__ == &quot;__main__&quot; :
    dates = pd.date_range('20130101', periods=6)
    df = pd.DataFrame(np.random.randn(6,4), index=dates, columns=list('ABCD'))

    currentpath = os.path.realpath(__file__)
    outputpath = os.path.join(currentpath, &quot;..\\test.xlsx&quot;)

    writer = pd.ExcelWriter(outputpath)
    df.to_excel(writer, &quot;thing1&quot;)

    writer.save() #if you remove this line it works as expected

    new_sheet = writer.book.add_worksheet(&quot;thing2&quot;)
    new_sheet.write(1,1,&quot;test&quot;)

    print writer.book.sheetnames

    writer.save()
    writer.close()"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>
<span class="pl-k">import</span> <span class="pl-s1">numpy</span> <span class="pl-k">as</span> <span class="pl-s1">np</span>
<span class="pl-k">import</span> <span class="pl-s1">os</span>


<span class="pl-k">if</span> <span class="pl-s1">__name__</span> <span class="pl-c1">==</span> <span class="pl-s">"__main__"</span> :
    <span class="pl-s1">dates</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-en">date_range</span>(<span class="pl-s">'20130101'</span>, <span class="pl-s1">periods</span><span class="pl-c1">=</span><span class="pl-c1">6</span>)
    <span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">DataFrame</span>(<span class="pl-s1">np</span>.<span class="pl-s1">random</span>.<span class="pl-en">randn</span>(<span class="pl-c1">6</span>,<span class="pl-c1">4</span>), <span class="pl-s1">index</span><span class="pl-c1">=</span><span class="pl-s1">dates</span>, <span class="pl-s1">columns</span><span class="pl-c1">=</span><span class="pl-en">list</span>(<span class="pl-s">'ABCD'</span>))

    <span class="pl-s1">currentpath</span> <span class="pl-c1">=</span> <span class="pl-s1">os</span>.<span class="pl-s1">path</span>.<span class="pl-en">realpath</span>(<span class="pl-s1">__file__</span>)
    <span class="pl-s1">outputpath</span> <span class="pl-c1">=</span> <span class="pl-s1">os</span>.<span class="pl-s1">path</span>.<span class="pl-en">join</span>(<span class="pl-s1">currentpath</span>, <span class="pl-s">"..<span class="pl-cce">\\</span>test.xlsx"</span>)

    <span class="pl-s1">writer</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">ExcelWriter</span>(<span class="pl-s1">outputpath</span>)
    <span class="pl-s1">df</span>.<span class="pl-en">to_excel</span>(<span class="pl-s1">writer</span>, <span class="pl-s">"thing1"</span>)

    <span class="pl-s1">writer</span>.<span class="pl-en">save</span>() <span class="pl-c">#if you remove this line it works as expected</span>

    <span class="pl-s1">new_sheet</span> <span class="pl-c1">=</span> <span class="pl-s1">writer</span>.<span class="pl-s1">book</span>.<span class="pl-en">add_worksheet</span>(<span class="pl-s">"thing2"</span>)
    <span class="pl-s1">new_sheet</span>.<span class="pl-en">write</span>(<span class="pl-c1">1</span>,<span class="pl-c1">1</span>,<span class="pl-s">"test"</span>)

    <span class="pl-k">print</span> <span class="pl-s1">writer</span>.<span class="pl-s1">book</span>.<span class="pl-s1">sheetnames</span>

    <span class="pl-s1">writer</span>.<span class="pl-en">save</span>()
    <span class="pl-s1">writer</span>.<span class="pl-en">close</span>()</pre></div>
<p dir="auto">Hello,</p>
<p dir="auto">try</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="import requests
from pandas.io.json import json_normalize
import json
import pandas as pd
response = requests.get(&quot;http://api.openweathermap.org/data/2.5/history/station?start=1356220800&amp;end=1356307200&amp;type=hour&amp;id=5530&quot;)
df = json_normalize(json.loads(response.text)['list'])
df['dt'] = pd.to_datetime(df['dt'], unit='s')
#df = df.set_index('dt')
df = df[['dt', 'main.temp.ma']]
print(df)

engine = 'openpyxl'
filename = &quot;out_%s.xlsx&quot; % engine
writer = pd.ExcelWriter(filename, engine=engine)
#df.to_excel(&quot;out.xls&quot;)
df.to_excel(writer)

engine = 'xlsxwriter'
filename = &quot;out_%s.xlsx&quot; % engine
writer = pd.ExcelWriter(filename, engine=engine)
#df.to_excel(&quot;out.xls&quot;)
df.to_excel(writer)

engine = 'xlwt'
filename = &quot;out_%s.xls&quot; % engine
writer = pd.ExcelWriter(filename, engine=engine)
#df.to_excel(&quot;out.xls&quot;)
df.to_excel(writer)"><pre class="notranslate"><span class="pl-k">import</span> <span class="pl-s1">requests</span>
<span class="pl-k">from</span> <span class="pl-s1">pandas</span>.<span class="pl-s1">io</span>.<span class="pl-s1">json</span> <span class="pl-k">import</span> <span class="pl-s1">json_normalize</span>
<span class="pl-k">import</span> <span class="pl-s1">json</span>
<span class="pl-k">import</span> <span class="pl-s1">pandas</span> <span class="pl-k">as</span> <span class="pl-s1">pd</span>
<span class="pl-s1">response</span> <span class="pl-c1">=</span> <span class="pl-s1">requests</span>.<span class="pl-en">get</span>(<span class="pl-s">"http://api.openweathermap.org/data/2.5/history/station?start=1356220800&amp;end=1356307200&amp;type=hour&amp;id=5530"</span>)
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-en">json_normalize</span>(<span class="pl-s1">json</span>.<span class="pl-en">loads</span>(<span class="pl-s1">response</span>.<span class="pl-s1">text</span>)[<span class="pl-s">'list'</span>])
<span class="pl-s1">df</span>[<span class="pl-s">'dt'</span>] <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-en">to_datetime</span>(<span class="pl-s1">df</span>[<span class="pl-s">'dt'</span>], <span class="pl-s1">unit</span><span class="pl-c1">=</span><span class="pl-s">'s'</span>)
<span class="pl-c">#df = df.set_index('dt')</span>
<span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">df</span>[[<span class="pl-s">'dt'</span>, <span class="pl-s">'main.temp.ma'</span>]]
<span class="pl-en">print</span>(<span class="pl-s1">df</span>)

<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s">'openpyxl'</span>
<span class="pl-s1">filename</span> <span class="pl-c1">=</span> <span class="pl-s">"out_%s.xlsx"</span> <span class="pl-c1">%</span> <span class="pl-s1">engine</span>
<span class="pl-s1">writer</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">ExcelWriter</span>(<span class="pl-s1">filename</span>, <span class="pl-s1">engine</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)
<span class="pl-c">#df.to_excel("out.xls")</span>
<span class="pl-s1">df</span>.<span class="pl-en">to_excel</span>(<span class="pl-s1">writer</span>)

<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s">'xlsxwriter'</span>
<span class="pl-s1">filename</span> <span class="pl-c1">=</span> <span class="pl-s">"out_%s.xlsx"</span> <span class="pl-c1">%</span> <span class="pl-s1">engine</span>
<span class="pl-s1">writer</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">ExcelWriter</span>(<span class="pl-s1">filename</span>, <span class="pl-s1">engine</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)
<span class="pl-c">#df.to_excel("out.xls")</span>
<span class="pl-s1">df</span>.<span class="pl-en">to_excel</span>(<span class="pl-s1">writer</span>)

<span class="pl-s1">engine</span> <span class="pl-c1">=</span> <span class="pl-s">'xlwt'</span>
<span class="pl-s1">filename</span> <span class="pl-c1">=</span> <span class="pl-s">"out_%s.xls"</span> <span class="pl-c1">%</span> <span class="pl-s1">engine</span>
<span class="pl-s1">writer</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-v">ExcelWriter</span>(<span class="pl-s1">filename</span>, <span class="pl-s1">engine</span><span class="pl-c1">=</span><span class="pl-s1">engine</span>)
<span class="pl-c">#df.to_excel("out.xls")</span>
<span class="pl-s1">df</span>.<span class="pl-en">to_excel</span>(<span class="pl-s1">writer</span>)</pre></div>
<p dir="auto">Only <code class="notranslate">out_xlsxwriter.xlsx</code> is create<br>
I was expecting <code class="notranslate">out_openpyxl.xlsx</code> and <code class="notranslate">out_xlwt.xls</code> to be created.</p>
<p dir="auto">See also <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="52758265" data-permission-text="Title is private" data-url="https://github.com/pandas-dev/pandas/issues/9139" data-hovercard-type="issue" data-hovercard-url="/pandas-dev/pandas/issues/9139/hovercard" href="https://github.com/pandas-dev/pandas/issues/9139">#9139</a></p>
<p dir="auto">Kind regards</p>