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; } } } } }