Set up Constraint in Xlsx

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Set up Constraint in Xlsx

Marcin Wyrwalski
Hello.

 I would like to ask, if there is a possibility to set up constraint to not allow user to write more than three decimall places in cell? I preparing and sending file to user, and user is supposed to return file with some data. I would like to set up constraints on required field, to allow only good values.
Has anybody have any ide it this can be done?

Have a Nice day
marcin Wyrwalski

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Set up Constraint in Xlsx

Rob Sargent
I guess I don't understand what is not "good" about extra digits after
the decimal point?  You can round to whatever precision you choose.
What if the user is using some tool to generate and insert the data and
it divides 1 by 3 and gets 0.3333333333.  You're going to make the user
trim that manually to 0.333.  That would not make me happy.

On 5/21/20 7:30 AM, Marcin Wyrwalski wrote:

> Hello.
>
>   I would like to ask, if there is a possibility to set up constraint to not allow user to write more than three decimall places in cell? I preparing and sending file to user, and user is supposed to return file with some data. I would like to set up constraints on required field, to allow only good values.
> Has anybody have any ide it this can be done?
>
> Have a Nice day
> marcin Wyrwalski
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Set up Constraint in Xlsx

Andreas Reichel
Rob,

On Thu, 2020-05-21 at 19:39 -0600, Rob Sargent wrote:
> I guess I don't understand what is not "good" about extra digits after
>
> the decimal point?

Very simple, but frequent use case: Loading data from a Spreadsheet
file into a database.
In this case, the read value is a FLOAT which needs to be forced into
DECIMAL(?,?). And this can be a real nightmare, because most of the End
Users just copy/paste the content from other data sources into the
Spreadsheet without thinking about it.

Also, certain data, like currency amounts or interest rates permit only
a fixed number of digits.

Spreadsheets actually do lack a BigDecimal data type.

Cheers


Reply | Threaded
Open this post in threaded view
|

Re: Set up Constraint in Xlsx

Andreas Reichel
In reply to this post by Marcin Wyrwalski
Marcin,

On Thu, 2020-05-21 at 13:30 +0000, Marcin Wyrwalski wrote:
> Hello.
>
>  I would like to ask, if there is a possibility to set up constraint to not allow user to write more than three decimall places in cell? I preparing and sending file to user, and user is supposed to return file with some data.

The closest thing I found look like that:
https://exceljet.net/formula/data-validation-number-multiple-100
(Validity defined by a formula.)

You will likely compare ROUND(x, 2) vs ROUND(x/100) and test for Zero
or something like that.
Cheers
Andreas


Reply | Threaded
Open this post in threaded view
|

Re: Set up Constraint in Xlsx

Tomasz Melcer-2
In reply to this post by Marcin Wyrwalski
On 21.05.2020 15:30, Marcin Wyrwalski wrote:
> I would like to ask, if there is a possibility to set up constraint
> to not allow user to write more than three decimall places in cell? I
> preparing and sending file to user, and user is supposed to return
> file with some data. I would like to set up constraints on required
> field, to allow only good values. Has anybody have any ide it this
> can be done?

As far as I understand, this is impossible. The XLSX file has no means
to limit the precision of numbers entered by the user. It only has means
to limit the precision of displaying them. Note that software (at least
MS Excel and LibreOffice Calc from my observations) considers any
numbers entered into cells as IEEE 754 double precision numbers (with
all related artifacts like rounding big integers).

Any rounding that needs to happen in your case will have to be handled
by your spreadsheet parsing code. If you absolutely need to make sure
the user cannot enter numbers that can't be represented as a DECIMAL(?,
?) type in your database, you should reconsider using the XLSX format as
means for providing data by your user.


--
Tomasz Melcer

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]