java - 使用 Java Apache POI 在 Excel 数据透视表中对包含日期和时间的列(按日期、月份和年份)进行分组

Question

我目前正在开发一个 Java 应用程序，使用 Java Apache POI 创建数据透视表。我有一列包含日期时间值，例如“6/21/2019 15:17:17”:

+---------------------------------+
| Date                | Values    |
+---------------------|-----------+
| 6/21/2019 15:17:17  | 4.31      |
| 6/21/2019 15:17:42  | 3.00      |
| 6/21/2019 15:17:42  | 1.45      |
| 6/21/2019 16:51:28  | 3.00      |
| 6/24/2019 20:08:33  | 3.00      |
| 6/24/2019 20:08:33  | 4.31      |
| 6/24/2019 20:08:33  | 10.15     |
| 6/25/2019 17:57:05  | 21.55     |
| 6/25/2019 18:12:35  | 4.35      |
+---------------------------------+

我需要使用 Java Apache POI 将数据透视表中的这一列分组为天(月、年)。

+---------------------------------+
| Result         | Sum of Values  |
+----------------|----------------+
| 21-Jun         | 11.46          |
| 24-Jun         | 17.46          |
| 25-Jun         | 25.9           |
+----------------|----------------+
| Grand Total    | 55.12          |
+---------------------------------+

关于如何做到这一点有什么想法吗？

附注一个similar question已经存在，但仍未得到答复

Answer 1

这并不像你想象的那么简单。到目前为止，apache poi 的数据透视表创建还非常初级。只要只需要默认的数据透视表就可以了。 Excel 将在打开文件时计算所有必需的内容。但如果需要自定义数据透视表，则需要更多的努力。 apache poi 并没有直接为此提供类和方法。使用底层 ooxml-schemas 类是必要的。

根据您的要求，我们需要设置适当的数据透视缓存，因为字段组设置在那里设置。 Apache poi 仅设置基本的数据透视缓存，希望 Excel 在打开文件时能够纠正此问题。

但是要设置正确的数据透视缓存，我们首先需要确定用作行标签的数据列中的唯一数据。这是因为只有唯一的数据才会存储在数据透视缓存中。

如果完成了，我们可以构建数据透视缓存，然后在那里设置字段组设置。

完整示例:

import java.io.FileOutputStream;

import org.apache.poi.ss.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.*;

import java.util.TimeZone;
import java.util.GregorianCalendar;

class CreatePivotTableDateValue {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   DataFormat format = workbook.createDataFormat();
   CellStyle dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(format.getFormat("M\\/d\\/yyyy hh:mm:ss"));

   Sheet sheet = workbook.createSheet();

   String[] headers = new String[]{"Date", "Value"};
   Row row = sheet.createRow(0);
   Cell cell;
   for (int c = 0; c < headers.length; c++) {
    cell = row.createCell(c); cell.setCellValue(headers[c]);
   }

   Object[][] data = new Object[][]{
    new Object[]{new GregorianCalendar(2019, 5, 21, 15, 17, 17), 4.31},
    new Object[]{new GregorianCalendar(2019, 5, 21, 15, 17, 42), 3.00},
    new Object[]{new GregorianCalendar(2019, 5, 21, 15, 17, 42), 1.45},
    new Object[]{new GregorianCalendar(2019, 5, 21, 16, 51, 28 ), 3.00},
    new Object[]{new GregorianCalendar(2019, 5, 24, 20, 8, 33), 3.00},
    new Object[]{new GregorianCalendar(2019, 5, 24, 20, 8, 33), 4.31},
    new Object[]{new GregorianCalendar(2019, 5, 24, 20, 8, 33), 10.15},
    new Object[]{new GregorianCalendar(2019, 5, 25, 17, 57, 05), 21.55},
    new Object[]{new GregorianCalendar(2019, 5, 25, 18, 12, 35), 4.35 }
   };

   for (int r = 0; r < data.length; r++) {
    row = sheet.createRow(r+1);
    Object[] rowData = data[r];
    for (int c = 0; c < rowData.length; c++) {
     cell = row.createCell(c);
     if (rowData[c] instanceof GregorianCalendar) {
      cell.setCellValue((GregorianCalendar)rowData[c]);
      cell.setCellStyle(dateStyle);
     } else if (rowData[c] instanceof Double) {
      cell.setCellValue((Double)rowData[c]);
     }
    }
   }

   sheet.setColumnWidth(0, 19 * 256);

   // create default pivot table
   AreaReference areaReference = new AreaReference("A1:B10",  SpreadsheetVersion.EXCEL2007);
   XSSFPivotTable pivotTable = ((XSSFSheet)sheet).createPivotTable(areaReference, new CellReference("D4"));
   pivotTable.addRowLabel(0);
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);

   // here ends direct apache poi support

   // customize pivot table
   // determine unique data in data column 0, the row label
   java.util.TreeSet<GregorianCalendar> uniqueItems = new java.util.TreeSet<GregorianCalendar>();
   for (int r = 0; r < data.length; r++) {
    GregorianCalendar calendar = (GregorianCalendar)data[r][0];
    calendar.setTimeZone(TimeZone.getTimeZone("GMT"));
    uniqueItems.add(calendar);
   }

   // we need min date and max date for grouping
   GregorianCalendar minDate = uniqueItems.first();
   GregorianCalendar maxDate = uniqueItems.last();

   // set proper pivot cache
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getSharedItems().setCount(uniqueItems.size());
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getSharedItems().setMinDate(minDate);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getSharedItems().setMaxDate(maxDate);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getSharedItems().setContainsDate(true);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getSharedItems().setContainsString(false);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getSharedItems().setContainsNonDate(false);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getSharedItems().setContainsSemiMixedTypes(false);
   for (GregorianCalendar item : uniqueItems) {
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
     .getSharedItems().addNewD().setV(item);
   }

   // set field group settings
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .addNewFieldGroup().setBase(0);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getFieldGroup().addNewRangePr().setGroupBy(org.openxmlformats.schemas.spreadsheetml.x2006.main.STGroupBy.DAYS);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getFieldGroup().getRangePr().setStartDate(minDate);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getFieldGroup().getRangePr().setEndDate(maxDate);

   // at least one grup item in group settings
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getFieldGroup().addNewGroupItems().setCount(1);
   pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)
    .getFieldGroup().getGroupItems().addNewS().setV("0");

   workbook.write(fileout);

  }

 }
}