Writing resutls into 2 different sheets in the same Excel file

Question

can you teach me whether Python can write into a same Excel file, but 2 different spreadsheets (tabs)?

Just for example, I want to pick and write the titles of below 4 websites, and write them into the same file title.xls but respectively in its Sheet1 and Sheet 2.

www.dailynews.com
www.dailynews.co.zw
www.gulf-daily-news.com
www.dailynews.gov.bw

I do them in 2 scripts, each for 2 websites:

from bs4 import BeautifulSoup
import urllib2
import xlwt

line_in_list = ['www.dailynews.com','www.dailynews.co.zw'] 
# line_in_list = [www.gulf-daily-news.com','www.dailynews.gov.bw']

book = xlwt.Workbook(encoding='utf-8', style_compression = 0)
sheet = book.add_sheet('Sheet1', cell_overwrite_ok = True) 
# sheet = book.add_sheet('Sheet2', cell_overwrite_ok = True)

for cor,websites in enumerate(line_in_list):
    url = "http://" + websites
    page = urllib2.urlopen(url)
    soup = BeautifulSoup(page.read())
    site_title = soup.find_all("title")
    print site_title
    sheet.write (cor, 0, site_title[0].text)

book.save("title.xls")

however, the script is overwriting the sheets. I can only have either Sheet1 or Sheet2 but never both.

any helps? thanks.

Why not have a `list` of `sheet`s? You can certainly have two sheets, but you can't assign the same name to both of them without a container in-between. — jonrsharpe, Aug 08 '14 at 09:16
thanks jonrsharpe. does it also apply when i run 2 scripts (1 script for 2 websites only), instead of picking 4 websites in 1 script? — Mark K, Aug 08 '14 at 09:29
I don't know what you mean. Does what apply? I doubt two scripts can access the same workbook without causing issues. — jonrsharpe, Aug 08 '14 at 09:31
sorry for the confusion. i am thinking: to have 2 scripts, each of them only picks 2 websites. i will run them one by one. after runnings, can i have 2 sheets in the same file written? — Mark K, Aug 08 '14 at 09:37
Why would you want to do that? Then you end up with duplicated code. — jonrsharpe, Aug 08 '14 at 09:40

score 8 · Answer 1 · edited Nov 30 '22 at 19:53

8

You can also do it using pandas.

import pandas as pd

# Add your data in list, which may contain a dictionary with the name of the      
# columns as the key
df1 = pd.DataFrame({'website': ['www.dailynews.com', 'www.dailynews.co.zw']})
df2 = pd.DataFrame({'website': ['www.gulf-daily-news.com', 'www.dailynews.gov.bw']})

# Create a new excel workbook
writer = pd.ExcelWriter('title.xlsx', engine='xlsxwriter')

# Write each dataframe to a different worksheet.
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')

# Save workbook
writer.close()

edited Nov 30 '22 at 19:53

Archit Jain

2,154
1
18
32

answered Feb 06 '18 at 08:34

Susa

361
4
9

1

I used this solution, was just missing the following: writer.save() – Susana Isabel Santos Feb 03 '22 at 21:30

score 4 · Accepted Answer · answered Aug 08 '14 at 09:19

4

If I correctly understood what you need. Sorry, can't comment to make it more clear.

sheet1 = book.add_sheet('Sheet1', cell_overwrite_ok = True) 
sheet2 = book.add_sheet('Sheet2', cell_overwrite_ok = True)
sheet1.write (cor, 0, site_title[0].text)
sheet2.write (cor, 0, site_title[0].text)

answered Aug 08 '14 at 09:19

dexter

141
3

thanks dexter. what if i want to run 2 scripts (each script for 2 websites only), instead of picking 4 websites in 1 script? – Mark K Aug 08 '14 at 09:30
as I remember, it's not possible to do it using only xlwt, but there're another ways. see the most-voted answer here http://stackoverflow.com/questions/2725852/writing-to-existing-workbook-using-xlwt – dexter Aug 08 '14 at 09:33

Herve H · Answer 3 · 2019-11-01T01:30:52.223

import numpy as np 
import pandas as pd

# Create a Dataframe
df1 = pd.DataFrame(np.random.rand(100).reshape(50,2),columns=['a','b'])
df2 = pd.DataFrame(np.random.rand(100).reshape(50,2),columns=['a','b'])

# Excel path
excelpath = 'path_to_your_excel.xlsx'

# Write your dataframes to difference sheets

with pd.ExcelWriter(excelpath) as writer:
    df1.to_excel(writer,sheet_name='Sheet1')
    df2.to_excel(writer,sheet_name = 'Sheet2')


""" I noticed that the above script overwrite all existing columns of in 
the excel. In case you want to keep some columns and sheet untouched, 
you might consider doing it the following way"""

import pandas as pd
import numpy as np
from openpyxl import load_workbook

book = load_workbook(excelpath)
writer = pandas.ExcelWriter(excelpath, engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df1.to_excel(writer, "Sheet1", columns=['a', 'b']) # only columns 'a' and 'b' will be populated 
df2.to_excel(writer,"Sheet2",columns=['a','b']) # only columns 'a' and 'b' will be populated 

writer.save()

score 0 · Answer 4 · answered Sep 09 '21 at 19:16

--Append Excel Data Sheet to Spreadsheet

import pandas as pd

#import os #from pandasql import sqldf

#pysqldf = lambda q: sqldf(q, globals())

df1 = pd.read_csv('MyData1.csv') df2 = pd.read_csv('MyData2.csv')

print(df1)

print(df2)

Differences_df = df1.merge(df2, indicator=True, how='outer')

#Differences_df[merged['_merge'] == 'right_only'] print(Differences_df)

with pd.ExcelWriter('MyInputData.xlsx', mode='a') as writer: Differences_df.to_excel(writer, sheet_name='Diff')

print("Spreadsheets Processed")

Writing resutls into 2 different sheets in the same Excel file

4 Answers4

Linked