public void CanCreateMappingForTemplate()
{
var emptyMapping = new MemoryStream(Resources.create_mapping_for_template_xlsm);
var templateBytes = new MemoryStream(Resources.create_mapping_for_template_docx);
var info = new FillMappingInfo()
{
TemplateName = "T01",
MappingName = "M01",
TestUrl = "http://localhost/api",
Payload = new FillMappingPayload()
{
Sources = new List <EvaluationSource>()
{
new EvaluationSource()
{
Name = "S1", Payload = JObject.Parse("{x: 5, y: 6}")
},
new EvaluationSource()
{
Name = "S2", Payload = JObject.Parse("{z: 10}")
}
}
}
};
var bytes = processor.CreateMappingForTemplate(templateBytes, emptyMapping, info);
Assert.NotEqual(0, bytes.Length);
using FileStream output = File.Open("./mappings.xlsm", FileMode.Create);
bytes.CopyTo(output);
}
public async Task <FillMappingResult> BuildMapping(FillMappingInfo info)
{
var template = repository.GetLatestTemplate(info.TemplateName);
if (template == null)
{
return(null);
}
var mapping = await GetMapping(info.TemplateName, null, info.MappingName);
var mappingBytes = mapping != null ? mapping.Buffer : new MemoryStream(Resources.empty_mappings_prod_xlsm);
var bytes = CreateMappingForTemplate(template.Buffer, mappingBytes, info);
return(new FillMappingResult()
{
FileName = $"{info.TemplateName}_{info.MappingName}.xlsm",
Buffer = bytes
});
}
public async Task <IActionResult> GetTemplateMappingExcelWithSources(
[FromRoute] string templateName,
[FromRoute] string mappingName,
[FromBody] FillMappingPayload payload)
{
var info = new FillMappingInfo()
{
TemplateName = templateName,
MappingName = mappingName,
TestUrl = $"{Request.Scheme}://{Request.Host}/api/evaluations",
Payload = payload
};
var mapping = await processor.BuildMapping(info);
var fileContents = mapping.Buffer.ToMemoryStream();
var contentType = "application/vnd.ms-excel.sheet.macroEnabled.12";
return(new FileContentResult(fileContents.ToArray(), contentType)
{
FileDownloadName = mapping.FileName
});
}
public Stream CreateMappingForTemplate(Stream templateBytes, Stream mappingBytes, FillMappingInfo info)
{
var templateFields = OpenXmlWordProcessing.FindTemplateFields(templateBytes);
var excelBytes = OpenXmlSpreadsheet.FillMappingsSheet(mappingBytes, templateFields, info);
return(excelBytes);
}
public static Stream FillMappingsSheet(Stream mappingBytes, IEnumerable <TemplateField> templateFields, FillMappingInfo mappingInfo)
{
using var mappingsStream = mappingBytes.ToMemoryStream();
using (SpreadsheetDocument mappingsDoc = SpreadsheetDocument.Open(mappingsStream, true))
{
FillMappingsSheet(mappingsDoc, templateFields, mappingInfo);
}
return(mappingsStream.ToMemoryStream());
}
private static void FillMappingsSheet(SpreadsheetDocument mappingsDoc, IEnumerable <TemplateField> templateFields, FillMappingInfo mappingInfo)
{
var worksheet = GetFirstWorkSheet(mappingsDoc);
var stringTablePart = GetOrCreatePart <SharedStringTablePart>(mappingsDoc);
var rowIndex = 3U;
foreach (var field in templateFields)
{
UpdateCellText(stringTablePart, worksheet, rowIndex, "A", field.Name);
UpdateCellText(stringTablePart, worksheet, rowIndex, "B", field.Parent);
if (field.IsCollection)
{
UpdateCellValue(worksheet, rowIndex, "C", "1", CellValues.Boolean);
}
else
{
UpdateCellText(stringTablePart, worksheet, rowIndex, "C", string.Empty);
}
UpdateCellText(stringTablePart, worksheet, rowIndex, "D", field.Content);
UpdateCellText(stringTablePart, worksheet, rowIndex, "F", string.Empty);
UpdateCellText(stringTablePart, worksheet, rowIndex, "G", string.Empty);
UpdateCellText(stringTablePart, worksheet, rowIndex, "H", string.Empty);
UpdateCellText(stringTablePart, worksheet, rowIndex, "I", string.Empty);
//UpdateCellFormula(worksheet, rowIndex, "I", $"IFNA(FORMULATEXT(F{rowIndex}),\"\")");
UpdateCellText(stringTablePart, worksheet, rowIndex, "J", string.Empty);
//UpdateCellFormula(worksheet, rowIndex, "K", $"IF(ISNA(FORMULATEXT(F{rowIndex})),\"\",IF(F{rowIndex}=J{rowIndex},1,IF(F{rowIndex}=IFNA(VALUE(J{rowIndex}),J{rowIndex}),1,2)))");
++rowIndex;
}
UpdateCellText(stringTablePart, worksheet, 14, "N", GetCustomDocumentProperty(mappingsDoc, "DocumentCreatorVersion") ?? "?");
UpdateCellText(stringTablePart, worksheet, 15, "N", mappingInfo.TemplateName);
UpdateCellText(stringTablePart, worksheet, 16, "N", mappingInfo.MappingName);
UpdateCellText(stringTablePart, worksheet, 17, "N", mappingInfo.TestUrl);
if (mappingInfo.Payload != null && mappingInfo.Payload.Sources != null)
{
rowIndex = 3U;
foreach (var source in mappingInfo.Payload.Sources)
{
if (source.Name != null && source.Payload != null)
{
UpdateCellText(stringTablePart, worksheet, rowIndex, "M", source.Name);
UpdateCellText(stringTablePart, worksheet, rowIndex, "N", source.Payload.ToString());
++rowIndex;
if (rowIndex > 11)
{
break;
}
}
}
}
}
