Merge multiple files into one workbook

[Jignesh Sutar]

I'm revisiting something I have been trying to do for some years now but have yet to find a solution.

I'm looking for a solution to be able to merge multiple excel files into one singe workbook. Albert-Jan had written the code below some year ago which I experimented with but found that when the files were merged the formatting was lost. The code below works as a demonstration on the test files shipped with xlrd/xlwt.

I have recently become familiar with XlsxWriter but that only writes files from scratch and I don't believe it can read excel files whatsoever to accomplish this task. openpyxl I am not familiar with but a quick glance at the documentation doesn't suggest there is anything there that could help either?

Wonder if anyone could point me the right direction how to solve this?

Many thanks.

Jignesh

#######################################
"""
Merge all xls files in a given directory into one multisheet xls file.
The sheets get the orginal file name, without the extension.
File names should not exceed 29 characters
"""
import xlrd, xlwt
import glob, os.path
def merge_xls (in_dir, out_file="C:/Temp/Merged_outout.xls"):

    print in_dir
    print
    xls_files   = glob.glob(in_dir + "*.xls")
    print xls_files
    sheet_names = [os.path.basename(v)[:-4] for v in xls_files]
    sheet_excl  = [os.path.basename(v)[:-4] for v in xls_files if len(os.path.basename(v)[:-4]) > 29]
    merged_book = xlwt.Workbook()

    if in_dir[-1:] != "/": in_dir = in_dir + "/"
    xls_files.sort()
    if xls_files:
        for k, xls_file in enumerate(xls_files):
            print "---> Processing file %s" % (xls_file)
            if len (sheet_names[k]) <= 29:
                book = xlrd.open_workbook(xls_file)
                if book.nsheets == 1:
                    ws    = merged_book.add_sheet(sheet_names[k])
                    sheet = book.sheet_by_index(0)
                    for rx in range(sheet.nrows):
                        for cx in range(sheet.ncols):
                            ws.write(rx, cx, sheet.cell_value(rx, cx))
                elif book.nsheets in range(2, 100):
                    for sheetx in range(book.nsheets):
                        sheet0n = sheet_names[k]+str(sheetx+1).zfill(2)
                        ws      = merged_book.add_sheet(sheet0n)
                        sheet   = book.sheet_by_index(sheetx)
                        for rx in range(sheet.nrows):
                            for cx in range(sheet.ncols):
                                ws.write(rx, cx, sheet.cell_value(rx, cx))
                else:
                    print "ERROR *** File %s has %s sheets (maximum is 99)" % (xls_file, book.nsheets)
                    raise
            else:
                print "WARNING *** File name too long: <%s.xls> (maximum is 29 chars) " % (sheet_names[k])
                print "WARNING *** File <%s.xls> was skipped." % (sheet_names[k])
        merged_book.save(out_file)
        print
        print "---> Merged xls file written to %s using the following source files: " % (out_file)
        for k, v in enumerate(sheet_names):
            if len(v) <= 29:
                print "\t", str(k+1).zfill(3), "%s.xls" % (v)
        print
        if sheet_excl:
            print "--> The following files were skipped because the file name exceeds 29 characters: "
            for k, v in enumerate(sheet_excl):
                print "\t", str(k+1).zfill(3), v
    else:
        print "NOTE *** No xls files in %s. Nothing to do." % (in_dir)

merge_xls(in_dir="/xlrd-0.9.3/tests/")
#######################################

[Chris Withers]

You can probably rig something up with the code in xlutils.filter, but it'll require some work on your part.

Chris

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python-excel...@googlegroups.com.
To post to this group, send email to python...@googlegroups.com.
Visit this group at http://groups.google.com/group/python-excel.
For more options, visit https://groups.google.com/d/optout.

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

[John McNamara]

Hi,

The pyexcel module can merge Excel files into one workbook. I haven't use it and I don't know if it preserves styles but here is an example from the docs:

"Merge all excel files in directory into a book where each file become a sheet":

https://pythonhosted.org/pyexcel/cookbook.html#merge-all-excel-files-in-directory-into-a-book-where-each-file-become-a-sheet

Regards,

John

[John Yeung]

The very top of the page you linked to has this message in a red box:

---
Warning: The pyexcel DOES NOT consider Fonts, Styles and Charts at
all. In the resulting excel files, fonts, styles and charts will not
be transferred.
---

Also, it appears to just be a wrapper for various other packages, and
all of the Excel formats seem to be handled by xlrd and xlwt. It
claims to write .xlsx files, but I am doubtful that it does.

John Y.

[John Yeung]

On Sat, Dec 6, 2014 at 2:19 PM, Jignesh Sutar <jignes...@gmail.com> wrote:
> openpyxl I am not familiar with but a quick glance at
> the documentation doesn't suggest there is anything there that could help
> either?

I believe your glance was too quick. openpyxl is actually the only
Python package I'm aware of that DOES read .xlsx formatting.

However, I have not had the time to try it out for myself.

John Y.