this post was submitted on 22 May 2024
30 points (100.0% liked)

Selfhosted

40403 readers
849 users here now

A place to share alternatives to popular online services that can be self-hosted without giving up privacy or locking you into a service you don't control.

Rules:

  1. Be civil: we're here to support and learn from one another. Insults won't be tolerated. Flame wars are frowned upon.

  2. No spam posting.

  3. Posts have to be centered around self-hosting. There are other communities for discussing hardware or home computing. If it's not obvious why your post topic revolves around selfhosting, please include details to make it clear.

  4. Don't duplicate the full text of your blog or github here. Just post the link for folks to click.

  5. Submission headline should match the article title (don’t cherry-pick information from the title to fit your agenda).

  6. No trolling.

Resources:

Any issues on the community? Report it using the report flag.

Questions? DM the mods!

founded 2 years ago
MODERATORS
ruud
Loki
CannaVet
devve
HybridSarcasm
[email protected]
30
Generate document from templates and database (self.selfhosted)
submitted 6 months ago* (last edited 6 months ago) by Biorix to c/selfhosted
34 comments fedilink hide all child comments
 

I'm looking for a solution to generate document (ideally docx but pdf is ok) from a database

Ex: I have a project entry (with client info, dates, information about the project, etc.) and I want to generate documents from a tender templates, containing selected entries from the database.

