com.efficio.fieldbook.web.nursery.service.impl.ExcelImportStudyServiceImpl.java Source code

Java tutorial

Introduction

Here is the source code for com.efficio.fieldbook.web.nursery.service.impl.ExcelImportStudyServiceImpl.java

Source

/*******************************************************************************
 * Copyright (c) 2012, All Rights Reserved.
 *
 * Generation Challenge Programme (GCP)
 *
 *
 * This software is licensed for use under the terms of the GNU General Public
 * License (http://bit.ly/8Ztv8M) and the provisions of Part F of the Generation
 * Challenge Programme Amended Consortium Agreement (http://bit.ly/KQX1nL)
 *
 *******************************************************************************/
package com.efficio.fieldbook.web.nursery.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.generationcp.middleware.domain.etl.MeasurementData;
import org.generationcp.middleware.domain.etl.MeasurementRow;
import org.generationcp.middleware.domain.etl.MeasurementVariable;
import org.generationcp.middleware.domain.etl.Workbook;
import org.generationcp.middleware.domain.oms.TermId;
import org.generationcp.middleware.exceptions.WorkbookParserException;
import org.springframework.stereotype.Service;

import com.efficio.fieldbook.web.nursery.service.ExcelImportStudyService;

@Service
public class ExcelImportStudyServiceImpl implements ExcelImportStudyService {

    private static final String TEMPLATE_DESCRIPTION_SHEET_FIRST_VALUE = "STUDY";
    private static final String TEMPLATE_SECTION_CONDITION = "CONDITION";
    private static final String TEMPLATE_SECTION_FACTOR = "FACTOR";
    private static final String TEMPLATE_SECTION_CONSTANT = "CONSTANT";
    private static final String TEMPLATE_SECTION_VARIATE = "VARIATE";

