Copying only worksheet of XLS to become new worksh

Question

Using the Python test code below I am trying to copy the only worksheet in an Excel (*.xls) file into a new Excel file with one worksheet.

The input spreadsheet looks like:

from copy import deepcopy
from xlrd import open_workbook
from xlutils.copy import copy as copy
from xlwt import Workbook

rb = open_workbook(r"C:\Temp\test1.xls",formatting_info=True)
wb = copy(rb)
new_book = Workbook()
r_sheet = rb.sheet_by_index(0)

sheets = []
w_sheet = deepcopy(wb.get_sheet(0))
w_sheet.set_name("test1")

for row_index in range(0, r_sheet.nrows):
    for col_index in range(0, r_sheet.ncols):
        cell_value = r_sheet.cell(row_index, col_index).value
        print cell_value
        w_sheet.write(row_index, col_index, cell_value)

sheets.append(w_sheet)
new_book._Workbook__worksheets = sheets

new_book.save(r"C:\Temp\test2.xls")

If I run the code it shows the output below and creates the new Excel file with the worksheet named test1.

Python 2.7.10 (default, May 23 2015, 09:40:32) [MSC v.1500 32 bit (Intel)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> ================================ RESTART ================================
>>> 
Col1
Col2
Col3
a
1.0
X
b
2.0
Y
c
3.0
Z
>>> 

Unfortunately, the output, which appears to have the correct number of cells written to it has all non-numeric cell values written as #VALUE!.

Using Python 2.7.10, is there a simple way to read the worksheet from one XLS and then write it as a worksheet in another XLS file?

I do not want to simply copy the spreadsheet and then rename the worksheet in the new one because once I can get this to work I want to copy the only worksheet in each of a dozen spreadsheets to become a worksheet of the same name in a spreadsheer with a dozen worksheets.

Answer 1

from xlrd import open_workbook
from xlutils.copy import copy

# Read the workbook
rb = open_workbook("input.xls", formatting_info=True)

# Copy into a new workbook
wb = copy(rb)

# Rename to 'test1' as the original post asked for 
ws = wb.get_sheet(0)
ws.name = 'test1'

# Save the new workbook
wb.save("output.xls")

Answer 2

Try using pyexcel. It will make life much easier for what you're trying to do.

Based on your final requirement of building a new workbook using one sheet from a dozen other workbooks, here is some code to help.

import pyexcel as pe

outputbook = "merged.xls"
inputbooks = {
  "test1.xls" : "Sheet1",
  "test2.xls" : "Sheet1",
  "test3.xls" : "Sheet1",
}

merged_book = None
for book, sheet in inputbooks.iteritems():
  wb = pe.get_book(file_name=book)
  if merged_book is None:
    merged_book = wb[sheet]
  else:
    merged_book = merged_book + wb[sheet]

merged_book.save_as(outputbook)

inputbooks is a dictionary mapping of the workbooks you wish to read in against their sheet.

You may need to install the additional plugin depending on which filetype you're working with:

pip install pyexcel pyexcel-xls

If that is not present you may see an error like:

IOError: No suitable library found for xls