Example #1
0
  @Override
  public String importQuestionnareOds(String filename, String questionnaireName) throws Exception {
    SpreadsheetDocument document = SpreadsheetDocument.loadDocument(new File(filename));
    Table sheet = document.getSheetByIndex(0);

    List<String> questions = new ArrayList<>();

    for (int i = 1; i < sheet.getRowList().size(); i++) {
      Row row = sheet.getRowList().get(i);

      String question = getCellStringValue(row, 0);

      if (StringUtils.isBlank(question)) {
        break;
      }

      String levelString = getCellStringValue(row, 1);
      long level = Long.valueOf(StringUtils.replace(levelString, "D", ""));
      String tagsString = getCellStringValue(row, 2);
      List<String> tags = Collections.emptyList();
      if (StringUtils.isNotBlank(tagsString)) {
        tags = Lists.newArrayList(StringUtils.split(tagsString, ", "));
      }
      String tip = StringUtils.defaultIfBlank(getCellStringValue(row, 3), null);

      XContentBuilder builder =
          jsonBuilder()
              .startObject()
              .field("title", question)
              .field("level", level)
              .field("tags", tags)
              .field("tip", tip)
              .endObject();

      IndexResponse indexResponse =
          client
              .prepareIndex(domainResolver.resolveQuestionIndex(), Types.question)
              .setSource(builder)
              .execute()
              .actionGet();

      questions.add(indexResponse.getId());
    }

    XContentBuilder questionnaireBuilder =
        jsonBuilder()
            .startObject()
            .field("name", questionnaireName)
            .field("questions", questions)
            .endObject();

    IndexResponse indexResponse =
        client
            .prepareIndex(domainResolver.resolveQuestionIndex(), Types.questionnaire)
            .setSource(questionnaireBuilder)
            .execute()
            .actionGet();

    return indexResponse.getId();
  }
Example #2
0
  @Override
  public Object getValueFromVariable(String reference, Integer lineNumber, String sheetName) {
    // Get the cell value
    // Get the variable value in the document
    Object cellValue = null;
    Cell varCell = null;
    Table sheet = null;
    if (reference != null && !reference.isEmpty()) {
      switch (scheme.getImportType()) {
        case ROW:
          Integer firstRow = scheme.getFirstRow();
          if (firstRow != null && firstRow >= 0 && scheme.getSheetName() != null) {
            sheet = doc.getTableByName(sheetName);
            if (sheet != null) {
              Row row = sheet.getRowByIndex(lineNumber);
              if (row != null) {
                Integer collNumber;
                try {
                  collNumber = getColumnFromReference(reference);
                } catch (NumberFormatException e) {
                  return null;
                }
                varCell = row.getCellByIndex(collNumber);
                if (varCell != null) {
                  cellValue = getCellValue(varCell);
                }
              }
            }
          }
          break;
        case SEVERAL:
          sheet = doc.getTableByName(sheetName);
          if (sheet != null) {
            varCell = sheet.getCellByPosition(reference);
            if (varCell != null) {
              cellValue = getCellValue(varCell);
            }
          }
          break;
        case UNIQUE:
          String[] references = reference.trim().split(ImportUtils.SHEET_CELL_SEPARATOR);
          if (references.length == 2) {

            sheet = doc.getTableByName(references[0]);
            if (sheet != null) {
              varCell = sheet.getCellByPosition(references[1]);
              if (varCell != null) {
                cellValue = getCellValue(varCell);
              }
            }
          }
          break;
        default:
          break;
      }
    }
    return cellValue;
  }
  private void putBasicInfo(Table table, int rowIndex, String key, Object value, int numbOfCols) {
    row = table.getRowByIndex(rowIndex);
    row.getCellByIndex(1).setCellStyleName(null);
    row.getCellByIndex(2).setCellStyleName(null);
    cell = CalcUtils.putHeader(row, 1, key);
    cell.setHorizontalAlignment(ExportConstants.ALIGH_HOR_RIGHT);

    cell = CalcUtils.createBasicCell(table, 2, rowIndex, value);
    CalcUtils.mergeCell(table, 2, rowIndex, numbOfCols, rowIndex);
  }
  public ProfFile parseFile(File file) throws NotasParserException {

    logger.debug("Lendo arquivo " + file.getName());

    SpreadsheetDocument planilha;
    try {
      planilha = SpreadsheetDocument.loadDocument(file);
    } catch (Exception e) {
      String msg = "Não pude ler o arquivo " + file.getName();
      logger.error(msg, e);
      throw new NotasParserException(msg);
    }

    Table table = null;
    try {
      table = planilha.getTableList().get(0);
    } catch (ArrayIndexOutOfBoundsException e) {
      String msg = "Arquivo " + file.getName() + " não possui planilha 0";
      logger.error(msg, e);
      throw new NotasParserException(msg);
    }

    // extract basic information
    String prof = table.getCellByPosition(CELL_PROF).getDisplayText();
    if (prof == null || prof.isEmpty()) {
      String msg =
          "Célula B64 do arquivo "
              + file.getName()
              + " (1o bimestre) deveria conter o nome do professor";
      logger.error(msg);
      throw new NotasParserException(msg);
    }

    ProfFile profFile = new ProfFile(prof, file.getName());
    for (int bim = 1; bim <= 4; bim++) {
      ProfSheet profSheet =
          parseSheet(planilha.getTableList().get(bim - 1), prof, Periodo.valueOf(bim));
      profFile.getSheets().add(profSheet);
    }

    return profFile;
  }
