Java 类org.apache.poi.ss.usermodel.CellType 实例源码

/**
 * 获取单元格的值
 * 
 * @param cell
 * @return
 */
public static Object getCellValue(Cell cell) {
    Object cellValue = null;
    CellType cellType = cell.getCellTypeEnum();// CellType.forInt(cell.getCellType());
    if (cellType == CellType.STRING) {
        cellValue = cell.getStringCellValue();
    } else if (cellType == CellType.NUMERIC) {
        if (DateUtil.isCellDateFormatted(cell)) {
            cellValue = cell.getDateCellValue();
        } else {
            cellValue = cell.getNumericCellValue();
        }
    } else if (cellType == CellType.BOOLEAN) {
        cellValue = cell.getBooleanCellValue();
    } else if (cellType == CellType.FORMULA) {
        cellValue = cell.getCellFormula();
    } else if (cellType == CellType.BLANK) {
        cellValue = "";
    }
    return cellValue;
}

/**
 * readCellByType is used because CELL_TYPE_FORMULA can be CELL_TYPE_NUMERIC, CELL_TYPE_NUMERIC(date) or CELL_TYPE_STRING.
 *
 * @param cellcell
 *            read (line and column)
 * @param type
 *            CELL_TYPE_FORMULA can be CELL_TYPE_NUMERIC, CELL_TYPE_NUMERIC(date) or CELL_TYPE_STRING
 * @return a string with the data (evalued if CELL_TYPE_FORMULA)
 */
private String readCellByType(Cell cell, CellType type) {
    String txt = "";
    if (cell != null) {
        switch (type) {
            case NUMERIC:
                txt = dateOrNumberProcessing(cell);
                break;
            case STRING:
                txt = String.valueOf(cell.getRichStringCellValue());
                logger.debug("CELL_TYPE_STRING: {}", txt);
                break;
            case FORMULA:
                txt = readCellByType(cell, cell.getCachedFormulaResultTypeEnum());
                logger.debug("CELL_TYPE_FORMULA: {}", txt);
                break;
            case BLANK:
                logger.debug("CELL_TYPE_BLANK (we do nothing)");
                break;
            default:
                logger.error(Messages.getMessage(EXCEL_DATA_PROVIDER_WRONG_CELL_TYPE_ERROR_MESSAGE), type);
                break;
        }
    }
    return txt;
}

private static void create(Stream<?> stream, Row row) {
    AtomicInteger counter = new AtomicInteger();
    stream.forEach(value -> {
        if (value != null) {
            if (value instanceof String) {
                row.createCell(counter.getAndIncrement(), CellType.STRING).setCellValue((String) value);
            } else if (value instanceof Number) {
                row.createCell(counter.getAndIncrement(), CellType.NUMERIC).setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Boolean) {
                row.createCell(counter.getAndIncrement(), CellType.BOOLEAN).setCellValue((Boolean) value);
            } else if (value instanceof Date) {
                row.createCell(counter.getAndIncrement(), CellType.NUMERIC).setCellValue((Date) value);
            } else if (value instanceof Calendar) {
                row.createCell(counter.getAndIncrement(), CellType.NUMERIC).setCellValue((Calendar) value);
            } else {
                row.createCell(counter.getAndIncrement(), CellType.ERROR);
            }
        } else {
            row.createCell(counter.getAndIncrement(), CellType.BLANK);
        }
    });
}

@Override
public String extract() throws ExtractorException {
    if (row == null)
        throw new BlankCellException("Empty row");
    if (row.getCell(columnId) == null)
        throw new ExtractorException("Column with index "+columnId+" does not exit");
    if (row.getCell(columnId).getCellTypeEnum() == CellType.BLANK)
        throw new BlankCellException("Empty cell value");
    try{
        switch (cellType) {
            case BOOLEAN:
                return String.valueOf(row.getCell(columnId).getBooleanCellValue());
            case NUMERIC:
                return String.valueOf(row.getCell(columnId).getNumericCellValue());
            case STRING:
                return String.valueOf(row.getCell(columnId).getStringCellValue());
            default:
                throw new ExtractorException("Unhandled cell type: "+cellType);
        }
    }catch (IllegalStateException e){
        // Most likely trying to read a non-numeric value like '--'
        // Hence we treat this as a blank cell
        throw new BlankCellException("Could not extract value", e);
    }
}

