public bool CaricaSurvey(string title, Stream stream)
{
//string filepath = "D:\\survey1.xlsx";
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false))
{
IEnumerable<Sheet> sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Eterovalutazione");
if (sheets.Count() == 0)
{
throw new Exception("File non valido");
}
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheets.First().Id);
Worksheet worksheet = worksheetPart.Worksheet;
WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
//Creazione di una nuova survey
Survey survey = new Survey();
uint curRow = 4;
bool eof = false;
do
{
//Prendo la domanda
string questionStr = GetCellValue(worksheet, wbPart, "B" + curRow);
if (!string.IsNullOrEmpty(questionStr))
{
//Creo la question e gli associo il testo della domanda
Question question = new Question();
question.Testo = questionStr;
//Ricerco la competenza associata
Competenza competenza = reposCompetenze.Get(c => c.Titolo.ToUpper() == questionStr.ToUpper());
if (competenza != null)
question.CompetenzaId = competenza.Id;
else
throw new Exception("Non trovata competenza " + questionStr);
//Aggiungo quella nulla
question.Children.Add(new SubQuestion
{
Testo = "La padronanza della competenza non è valutabile",
LivelloConoscenzaId = 1
});
AddSubQuestion(worksheet, wbPart, "E" + curRow, 2, question);
AddSubQuestion(worksheet, wbPart, "G" + curRow, 3, question);
AddSubQuestion(worksheet, wbPart, "I" + curRow, 4, question);
AddSubQuestion(worksheet, wbPart, "K" + curRow, 5, question);
//Leggo il valore atteso
string s_valAtteso = GetCellValue(worksheet, wbPart, "M" + curRow);
//Livello nullo ha id 1
question.ValoreAttesoId = Convert.ToInt32(s_valAtteso) + 1;
survey.Questions.Add(question);
curRow++;
}
else
eof = true;
}
while (!eof);
survey.Name = title;
survey.Active = true;
reposSurvey.Add(survey);
unitOfWork.Commit();
}
return true;
}
private void AddSubQuestion(Worksheet worksheet, WorkbookPart wbPart, string cell, int livelloConoscenzaId, Question question)
{
string a1 = GetCellValue(worksheet, wbPart, cell);
question.Children.Add(new SubQuestion
{
Testo = a1,
LivelloConoscenzaId = livelloConoscenzaId
});
}