Best method of creating a wall calendar with day information from a spreadsheet?

[Muotki]

New to the forum, I tried to search for my question but couldn't find a definite solution, albeit I might have used the wrong search criteria.

I want to desing a wall calendar, in the classical format with one page per month and the days in the classical table layout. The thing is I want to add some additional information found in a spreadsheet. I am looking for a way of automate this in one way or the other since it is about ten "items" for every day and I want to make this calendar for a couple of years. I have full control of the format of the data source, i.e. the spread sheet, and can convert it to any necessary data format (XLSX, CSV, JSON, XML, HTML, ...). My question is how to best do this in Affinity Publisher?

I presume I will make one or several Master pages, and I could create 36+ pages, but I would like to automate the year, month, date, and additional information for each page. If the first of the month is say Friday, then the first could of cells in the table will be empty so the automation has to be a bit intelligent. I've included a simple mock-up of what I want below. Ideally the automation inserts the year, month, and populates the table fully.

I found one forum topic that was of interest, Automation on Affinity Publisher but I doubt it will help me. On the other hand, Populate pages in an instant with Affinity Publisher’s powerful Data Merge Manager gave me quite some hope I will be able to solve this. I need help sorting out how handle that "the first of the month" isn't cell "A2".

Any suggestions? I plan to do this once, so the solution can require quite some manual work but I would prefer to have a solution that can be run at need arise. In this example I use text for the zodiac signs, but if I can get images I would of course be much more obliged.

An idea that strikes me right now; is it a better solution to generate the table in LaTeX or HTML, generate a PDF that is imported one page per Publisher page and then adding images around the calendar by hand/Master pages? How would the workflow from PDF to Affinity Publisher look?

[Old Bruce]

29 minutes ago, Muotki said:

so the automation has to be a bit intelligent.

Automation is dumb. It will never be smart outside of a marketing department's brochure.

You have to make a spreadsheet with blank cells for the Sunday thru Thursday in your example. And you need empty cells following the 31st, Monday thru Saturday.

Another thing you have to take into consideration is that there will be 4, 5 or 6 rows in the "classic format". So your spreadsheet needs 42 cells for each month.

<pedant on> For your example year of 1147 you'll need to use the Julian Calendar's earlier dates.<pedant off>

[Muotki]

I expected as much. 😒 Since the data is fully generated via Python (which also handles the Julian Calendar properly, or sufficiently proper for my needs) my strategy is now to generate the calendars in some format Published can import. Any suggestions for a bulk import to get one "table" per page?

[Old Bruce]

If you are using Python to generate the data then you could use the CSV module to generate the "tables". Or just import them into a Spreadsheet, here I have used Apple's Numbers.

Note that the column named 1 is for a Sunday, 2 is for a Monday etc.

Note that the Table in Publisher is made up of 6 Text Frames with 7 columns, each field is followed by a new paragraph. The Paragraph Style for the Dates has the Flow option set to "start in new column".

Numbers:

Publisher:

 

For adding more information to each Date (like your August 4th) then you'll need to double the number of columns*, have 1 and 1b, 2 and 2b, 3 and 3b, etc. The second columns need have nothing in them but they will need a different Paragraph Style.

===========================

There is another way of doing this using the Data Merge tool. It will require a spreadsheet that is vertically oriented and as a spreadsheet is much much harder to parse/visualize/read/make-sense-of.

[v_kyr]

See also related:

• SVG calendar generator
• APub Calendar Template 2020 (apply the data from the previews link)
• Gnu gcal  (one of the most powerful cal output tools, much better than standard Unix/DOS cal tools)
• Gnu gcal manual
• Python csvkit (csvkit is a suite of command-line tools for converting to and working with CSV, the king of tabular file formats)
• ... etc.

[Muotki]

1 hour ago, Old Bruce said:

Or just import them into a Spreadsheet, here I have used Apple's Numbers

I'm sorry, but I did not quite understand how you got from the spreadsheet in Numbers to Publisher? The spreadsheet is imported into a table using "File > Place..."?

I will experiment a bit with this I think, but at the same time I am thinking that maybe better to generate a PDF in LaTeX and then place it. I need to experiment with that as well. The main reason I do not use LaTeX from the beginning is that I want to change the font, and fonts has always been a pain in LaTeX, even with XeTeX. I will also add a few decorative images (i.e. jousting rabbits and snails) and I think Publisher is a way better tool for this than LaTeX.

[Old Bruce]

16 minutes ago, Muotki said:

I'm sorry, but I did not quite understand how you got from the spreadsheet in Numbers to Publisher?

In Numbers I use the Export to Comma Separated Values (CSV) files. The Data Merge in Publisher will read CSV files and XLSX files and TSV files. 

[Muotki]

I've done some experiments and the best solution so far is to create a HTML page with one table per month and layout it using a 9×9 grid of cells per day. Then I open the HTML page in Firefox (haven't compared with Safari yet) and saves it to a PDF in landscape mode, and I then open this PDF in Publisher, i.e. not place. I've done some mockups and it looks fine, I will now write the necessary Python script and use my real data.

Just thought I should add this conclusion in case you are interested...

[Alfred]

On 1/16/2022 at 6:32 PM, Old Bruce said:

<pedant on> For your example year of 1147 you'll need to use the Julian Calendar's earlier dates.<pedant off>

More pedantry: the heading should say “Anno Domini”, not “Anno Domine”.

[Old Bruce]

39 minutes ago, Alfred said:

More pedantry: the heading should say “Anno Domini”, not “Anno Domine”.

I am tempted to see you and raise the issue of good old Roman Numerals versus these modern Hindu-Arabic glyphs.

[Muotki]

Thought I should report on my progress: It seems like the best option is to generate a HTML file and have Firefox export it to a PDF, and open the PDF in Publisher as I've already reported. I prefer Firefox over Safari here as I can make some adjustments before saving. This is a screenshot from Publisher of part of the calendar:

The basis that each day consists of two columns and in total four rows (the last being blank). There is still some tweaking for the layout of each day, but I think I basically have solved my problem. ☺️