I have recently designed a module which will do bulk upload for different contents. Basically the user will upload an Excel file, I will have to read and validate headers of excel then each value of cell for all columns. I have designed a framework please validate.Below are the vo.Template Header details: Excel for each content is defined as template which will have an ID and will have the header (Excel column header) for each template:public class TemplateHeaderDetails extends BaseVO {    Integer templateId;    String templateName;    public String headerNames;    String mandatory;    Integer fieldId;    public Integer getTemplateId() {        return templateId;    }    public void setTemplateId(Integer templateId) {        this.templateId = templateId;    }    public String getTemplateName() {        return templateName;    }    public void setTemplateName(String templateName) {        this.templateName = templateName;    }    public String getHeaderNames() {        return headerNames;    }    public void setHeaderNames(String headerNames) {        this.headerNames = headerNames;    }    public String getMandatory() {        return mandatory;    }    public void setMandatory(String mandatory) {        this.mandatory = mandatory;    }    public Integer getFieldId() {        return fieldId;    }    public void setFieldId(Integer fieldId) {        this.fieldId = fieldId;    }}Each header column is given a field Id as you can see in the vo above.We have defined validation rules:public class Rule extends BaseVO {    String ruleName;    Integer ruleId;    String methodName;    String className;    String validationMessage;    List<String> referenceFields;    public String getRuleName() {        return ruleName;    }    public void setRuleName(String ruleName) {        this.ruleName = ruleName;    }    public Integer getRuleId() {        return ruleId;    }    public void setRuleId(Integer ruleId) {        this.ruleId = ruleId;    }    public String getMethodName() {        return methodName;    }    public void setMethodName(String methodName) {        this.methodName = methodName;    }    public String getClassName() {        return className;    }    public void setClassName(String className) {        this.className = className;    }    public String getValidationMessage() {        return validationMessage;    }    public void setValidationMessage(String validationMessage) {        this.validationMessage = validationMessage;    }    public List<String> getReferenceFields() {        return referenceFields;    }    public void setReferenceFields(List<String> referenceFields) {        this.referenceFields = referenceFields;    }}Each Header column (Field id ) will be mapped to a rulepublic class RulesMapping extends BaseVO {    Integer fieldId;    String referenceField;    Integer ruleId;    public Integer sequenceNumber;    String ruleConfig;    public Integer getRuleId() {        return ruleId;    }    public void setRuleId(Integer ruleId) {        this.ruleId = ruleId;    }    public  Integer getSequenceNumber() {        return sequenceNumber;    }    public  void setSequenceNumber(Integer sequenceNumber) {        this.sequenceNumber = sequenceNumber;    }    public Integer getFieldId() {        return fieldId;    }    public void setFieldId(Integer fieldId) {        this.fieldId = fieldId;    }    public String getReferenceField() {        return referenceField;    }    public void setReferenceField(String referenceField) {        this.referenceField = referenceField;    }    public String getRuleConfig() {        return ruleConfig;    }    public void setRuleConfig(String ruleConfig) {        this.ruleConfig = ruleConfig;    }}Below is the code which will read the Excel file, validate and update the database and update excel with comments of validation messages and success.import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.lang.reflect.InvocationTargetException;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.TreeMap;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.RichTextString;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class BulkUploader {    private static final String BULKUPLOAD_UPDATE_RECORD = BULKUPLOAD_UPDATE_RECORD;    // This will contain module name plus header details    List<TemplateHeaderDetails>  details = new ArrayList<TemplateHeaderDetails>();    // template Name to header details(contians column name ,mandatory columns , sequence)    List<HeaderDetails> headerDetails =null;    // all key value pair mapping (  holding column name and its respective value per row.    Map<String,Object>  records =  new HashMap<String,Object>();    // field ID to rules mapping    Map<Integer,List<RulesMapping>>  rulesMapping =  new HashMap<Integer,List<RulesMapping>>();    // Rule id to Rule     Map<Integer,Rule>  rules =  new HashMap<Integer,Rule>();    Map<Integer,List<String>>  errorMessages =  new HashMap<Integer,List<String>>();    // List of procedure to be executed    List<SP>  procedureList = new ArrayList<SP>();    public void setUpData(){        Map<Integer,String>  spColumnNames =new  TreeMap<Integer,String>();        spColumnNames.put(1,BDATE);        spColumnNames.put(2,ACTION);        spColumnNames.put(3,BBULLETIN_ID);        spColumnNames.put(4,BBULLETIN_NO);        spColumnNames.put(5,DATATitle);        spColumnNames.put(6,DATAStatus);        spColumnNames.put(7,DATAIntroduction (Rewritten));        spColumnNames.put(8,Summary);        spColumnNames.put(9,Direct Questions To);        spColumnNames.put(10,Effective Start Date);        spColumnNames.put(11,Tags/Keywords);        spColumnNames.put(12,Created Date);        spColumnNames.put(13,Creator Name);        SP sp = new SP();        sp.setProcedureName(POC_BULK_UPLOAD);        sp.setColumnSeq(spColumnNames);        procedureList.add(sp);    }    @SuppressWarnings(unchecked)    public void readExcel() throws IOException, IllegalAccessException, InstantiationException, ClassNotFoundException, SecurityException, NoSuchMethodException, IllegalArgumentException, InvocationTargetException{        FileInputStream file = new FileInputStream(new File(C:\\Data.xlsx));        //Get the workbook instance for XLS file        XSSFWorkbook workbook = new XSSFWorkbook(file);        //Get first sheet from the workbook        XSSFSheet sheet = workbook.getSheetAt(0);        // GET the header row        Row headerRow = sheet.getRow(0);        // LIst of headers from excel        List<String> headers = new ArrayList<String>();        Iterator<Cell> cells = headerRow.cellIterator();        while (cells.hasNext()) {            Cell cell = (Cell) cells.next();            RichTextString value = cell.getRichStringCellValue();            headers.add(value.getString());        }        // validate the template         Object[] headerValidation = validateTempalte(headers );        // if validation fails then write back the message to user.        if((Boolean) headerValidation[0]==false){            List<String> headerValidationMsg = (List<String>) headerValidation[1];            appendHeaderComments(workbook,sheet,headerValidationMsg,headers);            return ;        }        // Assign a number to header.This is done so that when we reading excel value we can identiy the cell value belongs to which column         Map<String,Integer> headerSeqNumber = assignHeaderSeqNumber(headers);        //         //Get iterator to all the rows in current sheet        int rowNumber=0;        Iterator<Row> rowIterator = sheet.iterator();        while (rowIterator.hasNext()) {            Row row = (Row) rowIterator.next();            if(rowNumber==0){                rowNumber++;                continue;            }            System.out.println(Row no +rowNumber);            Iterator<String> columnsIterator = headerSeqNumber.keySet().iterator();            while (columnsIterator.hasNext()) {                String name = columnsIterator.next();                System.out.println(Read columnName +name);                int cellType=row.getCell(headerSeqNumber.get(name)).getCellType();                switch (cellType) {                case Cell.CELL_TYPE_STRING:                    RichTextString value= row.getCell(headerSeqNumber.get(name)).getRichStringCellValue();                    String val=value!=null ?Utils.replaceNonAsciiChar(value.getString().trim()):null;                    val=val.replaceAll(:, );                    val=val.replaceAll(', );                    records.put(name,val);                    break;                case Cell.CELL_TYPE_NUMERIC:                    if (DateUtil.isCellDateFormatted(row.getCell(headerSeqNumber.get(name)))) {                        Date date = row.getCell(headerSeqNumber.get(name)).getDateCellValue();                         records.put(name,Utils.convertDateToString(date, dd-MMM-yyyy));                    }else{                        double numericVal= row.getCell(headerSeqNumber.get(name)).getNumericCellValue();                        records.put(name,numericVal);                    }                    break;                case Cell.CELL_TYPE_BLANK:                    System.err.println( blank cell type );                    records.put(name,null);                    break;                default:                    System.err.println( NEither string no number +row.getCell(headerSeqNumber.get(name)).getCellType());                    System.err.println( value +row.getCell(headerSeqNumber.get(name)).getStringCellValue());                    break;                }            }            // once a row is read validate each cell             Object[]  validationResults=validateRecords(records);            Boolean isDataValid =(Boolean) validationResults[0];            List<String> vMessages =new ArrayList<String>();            // if data is valid then update the value in db.            if (isDataValid) {                System.out.println( Valid data sending for upating record);                String msg = executeProcedure(records);                vMessages.add(msg);            }else{                // if there is validation issues then add the messages to list.                if(validationResults[1]!=null){                    vMessages = (List<String>) validationResults[1];                }                System.err.println( data is invalid);            }            errorMessages.put(rowNumber, vMessages);            rowNumber++;        }        // append success or failue to excel.         appendComments(sheet,headers);         String filePath=C:\\Data.xlsx;         FileOutputStream fileOut = new FileOutputStream(filePath);         workbook.write(fileOut);         fileOut.flush();         fileOut.close();    }    @SuppressWarnings(unchecked)    private Object[] validateRecords(Map<String,Object> recordValues) throws IllegalAccessException, InstantiationException, ClassNotFoundException, SecurityException, NoSuchMethodException, IllegalArgumentException, InvocationTargetException{        Object[] values = new Object[2];        List<String>  vMessages = new ArrayList<String>();        Boolean isDataUploadedValid = true;        Iterator<TemplateHeaderDetails> columnsIterator = details.iterator();        while (columnsIterator.hasNext()) {            TemplateHeaderDetails headerValue = columnsIterator.next();            String columnName=headerValue.getHeaderNames();            Object value= recordValues.get(columnName);            //System.out.println( columnName +columnName);            int fieldId = headerValue.getFieldId();            List<RulesMapping> ruleList= rulesMapping.get(fieldId);            //System.out.println( ruleList +ruleList);            if(ruleList!=null){                for (RulesMapping rulesMapping : ruleList) {                     String configuration =rulesMapping.getRuleConfig();                     int sequence =rulesMapping.sequenceNumber;                     Integer ruleId = rulesMapping.getRuleId();                     String config = rulesMapping.getRuleConfig();                     Rule rule =  rules.get(ruleId);                     String className=rule.getClassName();                     String methodName=rule.getMethodName();                     String message = rule.getValidationMessage();                     List<String> referenceFields = rule.getReferenceFields();                     Object obj=Class.forName(className).newInstance();                     //Class<?>[] parameterTypes = Util.getParameterTypes( value,referenceFields,config,message,columnName );                     Class<?>[] parameterTypes = new Class<?>[5];                     parameterTypes[0]=Object.class;                     parameterTypes[1]=List.class;                     parameterTypes[2]=String.class;                     parameterTypes[3]=String.class;                     parameterTypes[4]=String.class;                     Object[]  validationResults= (Object[]) obj.getClass().getMethod( methodName,parameterTypes).invoke(obj, value,referenceFields,config,message,columnName);                     Boolean isValidData = (Boolean) validationResults[0];                     if(!isValidData){                         isDataUploadedValid=false;                         vMessages.addAll((List<String>) validationResults[1]);                     }                }               }        }        values[0]=isDataUploadedValid;        values[1]=vMessages;        return values;    }    // checks if headers from excel matches the headers for template from db based on mandaotry filds.    private Object[] validateTempalte(List<String> headers){        Object[] values=new Object[2];        Boolean isTempateProper=true;        List<String> errorMessages = new ArrayList<String>();        //List<HeaderDetails> headerDetails = (List<HeaderDetails>) details.getHeaderDetails();        for (TemplateHeaderDetails headerDetail : details) {            if(headerDetail.getHeaderNames().equalsIgnoreCase(ACTION) ||headerDetail.getHeaderNames().equalsIgnoreCase(DATAID)                    ||headerDetail.getHeaderNames().equalsIgnoreCase(DATASTATUS ID) ||headerDetail.getHeaderNames().equalsIgnoreCase(Department Name)                           ||headerDetail.getHeaderNames().equalsIgnoreCase(Status of Record) ||headerDetail.getHeaderNames().equalsIgnoreCase(Description of the Status)                    ||headerDetail.getHeaderNames().equalsIgnoreCase(Name of the Excel File) ||headerDetail.getHeaderNames().equalsIgnoreCase(Updated By)){                    continue;            }            if(headerDetail.getMandatory().equalsIgnoreCase(Y) && !headers.contains(headerDetail.getHeaderNames())){                isTempateProper=false;                errorMessages.add(headerDetail.getHeaderNames() +  column is missing );            }        }        values[0]=isTempateProper;        values[1]=errorMessages;        return values;    }    private Map<String,Integer> assignHeaderSeqNumber(List<String> headers){        Map<String,Integer> headerSeqNumber = new HashMap<String, Integer>();        int i=0;        for (String columnName : headers) {              headerSeqNumber.put(columnName, i);              i++;        }        return headerSeqNumber;    }    private String executeProcedure(Map<String, Object> recordValues) {        try {            for (SP procedure : procedureList) {                String query =  call  + procedure.getProcedureName() +  ( ;                Map<Integer, String> params = procedure.columnSeq;                Iterator<Integer> keys = params.keySet().iterator();                int i = 1;                while (keys.hasNext()) {                    Integer seq = (Integer) keys.next();                    if (params.containsKey(seq)) {                        String columnName = params.get(seq);                        System.out.println( columName used in SP                                 + columnName);                        if (columnName.equalsIgnoreCase(ACTION)) {                            query +=  'I' ;                        } else if (columnName.equalsIgnoreCase(BBULLETIN_ID)) {                            query +=  '1' ;                        } else if (columnName                                .equalsIgnoreCase(Department Name)) {                            query +=  'Compliance' ;                        } else {                            query +=  ' + recordValues.get(columnName) + ' ;                        }                    } else {                        query +=  null ;                    }                    if (i <= params.size() - 1) {                        query +=  ,;                    }                    i++;                }                query +=  ,? ,?);                ServiceCaller.invokeService(BULKUPLOAD_UPDATE_RECORD, query);                System.out.println( complete query  + query);            }            return SUCSSES;        } catch (Exception e) {            e.printStackTrace();            System.err.println( Errro while upadating record);            return FAILED;        }    }    private void appendComments(XSSFSheet sheet ,List<String> headers){        int rowNumber=0;        Iterator<Row> rowIterator = sheet.iterator();        while (rowIterator.hasNext()) {            Row row = (Row) rowIterator.next();            if(rowNumber==0){                Cell headerCell = row.createCell(headers.size());                headerCell.setCellValue(Comments);                }else{                List<String> vMessages = errorMessages.get(rowNumber);                StringBuilder builder = new StringBuilder();                for (String message : vMessages) {                    builder.append(message);                }                Cell headerCell = row.createCell(headers.size());                headerCell.setCellValue(builder.toString());                }            rowNumber++;        }    }    private void appendHeaderComments(XSSFWorkbook workbook ,XSSFSheet sheet ,List<String> messages,List<String> headers) throws IOException{        Row headerRow = sheet.getRow(0);        Cell headerCell = headerRow.createCell(headers.size());        headerCell.setCellValue(Comments);        Row firstRow = sheet.getRow(1);        Cell commentCell = firstRow.createCell(headers.size());        String finalMessage=null;        for (String msg : messages) {            finalMessage+=msg;        }        commentCell.setCellValue(finalMessage);         String filePath=C:\\Data.xlsx;         FileOutputStream fileOut = new FileOutputStream(filePath);         workbook.write(fileOut);         fileOut.flush();         fileOut.close();    }    //  commnets field for each , combine messages from JAVA,DB.    private void getRecrodStatus(){    }    public List<TemplateHeaderDetails> getDetails() {        return details;    }    public void setDetails(List<TemplateHeaderDetails> details) {        this.details = details;    }    public List<HeaderDetails> getHeaderDetails() {        return headerDetails;    }    public void setHeaderDetails(List<HeaderDetails> headerDetails) {        this.headerDetails = headerDetails;    }    public Map<String, Object> getRecords() {        return records;    }    public void setRecords(Map<String, Object> records) {        this.records = records;    }    public Map<Integer, List<RulesMapping>> getRulesMapping() {        return rulesMapping;    }    public void setRulesMapping(Map<Integer, List<RulesMapping>> rulesMapping) {        this.rulesMapping = rulesMapping;    }    public Map<Integer, Rule> getRules() {        return rules;    }    public void setRules(Map<Integer, Rule> rules) {        this.rules = rules;    }    public Map<Integer, List<String>> getErrorMessages() {        return errorMessages;    }    public void setErrorMessages(Map<Integer, List<String>> errorMessages) {        this.errorMessages = errorMessages;    }    public List<SP> getProcedureList() {        return procedureList;    }    public void setProcedureList(List<SP> procedureList) {        this.procedureList = procedureList;    }}Please review design and implementation.
Validate and import data from an Excel file
java;design patterns;validation;excel