public IActionResult GetExcel([FromBody] ExcelDomain dados)
{
var nomearquivo = $"RespostasExcel-{DateTime.Now.ToString().Replace(':', '-').Replace('/', '-').Replace(' ', '-')}.csv";
var caminhoarquivo = $"{Directory.GetCurrentDirectory()}\\{nomearquivo}";
StreamWriter excel = new StreamWriter(caminhoarquivo, false, Encoding.UTF8);
try
{
string link = "ftp://ftp.site4now.net/" + nomearquivo;
var include = _repo1.Listar(new string[] { "Pergunta", "Usuario", "Questionario" });
var filtro = include.Where(a => a.QuestionarioId == dados.questionarioId && a.DataCriacao >= dados.data);
var respostas = filtro.Select(a => new
{
a.Texto,
a.Pergunta.Enunciado,
a.Pergunta.id,
a.Usuario.Nome,
a.UsuarioId,
a.QuestionarioId
});
if (respostas == null)
{
return(NotFound("Id ou data incorreta"));
}
excel.WriteLine("Id da pergunta;Enunciado da pergunta;Id do questionário;Nome do usuário;Id do usuário;Texto de resposta");
foreach (var x in respostas)
{
excel.WriteLine(x.id + ";" + x.Enunciado + ";" + x.QuestionarioId + ";" + x.Nome + ";" + x.UsuarioId + ";" + x.Texto);
}
excel.Close();
return(Ok("Excel criado com sucesso, segue link:" + link));
}
catch (System.Exception ex)
{
return(BadRequest(ex.Message));
}
}
private List <WordDomain> ConvertExcelDomainToWordDomain(ExcelDomain excelDomain)
{
var result = new List <WordDomain>();
excelDomain.BodyDomain.DataList.ForEach(p =>
{
var wordDomain = new WordDomain
{
Id = Guid.NewGuid(),
CreateTime = DateTime.Now.Ticks
};
p.ColumnList.ForEach(pro =>
{
switch (pro.ColumnNumber)
{
case 1:
wordDomain.Word = pro.Value;
break;
case 2:
wordDomain.Trascation = pro.Value;
break;
case 3:
wordDomain.Phonetic = pro.Value;
break;
case 4:
wordDomain.Voice = pro.Value;
break;
default:
break;
}
});
result.Add(wordDomain);
});
return(result);
}
private void AddErrorToExcelDomain(List <WordDomain> wordList, ref ExcelDomain excelDomain)
{
var errorRows = new List <RowDomain>();
//excelDomain.BodyDomain.DataList.ForEach(p=> {
// if (p.ErrorList.Any())
// {
// p.ErrorList.ForEach(pro =>
// {
// });
// }
//});
excelDomain.BodyDomain.DataList.ForEach(p =>
{
if (wordList.Select(obj => obj.Word).ToList().Any(item => item == p.ColumnList.FirstOrDefault().Value))
{
errorRows.Add(p);
}
});
excelDomain.BodyDomain.DataList = errorRows;
}
public static ExcelDomain CreateTemplateForExport(string localPath, string fileName)
{
var style1 = ExcelUtil.GetStyleTitle();
var style2 = ExcelUtil.GetStyleColumnName();
var style3 = ExcelUtil.GetStyleBody();
var domain = new ExcelDomain
{
LocalPath = localPath,
FileName = fileName,
Sheet = new SheetDomain
{
Name = fileName,
},
TitleList = new List <TitleDomain>
{
new TitleDomain
{
Title = fileName,
IsMerge = true,
StartPoint = 1,
EndPoint = 4,
RowNumber = 1,
StyleDomain = style1
},
new TitleDomain
{
Title = "Word",
RowNumber = 2,
IsMerge = false,
StartPoint = 1,
EndPoint = 1,
StyleDomain = style2
},
new TitleDomain
{
Title = "Translation",
RowNumber = 2,
IsMerge = false,
StartPoint = 2,
EndPoint = 2,
StyleDomain = style2
},
new TitleDomain
{
Title = "Phonetic ",
RowNumber = 2,
IsMerge = false,
StartPoint = 3,
EndPoint = 3,
StyleDomain = style2
},
new TitleDomain
{
Title = "Voice ",
RowNumber = 2,
IsMerge = false,
StartPoint = 4,
EndPoint = 4,
StyleDomain = style2
}
},
BodyDomain = new BodyDomain
{
StyleDomain = style3,
DataList = new List <RowDomain>
{
new RowDomain
{
RowNumber = 3,
ColumnList = new List <ColumnDomain>
{
new ColumnDomain
{
ColumnNumber = 1,
Value = "你好"
},
new ColumnDomain
{
ColumnNumber = 2,
Value = "hello"
},
new ColumnDomain
{
ColumnNumber = 3,
Value = "hel·lo"
},
new ColumnDomain
{
ColumnNumber = 4,
Value = string.Empty
}
}
}
}
}
};
return(domain);
}