public CellSettings(
    CellType cellType,
    HSSFCellStyle cellStyle,
    Object cellValue,
    String formula,
    Hyperlink link
    ) 
{
    this.cellType = cellType;
    this.cellStyle = cellStyle;
    this.cellValue = cellValue;
    this.formula = formula;
    this.link = link;
}

public void importValues(               
    CellType cellType,
    HSSFCellStyle cellStyle,
    Object cellValue,
    String formula,
    Hyperlink link
    ) 
{
    this.cellType = cellType;
    this.cellStyle = cellStyle;
    this.cellValue = cellValue;
    this.formula = formula;
    this.link = link;
}

public static String getStringValue(Cell cell) {
    switch (cell.getCellTypeEnum()) {
    case BOOLEAN:
        return cell.getBooleanCellValue() ? "1" : "0";
    case FORMULA:
        return cell.getCellFormula();
    case NUMERIC:
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    case STRING:
        return cell.getStringCellValue();
    default:
        return "";
    }

}

/**
 * Get Cell Data
 * 
 * @param cell
 * @return cellData
 */
@SuppressWarnings("deprecation")
private String getCellData(Cell cell) {
    /* Return Cell Type */
    CellType cellType = cell.getCellTypeEnum();

    /* Get Data */
    switch (cellType) {
        case BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());

        case NUMERIC:
            return String.valueOf(cell.getNumericCellValue());

        case STRING:
            return cell.getStringCellValue();

        case BLANK:
            return new String();

        default:
            return new String();
    }
}

public ExpowCellLib(int rowIndex, int columnIndex, CellType cellType, String value) {
    // 
    this.x = columnIndex; 
    this.y = rowIndex; 
    this.value = value;
    this.type = cellType;
    if (getCellTypeAsStr().equals(BadCellType)) {
        throw new IllegalArgumentException(
                String.format("Cell type is not valid:%d", 
                        cellType));
    }

    if (value == null) {
        throw new IllegalArgumentException(
                String.format("Cell value can't be null."));
    }
}

@Test
public void testGetCellTypeAsStr() throws Exception {
    //
    ExpowCellLib cell = new ExpowCellLib(0, 0, CellType.STRING, "StringValue"); 
    assertEquals("CELL_TYPE_STRING", cell.getCellTypeAsStr());

    cell = new ExpowCellLib(0, 0, CellType.BLANK, ""); 
    assertEquals("CELL_TYPE_BLANK", cell.getCellTypeAsStr());

    cell = new ExpowCellLib(0, 0, CellType.BOOLEAN, "true"); 
    assertEquals("CELL_TYPE_BOOLEAN", cell.getCellTypeAsStr()); 

    cell = new ExpowCellLib(0, 0, CellType.ERROR, ""); 
    assertEquals("CELL_TYPE_ERROR", cell.getCellTypeAsStr());

    cell = new ExpowCellLib(0, 0, CellType.FORMULA, ""); 
    assertEquals("CELL_TYPE_FORMULA", cell.getCellTypeAsStr());

    cell = new ExpowCellLib(0, 0, CellType.NUMERIC, ""); 
    assertEquals("CELL_TYPE_NUMERIC", cell.getCellTypeAsStr());
}

/**
 * 行単位で解析し、必要なら改ページを挿入する
 */
