java - HSSFWorkbook - 使用不同值的单独列写入 Excel 数据

Question

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

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



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

我正在尝试的示例代码，

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Report");
HSSFRow header = sheet.createRow(0);

 int counter = 1;
 for (DataClass e : getdata ) {  
    if(counter != 1)
       if(getdata.get(counter-1).getDeviceId() != (getdata.get(counter).getDeviceId())
                      #here i want to create seperate column for different deviceId values


  HSSFRow row = sheet.createRow(counter++);());
  row.createCell(0).setCellValue(e.getValue());
  row.createCell(1).setCellValue(e.getDate());

   }

我想将这些数据写入Excel，如下所示，

如何做到这一点？

Answer 1

创建一个新类来保存有关数据位置的信息:

class DataPosition {
    public Integer colNum;
    public Integer rowNum;

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

当您循环数据时，您会检查 deviceId 是否已包含在 Excel 中。如果是，则使用与该 deviceId 对应的最后一列和行号。如果不包含，则使用最后一列信息添加新的设备数据并将其存储在包含该数据位置的 map 中

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 = 1;
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 = 1;
        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());
}