Apub v2 blows out excel data

[kat]

Why is Apub v2 placed xlsx excel as a table's data wrong? I've attached the Apub, excel table and excel pdf.

Table issue.zip

[lacerto]

Yes, this seems to happen both on Windows and macOS, so even if the system regional settings match the formatting of the placed (interpreted) Excel document, e.g. the thousands and decimal separators are incorrectly read.

On macOS, one can get correct non-rasterized formatting when copy pasting from Excel (apparently the data will be in PDF format). but editing, if necessary, is pretty cumbersome. On Windows, formatted Excel data will be rasterized, and on both platforms, Excel data can also be pasted as tab separated data and then manually formatted, which is of course tedious, but still one way to get this kind of data formatted according to wishes.

Here's how the attached .xlsx file (with US formatting) will appear on macOS Sonoma 14.2.1 when placed (above) and pasted from Excel (below) in a system that has US regional settings as system settings (and also as language settings in Affinity apps, should that matter at all).

 

[bbrother]

@kat

XLS and XLSX are two different file formats of Microsoft Excel Spreadsheet.
The difference is that XLS (2003 Excel) uses a binary format, and XLSX (2007 Excel) uses the newer XML format as a method to save data.
According to "supported file formats for placement" list in the Placing content chapter (official V2 documentation) APub only supports XLSX files.

Placing content (Official V2 APub documentation)

Verifying the above, there is one conclusion. XLS files are not supported and can't be placed in APub.

4 hours ago, kat said:

Why is Apub v2 placed xlsx excel as a table's data wrong?

If you are concerned about incorrectly interpreted cell formatting and blank lines, which is not the case in the PDF version generated in Excel, then:

• When it comes to misinterpreting the formatting, this has always been a problem and until it is improved there is not much the user can really do.
  I get around this problem by copying the values directly from spreadsheet to a previously prepared table in APub (appropriate number of columns, rows, style).
  It requires more work but the formating is exactly how iit should be.
  If I really have to use an XLSX file, I try to make sure it does not have non-standard formatting in cells, only values and text.
  I work out the formatting and details in APub itself.
   
• For blank rows, APub did the job correctly. There are also blank lines in the XLSX file, so they have been mapped to match the original.
  There are no blank lines in the PDF because perhaps Excel exports to PDF by default by removing blank lines or offers such an option when exporting.
  I'm not sure because I gave up Office a long time ago and now im using OpenOffice.

example video — copying from spreadsheed to table prepared in APub↓
This is posible because spreadsheet data is copied as tabular data - that's what @lacerto mentioned.

@lacerto im not an expert of XLSX file format but is it even possible for APub to map the custom number formatting from an Excel XLSX file?
I always thought it was impossible, that it could only retrieve the plain value, but how would it know what rules to apply to get the right format?
I think XLSX only contains a reference to the formatting style, not the actual rules that create that formatting. These are probably saved in Excel.

It wouldn't make sense to save the custom formatting rules in the XLSX file itself. This type of file format implementation would be like 👽

[lacerto]

The bug seems to be related specifically to incorrect interpretation of custom formatted data.

This is how the currency cells are formatted in the original Excel sheet (at least when opened in Windows Excel by default using Finnish regional settings):

When I change this to standard currency formatting with US dollar, and specifying in Excel preferences that custom group and decimal separators (that is, the US comma and dot, instead of Finnish space and comma) should be used:

...and then save the document, and place it in Publisher, I wlll get formatting right:

I'd imagine that this works identically on macOS, but have not tested.

EDIT: So whenever using an Excel sheet that has different number separators from what the system uses, one can specify in Excel preferences exceptions (the setting is global, so once the reformatted Excel sheet has been saved, the regular system setting based formatting can be returned):

[bbrother]

7 minutes ago, lacerto said:

The bug seems to be related specifically to incorrect interpretation of custom formatted data.

I think this is because of the XLSX format specification. Standard formatings are fixed and therefore developers could associate references to such formats and format the regular value accordingly. In the case of custom ones based on user creativity, this is not possible.

Well, how do you write a parser for something like this (if you have only reference to that formatting and rules are missing) ?

[lacerto]

34 minutes ago, bbrother said:

I think this is because of the XLSX format specification. Standard formatings are fixed and therefore developers could associate references to such formats and format the regular value accordingly. In the case of custom ones based on user creativity, this is not possible.

It seems it has been possible to Adobe developers at least from 2012 (InDesign CS6):

 