protected void parseRow( Sheet sheet, SheetParser sheetParser, SheetData sheetData, Row row, int rowIndex) {
    int firstColNum = row.getFirstCellNum();
    int lastColNum = row.getLastCellNum() - 1;

    for ( int colIndex = firstColNum; colIndex <= lastColNum; colIndex++) {
        Cell cell = row.getCell( colIndex);
        if ( cell != null) {
            if ( cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue().contains( BreakParamParser.DEFAULT_TAG)) {
                // 改ページを挿入
                if ( isInMergedRegion( sheet, row, cell)) {
                    setRowBreakMergedRegion( sheet, row, cell);
                } else {
                    setRowBreak( sheet, row, cell);
                }
            }
        }
    }

}

/**
 * 対象シートの指定された範囲のセルのタイプint[行番号][列番号]の形式で取得する。 セルの値がnullの場合、Cell.CELL_TYPE_BLANKがセットされる。
 * 
 * @param sheet 対象となるシート
 * @param bStartRowIndex 範囲開始行番号
 * @param bEndRowIndex 範囲終了行番号
 * @param bStartColIndex 範囲開始列番号
 * @param bEndColIndex 範囲終了列番号
 * @return セルのタイプ
 */
public static CellType[][] getBlockCellType( Sheet sheet, int bStartRowIndex, int bEndRowIndex, int bStartColIndex, int bEndColIndex) {
    CellType[][] blockCellType = new CellType[bEndRowIndex - bStartRowIndex + 1][bEndColIndex - bStartColIndex + 1];
    int rowIdx = 0;
    for ( int bRowIndex = bStartRowIndex; bRowIndex <= bEndRowIndex; bRowIndex++) {
        int colIdx = 0;
        for ( int bColIndex = bStartColIndex; bColIndex <= bEndColIndex; bColIndex++) {
            Row row = sheet.getRow( bRowIndex);
            if ( row != null && row.getCell( bColIndex) != null) {
                blockCellType[rowIdx][colIdx] = row.getCell( bColIndex).getCellTypeEnum();
            } else {
                blockCellType[rowIdx][colIdx] = CellType.BLANK;
            }

            colIdx++;
        }
        rowIdx++;
    }

    return blockCellType;
}

/**
 * 行に情報(値、スタイル、タイプ)が設定されているかどうかの判定を行う
 * 
 * @param rowCellTypes 対象行のセルスタイル
 * @param rowCellValues 対象行のセルの値
 * @param rowCellStyles 対象行のセルタイプ
 * @return 行が空の場合はtrue、行に何らかの情報を持っている場合はfalse
 */
public static boolean isEmptyRow( CellType[] rowCellTypes, Object[] rowCellValues, CellStyle[] rowCellStyles) {
    // CellTypeの空判定 BLANKなら空とみなす
    for ( CellType cellType : rowCellTypes) {
        if ( cellType != CellType.BLANK) {
            return false;
        }
    }

    // 値の空判定 nullなら空とみなす
    for ( Object cellValue : rowCellValues) {
        if ( cellValue != null) {
            return false;
        }
    }

    // CellStyleの空判定 nullなら空とみなす
    for ( CellStyle cellStyle : rowCellStyles) {
        if ( cellStyle != null) {
            return false;
        }
    }
    return true;
}

/**
 * セルに情報(値、スタイル、タイプ)が設定されているかどうかの判定を行う
 * 
 * @param cellType 対象セルのスタイル
 * @param cellValue 対象セルの値
 * @param cellStyle 対象セルのタイプ
 * @return セルが空の場合はtrue、セルに何らかの情報を持っている場合はfalse
 */
public static boolean isEmptyCell( CellType cellType, Object cellValue, CellStyle cellStyle) {
    // CellTypeの空判定 BLANKなら空とみなす
    if ( cellType != CellType.BLANK) {
        return false;
    }

    // 値の空判定 nullなら空とみなす
    if ( cellValue != null) {
        return false;
    }

    // CellStyleの空判定 nullなら空とみなす
    if ( cellStyle != null) {
        return false;
    }
    return true;
}