Example #5
0
  @Override
  protected void getCorrespondances(List<ImportationSchemeModelDTO> schemeModelList)
      throws Throwable {
    for (ImportationSchemeModelDTO schemeModelDTO : schemeModelList) {
      // GetThe variable and the flexible element for the identification
      // key

      switch (scheme.getImportType()) {
        case ROW:
          Table sheetTable = null;
          if (scheme.getSheetName() != null && !scheme.getSheetName().isEmpty()) {
            sheetTable = doc.getTableByName(scheme.getSheetName());
          } else if (doc.getTableList().size() > 0) {
            sheetTable = doc.getTableList().get(0);
          }
          if (sheetTable != null) {
            int firstRow = 0;
            if (scheme.getFirstRow() != null) {
              firstRow = scheme.getFirstRow();
            }

            for (int i = firstRow; i < sheetTable.getRowCount(); i++) {
              getCorrespondancePerSheetOrLine(schemeModelDTO, i, scheme.getSheetName());
            }
          }
          break;
        case SEVERAL:
          for (Table sheet : doc.getTableList()) {
            getCorrespondancePerSheetOrLine(schemeModelDTO, null, sheet.getTableName());
          }
          break;
        case UNIQUE:
          getCorrespondancePerSheetOrLine(schemeModelDTO, null, null);
          break;
        default:
          break;
      }
    }
  }
  /**
   * @param table
   * @param prof
   * @param bim
   * @return a planilha parseada ou <code>null</code> caso a primeira célula de aulas dadas esteja
   *     vazia, ou algum outro problema aconteça
   * @throws NotasParserException
   */
  private ProfSheet parseSheet(Table table, String prof, Periodo bim) throws NotasParserException {

    logger.debug("Lendo período " + bim);

    ProfSheet profSheet = new ProfSheet(bim, prof);

    Coluna y = new Coluna(COL_FIRST_TARJETA);
    int i = 0;
    String posTurma = y.getValor().concat(LIN_TURMAS);
    String turma = "";
    turma = table.getCellByPosition(posTurma).getDisplayText();

    while (!turma.isEmpty() && !turma.contains("FIM")) {

      try {
        Tarjeta tarj = parseTarjeta(table, i, prof, bim);
        profSheet.getTarjetas().add(tarj);
      } catch (NotasParserException e) {
        logger.warn(
            "Tarjeta da "
                + turma
                + " "
                + bim
                + " não incluída na planilha de "
                + profSheet.getProfessor());
      }

      i++;
      y.inc(TARJETAS_DISTANCE);
      String pos = y.getValor().concat(LIN_TURMAS);
      Cell nextCell = table.getCellByPosition(pos);
      turma = nextCell.getDisplayText();
    }

    return profSheet;
  }
  public IndicatorEntryCalcTemplate(final IndicatorEntryData data, final SpreadsheetDocument exDoc)
      throws Throwable {
    this.data = data;

    Table table = null;
    String tableName = data.getLocalizedVersion("flexibleElementIndicatorsList").replace(" ", "_");
    if (exDoc == null) {
      doc = SpreadsheetDocument.newSpreadsheetDocument();
      table = doc.getSheetByIndex(0);
      table.setTableName(tableName);
    } else {
      doc = exDoc;
      table = doc.appendSheet(tableName);
    }

    coreCellStyle = CalcUtils.prepareCoreStyle(doc);

    int rowIndex = -1;
    int cellIndex = 0;

    // skip row
    ++rowIndex;

    // title
    CalcUtils.putMainTitle(
        table,
        ++rowIndex,
        data.getNumbOfCols(),
        data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase());

    // emptry row
    CalcUtils.putEmptyRow(table, ++rowIndex);

    // column headers
    row = table.getRowByIndex(++rowIndex);
    cellIndex = 0;
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name"));
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code"));
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue"));
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value"));
    row.setHeight(5, false);

    // empty row
    row = table.getRowByIndex(++rowIndex);
    row.setHeight(3.8, false);
    row.getCellByIndex(1).setCellStyleName(null);
    row.getCellByIndex(2).setCellStyleName(null);
    row.getCellByIndex(3).setCellStyleName(null);
    row.getCellByIndex(4).setCellStyleName(null);

    for (final IndicatorGroup group : data.getIndicators().getGroups()) {
      row = table.getRowByIndex(++rowIndex);
      CalcUtils.putGroupCell(table, 1, rowIndex, group.getName());
      CalcUtils.mergeCell(table, 1, rowIndex, data.getNumbOfCols(), rowIndex);
      for (final IndicatorDTO indicator : group.getIndicators()) {
        // indicator's detail sheet
        createDetailSheet(indicator);
        row = table.getRowByIndex(++rowIndex);
        // ind name
        cell = CalcUtils.createBasicCell(table, 1, rowIndex, null);
        CalcUtils.applyLink(
            cell,
            indicator.getName(),
            ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName());
        // code
        CalcUtils.createBasicCell(table, 2, rowIndex, indicator.getCode());
        // target
        putValueCell(table, rowIndex, 3, indicator.getObjective(), true);
        // current value
        putValueCell(table, rowIndex, 4, data.getFormattedValue(indicator), true);
      }
    }

    table.getColumnByIndex(0).setWidth(3.8);
    table.getColumnByIndex(1).setWidth(83);
    table.getColumnByIndex(2).setWidth(55);
    table.getColumnByIndex(3).setWidth(55);
    table.getColumnByIndex(4).setWidth(55);
  }
  private void createDetailSheet(final IndicatorDTO indicator) throws Throwable {
    final boolean isQualitative = indicator.getAggregation() == IndicatorDTO.AGGREGATE_MULTINOMIAL;
    final Table tableEx =
        doc.appendSheet(
            CalcUtils.normalizeAsLink(
                ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName()));
    int rowIndex = -1;

    List<PivotTableData.Axis> leaves =
        data.getEntryMap().get(indicator.getId()).getRootColumn().getLeaves();
    int numbOfLeaves = leaves.size();
    int numbOfCols = 4;

    // back to list link
    row = tableEx.getRowByIndex(++rowIndex);
    cell = tableEx.getCellByPosition(1, rowIndex);
    CalcUtils.applyLink(
        cell,
        data.getLocalizedVersion("goToIndicatorsList"),
        data.getLocalizedVersion("flexibleElementIndicatorsList"));
    CalcUtils.mergeCell(tableEx, 1, rowIndex, data.getNumbOfCols(), rowIndex);

    // title
    CalcUtils.putMainTitle(tableEx, ++rowIndex, numbOfCols, indicator.getName());

    // empty row
    CalcUtils.putEmptyRow(tableEx, ++rowIndex);

    // put details
    putBasicInfo(
        tableEx, ++rowIndex, data.getLocalizedVersion("code"), indicator.getCode(), numbOfCols);

    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("group"),
        data.getGroupMap().get(indicator.getGroupId()),
        numbOfCols);

    // type
    String type = null;
    ;
    if (isQualitative) {
      // qualitative
      type = data.getLocalizedVersion("qualitative");
    } else {
      // quantitative
      type = data.getLocalizedVersion("quantitative");
    }
    putBasicInfo(tableEx, ++rowIndex, data.getLocalizedVersion("type"), type, numbOfCols);

    // conditional
    if (isQualitative) {
      // qualitative

      // possible values
      row = tableEx.getRowByIndex(++rowIndex);

      // key
      cell = CalcUtils.putHeader(row, 1, data.getLocalizedVersion("possibleValues"));
      cell.setHorizontalAlignment(ExportConstants.ALIGH_HOR_RIGHT);

      // value
      final MultiItemText itemText = data.formatPossibleValues(indicator.getLabels());
      CalcUtils.createBasicCell(tableEx, 2, rowIndex, itemText.text);
      CalcUtils.mergeCell(tableEx, 2, rowIndex, numbOfCols, rowIndex);

    } else {
      // quantitative

      // aggregation method
      String aggrMethod = null;
      if (indicator.getAggregation() == IndicatorDTO.AGGREGATE_AVG)
        aggrMethod = data.getLocalizedVersion("average");
      else aggrMethod = data.getLocalizedVersion("sum");
      putBasicInfo(
          tableEx,
          ++rowIndex,
          data.getLocalizedVersion("aggregationMethod"),
          aggrMethod,
          numbOfCols);
      // units
      putBasicInfo(
          tableEx, ++rowIndex, data.getLocalizedVersion("units"), indicator.getUnits(), numbOfCols);

      // target value
      putBasicInfo(
          tableEx,
          ++rowIndex,
          data.getLocalizedVersion("targetValue"),
          indicator.getObjective(),
          numbOfCols);
    }

    // source of ver
    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("sourceOfVerification"),
        indicator.getSourceOfVerification(),
        numbOfCols);

    // comment
    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("indicatorComments"),
        indicator.getDescription(),
        numbOfCols);

    // value
    putBasicInfo(
        tableEx,
        ++rowIndex,
        data.getLocalizedVersion("value"),
        data.getFormattedValue(indicator),
        numbOfCols);
    // empty row
    CalcUtils.putEmptyRow(tableEx, ++rowIndex);

    row = tableEx.getRowByIndex(rowIndex);
    row.getCellByIndex(1).setCellStyleName(null);
    row.getCellByIndex(2).setCellStyleName(null);
    row.getCellByIndex(3).setCellStyleName(null);
    row.getCellByIndex(4).setCellStyleName(null);

    // data entry
    // header
    row = tableEx.getRowByIndex(++rowIndex);
    int cellIndex = 0;
    CalcUtils.putHeader(row, ++cellIndex, data.getLocalizedVersion("sideAndMonth"));
    Map<String, Integer> columnIndexMap = new HashMap<String, Integer>();
    for (PivotTableData.Axis axis : leaves) {
      CalcUtils.putHeader(row, ++cellIndex, axis.getLabel());
      columnIndexMap.put(axis.getLabel(), cellIndex);
    }

    // rows
    for (PivotTableData.Axis axis :
        data.getEntryMap().get(indicator.getId()).getRootRow().getChildren()) {
      row = tableEx.getRowByIndex(++rowIndex);
      CalcUtils.putHeader(row, 1, axis.getLabel());
      // populate empty cells
      for (int i = 0; i < numbOfLeaves; i++) {
        cell = CalcUtils.createBasicCell(tableEx, i + 2, rowIndex, "");
      }

      // insert values
      for (Map.Entry<PivotTableData.Axis, PivotTableData.Cell> entry : axis.getCells().entrySet()) {
        cellIndex = columnIndexMap.get(entry.getKey().getLabel());
        Object value = null;
        boolean rightAligned = false;
        if (isQualitative) {
          value = data.getLabelByIndex(indicator.getLabels(), entry.getValue().getValue());
        } else {
          value = new Long(Math.round(entry.getValue().getValue()));
          rightAligned = true;
        }
        putValueCell(tableEx, rowIndex, cellIndex, value, rightAligned);
      }
    }
    // col width
    tableEx.getColumnByIndex(0).setWidth(3.8);
    tableEx.getColumnByIndex(1).setWidth(60);
    for (int i = 2; i < 2 + numbOfLeaves; i++) {
      tableEx.getColumnByIndex(i).setWidth(30);
    }
  }
  /**
   * Extrai informação da tarjeta. Tarjeta gerada possui nome do professor identificado na própria
   * tarjeta. Mas mensagens de erro são dadas em nome do professor dono do arquivo, pois a mensagem
   * serve para ajudar o operador a identificar o local do problema.
   *
   * @param table
   * @param index índice da tarjeta na planilha, indo de 0 a N-1
   * @param professor dono da planilha, a princípio prof de todas as tarjetas
   * @param bim
   * @return
   * @throws NotasParserException
   */
  private Tarjeta parseTarjeta(Table table, int index, String profSheet, Periodo bim)
      throws NotasParserException {

    Coluna y = new Coluna(COL_FIRST_TARJETA);
    y.inc(TARJETAS_DISTANCE * index);

    String turma, aulasDadasStr, aulasPrevistasStr, materia, profTarjeta = "";
    String posTurma = y.getValor().concat(LIN_TURMAS);
    turma = table.getCellByPosition(posTurma).getDisplayText();
    String posAulasDadas = y.getValor().concat(LIN_AULAS_DADAS);
    aulasDadasStr = table.getCellByPosition(posAulasDadas).getDisplayText();
    String posAulasPrevistas = y.getValor().concat(LIN_AULAS_PREVISTAS);
    aulasPrevistasStr = table.getCellByPosition(posAulasPrevistas).getDisplayText();
    String posMateria = y.getValor().concat(LIN_MATERIAS);
    materia = table.getCellByPosition(posMateria).getDisplayText();
    Coluna yprof = new Coluna(y.getValor());
    yprof.dec();
    String posProf = yprof.getValor().concat(LIN_NOME_PROF);
    profTarjeta = table.getCellByPosition(posProf).getDisplayText();

    // se prof não foi identificado na tarjeta,
    // assumimos que o professor é o dono da planilha
    if (profTarjeta == null || profTarjeta.isEmpty()) {
      profTarjeta = profSheet;
    }

    if (turma.isEmpty()) {
      String msg =
          "Turma não especificada na tarjeta "
              + (index + 1)
              + " do "
              + bim
              + " do prof "
              + profSheet;
      logger.error(msg);
      throw new NotasParserException(msg);
    }
    if (materia.isEmpty()) {
      String msg =
          "Matéria não especificada na tarjeta "
              + (index + 1)
              + " do "
              + bim
              + " do prof "
              + profSheet;
      logger.error(msg);
      throw new NotasParserException(msg);
    }

    int aulasDadas = 0, aulasPrevistas = 0;
    try {
      aulasDadas = Integer.parseInt(aulasDadasStr);
    } catch (NumberFormatException e) {
      String message =
          "Aulas dadas não foram registradas na célula" + y.getValor() + LIN_AULAS_DADAS;
      logger.warn(message);
    }
    try {
      aulasPrevistas = Integer.parseInt(aulasPrevistasStr);
    } catch (NumberFormatException e) {
      String message =
          "Aulas previstas não foram registradas na célula "
              + y.getValor()
              + LIN_AULAS_PREVISTAS
              + " ("
              + profSheet
              + " - "
              + bim
              + ")";
      logger.warn(message);
    }

    Tarjeta tarj = new Tarjeta(turma, materia, profTarjeta, bim, aulasDadas, aulasPrevistas);

    // notas
    int row = Integer.parseInt(LIN_FIRST_NOTA);
    for (int i = 0; i < MAX_ALUNOS; i++) {

      String nota, aluno, faltas = "";
      String posNota = y.getValor().concat(Integer.toString(row));
      nota = table.getCellByPosition(posNota).getDisplayText();
      Coluna ya = new Coluna(y.getValor());
      ya.dec();
      String posAluno = ya.toString().concat(Integer.toString(row));
      aluno = table.getCellByPosition(posAluno).getDisplayText();
      Coluna yf = new Coluna(y.getValor());
      yf.inc();
      String posFaltas = yf.getValor().concat(Integer.toString(row));
      faltas = table.getCellByPosition(posFaltas).getDisplayText();

      int notaInt = 0, faltasInt = 0;
      String alteracao = null;
      if (nota != null && !nota.isEmpty()) {
        try {
          notaInt = Integer.parseInt(nota);
        } catch (NumberFormatException e) {
          if (nota != null && !nota.isEmpty()) alteracao = nota;
        }
        try {
          faltasInt = Integer.parseInt(faltas);
        } catch (NumberFormatException e) {;
        }
      }
      Conceito conc = new Conceito(new Aluno(aluno, turma), notaInt, faltasInt);
      conc.setAlteracao(alteracao);
      tarj.getNotas().add(conc);

      row++;
    }

    return tarj;
  }