需求：开发Polarion导出指定Work Item的Java Servlet及重复校验功能

一、核心问题梳理 #

现有代码仅尝试获取单个Work Item，未实现批量筛选符合时间/类型条件的Work Item逻辑，也缺失Excel导出的核心代码；同时未涉及导入前的重复记录校验功能。

二、分步实现方案 #

1. 批量查询符合条件的Work Item #

修正查询逻辑，使用Polarion Query语言批量筛选指定项目、类型、时间范围内的Work Item：

import java.util.Calendar;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServlet;
import org.apache.commons.collections4.CollectionUtils;
import com.polarion.alm.projects.IProjectService;
import com.polarion.alm.tracker.ITrackerService;
import com.polarion.alm.tracker.model.IWorkItem;
import com.polarion.platform.core.PlatformContext;

public class WorkItemExcelServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
    private ITrackerService trackerService;
    private IProjectService projectService;

    @Override
    public void init() {
        trackerService = PlatformContext.getPlatform().lookupService(ITrackerService.class);
        projectService = PlatformContext.getPlatform().lookupService(IProjectService.class);
    }

    // 批量查询符合条件的Work Item
    private List<IWorkItem> queryQualifiedWorkItems(String projectId, String workItemType) {
        // 计算时间范围：今日前2天
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DAY_OF_MONTH, -2);
        Date endDate = cal.getTime();
        // 起始日期2022-03-01
        Calendar startCal = Calendar.getInstance();
        startCal.set(2022, Calendar.MARCH, 1);
        Date startDate = startCal.getTime();

        // 构建Polarion查询语句
        String query = String.format(
            "project.id:\"%s\" AND type:\"%s\" AND created:[%s TO %s]",
            projectId,
            workItemType,
            startDate.getTime(),
            endDate.getTime()
        );

        // 执行查询
        return trackerService.queryWorkItems(query, null);
    }
}

2. 将Work Item导出到Excel #

使用Apache POI库实现Excel写入（需在Polarion部署环境中引入POI依赖）：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;

// 导出Work Item到Excel
private void exportToExcel(List<IWorkItem> workItems, HttpServletResponse response) throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Work Items");

    // 创建表头
    Row headerRow = sheet.createRow(0);
    String[] headers = {"ID", "标题", "类型", "创建时间", "状态"};
    for (int i = 0; i < headers.length; i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(headers[i]);
        // 设置表头加粗样式
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        cell.setCellStyle(style);
    }

    // 填充数据行
    int rowNum = 1;
    for (IWorkItem item : workItems) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(item.getId());
        row.createCell(1).setCellValue(item.getTitle());
        row.createCell(2).setCellValue(item.getType().getId());
        row.createCell(3).setCellValue(item.getCreated().toString());
        row.createCell(4).setCellValue(item.getStatus().getId());
    }

    // 自动调整列宽
    for (int i = 0; i < headers.length; i++) {
        sheet.autoSizeColumn(i);
    }

    // 设置响应头触发下载
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=\"work_items_export.xlsx\"");

    // 写入输出流
    try (OutputStream out = response.getOutputStream()) {
        workbook.write(out);
    }
}

3. 导入前重复记录校验 #

通过Work Item的业务唯一标识（示例用标题+类型组合，可替换为实际唯一字段）对比现有数据与Excel条目：

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

// 校验Excel中的重复记录（与现有Work Item对比）
private Set<String> detectDuplicates(Sheet excelSheet, String projectId) {
    Set<String> existingUniqueKeys = new HashSet<>();
    Set<String> duplicateKeys = new HashSet<>();

    // 先获取项目中现有Work Item的唯一标识
    List<IWorkItem> existingItems = trackerService.queryWorkItems("project.id:\"" + projectId + "\"", null);
    for (IWorkItem item : existingItems) {
        String key = item.getTitle() + "_" + item.getType().getId();
        existingUniqueKeys.add(key);
    }

    // 遍历Excel行检查重复
    for (int i = 1; i <= excelSheet.getLastRowNum(); i++) {
        Row row = excelSheet.getRow(i);
        if (row == null) continue;

        String title = row.getCell(1).getStringCellValue();
        String type = row.getCell(2).getStringCellValue();
        String key = title + "_" + type;

        if (existingUniqueKeys.contains(key)) {
            duplicateKeys.add(key);
        } else {
            existingUniqueKeys.add(key);
        }
    }

    return duplicateKeys;
}

三、Servlet请求处理整合 #

在doGet方法中整合逻辑处理导出请求：

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String projectId = request.getParameter("projectId");
    String workItemType = request.getParameter("type");

    if (projectService.searchProjects(projectId).isEmpty()) {
        response.getWriter().write("指定项目不存在");
        return;
    }

    List<IWorkItem> workItems = queryQualifiedWorkItems(projectId, workItemType);
    if (workItems.isEmpty()) {
        response.getWriter().write("无符合条件的Work Item");
        return;
    }

    // 执行导出
    exportToExcel(workItems, response);
}

四、注意事项 #

• 需在Polarion部署环境中引入Apache POI依赖（poi.jar、poi-ooxml.jar等）；
• 时间范围计算使用Date.getTime()获取毫秒值，匹配Polarion查询语法；
• 重复校验的唯一标识需根据业务场景调整，比如使用Work Item的外部ID或自定义唯一字段；
• 操作Polarion数据需确保当前用户拥有对应项目的访问权限。

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