CODEWITHSUNDEEP
excelpython-2.7csvxlrdxlwt
Nick M.
Nick M.

Reputation: 259

Adding a sheet to an existing excel worksheet without deleting other sheet

I am trying to add a sheet to the excel file: ex.xls and whenever I do it deletes all the previously made sheets.

How do I add a sheet to this excel file without deleting the other sheets?

Here is my code to create a sheet:

import xlwt
import xlrd

wb = Workbook()
Sheet1 = wb.add_sheet('Sheet1')
wb.save('ex.xls')

Upvotes: 4

Views: 39453

Answers (3)

Muzammil_Saeed
Muzammil_Saeed

Reputation: 1

Its because add_sheet function overwrites the existing sheet. Instead use create_sheet. It will solve your problem.

ws1 = wb.create_sheet("Mysheet") # insert at the end (default)
ws2 = wb.create_sheet("Mysheet", 0) # insert at first position
ws3 = wb.create_sheet("Mysheet", -1) # insert at the penultimate position

Upvotes: 0

Travis Boop
Travis Boop

Reputation: 53

Below is using "openpyxl" to create a new sheet in an excel workbook.

import openpyxl

wb=openpyxl.load_workbook("Example_Excel.xlsx")
wb.create_sheet("Sheet1")

If the sheets or workbook do not exist yet you will get an error, to avoid this

import openpyxl

wb=openpyxl.load_workbook("Example_Excel.xlsx")
try:
    wb["Sheet1"]
except:
    wb.create_sheet("Sheet1")

Depending on how you want to use it below is an example of writing info to multiple pages

import openpyxl

work_book = 'Example_Excel.xlsx'
sheets = "Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"

for current_sheet in sheets:
    wb=openpyxl.load_workbook(work_book)

    #if the sheet doesn't exist, create a new sheet
    try:
      wb[current_sheet]
    except:
      wb.create_sheet(current_sheet) 

    #wait for user to press "Enter" before starting on next sheet
    raw_input("Press Enter to continue...")

#The code for you wish repeated for each page
    #This example will print the sheet name to "B2" cell on that sheet
    cell ="B"+str(2)
    sheet=wb[current_sheet]
    sheet[cell].value= current_sheet

Upvotes: 3

mechanical_meat
mechanical_meat

Reputation: 169334

I believe this is the only way to do what you want:

import xlrd, xlwt
from xlutils.copy import copy as xl_copy

# open existing workbook
rb = xlrd.open_workbook('ex.xls', formatting_info=True)
# make a copy of it
wb = xl_copy(rb)
# add sheet to workbook with existing sheets
Sheet1 = wb.add_sheet('Sheet1')
wb.save('ex.xls')

Upvotes: 8

Related Questions