/**
 * {@link org.bbreak.excella.reports.util.ReportsTagUtil#getBlockCellValue(org.apache.poi.ss.usermodel.Sheet, int, int, int, int)} のためのテスト・メソッド。
 */
@Test
public void testGetBlockCellValue() {

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.getSheetAt( 0);

    Object[][] cellValues = ReportsUtil.getBlockCellValue( sheet, 0, 4, 0, 3);

    for ( int r = 0; r <= 4; r++) {
        for ( int c = 0; c <= 3; c++) {
            if ( cellValues[r][c] != null) {
                try {
                    assertEquals( PoiUtil.getCellValue( sheet.getRow( r).getCell( c)), cellValues[r][c]);
                } catch ( NullPointerException e) {
                    e.printStackTrace();
                    fail();
                }
            } else {
                assertTrue( sheet.getRow( r) == null || sheet.getRow( r).getCell( c) == null || sheet.getRow( r).getCell( c).getCellTypeEnum() == CellType.BLANK);
            }

        }
    }
}

/**
 * Return the cell type.
 *
 * @return the cell type
 * Will be renamed to <code>getCellType()</code> when we make the CellType enum transition in POI 4.0. See bug 59791.
 */
@Override
public CellType getCellTypeEnum() {
  if(contentsSupplier.getContent() == null || type == null) {
    return CellType.BLANK;
  } else if("n".equals(type)) {
    return CellType.NUMERIC;
  } else if("s".equals(type) || "inlineStr".equals(type)) {
    return CellType.STRING;
  } else if("str".equals(type)) {
    return CellType.FORMULA;
  } else if("b".equals(type)) {
    return CellType.BOOLEAN;
  } else if("e".equals(type)) {
    return CellType.ERROR;
  } else {
    throw new UnsupportedOperationException("Unsupported cell type '" + type + "'");
  }
}

/**
 * Not supported
 */
@Override
public CellType getCachedFormulaResultTypeEnum() {
  if (type != null && "str".equals(type)) {
    if(contentsSupplier.getContent() == null || cachedFormulaResultType == null) {
      return CellType.BLANK;
    } else if("n".equals(cachedFormulaResultType)) {
      return CellType.NUMERIC;
    } else if("s".equals(cachedFormulaResultType) || "inlineStr".equals(cachedFormulaResultType)) {
      return CellType.STRING;
    } else if("str".equals(cachedFormulaResultType)) {
      return CellType.FORMULA;
    } else if("b".equals(cachedFormulaResultType)) {
      return CellType.BOOLEAN;
    } else if("e".equals(cachedFormulaResultType)) {
      return CellType.ERROR;
    } else {
      throw new UnsupportedOperationException("Unsupported cell type '" + cachedFormulaResultType + "'");
    }
  }
  else  {
    throw new IllegalStateException("Only formula cells have cached results");
  }
}

@Test
public void testForumulaOutsideCellIgnored() throws Exception {
  try(
      InputStream is = new FileInputStream(new File("src/test/resources/formula_outside_cell.xlsx"));
      Workbook wb = StreamingReader.builder().open(is);
  ) {
    Iterator<Row> rows = wb.getSheetAt(0).iterator();
    Cell cell = null;
    while(rows.hasNext()) {
      Iterator<Cell> cells = rows.next().iterator();
      while(cells.hasNext()) {
          cell = cells.next();
      }
    }
    assertNotNull(cell);
    assertThat(cell.getCellTypeEnum(), is(CellType.NUMERIC));
  }
}

@Override
public void writeLink(final String text, final String link)
    throws IOException {

  final SXSSFCell cell =
      (SXSSFCell) this.row.createCell(this.colCount++, CellType.FORMULA);
  // final Cell cell = this.row.createCell(this.colCount++);

  if (text != null) {

    if (link != null) {
      cell.setCellFormula("HYPERLINK(\""
          + link.replace("\"", "\"\"") + "\",\"" + text.replace("\"", "\"\"")
          + "\")");
      cell.setCellStyle(this.linkStyle);
    }

    cell.setCellValue(text);
  }
}