The clip is from Windows that uses Finnish regional settings with space and comma as group and decimal separator, and as text is interpreted rather than using live formats, needs to be find-replaced to set formats correctly. InDesign would probably read the file correctly if system regional settings were changed and the app restarted. 

InDesign reads the custom formats meaningfully both from .XLS and .XLSX formats, so I think this is basically just a question of maturity of apps, and level of programming skills.

[bbrother]

So I was wrong. All necessary information is contained in the binary data of the XLS format and <xml> data of the XLSX format.
Thank you @lacerto for the clarification. I haven't used XLSX files for a long time since I switched to OpenOffice.

So, developers, it's time to roll up your sleeves and get to work 💻, we need to improve it so that it works properly!

[kat]

My solution: Place pdf in AD, remove mask on table, copy>paste AD table into Apub.

Does Apub come with any table formats, or format templates, so I wouldn't need to format in Excel.

Of course it would be nice to paste excel directly into Apub. Interesting thread. Thanks for the help. 

[kat]

1. Make the table in Apub.
2. Copy paste from Excel.
3. Format in Apub. See screenshot

It does work @lacerto

A lot of duplicating effort. I had previously tried saving an Apub table format, but the text style didn't come thru with the table format.

I need to figure out how to deal with table as I'd prefer content creators to give me excel files. This time I got pdfs with a ton of little text boxes.

[bbrother]

2 hours ago, kat said:

Does Apub come with any table formats, or format templates, so I wouldn't need to format in Excel.

No. You need to build such a library of templates yourself, creating new table formats on by one.

@kat Important thing to know and a big problem is that table formats are 'document specific' wchich in simple means that they aren't available in other document then they where created at and saved at.

Fortunately, the option to import table formats from other documents comes to the rescue here.

You can create a separate .afpub document that will hold several table formats, and just for that, and then import styles from such a document.
It is a kind of source of table formats, but in the form of a separate .afpub file saved in a convenient place on the disk.
This isn't as convenient as if it could be if the custom table formats once saved were available globally in the app and fo every document.

The moral of the above is:

Quote

You have to deal with what is and live with the hope that it will be improved by developers in the future

 

[kat]

So formats are for color, stroke, etc only. No text formatting included?

Good thought @bbrother: You have to deal with what is and live with the hope that it will be improved by developers in the future

[bbrother]

4 hours ago, kat said:

So formats are for color, stroke, etc only. No text formatting included?

No, fortunately, you are not limited only to the visual aspects. Additionally, in the "Edit table format" dialog box, you can also create cell formats (1) to which you can assign the previously created text style (2). Cell formats with an assigned text style can then be used to build a table format. You can apply them to different rows like header or footer etc.

[lacerto]

I had another look on this, and now on macOS. There I first changed number formatting to US English style using System Regional settings (from the default Finnish one), and then simply just opened your .XLSX file in Microsoft Excel (latest version). The formatting came correct out of the box, including the date, and surprisingly were not "custom" formats in macOS Excel (which, btw, in many ways seems smarter than its Windows cousin). So what I did was that I just copied the Excel data onto Clipboard, selected all cells in Publisher imported .XLSX file that is otherwise properly formatted, just messing up number formats, and pasted. Alignments come wrong, but it is a simple task to right align required columns, and vertically center align cells.

 

On Windows (using Finnish regional settings, at least), I had to open the .XLSX file in Excel and change number and date formatting that were interpreted as "custom" to standard US formats, and save back. After that, Publisher opened the file correctly (excluding alignments). On Windows, you can also use Clipboard to transfer correctly formatted data via Clipboard (using the default Rich Text Format), but on Windows reformatted .XLSX is read correctly (on macOS it will still be read incorrectly).

[kat]

I'm on mac OS, so sounds like my excel will not import correct. Thanks for the info @lacerto

[lacerto]

15 hours ago, kat said:

I'm on mac OS, so sounds like my excel will not import correct.

It seems my compliments to macOS version of Excel were premature -- I have US English UI language active in the operating system but Finnish regional settings (including date and number formatting), but my Excel (also using US English UI) just keeps on showing all cell formatting using US English regional settings, even if I have the default editing preference (in Excel) set so that the system group and decimal separator should be used!

I have macOS Sonoma 14.2.1 and latest Excel 16.8.3 installed. So it seems that the current macOS version of Excel forces US English formatting according to OS UI (and/or Office 365 UI language), no matter what. 

On Windows it works in a predictable way even if it requires manual intervention: the formatting you had in your Excel sheet shows as being "custom", but after having been changed to use explicit US English formatting (using Excel Windows version), I could open the file in Publisher so that date and number formatting show correctly.