I am creating a class called excel functions for xlutils, xlrd, and xlwt that I might ultimately make a library. If you are interested in helping I am trying to make a delete sheet function.
You might want to move towards openpyxl and/or pyexcel because they are easier and have functions for this.
Here is how to copy using open pyxl: Copy whole worksheet with openpyxl
Here is the documentation for pyexcel which is a wrapper for xlwt, xlrd, and xlutils: https://pyexcel.readthedocs.io/en/latest/
If you want to extract data from one excel workbook and output to another you would want to use createCopy(original workbook, other workbook, original filename, new filename)
import xlwt
import xlrd
import xlutils.copy
import xlutils class excelFunctions():
def getSheetNumber(self, fileName, sheetName):
# opens existing workbook
workbook = xlrd.open_workbook(fileName, on_demand=True)
#turns sheet name into sheet number
for index, sheet in enumerate(workbook.sheet_names()):
if sheet == sheetName:
return index
def createSheet(self, fileName, sheetName):
# open existing workbook
rb = xlrd.open_workbook(fileName, formatting_info=True, on_demand=True)
# make a copy of it
wb = xl_copy(rb)
# creates a variable called sheets which stores all the sheet names
sheets = rb.sheet_names()
# creates a string which is equal to the sheetName user input
str1 = sheetName
# checks to see if the given sheetName is a current sheet
if (str1 not in sheets):
# add sheet to workbook with existing sheets
Sheet = wb.add_sheet(sheetName)
# save the sheet with the same file name as before
wb.save(fileName)
else:
# this declares the sheet variable to be equal to the sheet name the user gives
sheet = wb.get_sheet(self.getSheetNumber(fileName, sheetName))
# save the sheet with the same file name as before
wb.save(fileName)
def createCopy(self, fileName, fileName2, sheetName, sheetName2):
# open existing workbook
rb = xlrd.open_workbook(fileName, formatting_info=True)
# defines sheet as the name of the sheet given
sheet = rb.sheet_by_name(sheetName)
# makes a copy of the original sheet
wb = xl_copy(rb)
# creates an int called column_count which is equal to the sheets maximum columns
column_count = sheet.ncols - 1
# creates a blank array called stuff
Stuff = []
# this loops through adding columns from the given sheet name
for i in range (0, column_count):
Stuff.append([sheet.cell_value(row, i) for row in range(sheet.nrows)])
# create a sheet if there is not already a sheet
self.createSheet(fileName, sheetName2)
# defines sheet as the new sheet
sheet = wb.get_sheet(self.getSheetNumber(fileName, sheetName2))
# this writes to the sheet
for colidx, col in enumerate(Stuff):
for rowidx, row in enumerate(col):
sheet.write(rowidx, colidx, row)
# this saves the file
wb.save(fileName2)