/**
 * Add a new row to the sheet
 */
@Override
public void add(Entity entity)
{
    if (entity == null) throw new IllegalArgumentException("Entity cannot be null");
    if (cachedAttributes == null)
        throw new MolgenisDataException("The attribute names are not defined, call writeAttributeNames first");

    int i = 0;
    Row poiRow = sheet.createRow(row++);
    for (Attribute attribute : cachedAttributes)
    {
        Cell cell = poiRow.createCell(i++, CellType.STRING);
        cell.setCellValue(toValue(entity.get(attribute.getName())));
    }

    entity.getIdValue();
}

private Function<Cell, String> cell2Str(final boolean isCSV) {
  return new Function<Cell, String>() {

    @Override
    public String apply(Cell item) {
      if (item == null) return "";

      item.setCellType(CellType.STRING);
      String val = item.toString();
      if (isCSV && val.contains(",")) {
        val = val.replaceAll("\"", "\"\"");
        return '"' + val + '"';
      }
      return val;
    }

  };
}

private boolean isRowEmpty(Row row) {
    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        if (cell != null && cell.getCellTypeEnum() != CellType.BLANK)
            return false;
    }
    return true;
}

@Override
public String asString() {
    if (excelCell == null) {
        // POI sometimes returns null when a cell is empty...
        return null;
    }
    if (excelCell.getCellTypeEnum() == CellType.NUMERIC
        || excelCell.getCellTypeEnum() == CellType.FORMULA) {
        excelCell.setCellType(CellType.STRING);
    }
    return emptyToNullTrimmed(excelCell.getRichStringCellValue().getString(), trimValue);
}

private<T> T tryGetValue(Function<Double, T> cast) {
    if (excelCell != null) {
        if (excelCell.getCellTypeEnum() == CellType.FORMULA) {
            excelCell.setCellType(CellType.NUMERIC);
        }
        if(excelCell.getCellTypeEnum() == CellType.NUMERIC) {
            return cast.apply(excelCell.getNumericCellValue());
        }
    }
    return null;
}

public static Object safeUserModeCellValue(
        org.apache.poi.ss.usermodel.Cell cell) {
    if (cell == null) {
        return null;
    }
    CellType type = cell.getCellTypeEnum();
    Object result;
    switch (type) {
    case _NONE:
        result = null;
        break;
    case NUMERIC:
        result = cell.getNumericCellValue();
        break;
    case STRING:
        result = cell.getStringCellValue();
        break;
    case FORMULA:
        throw new IllegalStateException("The formula cell is not supported");
    case BLANK:
        result = null;
        break;
    case BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
    case ERROR:
        throw new RuntimeException("Cell has an error");
    default:
        throw new IllegalStateException(
                "Cell type: " + type + " is not supported");
    }
    return result;
    // return (result == null) ? null : result.toString();
}

private static String getCellString(Cell cell) {
    if (cell.getCellTypeEnum() == CellType.FORMULA) {
        try {
            return String.valueOf(cell.getNumericCellValue());
        } catch (Exception e) {
            return cell.getRichStringCellValue().toString(); 
        }
    } else {
        return cell.toString();
    } 
}

/**
 * Final formatting of the sheet upon completion of writing the data. For
 * example, we can only size the column widths once the data is in the
 * report and the sheet knows how wide the data is.
 */
protected void finalSheetFormat() {
    final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    if (isHierarchical()) {
        /*
         * evaluateInCell() is equivalent to paste special -> value. The formula refers to cells
         * in the other sheet we are going to delete. We sum in the other sheet because if we
         * summed in the main sheet, we would double count. Subtotal with hidden rows is not yet
         * implemented in POI.
         */
        for (final Row r : sheet) {
            for (final Cell c : r) {
                if (c.getCellTypeEnum() == CellType.FORMULA) {
                    evaluator.evaluateInCell(c);
                }
            }
        }
        workbook.setActiveSheet(workbook.getSheetIndex(sheet));
        if (hierarchicalTotalsSheet != null) {
            workbook.removeSheetAt(workbook.getSheetIndex(hierarchicalTotalsSheet));
        }
    } else {
        evaluator.evaluateAll();
    }
    for (int col = 0; col < getPropIds().size(); col++) {
        sheet.autoSizeColumn(col);
    }
}