Here is what I tried until now :

  • Custom database (tinyDB) + custom webpage form + a docx template with jinja markups served by a homemade webpage hosted on pythonanywhere (lot of work and not reliable as I'm doing everything myself)
  • Nocodb form and database (no document generation yet) (self-hosted or on cloud)
  • Airtable (closed source and on cloud) for forms, database and document generation

Airtable is what I'm currently trying because it's the only one that I found that have lot of support and adds on.

There are a few options on Airtable for document generation, lot of which cost around $30/month which is why I'm looking for a viable alternative. Ideally I would like to be able to upload my already made templates.

More point to the solution if it's supports geodata

I'm considering keeping airtable and using the api to generate document with the python program I used on the first point, but I'd like to know if there is more options.

EDIT2: to be concise: I'm looking for an alternative to Airtable + Make. Ideally, FOSS and self-hostable or on cloud

Edit: precision: I need to create a document that contains multiple items of the db. Ex: I need to create a resume with different experiences that are saved in the database

Here is an example of a template:

all 35 comments
sorted by: hot top controversial new old
[–] [email protected] 7 points 6 months ago (1 children)

Does a simple "mail merge" work?

https://books.libreoffice.org/en/WG71/WG7114-MailMerge.html

[–] Biorix 1 points 6 months ago (1 children)

Interesting! I'll take a look Although, I'm not sure if I'll be able to implement it in my company. Thanks!

[–] slazer2au 3 points 6 months ago (1 children)

This may to against self hosting but doesn't MS Word have this capability?

[–] Biorix 2 points 6 months ago

Yes, it does have the same option that libreoffice. But this solution would not be better than what I have currently

[–] stuner 7 points 6 months ago (1 children)

Is using Latex an option? I've done that and it works quite nicely. You can easily populate a template e.g. using Python.

[–] Biorix 1 points 6 months ago (2 children)

It is, but the main issue is how to select the values from the database and to render without the user needing to tweak anything

Latex, MD, Word is equivalent (although Word is preferred) since I'll just place universal markers in it

[–] stuner 1 points 6 months ago

One way to do it is have a small Python (or any other scripting language really) script that performs text replacements in the Latex source file. This is much easier in Latex because it's plain text. I don't know of a solution that doesn't involve writing your own code (apart from LO/Word serial letters).

[–] redbr64 1 points 6 months ago (1 children)

I am working on something similar and also planning on LaTex because it will be so easy to do find and replace because it's plain text (just adding placeholders like ##NAME## or whatever), but I'm only planning on outputting PDFs, which would be easy enough. I don't think there's many viable solutions to go LaTex to docx if that's a big requirement for you

[–] Biorix 1 points 6 months ago (1 children)

I'm using docxtpl which uses jinja placeholder to set values.

For your solution, you should use jinja It's used for whatever text files you want, htlm, txt, latex, MD, etc. And you can put code into the document (see my examples document in the post)

[–] redbr64 1 points 6 months ago (1 children)

Ah thanks! I am working with .NET, and I was surprised how there's little out there in terms of (open source) libraries for LaTex (I did some research since this comment). I might end up going with docx via the OpenXML API. Also, I haven't really used LaTex before (has been on on my learning to-do list), and once I started messing with some templates, I realized I need to learn a lot more first.

One thing with my documents is that find and replace alone won't work, as I need to replace some patterns. I am generating resumes, so I need to take something like a pattern for a job, and then repeat it several times

[–] Biorix 2 points 6 months ago (1 children)

Yes, that why I recommand jinja. as it can be used in OpenXML as well as latex or anything in plain text.

Let's say you want to place a table that corresponds to a certain pattern, you could add it to your file conditionally. In your Word document, you could add that :

{% for job in jobs %}
   {% if job.style == 1 %}
       {{ job.name }} |  {{ job.date}} | ...
   {% else %}
       - {{ job.name }}
       - {{ job.date}}
       - ...
    {% endif %}
{% endfor %}

I don't know .NET but you can probably call a Jinja tools

Also for the resume, you might be insterested in Rx Resume

[–] redbr64 2 points 6 months ago* (last edited 6 months ago)

Ah thanks for letting me know about Rx Resume! Great resource, and actually solves the last mile problem (creating the document) of my little personal app. I am a bit of a jack of all trades, so I made a little database for the resume where the lowest level item (the little bullet points in the experience) can have tags attached to them. So I might describe the same job/experience in multiple ways depending on who the audience is, and then filter for the tags to only get the bullet points that are relevant for that position and generate a resume.

Now instead of going into some whole slog of coding document generation, I can just export that bit as JSON and import into Rx Resume! Thanks again!

[–] [email protected] 4 points 6 months ago

I've been using ReportLabs to make pdfs in Python. It took some getting used to but I can put together just about anything I can imagine. I still get a little lost when I need to dig into the source code and how it generates pdfs from templates though.

[–] Ekpu 3 points 6 months ago* (last edited 6 months ago) (2 children)

I am searching for a similiar Software. Ideal would be a low code database platform selfhostet with easy document generation. I am still searching but you can take a look at https://rei3.de/en/home. Low code and database works good. The document generation is a bit hacky but you can fill placeholders oft a HTML side. But they hinted that they will have to work in this because the library they used is no longer supportet. So I hope it will geht easier.

Plus side it comes with user management and some "apps" out oft the box and all oft the "apps" are interoperable. Self created apps are Versionen out oft the box and you have to sign them with a generated developer key which is also very nice.

[–] Ekpu 1 points 6 months ago

I had another idea. Maybe Dokuwiki with the struct and burocreacy Plugin would could work for you.

[–] Biorix 1 points 6 months ago (2 children)

Thanks! This look interesting. I can't find the document generation though

[–] Ekpu 2 points 6 months ago

Look in the community board. Link is in the bottom oft the page. There is a thread with an example. Ah I see. The better thread is in the german part here https://community.rei3.de/d/220-pdf-aus-formulardatensatz-erstellen

I hope with translation it is usable.

[–] Ekpu 1 points 6 months ago

This one is even better and in english: https://community.rei3.de/d/120-generate-dynamic-htmlpdf

[–] [email protected] 2 points 6 months ago

I'm very confused about what your requirements are based on reading your post and some of your responses to comments, but I'm going to suggest that you look into Quarto

[–] markstos 2 points 6 months ago

I’ve generated HTML before and then used an HTML to PDF converter as a second step. If you were already familiar with building building webpages, this might be a good option.

[–] dfense 2 points 6 months ago* (last edited 6 months ago) (1 children)

I am using appsmith for the app, with a pgsql db. Then n8n to query pgsql with its JSON functions and finally sending it to apitemplate.io to generate a PDF from the JSON and a template. The PDF then goes into nextcloud. With apitemplate you get 50 executions a month for free, which is enough for my use case. Hope this helps. (The use case is an equipment maintenance tool for the SCBAs of our fire brigade, where I need reporting for proving that maintenance was carried out)

[–] Biorix 1 points 6 months ago (1 children)

Ha that looks like something I can use!

I think that Airtable + Make is still easier to use with less code, so my colleagues might prefer it over your solution

But I definitely keep that in mind. I have another tool where that might be useful.

[–] dfense 2 points 6 months ago* (last edited 6 months ago) (1 children)

I do not know Airtable too well, but n8n might have a connector for it. Then you could use the rest. good luck!

[–] Biorix 1 points 6 months ago

Oh wow that looks great! I didn't know about n8n

[–] [email protected] 2 points 6 months ago (1 children)

Ive got some stuff that i think is similar to what you are trying where i have an excel file template and use python to read from the database and populate cells in excel and then save a pdf.

There are a couple different options for python libraries - openpyxl, xlwings, or pywin32.

It is annoying and goofy, but works. Excel can be very flexible with getting everything sized just right for what your final output/pdf should look like.

[–] Biorix 2 points 6 months ago (1 children)

Thank you !

It's pretty much the solution I already have: I generate a doc with DocxTemplate through a webpage made with Flask.

What I'm looking for is a solution with a user-friendly pipeline that can be easily used by anyone once it's set up

Form > generate new entry in a db

Entry > Select entry from tags > generate documents from template

Airtable is already proposing something similar, which is why I'm currently trying it with zapier, Documint, and other automation solutions

I'm looking for a self host solution but mostly a free and polished one

[–] florian 2 points 6 months ago* (last edited 6 months ago) (1 children)

I seems a long time ago, I build the same thing as you. For templating I used https://github.com/christopher-ramirez/secretary and as "database" I used Google sheets. Still use it at work.

It never was really user friendly so I am looking to replace it. That is also the main issue with your self build solution? Or are there other issues as well?

I didn't know about Documint. Looks interesting, will check it out, thanks 👍

[–] Biorix 2 points 6 months ago (1 children)

Nice!

Secretary looks exactly like Docxtemplate, which I used on my homemade webpage. Maybe it's a fork.

It never was really user friendly so I am looking to replace it. That is also the main issue with your self build solution? Or are there other issues as well?

Yes exactly. I need people to be able to use the tools easily and reliably and to be able to add templates themselves.

Also, my main job is not developing tools and I can only do it on my spare time, so the fastest it is to implement the better.

After a week to try, Airtable + Make seems to be the easiest. Airtable also offers to generate documents directly in it, but it lacks options.

My main issue with a lot of tool (except Docxtemplate or similar) is to renders array of data. Most solution replace a {{tag}} with an info, but what I would need is a {{array[i]}} and to be able to generate different paragraphs.

[–] florian 2 points 6 months ago (1 children)

What is "Make"? Hard to Google name.

Have you considered open sourcing you self built solution?

[–] Biorix 2 points 6 months ago

It used to be integromat. You can look it up more easily, haha

I almost succeeded with it, but to integrate multiple entries in one document, it was too complicated.

So, I'm modifying the code I originally made to make API calls to Airtable, and I assemble the doc with docxtpl

I started by creating an API on my already functioning flask app, so everything can be done through Airtable page. But I was worried about opening an API to the world, so I decided to make a local python app that I'll probably compile to .exe

And yes, I can open source, but it's really tailored to me needs

[–] [email protected] 2 points 6 months ago

As part of a much larger project that was all-in on Java to begin with, I've used Tomcat to serve pages and PDFs/office doc formats rendered based on a postgres backend and FreeMarker templates.

For these purposes, you don't want that level of complexity (or Java) but the general premise and template libs should be available in nearly any sane web programming language.

[–] [email protected] 1 points 6 months ago

I use django for this exact thing, it's my day job

[–] Biorix 1 points 6 months ago

Sorry, my comments and edits seem to fail every time

[–] [email protected] 1 points 6 months ago

You can link excel cells to fields in a word document... can probably use vba to systematically iterate through a list of data to populate your linked cells to generate your documents.