Hi @lambertus​ , would it be possible to have your macro stored in an Excel template that can be used during the write process in your workspace? That might be an easier option for you if it will work in your use case.

Yes that is the case. The macro is stored in a separate PERSONAL.xlsb. It contains basic formatting which I want to apply to different Excel outputs in different workbenches. The formatting used is not included in the current Excel writer.

@lambertus​,

I think I'm following what you are trying to do now. Have you looked at the ExcelStyler transformer? For more functionality I have used openpyxl in a python caller transformer to modify the Excel document with additional formatting after it was created with an Excel FeatureWriter. As far as running the macro, you are going down the right path with win32com, but I have had issues with this in the past, so I went the python package openpyxl route.

 

Here is the openpyxl documentation if you want to take a look. It may not be your best option, but wanted to pass this along.

https://openpyxl.readthedocs.io/en/stable/

 

 

 

 

 

Thanks @Chris Warren​ ! I will dive into the openpyxl route :)

I assume you used this in a shutdown python script?

I have done it with a shut-down script as well as with the python caller after the feature writer.

Okay! Is it possible to share these python scripts with me?

This is an example of using the python caller after the feature writer. You can go with a python shut-down script instead if you want. Very basic example that only fills in header information in the Excel document. Of course you could do all of your formatting and other manipulation that you would need here as well. In my case I am using an Excel template that contains all of my formatting for this specific report, so I just use this to fill in the header information. Not that there isn't a way to do this with pure FME, as there are many ways to go about it.

 

import fme
import fmeobjects
import openpyxl
 
def excel_header(feature):
    '''
    Add the project information
    headers.
    '''
    # Excel path
    excel_document = feature.getAttribute('_dataset')
    
    # Workbook
    wb = openpyxl.load_workbook(excel_document)
    
    # Worksheet
    ws = wb.active
    
    # Filename value
    filename = feature.getAttribute('_filename')
    wse'B1'] = filename
    
    # Coordinate system
    coordinate_system = feature.getAttribute('Coordinate System')
    ws 'B2'] = coordinate_system
    
    # Layer
    layer = feature.getAttribute('autocad_layer')
    wsi'B3'] = layer
    
    # Entity handle
    handle = feature.getAttribute('autocad_entity_handle')
    wsa'B4'] = handle
    
    # Timestamp
    timestamp = feature.getAttribute('_timestamp')
    ws 'B5'] = timestamp
       
    # Save the workbook
    wb.save(excel_document)

 openpyxl documentation

https://openpyxl.readthedocs.io/en/stable/

 

Thanks for your example @Chris Warren​!

 

In the meanwhile I created a script with openpyxl which applies 5 formatting steps to a saved Excel file.