private String getCellValue(final Cell cell, int count) {
    String cellValue = null;
    CellType ct = cell.getCellTypeEnum();
    switch (ct) {
    case STRING:
        cellValue = cell.getStringCellValue();
        break;
    case NUMERIC:
        cellValue = String.valueOf(cell.getNumericCellValue());
        break;
    case BOOLEAN:
        cellValue = String.valueOf(cell.getBooleanCellValue());
        break;
    case BLANK:
        if (count > -1) {
            cellValue = "BLANK" + String.valueOf(count);
        }
        break;
    case FORMULA:
        CellType cacheCellType = cell.getCachedFormulaResultTypeEnum(); {
        switch (cacheCellType) {
        case STRING:
            cellValue = cell.getStringCellValue();
            break;
        case NUMERIC:
            cellValue = String.valueOf(cell.getNumericCellValue());
            break;
        case BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        default:
            cellValue = cell.getCellFormula();
        }
    }
        break;
    default:
        cellValue = null;
    }
    return cellValue;
}

private Map<String, String> getRow(Sheet sheet, int index, Map<Integer, String> columnNames, DataFormatter formatter) {
    Map<String, String> values = new HashMap<>();
    for (Cell cell : sheet.getRow(index)) {
        String key = columnNames.get(cell.getColumnIndex());
        if (key != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC && !DateUtil.isCellDateFormatted(cell)) values.put(key, String.valueOf(cell.getNumericCellValue()));
            else values.put(key, formatter.formatCellValue(cell));
        }
    }
    return values;
}

private static Cell mockCell(String text, long index) {
    Cell cell = mock(Cell.class);
    RichTextString value = mock(RichTextString.class);
    when(value.getString()).thenReturn(text);
    when(cell.getColumnIndex()).thenReturn((int) index);
    when(cell.getCellTypeEnum()).thenReturn(CellType.STRING);
    when(cell.getRichStringCellValue()).thenReturn(value);
    return cell;
}

private static Cell mockCell(double value, long index) {
    Cell cell = mock(Cell.class);
    when(cell.getColumnIndex()).thenReturn((int) index);
    when(cell.getCellTypeEnum()).thenReturn(CellType.NUMERIC);
    when(cell.getNumericCellValue()).thenReturn(value);
    return cell;
}

