Stages Documentation

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
Last revisionBoth sides next revision
72:custom_reports_references_poijava [2019/03/08 19:18] – created ext-bkkr72:custom_reports_references_poijava [2019/03/08 20:29] – ext-bkkr
Line 1: Line 1:
 ====== POI Javascript Wrapper Functions ====== ====== POI Javascript Wrapper Functions ======
 +
 +These functions can be used to retrieve values from an Excel file using the Java POI library.
 +
 +<code>
 +function getNumericValue(sheet,row, col) {
 + Row = sheet.getRow(row);
 + if (Row != null) {
 + Cell = Row.getCell(col);
 + if (Cell != null) {
 + return(Cell.getNumericCellValue());
 + }
 + }
 + return "";
 +}
 +function getStringValue(sheet,row, col) {
 + Row = sheet.getRow(row);
 + Cell = Row.getCell(col);
 + if (Cell != null) {
 + if (Cell.getCellType() == Cell.CELL_TYPE_STRING) {
 + return Cell.getStringCellValue();
 + }
 + }
 + return "";
 +}
 +function getDateValue(sheet,row, col) {
 + Row = sheet.getRow(row);
 + Cell = Row.getCell(col);
 + if (Cell != null) {
 + if ((Cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
 + || (Cell.getCellType() == Cell.CELL_TYPE_FORMULA)) {
 + return Cell.getDateCellValue();
 + }
 + }
 + return "";
 +}
 +/* taken vom http://trephine.org/t/index.php?
 +title=Extracting_Excel_data_with_Apache_POI_and_Rhino */
 +function cellValue(sheet, CellRow, CellCol) {
 + row = sheet.getRow(CellRow);
 + if (row != null) {
 + cell = row.getCell(CellCol);
 + if (cell != null) {
 + var t = cell.cellType;
 + if (t == Cell.CELL_TYPE_NUMERIC) {
 + if (DateUtil.isCellDateFormatted(cell)) return new
 + Date(cell.dateCellValue.time);
 + else return cell.numericCellValue + 0.0;
 + }
 + if (t == Cell.CELL_TYPE_STRING) return cell.stringCellValue + '';
 + if (t == Cell.CELL_TYPE_FORMULA) {
 + try {
 + if (DateUtil.isCellDateFormatted(cell)) return new
 + Date(cell.dateCellValue.time);
 + else return cell.numericCellValue + 0.0;
 + } catch(err) { }
 + try { return cell.stringCellValue + ''; } catch(err) { }
 + try { return cell.numericCellValue + 0.0; } catch(err) { }
 + try { return !!cell.booleanCellValue; } catch(err) { }
 + try { return cell.errorCellValue + 0; } catch(err) { }
 + return '';
 +}
 + if (t == Cell.CELL_TYPE_BLANK) return '';
 + if (t == Cell.CELL_TYPE_BOOLEAN) return !!cell.booleanCellValue;
 + if (t == Cell.CELL_TYPE_ERROR) return cell.errorCellValue + 0;
 + }
 + }
 + return null;
 +}
 +function isCellEmpty(sheet,row, col) {
 + Row = sheet.getRow(row);
 + if (Row != null) {
 + Cell = Row.getCell(col);
 + if (Cell != null) {
 + if (Cell.getCellType() != Cell.CELL_TYPE_BLANK) {
 + return false;
 + }
 + }
 + }
 + return true;
 +}
 +</code>