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

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 fo

Solution 1:

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)

Solution 2:

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
    ForEach ws1 In wb1.Worksheets
      Set ws2 = wb2.Worksheets(ws1.Name)
      ws1.Cells.Copy
      ws2.Cells.PasteSpecial (xlPasteFormats)
    Next ws1
EndSub