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