How Do I Use Excel's Format Painter Across a Whole Workbook

Question

Every week I generate a large excel sheet using Python/Pandas. However, the xls writer in Pandas does not allow one to format the excel sheets likely because of the proprietary format. Currently, I have to go worksheet by worksheet in the newly generated file and copy the formatting from the sheet the week before which is a little obnoxious.

Is there a way (in order of preference):

Copy all the formatting from one excel sheet to another in Python
Format Paint all sheets from a workbook to a second workbook
This would be making a sheet with formatting and links which I could update and than resave, but I'm hoping for a solution like (1) or (2).

I am able to write out formats with xlwt (from python not pandas, though you could do it via pandas also). If interested see here: http://stackoverflow.com/questions/25863381/python-fastest-way-to-write-pandas-dataframe-to-excel-on-multiple-sheets/25871524#25871524 — JohnE, Sep 25 '14 at 18:24
Interesting stuff. I'm still hoping for a wholesale copy at some point, but I might be out of luck. — rhaskett, Oct 01 '14 at 21:29
One way I deal with this is to output the data from pandas as usual. Then make a new workbook, link it to the pandas output, and then format as desired. New/different output from pandas? Copy the formatted version and change the data source. — Paul H, Jul 31 '15 at 02:18

Grysik · Accepted Answer · 2015-08-04T14:14:27.543

2

I'd do it that way:

import win32com.client
xlPasteFormats                 = -4122
xlPasteSpecialOperationNone    = -4142
excelInstance = win32com.client.gencache.EnsureDispatch ("Excel.Application")
workbook = excelInstance.Workbooks.Item(1)
worksheet = workbook.Worksheets(1)
worksheet2 = workbook.Worksheets(3)
cells1 = worksheet.UsedRange
cells2 = worksheet2.UsedRange
cells1.Copy()
cells2.PasteSpecial(xlPasteFormats, xlPasteSpecialOperationNone)

which is quite similar to solution in VBA, because uses the same functions, but does it via COM, so you stay completely in Python.

In this code I had workbook open. If you want to open workbook you should put:

filepath = r"path:\To\Excel\Workbook"
excelInstance.Workbooks.Open(filepath)

edited Aug 04 '15 at 14:14

answered Aug 04 '15 at 14:08

Grysik

807
7
16

this is really close, but I'm having trouble pasting from workbook to workbook. excelInstance.Workbooks.Open(filepath); excelInstance.Workbooks.Open(filepath2) – rhaskett Aug 05 '15 at 23:10
got it... this is awesome. Thanks. – rhaskett Aug 05 '15 at 23:16

score 1 · Answer 2 · edited May 23 '17 at 10:26

Here is the VBA way to do it (from sancho.s's answer in this question), assuming the sheets in each workbook are named the same. You may be able to use those objects in Python, or at least create this macro in the workbook you copy from.

Sub FormatMAC()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = Workbooks("Results_2012 - Template - Master.xlsx")
    Set wb2 = Workbooks("Copy of Results_2012 - Template1.xlsm")
    Dim ws1 As Worksheet, ws2 As Worksheet
    For Each ws1 In wb1.Worksheets
      Set ws2 = wb2.Worksheets(ws1.Name)
      ws1.Cells.Copy
      ws2.Cells.PasteSpecial (xlPasteFormats)
    Next ws1
End Sub

How Do I Use Excel's Format Painter Across a Whole Workbook

2 Answers2

Linked