Hi Nick,
I just revised my proggy (see Attachment) to log the Format String too.
Its "@"
I'd be up for a dialogue with Dave North:
Its a very long time since I've been involved with POI,
so my knowledge of POI & the xslx Format are sketchy,
but I have got the mathematical, analytical & Java skills.
And I need to find something to take my mind off Boris Johnson & cronies...
All the best,
DaveLaw
On 20/10/2019 22:50, Nick Burch wrote:
On Sun, 20 Oct 2019, David Law wrote:
the Cells have no Format. Take a look at the attached File, cell
F10. Andreas tells me it was entered as 0.1066913 & that's how its
displayed too, although it has no format.
Numeric cells have a default format if nothing else is applied, it
could be that perhaps?
I know that David North did some work a few years ago on trying to
understand + match the Excel floating point rules, it might be worth
having a look at some of his mailing list posts for more details. He
isn't involved much in POI at the moment (day job priority changes),
but we can always ping him to chime in if needed!
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Arrays;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ApachePoiExcelReadManticoreScaledTest {
private static final String FILE_NAME =
"./ifrsbox_DataCaptureExample.xlsx";
private static final int DECIMALS_07 = 7;
public static void main(final String[] args) {
try(final FileInputStream excelFile = new
FileInputStream(new File(FILE_NAME));
final XSSFWorkbook workbook = new
XSSFWorkbook(excelFile)
) {
final XSSFSheet sheet = (XSSFSheet)
workbook.getSheetAt(0);
System.out.println("Sheet.: " + sheet.getSheetName());
sheet .forEach(currentRow -> {
if (currentRow.getRowNum() <= 2) {
return;
}
currentRow.forEach(currentCell -> {
final XSSFCell xssfCell =
(XSSFCell) currentCell;
final CellType cellType =
xssfCell.getCellType();
final String rawValue =
xssfCell.getRawValue();
final XSSFCellStyle style =
xssfCell.getCellStyle();
if (xssfCell.getColumnIndex() != 5) {
return;
}
System.out.print("type=" +
rPad(cellType.toString(), 9));
/*
* TODO Warning: use rawValue, NOT
cellValue.
*
* TODO Warning: use new
BigDecimal(String) constructor.
* ............: not new
BigDecimal(double) (see Javadoc)
*
* TODO Learn how to use BigDecimal
Precision & Scale!!!!
*/
switch (cellType) {
case NUMERIC : final String
cellValue = Double.valueOf(xssfCell.getNumericCellValue()).toString();
/**/ final BigDecimal
rawBig = new BigDecimal(rawValue).setScale(DECIMALS_07,
RoundingMode.HALF_EVEN);
/**/ final String
rawString = rawBig.toString();
/**/
System.out.print("cellFmt=" + rPad(style.getDataFormatString(), 25));
/**/
System.out.print("poiValue=" + rPad(cellValue, 25));
/**/
System.out.print("rawValue=" + rPad(rawValue, 25));
/**/ if
(cellValue.equals(rawString)) {
/**/ break;
/**/ }
/**/
System.out.print("rawBig=" + rPad(rawString, 25));
/**/
System.out.print("scale=" + rPad(String.valueOf(rawBig.scale()), 8));
/**/
System.out.print("precision=" + rPad(String.valueOf(rawBig.precision()), 8));
/**/ break;
case FORMULA :
System.out.print("formula=" + rPad(rawValue, 25));
/**/ break;
default : throw new
NullPointerException("Exception of your choice...");
}
});
System.out.println();
});
} catch (final IOException e) {
e.printStackTrace();
}
}
public static final String rPad(final CharSequence value, final int
padLength) {
final char[] padChars = new char[Math.max(0, padLength -
value.length())];
Arrays.fill (padChars, ' ');
return value.toString().concat(String.valueOf(padChars));
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