private static Cell mockCell(Date value, long index) {
    Cell cell = mock(Cell.class);
    when(cell.getColumnIndex()).thenReturn((int) index);
    when(cell.getCellTypeEnum()).thenReturn(CellType.NUMERIC);
    CellStyle style = mock(CellStyle.class);
    when(style.getDataFormat()).thenReturn((short) 0x0e);
    when(style.getDataFormatString()).thenReturn("");
    when(cell.getCellStyle()).thenReturn(style);
    when(cell.getDateCellValue()).thenReturn(value);
    return cell;
}

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER, OaImporter.OaType.lsoa.name(), OaImporter.OaType.lsoa.datasourceSpec.getDescription());
    // Loop over years
    for (int sheetId = 0; sheetId < getWorkbook().getNumberOfSheets(); sheetId++){
        Sheet sheet = getWorkbook().getSheetAt(sheetId);

        int year;
        try {
            year = Integer.parseInt(sheet.getSheetName().substring(sheet.getSheetName().length()-4, sheet.getSheetName().length()));
        }catch (NumberFormatException e){
            // Sheetname does not end in a year
            continue;
        }

        // Create extractors for each timed value
        List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();

        RowCellExtractor subjectExtractor = new RowCellExtractor(0, CellType.STRING);
        ConstantExtractor timestampExtractor = new ConstantExtractor(String.valueOf(year));

        // Get the attribute label row and create TimedValueExtractors
        Row attributeLabelRow = sheet.getRow(5);
        for (int columnId = 0; columnId < attributeLabelRow.getLastCellNum(); columnId++){
            RowCellExtractor tmpAttributeLabelExtractor = new RowCellExtractor(columnId, CellType.STRING);
            tmpAttributeLabelExtractor.setRow(attributeLabelRow);
            Attribute attribute = AttributeUtils.getByProviderAndLabel(getProvider(), tmpAttributeLabelExtractor.extract());
            if (attribute != null){
                ConstantExtractor attributeExtractor = new ConstantExtractor(attribute.getLabel());
                RowCellExtractor valueExtractor = new RowCellExtractor(columnId, CellType.NUMERIC);
                timedValueExtractors.add(new TimedValueExtractor(getProvider(), subjectType, subjectExtractor, attributeExtractor, timestampExtractor, valueExtractor));
            }
        }

        // Extract timed values
        excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
    }

    getWorkbook().close();
}

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    // Choose the apppropriate workbook sheet
    Workbook workbook = excelUtils.getWorkbook(
            downloadUtils.fetchInputStream(new URL(DATASOURCE), getProvider().getLabel(), DATASOURCE_SUFFIX));
    Sheet sheet = workbook.getSheet("Active People Survey");
    String year = "2013";

    List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();
    RowCellExtractor subjectExtractor = new RowCellExtractor(0, CellType.STRING);
    ConstantExtractor timestampExtractor = new ConstantExtractor(year);

    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER,
            OaImporter.OaType.localAuthority.name(), OaImporter.OaType.localAuthority.datasourceSpec.getDescription());
    for (AttributeLabel attributeLabel : AttributeLabel.values()){
        ConstantExtractor attributeExtractor = new ConstantExtractor(attributeLabel.name());
        RowCellExtractor valueExtractor
                = new RowCellExtractor(getAttributeColumnId(attributeLabel), CellType.NUMERIC);
        timedValueExtractors.add(new TimedValueExtractor(
                getProvider(),
                subjectType,
                subjectExtractor,
                attributeExtractor,
                timestampExtractor,
                valueExtractor));
    }
    excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
}

