[Bug 61495] FormulaEvaluator does evaluate Number wrong. [=TEXT(nr;FORMAT)]

[bugzilla]

https://bz.apache.org/bugzilla/show_bug.cgi?id=61495

L.Dag  changed:

   What|Removed |Added

Version|3.16-FINAL  |3.17-FINAL

--- Comment #2 from L.Dag  ---
Hello again,
I did HOTFIX this problem for my usecase, however i think that this still
should be adressed since it is a bug after all.
The Hotfix is made by before reading in anything from the Workbook, i do parse
every Formula Cell and replace the "0,00" to a "0.00" so the FormulaEvaluator
can operate as intended.

#ACTUAL_CODE##

/**
 * @author l.dag
 * 
 * searches Each Sheet, Row and Cell for Formulas with "0,00"(=German standard)
and replaces with "0.00"(=US standard)
 *
 */
public static void repairFormulas(final Workbook wb) {
  wb.sheetIterator().forEachRemaining(sheet ->
IntStream.range(sheet.getFirstRowNum(), sheet.getLastRowNum()).filter(i ->
sheet.getRow(i)!=null).mapToObj(sheet::getRow)
  .flatMap(r -> IntStream.range(r.getFirstCellNum(),
r.getLastCellNum()).filter(i -> r.getCell(i)!=null).mapToObj(r::getCell))
  .filter(c -> Cell.CELL_TYPE_FORMULA == c.getCellType() &&
c.getCellFormula().contains("\"0,00\"")).forEach(c ->
c.setCellFormula(c.getCellFormula().replace("\"0,00\"", "\"0.00\"";
}

-- 
You are receiving this mail because:
You are the assignee for the bug.
-
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org

[Bug 62088] DataFormatter should provide additional createFormat method for callers that do not work with Cell (SAX)

[bugzilla]

https://bz.apache.org/bugzilla/show_bug.cgi?id=62088

--- Comment #3 from bhuber  ---
please also see following Ticket of me on stackoverflow:

https://stackoverflow.com/questions/48701673/parse-excel-decimal-format-with-apache-poi-to-java-bigdecimal/48706525#48706525

I solved it now with a different approach, so maybe this feature is not needed
anymore..

-- 
You are receiving this mail because:
You are the assignee for the bug.
-
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org

[Bug 62088] DataFormatter should provide additional createFormat method for callers that do not work with Cell (SAX)

[bugzilla]

https://bz.apache.org/bugzilla/show_bug.cgi?id=62088

bhuber  changed:

   What|Removed |Added

 Status|NEEDINFO|NEW

--- Comment #2 from bhuber  ---
@Nick Burch

Yes the private method "Format createFormat(double cellValue, int formatIndex,
String sFormat)" does exactly do what i need.

My use-case is the following:

I need to import Excel-files that are huge, and therefore i need to use the
"XSSF and SAX Event API" for memory-reasons (save HEAP-memory).

Numbers are saved by Excel als Floating-point values like this:

62.4740993

We have a convention for our software that all BigDecimals that we save into
our database need to have precision of max. 9 digits before the fraction and
max. 7 digits after the fraction. As you see, the above number has 15 digits
after the fraction which is too long. As i do not want to deal with
floating-point problem at all i just want to parse the number as it is shown to
the user in the Excel-GUI. To do this with apache-poi i can call the function
DataFormatter.formatRawCellContents(..) which gives me the following value for
the above example:

62,474

As you see the decimal separator is a comma (,). I need to replace the decimal
separator of the excel-format with the decimal-separator of BigDecimal so i can
use BigDecimal with this value. But because the decimal-separator of the
excel-format is specific to the country i can not just replace it. I need to
find out the decimal-separator of the format like follow, and replace it with a
dot (.).

String xlsxValue = formatter.formatRawCellContents(
Double.parseDouble(cellValue.getValue()),
cellValue.getFormatIndex(),
cellValue.getFormatString());

CellStyle style = new CellStyleHack(
cellValue.getFormatIndex(), cellValue.getFormatString());
Cell cell = new CellHack(Double.parseDouble(cellValue.getValue()),
style);
java.text.Format format = dataFormatter.createFormat(cell);
if (format instanceof DecimalFormat) {
DecimalFormat decimalFormat = ((DecimalFormat) format);
char dSep =
decimalFormat.getDecimalFormatSymbols().getDecimalSeparator();
char gSep =
decimalFormat.getDecimalFormatSymbols().getGroupingSeparator();
String cSymbol =
decimalFormat.getDecimalFormatSymbols().getCurrencySymbol();
String posPre = decimalFormat.getPositivePrefix();
String posSuf = decimalFormat.getPositiveSuffix();
String negPre = decimalFormat.getNegativePrefix();
String negSuf = decimalFormat.getNegativeSuffix();

// java always expects '.' as decimal seperator for BigDecimal and
Double.
xlsxValue = xlsxValue.replace("" + gSep, "");
xlsxValue = xlsxValue.replace(dSep, '.');
if (cSymbol != null) {
// remove €
xlsxValue = xlsxValue.replace(cSymbol, "");
}
if (posPre != null) {
// remove %
xlsxValue = xlsxValue.replace(posPre, "");
}
if (posSuf != null) {
// remove %
xlsxValue = xlsxValue.replace(posSuf, "");
}
if (negPre != null) {
// remove %
xlsxValue = xlsxValue.replace(negPre, "");
}
if (negSuf != null) {
// remove %
xlsxValue = xlsxValue.replace(negSuf, "");
}
xlsxValue = xlsxValue.trim();
}
return xlsxValue;

-- 
You are receiving this mail because:
You are the assignee for the bug.
-
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org

[Bug 62088] DataFormatter should provide additional createFormat method for callers that do not work with Cell (SAX)

[bugzilla]

https://bz.apache.org/bugzilla/show_bug.cgi?id=62088

Nick Burch  changed:

   What|Removed |Added

 Status|NEW |NEEDINFO

--- Comment #1 from Nick Burch  ---
We have a private method that I think may be what you want:

private Format createFormat(double cellValue, int formatIndex, String sFormat)

If you enable access to that method via reflection + setAccessible, does that
do what you need?

Could you also clarify why you want the Format object rather than the formatted
string?

-- 
You are receiving this mail because:
You are the assignee for the bug.
-
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org

[Bug 62088] New: DataFormatter should provide additional createFormat method for callers that do not work with Cell (SAX)

[bugzilla]

https://bz.apache.org/bugzilla/show_bug.cgi?id=62088

Bug ID: 62088
   Summary: DataFormatter should provide additional createFormat
method for callers that do not work with Cell (SAX)
   Product: POI
   Version: unspecified
  Hardware: PC
OS: Linux
Status: NEW
  Severity: enhancement
  Priority: P2
 Component: XSSF
  Assignee: dev@poi.apache.org
  Reporter: bernd.hu...@orbiz.com
  Target Milestone: ---

I use the "XSSF and SAX Event API". 

Because of that i do not have a "Cell" object.

I can hapilly use the DataFormatter.formatRawCellContents(double value, int
formatIndex, String formatString) method, because i have all parameters needed
to call it.

But i can not use the DataFormatter.createFormat(Cell cell) method because it
expects a Cell-object as parameter.

Could you please provide an additional createFormat-Method that also accepts
"double value, int formatIndex, String formatString" as parameters instead?

This would add helpful functionality for all of us using the "XSSF and SAX
Event API".

-- 
You are receiving this mail because:
You are the assignee for the bug.
-
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org

[Bug 62040] Quotient.evaluate function does not accept cell refences

[bugzilla]

https://bz.apache.org/bugzilla/show_bug.cgi?id=62040

--- Comment #3 from Dominik Stadler  ---
That would be perfekt!

-- 
You are receiving this mail because:
You are the assignee for the bug.
-
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org