java - POI - Excel 不同 ID 的单独列

Question

我正在尝试从 mysql 获取数据并将其写入 Excel，条件如下，

  List<DataClass> getdata = reports.getReportData(); //This will return data from jdbcTemplate

getdata 包含如下数据，

    deviceId    value  date
    T01         59        2-sep
    T01         67        3-sep
    T01         78        4-sep
    T01         79        5-sep
    T02         68        2-sep
    T02         69        3-sep
    T02         75        4-sep
    T03         70        2-sep
    T03         78        3-sep
    T03         80        4-sep
    T03         89        5-sep

class DataPosition {
    public Integer colNum;
    public Integer rowNum;

    public DataPosition(Integer colNum,Integer rowNum) {
        this.colNum = colNum;
        this.rowNum = rowNum;
    }
}

Map<String, DataPosition> excelIds = new HashMap<>();
DataPosition position;
// Start at -2 so when it comes to the first value, you add 2 to place the column number to 0
Integer currentColNum = -2;
Integer currentRowNum;
HSSFRow row;
for (DataClass e: getdata) {  
    position = excelIds.get(e.getDeviceId());
    if (position != null) {
        // Add a new row
        position.rowNum++;
        currentRowNum = position.rowNum;
    } else {
        // Add a new column (increments by two because you need two columns for the representation)
        currentColNum += 2;
        currentRowNum = 0;
        excelIds.put(e.getDeviceId(), new DataPosition(currentColNum, currentRowNum));
    }

    row = sheet.getRow(currentRowNum);
    if (row == null) {
        row = sheet.createRow(currentRowNum);
    }

    row.createCell(currentColNum).setCellValue(e.getValue());
    row.createCell(currentColNum + 1).setCellValue(e.getDate());
}

但它的写法如下，

但我想显示如下，

如何在此处打印设备 ID 和日期作为每个设备的标题？

更新了代码，

Map<String, DataPosition> excelIds = new HashMap<>();
DataPosition position;
// Start at -2 so when it comes to the first value, you add 2 to place the column number to 0
Integer currentColNum = -2;
Integer currentRowNum;
HSSFRow row;
for (DataClass e: getdata) {  
    position = excelIds.get(e.getDeviceId());
    if (position != null) {
        // Add a new row
        position.rowNum++;
        currentRowNum = position.rowNum;
    } else {
        // Add a new column (increments by two because you need two columns for the representation)
        currentColNum += 2;
        currentRowNum = 0;
        excelIds.put(e.getDeviceId(), new DataPosition(currentColNum, currentRowNum));
newValueFlag = true;
    }
    row = sheet.getRow(currentRowNum);
    if (row == null) {
        row = sheet.createRow(currentRowNum);
    }
if (newValueFlag) {
     Cell newCell = row.createCell(currentColNum);
     newCell.setCellValue(e.getDeviceId());
     newCell.setCellStyle(style);
     Cell newCell2 = row.createCell(currentColNum + 1);
     newCell2.setCellValue("Date");
     newCell2.setCellStyle(style);
     row = sheet.createRow(currentRowNum + 1);
    row.createCell(currentColNum).setCellValue(e.getValue());
    row.createCell(currentColNum + 1).setCellValue(e.getDate);
    newValueFlag = false;
    excelIds.put(e.getDeviceId(), new 
    DataPosition(currentColNum, currentRowNum + 1));
}
else{

    row.createCell(currentColNum).setCellValue(e.getValue());
    row.createCell(currentColNum + 1).setCellValue(e.getDate());
}
}

如果我尝试进行上述更改，那么我会丢失每个 deviceId 值的第一行。

Answer 1

在创建行之前，将 currentRowNum 加 1 并在其中添加详细信息。然后通过

创建静态行

Row heading= sheet.createRow(0);

并在其中设置 deviceIds 和 Date。

您需要在顶部有一行，因此在循环之后，您将获得位置和设备 ID 的完整 map ，然后您可以在顶部第 0 个位置创建静态行，并从该 map 添加这些设备 ID使用循环。