@Override
public List<Attribute> getTimedValueAttributes(String datasourceID) throws Exception {
    RowCellExtractor attributeNameExtractor = new RowCellExtractor(0, CellType.STRING);

    if (null == getWorkbook()) {
        setWorkbook(excelUtils.getWorkbook(
                downloadUtils.fetchInputStream(new URL(DATAFILE), getProvider().getLabel(), DATAFILE_SUFFIX)));
    }

    Map<String, Attribute> attributes = new HashMap<>();
    Sheet sheet = workbook.getSheetAt(3);
    Iterator<Row> rowIterator = sheet.iterator();
    Row header = rowIterator.next();
    while (rowIterator.hasNext()){
        Row row = rowIterator.next();

        attributeNameExtractor.setRow(row);
        String attributeLabel = attributeNameExtractor.extract();

        if (!attributes.containsKey(attributeLabel))
            attributes.put(
                    attributeLabel,
                    new Attribute(getProvider(), attributeLabel, attributeLabel)
            );
    }
    workbook.close();
    return new ArrayList<>(attributes.values());
}

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    // Choose the apppropriate workbook sheet
    Workbook workbook = excelUtils.getWorkbook(
            downloadUtils.fetchInputStream(new URL(DATASOURCE), getProvider().getLabel(), DATASOURCE_SUFFIX));
    Sheet sheet = workbook.getSheetAt(1);
    String year = "2014";

    List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();
    RowCellExtractor subjectExtractor = new RowCellExtractor(1, CellType.STRING);
    ConstantExtractor timestampExtractor = new ConstantExtractor(year);

    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER,
            OaImporter.OaType.localAuthority.name(), OaImporter.OaType.localAuthority.datasourceSpec.getDescription());
    for (AttributeLabel attributeLabel : AttributeLabel.values()){
        ConstantExtractor attributeExtractor = new ConstantExtractor(attributeLabel.name());
        RowCellExtractor valueExtractor
                = new RowCellExtractor(getAttributeColumnId(attributeLabel), CellType.NUMERIC);
        timedValueExtractors.add(new TimedValueExtractor(
                getProvider(),
                subjectType,
                subjectExtractor,
                attributeExtractor,
                timestampExtractor,
                valueExtractor));
    }
    excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
}

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER,
            OaImporter.OaType.localAuthority.name(), OaImporter.OaType.localAuthority.datasourceSpec.getDescription());

    ExcelUtils excelUtils = new ExcelUtils();

    File localFile = downloadUtils.fetchFile(new URL(DATAFILE), getProvider().getLabel(), ".zip");
    ZipFile zipFile = new ZipFile(localFile);
    for (AttributePrefix attributePrefix : AttributePrefix.values()){
        ZipArchiveEntry zipEntry = zipFile.getEntry(bookNames[attributePrefix.ordinal()]);
        Workbook workbook = excelUtils.getWorkbook(zipFile.getInputStream(zipEntry));
        for (String sheetName : sheetNames) {
            RowCellExtractor subjectLabelExtractor = new RowCellExtractor(1, CellType.STRING);
            ConstantExtractor timestampExtractor = new ConstantExtractor("2016");   // FIXME: Need to generalise when we update Datasource to be time aware

            List<TimedValueExtractor> extractors = new ArrayList<>();

            for (String metricName : metricNames) {
                ConstantExtractor attributeLabelExtractor = new ConstantExtractor(getAttributeLabel(attributePrefix, sheetName, metricName));
                RowCellExtractor valueExtractor;
                switch (metricName){
                    case "Mean":
                        valueExtractor = new RowCellExtractor(5, CellType.NUMERIC);
                        break;
                    case "Median":
                        valueExtractor = new RowCellExtractor(3, CellType.NUMERIC);
                        break;
                    default:
                        throw new Error("Unknown metric name: " + metricName);
                }
                extractors.add(new TimedValueExtractor(getProvider(), subjectType, subjectLabelExtractor, attributeLabelExtractor, timestampExtractor, valueExtractor));
            }

            Sheet sheet = workbook.getSheet(sheetName);
            excelUtils.extractAndSaveTimedValues(sheet,this,extractors);
        }
    }
}

@Test
public void extract() throws Exception {
    RowCellExtractor extractor = new RowCellExtractor(1, CellType.NUMERIC);

    extractor.setRow(workbook.getSheet("sheet").getRow(0));
    assertEquals("5.0", extractor.extract());

    extractor.setRow(workbook.getSheet("sheet").getRow(1));
    assertEquals("6.0", extractor.extract());
}

@Test
public void extractBlankValue() throws Exception {
    RowCellExtractor extractor = new RowCellExtractor(2, CellType.BOOLEAN);

    extractor.setRow(workbook.getSheet("sheet").getRow(0));
    assertEquals("true", extractor.extract());

    extractor.setRow(workbook.getSheet("sheet").getRow(1));
    thrown.expect(BlankCellException.class);
    thrown.expectMessage("Empty cell value");
    extractor.extract();
}

@Test
public void extractSillyValue() throws Exception {

    RowCellExtractor extractor = new RowCellExtractor(3, CellType.NUMERIC);

    extractor.setRow(workbook.getSheet("sheet").getRow(0));
    thrown.expect(BlankCellException.class);
    thrown.expectMessage(new StringStartsWith("Could not extract value"));
    extractor.extract();

    extractor.setRow(workbook.getSheet("sheet").getRow(1));
    assertEquals("7.0", extractor.extract());

}