Subversion Repositories SmartDukaan

package com.spice.profitmandi.common.util;

import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
import com.spice.profitmandi.common.model.ProfitMandiConstants;
import com.spice.profitmandi.common.model.TagListingModel;

public class ExcelUtils {
        private static final String TAG_ID = "Tag Id";
        private static final String TAG_LABEL = "Tag Label";
        private static final String ITEM_ID = "Item Id";
        private static final String BRAND = "Brand";
        private static final String MODEL_NAME = "Model Name";
        private static final String MODEL_NUMBER = "Model Number";
        private static final String COLOR = "Color";
        private static final String SELLING_PRICE = "Selling Price";
        private static final String MOP = "MOP";
        private static final String SUPPORT_PRICE = "Support Price";
        private static final String START_DATE = "Start Date";
        private static final String TAG_LISTING = "Tag Listing";
        
        private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
        
        public static void main(String[] args) throws Throwable{
                parse(null);
        }
        public static List<TagListingModel> parse(InputStream inputStream) throws ProfitMandiBusinessException {
                
                List<TagListingModel> tagListings = new ArrayList<>();
                XSSFWorkbook myWorkBook = null;
                try{
                        //FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
                        myWorkBook = new XSSFWorkbook (inputStream);
                        
                        myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
                        // Return first sheet from the XLSX workbook 
                        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
                        
                        for(int rowNumber = mySheet.getFirstRowNum(); rowNumber <= mySheet.getLastRowNum(); rowNumber++){
                                Row row = mySheet.getRow(rowNumber);
                                if(rowNumber == 0 && row != null){
                                        if(row.getPhysicalNumberOfCells() == 11){
                                                if(!(TAG_ID.equals(row.getCell(0).toString()) || row.getCell(0).getCellTypeEnum() != CellType.STRING)){
                                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1000");
                                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                        throw profitMandiBusinessException;
                                                }
                                                if(!(ITEM_ID.equals(row.getCell(2).toString()) || row.getCell(2).getCellTypeEnum() != CellType.STRING)){
                                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1002");
                                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                        throw profitMandiBusinessException;
                                                }
                                                if(!(SELLING_PRICE.equals(row.getCell(7).toString()) || row.getCell(7).getCellTypeEnum() != CellType.STRING)){
                                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7).toString(), "TGLSTNG_VE_1007");
                                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                        throw profitMandiBusinessException;
                                                }
                                                if(!(MOP.equals(row.getCell(8).toString()) || row.getCell(8).getCellTypeEnum() != CellType.STRING)){
                                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7).toString(), "TGLSTNG_VE_1007");
                                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                        throw profitMandiBusinessException;
                                                }
                                                if(!(SUPPORT_PRICE.equals(row.getCell(9).toString()) || row.getCell(9).getCellTypeEnum() != CellType.STRING)){
                                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(8).toString(), "TGLSTNG_VE_1008");
                                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                        throw profitMandiBusinessException;
                                                }
                                                if(!(START_DATE.equals(row.getCell(10).toString()) || row.getCell(10).getCellTypeEnum() != CellType.STRING)){
                                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(9).toString(), "TGLSTNG_VE_1009");
                                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                        throw profitMandiBusinessException;
                                                }
                                        }else{
                                                ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, row.getPhysicalNumberOfCells(), "TGLSTNG_VE_1011");
                                                LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                throw profitMandiBusinessException;
                                        }
                                        //Headers are OK. Lets break now and iterate through first row.
                                        break;
                                }else if(rowNumber == 0 && row == null){
                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, "", "TGLSTNG_VE_1012");
                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                        throw profitMandiBusinessException;
                                        // missing headers in first row
                                }
                                //LOGGER.info("rowNumber {}", rowNumber);
                                LOGGER.info("row {}", row);
                                //LOGGER.info("row {}", row.getLastCellNum());
                                if(rowNumber > 0 && row != null && row.getLastCellNum() == 11){
                                        TagListingModel tagListing = new TagListingModel();
                                        if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
                                                tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
                                        }else{
                                                ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
                                                LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                throw profitMandiBusinessException;
                                        }
                                        
                                        
                                        if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){
                                                tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
                                        }else{
                                                ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
                                                LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                throw profitMandiBusinessException;
                                        }

                                        if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){
                                                tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
                                        }else{
                                                ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
                                                LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                throw profitMandiBusinessException;
                                        }
                                        if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
                                                tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
                                        }else{
                                                ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");
                                                LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                throw profitMandiBusinessException;
                                        }
                                        if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){
                                                tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
                                        }else{
                                                ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
                                                LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                throw profitMandiBusinessException;
                                        }
                                        if(row.getCell(10) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(10))){
                                                Date date = row.getCell(10).getDateCellValue();
                                                LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
                                                tagListing.setStartDate(startDate);
                                        }else{
                                                ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
                                                LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                                throw  profitMandiBusinessException;
                                        }
                                        tagListings.add(tagListing);
                                } else {
                                        ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, "", "TGLSTNG_VE_1012");
                                        LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
                                        throw profitMandiBusinessException;
                                }
                                
                        }
                        myWorkBook.close();
                }catch(IOException ioException){
                        ioException.printStackTrace();
                        throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
                }finally{
                        if(myWorkBook != null){
                                try {
                                        myWorkBook.close();
                                } catch (IOException e) {
                                        // TODO Auto-generated catch block
                                        e.printStackTrace();
                                }
                        }
                }
                return tagListings;
        }
}