Re: Set up Constraint in Xlsx
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: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
Re: Set up Constraint in Xlsx
Marcin, On Thu, 2020-05-21 at 13:30 +, 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
Re: Set up Constraint in Xlsx
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
Re: Set up Constraint in Xlsx
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.33. 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: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org - To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