    @Override
    public void importWorkbook(Workbook workbook, String filename) throws WorkbookParserException {
        try {
            HSSFWorkbook xlsBook = new HSSFWorkbook(new FileInputStream(new File(filename))); //WorkbookFactory.create(new FileInputStream(new File(filename))); 

            validate(xlsBook, workbook);

            importDataToWorkbook(xlsBook, workbook);

        } catch (WorkbookParserException e) {
            throw e;

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void importDataToWorkbook(HSSFWorkbook xlsBook, Workbook workbook) {
        if (workbook.getObservations() != null) {
            HSSFSheet observationSheet = xlsBook.getSheetAt(1);
            int xlsRowIndex = 1; //row 0 is the header row
            for (MeasurementRow wRow : workbook.getObservations()) {
                HSSFRow xlsRow = observationSheet.getRow(xlsRowIndex);
                for (MeasurementData wData : wRow.getDataList()) {
                    String label = wData.getLabel();
                    int xlsColIndex = findColumn(observationSheet, label);
                    Cell cell = xlsRow.getCell(xlsColIndex);
                    String xlsValue = "";

                    if (cell != null) {
                        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            Double doubleVal = Double.valueOf(cell.getNumericCellValue());
                            Integer intVal = Integer.valueOf(doubleVal.intValue());
                            if (Double.parseDouble(intVal.toString()) == doubleVal.doubleValue()) {
                                xlsValue = intVal.toString();
                            } else {
                                xlsValue = doubleVal.toString();
                            }

                        } else
                            xlsValue = cell.getStringCellValue();
                    }
                    wData.setValue(xlsValue);
                }
                xlsRowIndex++;
            }
        }
    }

    private void validate(HSSFWorkbook xlsBook, Workbook workbook) throws WorkbookParserException {
        HSSFSheet descriptionSheet = xlsBook.getSheetAt(0);
        HSSFSheet observationSheet = xlsBook.getSheetAt(1);

        validateNumberOfSheets(xlsBook);
        validateDescriptionSheetFirstCell(descriptionSheet);
        validateSections(descriptionSheet);
        validateRequiredObservationColumns(observationSheet, workbook);
        validateNumberOfRows(observationSheet, workbook);
        validateRowIdentifiers(observationSheet, workbook);
        validateObservationColumns(getAllVariates(descriptionSheet), workbook);
    }

    private void validateNumberOfSheets(HSSFWorkbook xlsBook) throws WorkbookParserException {
        if (xlsBook.getNumberOfSheets() != 2) {
            throw new WorkbookParserException("error.workbook.import.invalidNumberOfSheets");
        }
    }

    private void validateDescriptionSheetFirstCell(HSSFSheet descriptionSheet) throws WorkbookParserException {
        if (!TEMPLATE_DESCRIPTION_SHEET_FIRST_VALUE
                .equalsIgnoreCase(descriptionSheet.getRow(0).getCell(0).getStringCellValue())) {
            throw new WorkbookParserException("error.workbook.import.invalidFormatDescriptionSheet");
        }
    }

    private void validateSections(HSSFSheet descriptionSheet) throws WorkbookParserException {
        int conditionRow = findRow(descriptionSheet, TEMPLATE_SECTION_CONDITION);
        int factorRow = findRow(descriptionSheet, TEMPLATE_SECTION_FACTOR);
        int constantRow = findRow(descriptionSheet, TEMPLATE_SECTION_CONSTANT);
        int variateRow = findRow(descriptionSheet, TEMPLATE_SECTION_VARIATE);
        if (conditionRow <= 0 || factorRow <= conditionRow || constantRow <= conditionRow
                || variateRow <= conditionRow) {
            throw new WorkbookParserException("error.workbook.import.invalidSections");
        }
    }

    private void validateRequiredObservationColumns(HSSFSheet obsSheet, Workbook workbook)
            throws WorkbookParserException {
        int entryCol = findColumn(obsSheet, getColumnLabel(workbook, TermId.ENTRY_NO.getId()));
        int plotCol = findColumn(obsSheet, getColumnLabel(workbook, TermId.PLOT_NO.getId()));
        if (plotCol == -1) {
            plotCol = findColumn(obsSheet, getColumnLabel(workbook, TermId.PLOT_NNO.getId()));
        }
        int gidCol = findColumn(obsSheet, getColumnLabel(workbook, TermId.GID.getId()));
        int desigCol = findColumn(obsSheet, getColumnLabel(workbook, TermId.DESIG.getId()));
        if (entryCol <= -1 || plotCol <= -1 || gidCol <= -1 || desigCol <= -1) {
            throw new WorkbookParserException("error.workbook.import.requiredColumnsMissing");
        }
    }

    private void validateNumberOfRows(HSSFSheet observationSheet, Workbook workbook)
            throws WorkbookParserException {
        if (workbook.getObservations() != null
                && observationSheet.getLastRowNum() != workbook.getObservations().size()) {
            throw new WorkbookParserException("error.workbook.import.observationRowCountMismatch");
        }
    }

    private void validateRowIdentifiers(HSSFSheet observationSheet, Workbook workbook)
            throws WorkbookParserException {
        if (workbook.getObservations() != null) {
            String gidLabel = getColumnLabel(workbook, TermId.GID.getId());
            String desigLabel = getColumnLabel(workbook, TermId.DESIG.getId());
            String entryLabel = getColumnLabel(workbook, TermId.ENTRY_NO.getId());
            int gidCol = findColumn(observationSheet, gidLabel);
            int desigCol = findColumn(observationSheet, desigLabel);
            int entryCol = findColumn(observationSheet, entryLabel);
            int rowIndex = 1;
            for (MeasurementRow wRow : workbook.getObservations()) {
                HSSFRow row = observationSheet.getRow(rowIndex++);

                Integer gid = getMeasurementDataValueInt(wRow, gidLabel);
                String desig = getMeasurementDataValue(wRow, desigLabel);
                Integer entry = getMeasurementDataValueInt(wRow, entryLabel);
                Integer xlsGid = getExcelValueInt(row, gidCol);
                String xlsDesig = row.getCell(desigCol).getStringCellValue();
                Integer xlsEntry = getExcelValueInt(row, entryCol);

                if (gid == null || desig == null || entry == null || xlsDesig == null || !gid.equals(xlsGid)
                        || !desig.trim().equalsIgnoreCase(xlsDesig.trim()) || !entry.equals(xlsEntry)) {

                    throw new WorkbookParserException("error.workbook.import.observationRowMismatch");
                }
            }
        }
    }

    private void validateObservationColumns(List<String> allXlsVariates, Workbook workbook)
            throws WorkbookParserException {
        if (workbook.getMeasurementDatasetVariables() != null) {
            for (MeasurementVariable mvar : workbook.getVariates()) {
                if (!allXlsVariates.contains(mvar.getName().toUpperCase())) {
                    throw new WorkbookParserException("error.workbook.import.columnsMismatch");
                }
            }
        }
    }

    private List<String> getAllVariates(HSSFSheet descriptionSheet) {
        List<String> variates = new ArrayList<String>();

        int startRowIndex = findRow(descriptionSheet, TEMPLATE_SECTION_VARIATE) + 1;
        int endRowIndex = descriptionSheet.getLastRowNum();

        if (startRowIndex <= endRowIndex) {
            for (int rowIndex = startRowIndex; rowIndex <= endRowIndex; rowIndex++) {
                variates.add(descriptionSheet.getRow(rowIndex).getCell(0).getStringCellValue().toUpperCase());
            }
        }

        return variates;
    }

    private int findRow(HSSFSheet sheet, String cellValue) {
        int result = 0;
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row != null) {
                HSSFCell cell = row.getCell(0);
                if (cell != null && cell.getStringCellValue() != null) {
                    if (cell.getStringCellValue().equals(cellValue)) {
                        return i;
                    }
                }
            }
        }

