Search in sources :

Example 1 with SpreadsheetException

use of com.serotonin.m2m2.vo.emport.SpreadsheetException in project ma-core-public by infiniteautomation.

the class PointValueEmporter method importRow.

/*
     * (non-Javadoc)
     * @see com.serotonin.m2m2.vo.emport.AbstractSheetEmporter#importRow(org.apache.poi.ss.usermodel.Row)
     */
@Override
protected void importRow(Row rowData) throws SpreadsheetException {
    int cellNum = 0;
    // Data Point XID
    Cell xidCell = rowData.getCell(cellNum++);
    if (xidCell == null)
        throw new SpreadsheetException(rowData.getRowNum(), "emport.error.xidRequired");
    if ((xidCell.getStringCellValue() == null) || (xidCell.getStringCellValue().isEmpty()))
        throw new SpreadsheetException("emport.error.xidRequired");
    // First Check to see if we already have a point
    String xid = xidCell.getStringCellValue();
    DataPointVO dp = voMap.get(xid);
    DataPointRT dpRt = rtMap.get(xid);
    // We will always have the vo in the map but the RT may be null if the point isn't running
    if (dp == null) {
        dp = dataPointDao.getDataPoint(xid);
        if (dp == null)
            throw new SpreadsheetException(rowData.getRowNum(), "emport.error.missingPoint", xid);
        dpRt = Common.runtimeManager.getDataPoint(dp.getId());
        rtMap.put(xid, dpRt);
        voMap.put(xid, dp);
    }
    PointValueTime pvt;
    // Cell Device name (Not using Here)
    cellNum++;
    // Cell Point name (Not using Here)
    cellNum++;
    // Cell Time
    Date time = rowData.getCell(cellNum++).getDateCellValue();
    // delete/add column
    Cell modifyCell = rowData.getCell(7);
    boolean add = false;
    boolean delete = false;
    if (modifyCell != null) {
        String modification = (String) modifyCell.getStringCellValue();
        if (modification.equalsIgnoreCase("delete")) {
            delete = true;
        } else if (modification.equalsIgnoreCase("add")) {
            add = true;
        } else {
            throw new SpreadsheetException(rowData.getRowNum(), "emport.spreadsheet.modifyCellUnknown");
        }
    }
    // What do we do with the row
    if (delete) {
        if (time == null) {
            throw new SpreadsheetException(rowData.getRowNum(), "emport.error.deleteNew", "no timestamp, unable to delete");
        } else {
            try {
                this.rowsDeleted += Common.runtimeManager.purgeDataPointValue(dp.getId(), time.getTime());
            } catch (Exception e) {
                if (e instanceof DataIntegrityViolationException)
                    throw new SpreadsheetException(rowData.getRowNum(), "emport.error.unableToDeleteDueToConstraints");
                else
                    throw new SpreadsheetException(rowData.getRowNum(), "emport.error.unableToDelete", e.getMessage());
            }
        }
        // Done now
        return;
    } else if (add) {
        // Cell Value
        Cell cell;
        cell = rowData.getCell(cellNum++);
        // Create a data value
        DataValue dataValue;
        switch(dp.getPointLocator().getDataTypeId()) {
            case DataTypes.ALPHANUMERIC:
                dataValue = new AlphanumericValue(cell.getStringCellValue());
                break;
            case DataTypes.BINARY:
                switch(cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        dataValue = new BinaryValue(new Boolean(cell.getBooleanCellValue()));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (cell.getNumericCellValue() == 0)
                            dataValue = new BinaryValue(new Boolean(false));
                        else
                            dataValue = new BinaryValue(new Boolean(true));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        if (cell.getStringCellValue().equalsIgnoreCase("false"))
                            dataValue = new BinaryValue(new Boolean(false));
                        else
                            dataValue = new BinaryValue(new Boolean(true));
                        break;
                    default:
                        throw new SpreadsheetException(rowData.getRowNum(), "common.default", "Invalid cell type for extracting boolean");
                }
                break;
            case DataTypes.MULTISTATE:
                dataValue = new MultistateValue((int) cell.getNumericCellValue());
                break;
            case DataTypes.NUMERIC:
                dataValue = new NumericValue(cell.getNumericCellValue());
                break;
            default:
                throw new SpreadsheetException(rowData.getRowNum(), "emport.spreadsheet.unsupportedDataType", dp.getPointLocator().getDataTypeId());
        }
        // Cell Rendered Value (Not using yet)
        cellNum++;
        // Cell Annotation
        Cell annotationRow = rowData.getCell(cellNum++);
        if (annotationRow != null) {
            String annotation = annotationRow.getStringCellValue();
            // TODO These methods here do not allow updating the Annotation. We need to be a set point source for that to work
            TranslatableMessage sourceMessage = new TranslatableMessage("common.default", annotation);
            pvt = new AnnotatedPointValueTime(dataValue, time.getTime(), sourceMessage);
        } else {
            pvt = new PointValueTime(dataValue, time.getTime());
        }
        // Save to cache if running
        if (dpRt != null)
            dpRt.savePointValueDirectToCache(pvt, null, true, true);
        else {
            if (pointValueDao instanceof EnhancedPointValueDao) {
                DataSourceVO<?> ds = getDataSource(dp.getDataSourceId());
                ((EnhancedPointValueDao) pointValueDao).savePointValueAsync(dp, ds, pvt, null);
            } else {
                pointValueDao.savePointValueAsync(dp.getId(), pvt, null);
            }
        }
        // Increment our counter
        this.rowsAdded++;
    }
}
Also used : DataPointVO(com.serotonin.m2m2.vo.DataPointVO) DataSourceVO(com.serotonin.m2m2.vo.dataSource.DataSourceVO) DataValue(com.serotonin.m2m2.rt.dataImage.types.DataValue) ExportDataValue(com.serotonin.m2m2.vo.export.ExportDataValue) BinaryValue(com.serotonin.m2m2.rt.dataImage.types.BinaryValue) SpreadsheetException(com.serotonin.m2m2.vo.emport.SpreadsheetException) Date(java.util.Date) DataIntegrityViolationException(org.springframework.dao.DataIntegrityViolationException) SpreadsheetException(com.serotonin.m2m2.vo.emport.SpreadsheetException) MultistateValue(com.serotonin.m2m2.rt.dataImage.types.MultistateValue) DataIntegrityViolationException(org.springframework.dao.DataIntegrityViolationException) AlphanumericValue(com.serotonin.m2m2.rt.dataImage.types.AlphanumericValue) TranslatableMessage(com.serotonin.m2m2.i18n.TranslatableMessage) EnhancedPointValueDao(com.serotonin.m2m2.db.dao.EnhancedPointValueDao) NumericValue(com.serotonin.m2m2.rt.dataImage.types.NumericValue) Cell(org.apache.poi.ss.usermodel.Cell)

