pkokki / documentcreator Goto Github PK

An opinionated solution for creating documents from JSON objects (API payloads) based on Word templates and Excel formula configuration.

Home Page: https://doc-creator.azurewebsites.net/

License: MIT License

C# 70.02% JavaScript 0.79% TypeScript 14.13% CSS 0.70% HTML 3.90% VBA 10.37% Batchfile 0.10%

expression-evaluator document-generation math-expression-evaluator excel-formula

documentcreator's Introduction

Document Creator

An opinionated solution for creating documents from JSON objects (API payloads) based on Word templates and Excel formula configuration.

Getting Started

Create Word documents and HTML pages by combining Word templates (documents with content controls) and JSON data via transformations defined as Excel formulas.

Try the quickstart to create a document in four steps.
View the API documentation

Transformation examples

Given the data

{
    "x1": 10,
    "x2": 2,
    "x3": { "firstname": "john", "surname": "smith" },
    "x4": [100, 200, 300, 400],
    "x5": {
        "y1": "A quick brown fox jumps over the lazy dog",
        "y2": "brown",
        "y3": "#red#",
        "y4": [ 1, 2, 3]
    }
}

we can evaluate the following expressions

expression	result (in el-GR culture)
`8 / 2 * (2 + 2)`	16
`x1 * x2 - x1 / x2`	15
`CONCATENATE(x3.firstname, " ", x3.surname)`	john smith
`SUM(x4) * 24%`	240,00
`x4`	['100','200','300','400']
`PROPER(REPLACE(x5.y1, SEARCH(x5.y2, x5.y1), LEN(x5.y2), x5.y3))`	A Quick #Red# Fox Jumps Over The Lazy Dog
`IF(x1 + IFNA(missing.path, x2) > 10, ">10", "<=10")`	>10
`DATE(2020, 4, 28) + x5.y4[1]`	30/4/2020
`IF(__A1 > __A2, UPPER(__A3), "?")`	JOHN SMITH

See the full list of supported Excel functions. Suggest the implementation of missing functions by opening an issue.

You can test it live here.

Installation

TBD

Built with

API

ASP.NET Core
Open XML SDK - to generate documents and extract content from Word and Excel files
Open XML PowerTools - to convert Word documents to HTML/CSS

Web client

Tests

Contributing

Contributions are welcome. Please contact the project maintainer.

Versions

For the versions available, see the tags on this repository.

Authors

Panos - Initial work - pkokki

License

This project is licensed under the MIT License - see the LICENSE.TXT file for details

Credits

Tim Hall, JSON conversion and parsing for VBA
E. W. Bachtal, Excel Formula Parsing in C#
Eric White, Open XML PowerTools

documentcreator's People

Contributors

Stargazers

Watchers

documentcreator's Issues

enhance excel mapping file

P13="INFORMATION"
P14=Agent --- emptymappings version
P15=TemplateName
P16=MappingName

Remove columns A (Id) and H (Id), hidden E and I(TargetId)
Add column "Formula" after Transformation(J) and formula "=FORMULATEXT(RC[-1])"

handle _ in templateName and mappingName

remove dependency from gdiplus.dll

for templates with images

multiple template fields when AlternateContent

dynamically select repository

normalize icons/support for svg paths

implement IRepository for azure

support output formatting in mapping worksheet

subtotal + tax = total

quantity = 1/3
price = 1,3
subtotal = quantity * price, but also need TEXT(F5, "0,00") or read formatting from excel cell or equivalent
tax = subtotal * 10%
total = subtotal + tax

support TEXT function in mappings

add MacroOptions to user-defined functions

See

Public Functon MyUserDefinedFunction(ByVal sText As String) As String 
End Function 

Public Sub DefineFunction 
   Dim sFunctionName As String 
   Dim sFunctionCategory As String 
   Dim sFunctionDescription As String 
   Dim aFunctionArguments(1 To 2) As String 

   sFunctionName = "MyUserDefinedFunction" 
   sFunctionDescription = "This is my new user defined function" 
   sFunctionCategory = 7 ' Text category  
   aFunctionArguments(1) = "String to contain the name" 
   aFunctionArguments(2) = "Long to contain the value" 

   Application.MacroOptions Macro:=sFunctionName, _ 
         Description:=sFunctionDescription, _ 
         Category:=sFunctionCategory, _ 
         ArgumentDescriptions:=aFunctionArguments 
End Sub

{
  sources: [{name:'RQ', value:{json object}}]
}

var expressions = new List<TemplateFieldExpression>
{
    new TemplateFieldExpression() { Name = "F02", Cell = "J14", Expression = "=1" },
    new TemplateFieldExpression() { Name = "F03", Cell = "J15", Expression = "=J14+J16" },
    new TemplateFieldExpression() { Name = "F04", Cell = "J16", Expression = "=2" },
};
Assert.Equal("3", expressions[1].Result.Value);

return correct content of content controls

all content, but not the placeholder text

enhance POST api/evaluations for Excel addin

permit null templateName - use field information included in request only
return cell property in result

add source from swagger

fix order of repeating section items to match order of payload items

Payload:

"InterestTable": [
  {"Period": 1,"Interest": 0.2,"Points": 500},
  {"Period": 3,"Interest": 0.25,"Points": 1000},
  {"Period": 5,"Interest": 1.25,"Points": 2000}
]

Word:

Περίοδος | Επιτόκιο | Πόντοι
1 | 0,2 | 500
5 | 1,25 | 2000
3 | 0,25 | 1000

{
  "templateName":"",
  "sources": [{ "name": "", "payload": {} }],
  "expressions": [{ "name": "", "cell": "", "expression": "", "parent": "", "isCollection": true }]
}