        return result;
    }

    private int findColumn(HSSFSheet sheet, String cellValue) {
        int result = -1;
        if (cellValue != null) {
            HSSFRow row = sheet.getRow(0); //Encabezados
            int cells = row.getLastCellNum();
            for (int i = 0; i < cells; i++) {
                HSSFCell cell = row.getCell(i);
                if (cell.getStringCellValue().equals(cellValue)) {
                    return i;
                }
            }
        }
        return result;
    }

    private String getColumnLabel(Workbook workbook, int termId) {
        List<MeasurementVariable> variables = workbook.getMeasurementDatasetVariables();
        for (MeasurementVariable variable : variables) {
            if (variable.getTermId() == termId) {
                return variable.getName();
            }
        }
        return null;
    }

    private MeasurementData getMeasurementData(MeasurementRow row, String label) {
        for (MeasurementData data : row.getDataList()) {
            if (data.getLabel().equals(label)) {
                return data;
            }
        }
        return null;
    }

    private String getMeasurementDataValue(MeasurementRow row, String label) {
        MeasurementData data = getMeasurementData(row, label);
        if (data != null) {
            return data.getValue();
        }
        return null;
    }

    private Integer getMeasurementDataValueInt(MeasurementRow row, String label) {
        MeasurementData data = getMeasurementData(row, label);
        if (data != null && NumberUtils.isNumber(data.getValue())) {
            return Integer.valueOf(data.getValue());
        }
        return null;
    }

    private Integer getExcelValueInt(HSSFRow row, int columnIndex) {
        Cell cell = row.getCell(columnIndex);
        String xlsStr = "";
        if (cell.getCellType() == Cell.CELL_TYPE_STRING)
            xlsStr = cell.getStringCellValue();
        else
            xlsStr = String.valueOf((int) cell.getNumericCellValue());
        if (NumberUtils.isNumber(xlsStr)) {
            return Integer.valueOf(xlsStr);
        }
        return null;
    }
}