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.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.xssf.usermodel.XSSFRow;
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 Throwable {
                
                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);
                        LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
                        
                        for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){
                                XSSFRow row = mySheet.getRow(rowNumber);
                                LOGGER.info("row {}", row);
                                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);
                        }
                        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;
        }
}