I have an optimization problem that runs in a for loop. I want the results of each new iteration to be saved in a different tab in the same workbook.
This is what I'm doing. Instead of giving me multiple tabs in the same workbook, I'm getting multiple workbooks.
from openpyxl import Workbook
wb1 = Workbook()
for i in range(n):
ws = wb1.active()
ws.title = str(i)
#code on formatting sheet, optimization problem
wb1.save('outfile'+str(i)+'.xlsx')
Every iteration you are grabbing the same worksheet - ws = wb1.active() - and then simply saving your results to a different workbook.
You simply need to create a new sheet on each iteration. Something like this:
from openpyxl import Workbook
wb1 = Workbook()
for i in range(n):
ws = wb1.create_sheet("run " + str(i))
#code on formatting sheet, optimization problem
wb1.save('outfile.xlsx')
Notice that the save is indented out to simply save the file once all worksheets have been formatted. It is not necessary to save on each iteration. The saving operation can take time, especially when adding more tabs.
This code will create Excel Workbook containing worksheets same as the number of strings in a text file taken as the input. Here i have a text file named 'sample.txt' having 3strings. This code will so create 3 worksheets in a workbook named 'reformatted.data.xls'.
Also i have removed the default worksheets that get created automatically when the workbook object is created.
import xlwt
from openpyxl import Workbook
wb1 = Workbook()
row = 0
f = open('C:\Desktop\Mytestcases\sample.txt')
lines = f.readlines()
for i in range(len(lines)):
ws = wb1.create_sheet("worksheet" + str(i))
ws.cell(row=1, column=1).value = lines[i]
row += 1
sheet = wb1.get_sheet_by_name('Sheet')
wb1.remove_sheet(sheet)
wb1.save('reformatted.data.xls')
