Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
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:
Export to Excel with Macros Enabled:
You can generate an Excel file directly from PySpark without converting to Pandas first. Hereโs how:
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')
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
Remember to adapt these solutions based on your specific use case and requirements. Happy coding! ๐
Connect with Databricks Users in Your Area
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโt want to miss the chance to attend and share knowledge.
If there isnโt a group near you, start one and help create a community that brings people together.