Hey Sai,

I totally get the frustration of being stuck with writing to a single sheet using spark-excel—been there! Luckily, there are a few solid workarounds to get your data into multiple tabs in the same Excel file. Let me break down the most practical options for you:

If your data isn't massive enough to cause memory issues on the driver node, converting Spark DataFrames to Pandas DataFrames and using ExcelWriter is the quickest way. Pandas natively supports writing multiple sheets to one Excel file.

Here's a quick Python example:

# Convert Spark DataFrames to Pandas DataFrames
df_sheet1 = spark.sql("SELECT * FROM your_table_1").toPandas()
df_sheet2 = spark.sql("SELECT * FROM your_table_2").toPandas()

# Write to multiple sheets
from pandas import ExcelWriter

with ExcelWriter("your_output.xlsx") as writer:
    df_sheet1.to_excel(writer, sheet_name="Sales_Data", index=False)
    df_sheet2.to_excel(writer, sheet_name="Customer_Data", index=False)

Pro tip: Skip index=False if you want to keep the Pandas index column in your Excel sheet.

For Scala/Spark projects, Apache POI is a robust Java library that lets you build Excel files from scratch, including multiple sheets. You'll need to add the POI dependencies first, then write code to populate each sheet with your Spark data.

First, add these dependencies to your build.sbt:

libraryDependencies += "org.apache.poi" % "poi" % "4.1.2"
libraryDependencies += "org.apache.poi" % "poi-ooxml" % "4.1.2"

Then, here's a sample Scala snippet:

import org.apache.poi.xssf.usermodel.XSSFWorkbook
import java.io.FileOutputStream

// Create a new Excel workbook
val workbook = new XSSFWorkbook()

// Function to write a Spark DataFrame to a sheet
def writeDFToSheet(df: org.apache.spark.sql.DataFrame, sheetName: String): Unit = {
    val sheet = workbook.createSheet(sheetName)
    // Write header row
    val headerRow = sheet.createRow(0)
    df.columns.zipWithIndex.foreach { case (col, idx) =>
        headerRow.createCell(idx).setCellValue(col)
    }
    // Write data rows
    df.collect().zipWithIndex.foreach { case (row, rowIdx) =>
        val dataRow = sheet.createRow(rowIdx + 1)
        row.toSeq.zipWithIndex.foreach { case (value, colIdx) =>
            dataRow.createCell(colIdx).setCellValue(value.toString)
        }
    }
}

// Write your DataFrames to separate sheets
writeDFToSheet(spark.table("table1"), "Sheet1")
writeDFToSheet(spark.table("table2"), "Sheet2")

// Save the workbook
val outputStream = new FileOutputStream("multi_sheet_output.xlsx")
workbook.write(outputStream)
outputStream.close()
workbook.close()

Note: collect() pulls all data to the driver node, so this works best for smaller datasets. For large data, you might need to process in chunks or use distributed writing tools.

If you prefer Python over Scala, OpenPyXL is a great library for manipulating Excel files. Similar to POI, you can create sheets and populate them with data from Spark DataFrames:

from openpyxl import Workbook

# Initialize workbook
wb = Workbook()
ws1 = wb.active
ws1.title = "First_Sheet"

# Write header
ws1.append(df1.columns.tolist())
# Write data rows
for row in df1.collect():
    ws1.append(list(row))

# Create second sheet
ws2 = wb.create_sheet("Second_Sheet")
ws2.append(df2.columns.tolist())
for row in df2.collect():
    ws2.append(list(row))

# Save the file
wb.save("multi_sheet.xlsx")

Each of these methods should solve your problem—pick the one that fits your tech stack and data size best!

内容的提问来源于stack exchange，提问作者Bharath