JMeter导入Maven依赖Jar实现API数据写入Excel问题求助

使用JMeter从API响应中提取数据并写入Excel。

方案1：基于Apache POI的Java代码 + Maven Shade插件生成Fat Jar #

编写了如下Java代码：

package org.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestWriteInExcel {
    public TestWriteInExcel() {
    }

    public static void writeXLSXFile(int row, int col, String actualValue, String fname) throws IOException {
        Cell cell = null;

        try {
            FileInputStream file = new FileInputStream(fname);
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow sheetrow = sheet.getRow(row);
            if (sheetrow == null) {
                sheetrow = sheet.createRow(row);
            }

            cell = sheetrow.getCell(col);
            if (cell == null) {
                cell = sheetrow.createCell(col);
            }

            cell.setCellValue(actualValue);
            file.close();
            FileOutputStream outFile = new FileOutputStream(fname);
            workbook.write(outFile);
            outFile.close();
        } catch (FileNotFoundException var10) {
            FileNotFoundException e = var10;
            e.printStackTrace();
        } catch (IOException var11) {
            IOException e = var11;
            e.printStackTrace();
        }

    }
}

对应的pom.xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>testWriteinExcel</artifactId>
    <version>1.0-SNAPSHOT</version>

    <build>
        <plugins>
            <!-- Maven Shade Plugin -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>3.2.4</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <minimizeJar>true</minimizeJar>
                            <filters>
                                <filter>
                                    <artifact>*:*</artifact>
                                    <excludes>
                                        <exclude>META-INF/*.SF</exclude>
                                        <exclude>META-INF/*.DSA</exclude>
                                        <exclude>META-INF/*.RSA</exclude>
                                    </excludes>
                                </filter>
                            </filters>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>


    <dependencies>
        <dependency>
            <groupId>com.syncthemall</groupId>
            <artifactId>boilerpipe</artifactId>
            <version>1.2.1</version>
        </dependency>
        <!-- JMeter Core -->
        <dependency>
            <groupId>org.apache.jmeter</groupId>
            <artifactId>ApacheJMeter_core</artifactId>
            <version>5.5</version> <!-- Choose the JMeter version you're working with -->
        </dependency>

        <!-- JMeter HTTP Samplers -->
        <dependency>
            <groupId>org.apache.jmeter</groupId>
            <artifactId>ApacheJMeter_http</artifactId>
            <version>5.5</version>
        </dependency>

        <!-- JMeter Java Sampler -->
        <dependency>
            <groupId>org.apache.jmeter</groupId>
            <artifactId>ApacheJMeter_java</artifactId>
            <version>5.5</version>
        </dependency>

        <!-- JMeter Dependencies (Optional) -->
        <dependency>
            <groupId>org.apache.jmeter</groupId>
            <artifactId>ApacheJMeter_junit</artifactId>
            <version>5.5</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>1.7.30</version>
        </dependency>
    </dependencies>

</project>

方案2：无Maven环境下调用Fat Jar #

编写了如下Java调用代码：

package example1.test;

import org.excel.TestWriteInExcel;
import java.io.IOException;

public class Main {
    public static void main(String[] args) throws IOException {

        int row = 1;
        int col = 0;
        String actualValue =  "IFT Instant Single";
        TestWriteInExcel.writeXLSXFile(row,col,actualValue,"E:/Practice/Test_for_excel/filename.xlsx");

        col = col+1;
        String tf = "7778899I" ;
        TestWriteInExcel.writeXLSXFile(row,col,tf,"E:/Practice/Test_for_excel/filename.xlsx");

    }
}

运行错误 #

运行时先后出现以下错误：

Exception in thread "main" org.apache.xerces.parsers.ObjectFactory$ConfigurationError: Provider org.apache.xerces.parsers.XIncludeAwareParserConfiguration not found

添加Xerces Jar后又出现：

Exception in thread "main" org.apache.poi.ooxml.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Can't read content types part !

1. 能否在JMeter中直接编写代码并解决Maven依赖？
2. 如何从普通Java文件运行带Maven依赖的Jar？
3. 寻求实现「从API响应提取数据写入Excel」的替代方案。

问题1：JMeter中直接编写代码并处理依赖 #

可以直接在JMeter内完成，无需手动处理Maven依赖：

• 使用JSR223 Sampler/PostProcessor，选择Groovy语言（JMeter 5.x及以上版本默认包含Apache POI相关依赖）。
• 示例代码（假设已用JSON Extractor提取到actualValue和tf变量）：

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

def filePath = "E:/Practice/Test_for_excel/filename.xlsx"
def rowNum = 1
def workbook = null

try {
    def file = new File(filePath)
    // 不存在则创建新工作簿
    workbook = file.exists() ? new XSSFWorkbook(new FileInputStream(file)) : new XSSFWorkbook()
    def sheet = workbook.getSheetAt(0) ?: workbook.createSheet("Sheet1")
    def row = sheet.getRow(rowNum) ?: sheet.createRow(rowNum)
    
    // 写入提取的变量
    def cell1 = row.getCell(0) ?: row.createCell(0)
    cell1.setCellValue(vars.get("actualValue"))
    
    def cell2 = row.getCell(1) ?: row.createCell(1)
    cell2.setCellValue(vars.get("tf"))
    
    def out = new FileOutputStream(filePath)
    workbook.write(out)
    out.close()
} catch (Exception e) {
    log.error("写入Excel失败", e)
} finally {
    workbook?.close()
}

• 优势：无需打包Jar，直接利用JMeter内置依赖，避免版本冲突。

问题2：普通Java文件运行带Maven依赖的Jar #

有两种可靠方式：

1. 用Maven Exec插件运行：在pom.xml中添加插件配置，自动处理依赖：

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>exec-maven-plugin</artifactId>
    <version>3.1.0</version>
    <executions>
        <execution>
            <goals>
                <goal>java</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <mainClass>example1.test.Main</mainClass>
    </configuration>
</plugin>

运行命令：mvn exec:java

2. 手动指定类路径运行：将Fat Jar和所有依赖Jar放在同一目录，执行命令：

# Windows系统
java -cp "testWriteinExcel-1.0-SNAPSHOT.jar;*.jar" example1.test.Main

# Linux/macOS系统
java -cp "testWriteinExcel-1.0-SNAPSHOT.jar:*.jar" example1.test.Main

另外，你遇到的Can't read content types part错误，大概率是目标Excel文件不存在或损坏：

• 先检查filename.xlsx是否存在，不存在则需在代码中先创建新工作簿；
• 若文件存在，手动打开确认是否正常，排除损坏可能。

问题3：替代方案（无需编写Java代码） #

方案A：JMeter结果文件转Excel #

• 用JSON Extractor提取目标字段，保存为变量；
• 在jmeter.properties中配置sample_variables=actualValue,tf，将变量输出到测试结果文件；
• 测试完成后，将results.csv或jmeter.log导入Excel整理数据。

方案B：使用JMeter插件的Excel Writer #

• 安装JMeter Plugins Manager，搜索安装「Excel Writer」插件；
• 添加「Excel Writer」元件，配置要写入的变量、Excel路径和行列信息，直接完成数据写入。

方案C：CSV中间格式过渡 #

• 用JSR223 Sampler将提取的变量写入CSV文件；
• 测试完成后将CSV文件导入Excel，这种方式简单稳定，避免POI相关版本问题。

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