Save workbook with macros

[Lightyagami]

Hi, Is there any way to save a workbook without losing the macros in databricks?

[Kaniz]

Hi @Lightyagami, When working with Databricks and dealing with macros, there are a few approaches you can consider to save a workbook without losing the macros:

1. Export to Excel with Macros Enabled:

   • You can generate an Excel file directly from PySpark without converting to Pandas first. Here’s how:

     df_spark.write.format("com.crealytics.spark.excel") \
         .option("header", "true") \
         .mode("overwrite") \
         .save(path)
     

     Make sure you have the com.crealytics:spark-excel library installed (you can add it as a Maven libra...¹.

2. Convert to Pandas and Export:

   • While there’s no direct way to save an Excel document from a Spark DataFrame, you can convert your Spark DataFrame to a Pandas DataFrame and then export it. Here’s an example:

     # Create a dummy Spark DataFrame
     spark_df = spark.sql('SELECT * FROM default.test_delta LIMIT 100')
     
     # Convert Spark DataFrame to Pandas DataFrame
     pandas_df = spark_df.toPandas()
     
     # Export Pandas DataFrame to an XLSX file
     pandas_df.to_excel('excel_test.xlsx', engine='xlsxwriter')
     

     Note that you might need to adjust the file paths and permissions as needed ¹.

3. Workaround for Macros in XLSM Files:

   • If you specifically need to work with XLSM files (Excel files with macros enabled), you can use recent versions of XlsxWriter. Extract the VbaProject.bin macro file from an existing XLSM file and insert it into your new file. Here’s a snippet:

     import pandas as pd
     
     df = pd.DataFrame({'First': [5, 2, 0, 10, 4], 'Second': [9, 8, 21, 3, 8]})
     writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
     # Continue with writing your DataFrame to the XLSM file
     

     Again, ensure that you have the necessary libraries installed ².

Remember to adapt these solutions based on your specific use case and requirements. Happy coding! 🚀