Example 2 with SpreadsheetException

use of com.serotonin.m2m2.vo.emport.SpreadsheetException in project ma-core-public by infiniteautomation.

the class SpreadsheetEmporter method importSheet.

/**
 * Imports one sheet
 */
private void importSheet(AbstractSheetEmporter sheetEmporter) {
    Sheet sheet = wb.getSheet(sheetEmporter.getSheetName());
    if (sheet == null) {
        errorMessages.add(new TranslatableMessage("emport.spreadsheet.sheetNotFound", sheetEmporter.getSheetName()));
        return;
    }
    int cellNum = 0;
    Cell cell;
    Row row;
    int numRows = sheet.getLastRowNum() + 1;
    if (numRows == 0) {
        errorMessages.add(new TranslatableMessage("emport.spreadsheet.nowRows", sheetEmporter.getSheetName()));
        return;
    }
    // check the headers are correct, just in case the user has reordered the columns
    rowNum = 0;
    row = sheet.getRow(rowNum);
    if (sheetEmporter.hasHeaders()) {
        String[] headers = sheetEmporter.getHeaders();
        if (row.getLastCellNum() < headers.length) {
            errorMessages.add(new TranslatableMessage("emport.spreadsheet.headerLengthIncorrect", sheetEmporter.getSheetName(), headers.length, row.getLastCellNum()));
            return;
        }
        for (cellNum = 0; cellNum < headers.length; cellNum++) {
            cell = row.getCell(cellNum);
            String cellValue;
            try {
                cellValue = readStringCell(rowNum, cell);
            } catch (SpreadsheetException e) {
                cellValue = "";
            }
            if (cellValue == null || !cellValue.equalsIgnoreCase(headers[cellNum])) {
                errorMessages.add(new TranslatableMessage("emport.spreadsheet.headersMismatch", sheetEmporter.getSheetName(), cellNum, headers[cellNum], cellValue));
                return;
            }
        }
        rowNum += 1;
    }
    // import the actual data rows
    for (; rowNum < numRows; rowNum++) {
        try {
            // Import this row
            row = sheet.getRow(rowNum);
            if (!isRowEmpty(row)) {
                sheetEmporter.importRow(row);
                rowsProcessed++;
            }
        } catch (Exception e) {
            if (e instanceof SpreadsheetException) {
                ((SpreadsheetException) e).setRowNum(rowNum);
                errorMessages.addAll(((SpreadsheetException) e).getMessages());
            } else {
                errorMessages.add(new TranslatableMessage("common.default", e.getMessage() + " - row " + rowNum));
            }
            rowErrors++;
        }
    }
    rowsAdded += sheetEmporter.getRowsAdded();
    rowsDeleted += sheetEmporter.getRowsDeleted();
}
Also used : TranslatableMessage(com.serotonin.m2m2.i18n.TranslatableMessage) Row(org.apache.poi.ss.usermodel.Row) Sheet(org.apache.poi.ss.usermodel.Sheet) Cell(org.apache.poi.ss.usermodel.Cell) IOException(java.io.IOException)

Aggregations

TranslatableMessage (com.serotonin.m2m2.i18n.TranslatableMessage)2 Cell (org.apache.poi.ss.usermodel.Cell)2 EnhancedPointValueDao (com.serotonin.m2m2.db.dao.EnhancedPointValueDao)1 AlphanumericValue (com.serotonin.m2m2.rt.dataImage.types.AlphanumericValue)1 BinaryValue (com.serotonin.m2m2.rt.dataImage.types.BinaryValue)1 DataValue (com.serotonin.m2m2.rt.dataImage.types.DataValue)1 MultistateValue (com.serotonin.m2m2.rt.dataImage.types.MultistateValue)1 NumericValue (com.serotonin.m2m2.rt.dataImage.types.NumericValue)1 DataPointVO (com.serotonin.m2m2.vo.DataPointVO)1 DataSourceVO (com.serotonin.m2m2.vo.dataSource.DataSourceVO)1 SpreadsheetException (com.serotonin.m2m2.vo.emport.SpreadsheetException)1 ExportDataValue (com.serotonin.m2m2.vo.export.ExportDataValue)1 IOException (java.io.IOException)1 Date (java.util.Date)1 Row (org.apache.poi.ss.usermodel.Row)1 Sheet (org.apache.poi.ss.usermodel.Sheet)1 DataIntegrityViolationException (org.springframework.dao.DataIntegrityViolationException)1