Problem with saving values with leading zeros from SpreadsheetDoc to Excel

Hello!

We faced with some issue with exporting spreadsheet document to Excel.

If we have some values with leading zeros (for example Spare Item Part Number can be 0004213).

When we save print form to Excel in value field of excel it shows like 4213 (so our customers have problems with uploading from excel in their systems).

Is it a bug or maybe you can advise us how we can solve this issue.

In attachments example of spreadsheet (placed in zip because forum doesn't accept mxl) and screenshot from Excel.
Thank you.

Good morning, Pavel!

Try saving as Excel 95 format. In this case instead of formatting, leading zeros are saved as strings.

Good morning, Timofey Bugaevsky,

Thank you for your quick response.

It solves our problem, but it looks like temporary action because in my vision its not correct way to push users to save it in 95 format.

From users side everyone want to use actual format.

So is it platform bug? Could your register it?

Thank you.

Pavel,

I'm not sure that this is a bug, because leading zeros normally do not mean anything and you can search after trimming them.

You can also save numbers with leading single quote character, so they will not be numbers actually and become strings.

Another option is to add saving as CSV command and generate this file using the script.

Timofey Bugaevsky,

For example auto spare parts business. Part # could start from zeros or not and it does means (because in customers\suppliers databases they have also # with zeros).

All options you offer looks like workarounds.

In my vision it's not correct to cut zeros automatically from platform side for the values with type String, because for different kinds of business it could have different means. Anyway we always could cut it from source.

It will be more reliable, if you develop a data synchronization mechanism to transfer data into third-party database.

Timofey Bugaevsky,

We cannot develop data synchronization mechanisms with all suppliers' systems and customers don't interested to spend money for that because all suppliers accept Excel.

Regarding your previous options:
1) Unfortunately we also cannot use saving in 95 because in this format logo and colors are different, pictures become compressed.
2) When we try to put single quote in our spreadsheet and export to excel. In excel field have "General" format (not text) - so we see ' in field. In case we put the same value with quote in excel it automatically comes to text format and quote dissapear. (see attached screenshot Export Bug 1)

One more example.

Value "90915-YZZD4" is saved as 90915... Is it also not a bug? (see attached screenshot Export Bug 2)

You can add an Export CSV button and create CSV file for those users. If they open it with Excel, they will have plain data inside and it will be easy for them. You can even name this button Open in Excel.

Timofey Bugaevsky,

You still think that it is not a bug? When "90915-YZZD4" is saved as 90915. Or When string with ' comes not to Text format?

Of course we can create our own commands, but all of this looks like workarounds in case of platform provide those functionality but not working in proper way...

Would you please provide an MXL file. I could not reproduce this issue. Please see attached files.

Timofey Bugaevsky,

Please find in attached files.

Problems are with field "Catalog #" in rows: 18-20, 22, 29

Thank you for the information, Pavel.

I have sent this information to the developer team.

Hello Timofey Bugaevsky,

Do you have any updates regarding this issue? It's very important for us and our customer, because most documents have such problems in case of their business. And we think that its really not correct to cut zeros automatically. Because in some businesses it's not allowed. And in general if its really needed developer could do it by himself using source.

Or at least platform should define single quote and put correct cell type if found it.
Thanks

Hello, Pavel.

Developers are investigating this issue.

Hello, Timofey Bugaevsky,

Could you please clarify when we can expect the solution? Our customer constantly work through excel and cannot do it properly. It's very urgent question for us and for them.

Thanks

Hello, Pavel.

It is still under discussion. I'm sorry.

Hello,

I faced the same problem.

For now we have to do the following:
In 1c I add quote to the value of cells and then in Excel use a macro to change format
of selected cells.

Sub FormatChange()
'
' FormatChange Macro
' Format change

   Dim oCell As Range

   For Each oCell In Selection.Cells
        oCell = oCell.Value
   Next

End Sub

Hello,

Again we have faced on one of our projects with export problems to Excel format later that 95.
And its not regarding leading zeros.

Find attached files.
Are 1C still thinking that its not a bug?

Hello, Pavel!

I have good news, this behavior will be changed in one of future versions of 1C:Enterprise platform. Unfortunately so far I'm unable to tell the version number.

Timofey Bugaevsky,

Sounds great.

Could you update us please when you will be able to share a version number.


Thanks

Hello,

Still not have any details regarding this issue?

Hello, Pavel!

Thank you for your interest.
I will double-check and let you know.

Good news: this issue will be fixed in 1C:Enterprise platform 8.3.10.

Hello, Timofey Bugaevsky.

Thanks for the updates. Will wait 8.3.10.