4

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')
Nick M.
  • 259
  • 1
  • 8
  • 22
  • I assume you can't do `Sheet2 = wb.add_sheet('Sheet2')`? – BruceWayne Jun 28 '16 at 16:44
  • I can, but that just creates two new sheets while deleting all the other sheets – Nick M. Jun 28 '16 at 17:14
  • 1
    This is because you are creating a new workbook (an new excel file), adding a worksheet, and then overwriting your existing excel file. I know this doesn't solve your question Bernie did that, but thought you might appreciate an explanation as to why this failed. – Bill Kidd Nov 16 '17 at 08:08

3 Answers3

8

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')
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • Cheers, mate. Glad I'm able to help you. – mechanical_meat Jun 29 '16 at 14:26
  • If you don't mind seeing as your the only one who really knows this stuff: http://stackoverflow.com/questions/38063661/python-excel-how-to-turn-sheet-name-into-sheet-number – Nick M. Jun 29 '16 at 14:30
  • How do I copy every thing from the first sheet of an excel sheet to the fifth sheet while referencing them by name – Nick M. Jun 29 '16 at 14:34
  • Maybe create a new question for that. – mechanical_meat Jun 29 '16 at 14:40
  • One thing to keep in mind is that xlrd is for reading existing excel files only, while xlwt is for writing only. That is why as Bernie shows you need the utils to bridge between. Here you can see that the existing file is being opened, the data copied into a new workbook, then a new sheet is added and finally writing over the existing excel file. – Bill Kidd Nov 16 '17 at 08:10
3

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
Travis Boop
  • 53
  • 8
0

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
Muzammil_Saeed
  • 1