如何通过SQL将Oracle的SPOOL数据导入Excel工作簿？

Great question! Let's break this down clearly:

First, the reason your attempt to use set sqlformat csv and spool to a .xlsx file failed is simple: .xlsx isn't a plain text format—it's a compressed collection of XML files with a strict structure. CSV is raw comma-separated text, so dumping CSV content into a .xlsx file confuses Excel, which expects that compressed XML structure instead of plain text.

Now, for your goal of creating Excel workbooks and importing CSV data into them using SQL/PLSQL-based workflows, here are practical, actionable solutions:

Oracle doesn't have built-in SQL functions to create Excel files directly, but you can use PL/SQL with UTL_FILE alongside open-source packages designed to handle Excel's complex format.

For example, using the widely-used XLSX_WRITER PL/SQL package (you'll need to install it first), you can generate a valid .xlsx file with multiple sheets, pulling data directly from your tables:

DECLARE
  v_workbook xlsx_writer.workbook_type;
  v_sheet xlsx_writer.sheet_type;
BEGIN
  -- Initialize a new workbook
  v_workbook := xlsx_writer.new_workbook;
  v_sheet := xlsx_writer.add_sheet(v_workbook, 'Customer_Data');

  -- Write header row
  xlsx_writer.write_row(v_sheet, 1, 'ID', 'Full_Name', 'Email');

  -- Pull and write data from your table
  FOR rec IN (SELECT customer_id, full_name, email FROM your_customers_table) LOOP
    xlsx_writer.write_row(v_sheet, xlsx_writer.get_last_row(v_sheet)+1, rec.customer_id, rec.full_name, rec.email);
  END LOOP;

  -- Save the workbook to an Oracle directory (create this directory object first)
  xlsx_writer.save_workbook(v_workbook, 'EXPORT_DIR', 'customer_records.xlsx');
END;
/

Note: You'll need to create an Oracle directory object pointing to a filesystem location accessible by the database, and grant read/write permissions to your user for that directory.

This is my go-to approach because it plays to SQL*Plus's strength (exporting clean, reliable CSVs) and uses external tools to handle Excel's complexity. You can use PowerShell, Python, or VBScript to automate merging multiple CSVs into a single Excel workbook with separate sheets.

Here's a quick PowerShell example that combines exported CSVs:

# Create a background Excel instance
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Add()

# Import first CSV into Sheet 1
$sheet1 = $workbook.Sheets.Item(1)
$sheet1.Name = 'Customers'
$sheet1.Cells.LoadFromCSV("C:\exports\customers.csv")

# Add a new sheet for the second CSV
$sheet2 = $workbook.Sheets.Add()
$sheet2.Name = 'Orders'
$sheet2.Cells.LoadFromCSV("C:\exports\orders.csv")

# Save and clean up to avoid lingering Excel processes
$workbook.SaveAs("C:\exports\combined_records.xlsx")
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

You can tie this together with a batch/shell script that runs your SQL*Plus export scripts first, then executes the PowerShell/Python merge script automatically.

If you don't need full automation, Oracle SQL Developer has a built-in export wizard that lets you select multiple tables and export them directly to a single .xlsx file (each table gets its own sheet). Just right-click your schema or tables, select "Export", choose "Excel (.xlsx)" as the format, and follow the prompts.

Final Note #

Never try to save raw CSV content as .xlsx directly—they're fundamentally different file formats. For automated workflows, exporting CSVs with SQL*Plus and merging via a script is the most scalable approach. If you need to generate Excel files entirely within Oracle, use a dedicated PL/SQL Excel package like XLSX_WRITER.

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