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