how to write into excel file without dataframe using xlsxwriter

Question

If my dataframe is empty then I simply need to create empty excel file and write in it "There is no data for selected timeframe "

folder_list = ['San Diego', 'Vista']
if not df.empty:
    # if daraframe is not empty then do this:
    for location, d in df.groupby('OfficeLocation'):
        for folder in folder_list:
            if folder == location: 
                writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{location}.xlsx',engine='xlsxwriter')                  
            else: 
                print('df is empty')
            writer.save()
# if dataframe is empty I need to create empty excel file and wirte in cell A1 "There is no data for seleted timeframe"
else:
    for folder in folder_list:
        # this creates empty file with sheet name 'Sheet1'
        writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{folder}.xlsx',engine='xlsxwriter') 
        wb = writer.book
        ws = writer.sheets['Sheet1'] # this gives me an error
        writer.save()

Error:

Traceback (most recent call last):
  File "\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\tempCodeRunnerFile.py", line 41, in <module>
    ws = writer.sheets['Sheet1'] # this gives me an error
KeyError: 'Sheet1'

r.ook · Accepted Answer · 2019-10-28 18:49:03Z

2

You should be adding a new sheet instead:

for folder in folder_list:
    # this creates empty file with sheet name 'Sheet1'
    writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{folder}.xlsx', engine='xlsxwriter') 
    wb = writer.book
    ws = wb.add_worksheet() # change to this
    writer.save()

See relevant documentation here: https://xlsxwriter.readthedocs.io/workbook.html#add_worksheet

If you were to user the default openpyxl engine, then the respective method is:

ws = wb.create_sheet()

edited Oct 28, 2019 at 18:49

answered Oct 28, 2019 at 18:29

r.ook

13.9k2 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Serdia Over a year ago

Thanks, but it gives an error: 'Workbook' object has no attribute 'create_sheet'

r.ook Over a year ago

Are you committed to using xlsxwriter as the engine? I'm using openpyxl and this method works (openpyxl is the default engine used by pandas if you don't designate an engine).

r.ook Over a year ago

I believe for xlsxwriter the equivalent is .add_worksheet(). I'll edit the answer shortly

Serdia Over a year ago

Yes. That is correct. .add_worksheet() is what I needed. Thank you. So in a future what is the best engine to use?` xlsxwriter` or openpyxl?

r.ook Over a year ago

That's up to your preference. I only asked because I wasn't familiar with xlsxwriter, but either one should suffice as long as you are comfortable with it.

Florian Bernard · Accepted Answer · 2019-10-28 18:59:28Z

1

Why you don't simply create it in the same loop like this.

folder_list = ['San Diego', 'Vista']

# if daraframe is not empty then do this:
for location, d in df.groupby('OfficeLocation'):
    for folder in folder_list:
        if folder == location:
           if not df.empty: 
                writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{location}.xlsx',engine='xlsxwriter')                  
            else:
                writer=pd.ExcelWriter(f'\\\\my\username\Documents\Python\Split DataFrame by Multiple dataframes\{folder}\{folder}.xlsx',engine='xlsxwriter') 
                df_empty = pd.DataFrame({"There is no data for seleted timeframe" : []})
                df_empty.to_excel(writer, index=False)
                print('df is empty')
            writer.save()

But with your method you create a new file each times.

edited Oct 28, 2019 at 18:59

answered Oct 28, 2019 at 18:26

Florian Bernard

2,5891 gold badge11 silver badges24 bronze badges

4 Comments

Serdia Over a year ago

No, in a first part of a loop it will not generate file if dataframe is empty

Serdia Over a year ago

I tried to put if not df.empty: in that location but it does not create any excel files. It works only if it at the beginning of the loop

Serdia Over a year ago

Thank. It works as well, but I think you need to convert df_empty to DataFrame: df_empty = pd.DataFrame({'There is no data for seleted timeframe': []})

Florian Bernard Over a year ago

@Oleg yes, code updated. Fell free to mark this awnser as accepted by ticking the check mark.

Collectives™ on Stack Overflow

how to write into excel file without dataframe using xlsxwriter

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related