Pentaho如何通过字段或变量指定文件名创建Excel文件

Got it, let's tackle this—since you're already pulling unique Location values and used them for CSV filenames, switching to Excel just needs a small tweak because the Microsoft Excel Output step doesn't let you pick a field directly for the filename like Table Input does for CSV. Here's how to make it work:

Option 1: Use Variables for Single Excel File #

If you're generating one Excel file per unique Location (e.g., processing one Location at a time), follow these steps:

• After getting your unique Location value (via Sort + Unique Rows or whatever method you're using), add a Set Variable step.
  • In the step configuration:
    • Select your Location field as the "Field to use"
    • Set a variable name (like EXCEL_FILENAME)
    • Choose the variable scope (pick "Current job" if this is part of a job, or "Current transformation" if it's a standalone transform)
• Next, add the Microsoft Excel Output step. In the "Filename" field, enter ${EXCEL_FILENAME}.xlsx (or .xls if you need the older format). Kettle will replace the variable with your Location value when running the transform.

Option 2: Batch Generate Multiple Excel Files (One per Location) #

If you need to create an Excel file for every unique Location in your dataset, you'll need to wrap this in a Job to loop through each Location:

1. Create a transformation that fetches all unique Location values, then use Copy Rows to Result to pass these values to the job.
2. In your Job, add a Start step, followed by a Transformation step (the one that gets unique Locations and copies to result).
3. Add a Loop step (or use a Job Executor with a loop) that iterates over each row in the result set.
4. Inside the loop, add a transformation that:
   • Uses Get rows from result to pull the current Location value
   • Uses Set Variable to set the filename variable as in Option 1
   • Runs your data processing and outputs to Excel using the variable-based filename

Bonus: JavaScript Alternative #

If you prefer, you can use a JavaScript step instead of Set Variable to build the filename directly:

// Build the full filename with .xlsx extension
var excelFilename = Location + ".xlsx";
// Set the variable for use in the Excel Output step
setVariable("EXCEL_FILENAME", excelFilename, "job");

Then reference ${EXCEL_FILENAME} in the Excel Output's filename field just like before.

Just double-check that your variable scope matches where you're using it—if the Excel step is in the same job as the Set Variable, "Current job" works. If it's in a nested transformation, you might need "Root job" to make the variable accessible.

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