Subversion Repositories SmartDukaan

Rev

Rev 22228 | Rev 22230 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
21786 ashik.ali 1 
package com.spice.profitmandi.common.util;
2 
 
3 
import java.io.IOException;
4 
import java.io.InputStream;
5 
import java.time.LocalDateTime;
6 
import java.time.ZoneId;
7 
import java.util.ArrayList;
8 
import java.util.Date;
9 
import java.util.List;
10 
 
11 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
12 
import org.apache.poi.ss.usermodel.CellType;
13 
import org.apache.poi.ss.usermodel.Row;
14 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
15 
import org.apache.poi.xssf.usermodel.XSSFSheet;
16 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
22226 amit.gupta 17 
import org.slf4j.Logger;
18 
import org.slf4j.LoggerFactory;
21786 ashik.ali 19 
 
20 
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
21 
import com.spice.profitmandi.common.model.ProfitMandiConstants;
22 
import com.spice.profitmandi.common.model.TagListingModel;
23 
 
24 
public class ExcelUtils {
25 
	private static final String TAG_ID = "Tag Id";
26 
	private static final String TAG_LABEL = "Tag Label";
27 
	private static final String ITEM_ID = "Item Id";
28 
	private static final String BRAND = "Brand";
29 
	private static final String MODEL_NAME = "Model Name";
30 
	private static final String MODEL_NUMBER = "Model Number";
31 
	private static final String COLOR = "Color";
32 
	private static final String SELLING_PRICE = "Selling Price";
22204 amit.gupta 33 
	private static final String MOP = "MOP";
21786 ashik.ali 34 
	private static final String SUPPORT_PRICE = "Support Price";
35 
	private static final String START_DATE = "Start Date";
36 
	private static final String TAG_LISTING = "Tag Listing";
37 
 
22226 amit.gupta 38 
	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
21786 ashik.ali 39 
 
40 
	public static void main(String[] args) throws Throwable{
41 
		parse(null);
42 
	}
22229 amit.gupta 43 
	public static List<TagListingModel> parse(InputStream inputStream) throws ProfitMandiBusinessException {
21786 ashik.ali 44 
 
45 
		List<TagListingModel> tagListings = new ArrayList<>();
46 
		XSSFWorkbook myWorkBook = null;
47 
		try{
48 
			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
49 
			myWorkBook = new XSSFWorkbook (inputStream);
50 
 
51 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
52 
			// Return first sheet from the XLSX workbook
53 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
54 
 
55 
			for(int rowNumber = mySheet.getFirstRowNum(); rowNumber <= mySheet.getLastRowNum(); rowNumber++){
56 
				Row row = mySheet.getRow(rowNumber);
57 
				if(rowNumber == 0 && row != null){
22204 amit.gupta 58 
					if(row.getPhysicalNumberOfCells() == 11){
21786 ashik.ali 59 
						if(!(TAG_ID.equals(row.getCell(0).toString()) || row.getCell(0).getCellTypeEnum() != CellType.STRING)){
60 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1000");
61 
							//LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
62 
							throw profitMandiBusinessException;
63 
						}
64 
						if(!(ITEM_ID.equals(row.getCell(2).toString()) || row.getCell(2).getCellTypeEnum() != CellType.STRING)){
65 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1002");
66 
							//LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
67 
							throw profitMandiBusinessException;
68 
						}
22204 amit.gupta 69 
						if(!(SELLING_PRICE.equals(row.getCell(7).toString()) || row.getCell(7).getCellTypeEnum() != CellType.STRING)){
70 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7).toString(), "TGLSTNG_VE_1007");
21786 ashik.ali 71 
							//LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
72 
							throw profitMandiBusinessException;
73 
						}
22204 amit.gupta 74 
						if(!(MOP.equals(row.getCell(8).toString()) || row.getCell(8).getCellTypeEnum() != CellType.STRING)){
21786 ashik.ali 75 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7).toString(), "TGLSTNG_VE_1007");
76 
							//LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
77 
							throw profitMandiBusinessException;
78 
						}
22204 amit.gupta 79 
						if(!(SUPPORT_PRICE.equals(row.getCell(9).toString()) || row.getCell(9).getCellTypeEnum() != CellType.STRING)){
21786 ashik.ali 80 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(8).toString(), "TGLSTNG_VE_1008");
81 
							//LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
82 
							throw profitMandiBusinessException;
83 
						}
22204 amit.gupta 84 
						if(!(START_DATE.equals(row.getCell(10).toString()) || row.getCell(10).getCellTypeEnum() != CellType.STRING)){
21786 ashik.ali 85 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(9).toString(), "TGLSTNG_VE_1009");
86 
							//LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
87 
							throw profitMandiBusinessException;
88 
						}
89 
					}else{
90 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, row.getPhysicalNumberOfCells(), "TGLSTNG_VE_1011");
91 
						//LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
92 
						throw profitMandiBusinessException;
93 
					}
22228 amit.gupta 94 
					//Headers are OK. Lets break now and iterate through first row.
95 
					break;
21786 ashik.ali 96 
				}else if(rowNumber == 0 && row == null){
97 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, "", "TGLSTNG_VE_1012");
22226 amit.gupta 98 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 99 
					throw profitMandiBusinessException;
100 
					// missing headers in first row
101 
				}
22229 amit.gupta 102 
				//LOGGER.info("rowNumber {}", rowNumber);
22227 amit.gupta 103 
				LOGGER.info("row {}", row);
22229 amit.gupta 104 
				//LOGGER.info("row {}", row.getLastCellNum());
22208 amit.gupta 105 
				if(rowNumber > 0 && row != null && row.getLastCellNum() == 11){
21786 ashik.ali 106 
					TagListingModel tagListing = new TagListingModel();
107 
					if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
108 
						tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
109 
					}else{
110 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
22229 amit.gupta 111 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 112 
						throw profitMandiBusinessException;
113 
					}
114 
 
115 
 
116 
					if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){
117 
						tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
118 
					}else{
119 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
22229 amit.gupta 120 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 121 
						throw profitMandiBusinessException;
122 
					}
22204 amit.gupta 123 
 
21786 ashik.ali 124 
					if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){
125 
						tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
126 
					}else{
127 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
22229 amit.gupta 128 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 129 
						throw profitMandiBusinessException;
130 
					}
131 
					if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
22204 amit.gupta 132 
						tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
133 
					}else{
134 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(7), "TGLSTNG_VE_1010");
22229 amit.gupta 135 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
22204 amit.gupta 136 
						throw profitMandiBusinessException;
137 
					}
138 
					if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){
21786 ashik.ali 139 
						tagListing.setSupportPrice(Double.valueOf(row.getCell(8).toString()).floatValue());
140 
					}else{
141 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(8).toString(), "TGLSTNG_VE_1010");
22229 amit.gupta 142 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 143 
						throw profitMandiBusinessException;
144 
					}
22204 amit.gupta 145 
					if(row.getCell(10) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(10))){
146 
						Date date = row.getCell(10).getDateCellValue();
21786 ashik.ali 147 
						LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
148 
						tagListing.setStartDate(startDate);
149 
					}else{
150 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
22229 amit.gupta 151 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 152 
						throw  profitMandiBusinessException;
153 
					}
154 
					tagListings.add(tagListing);
22209 amit.gupta 155 
				} else {
156 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, "", "TGLSTNG_VE_1012");
22226 amit.gupta 157 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
22209 amit.gupta 158 
					throw profitMandiBusinessException;
21786 ashik.ali 159 
				}
160 
 
161 
			}
162 
			myWorkBook.close();
163 
		}catch(IOException ioException){
22206 amit.gupta 164 
			ioException.printStackTrace();
21786 ashik.ali 165 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
166 
		}finally{
167 
			if(myWorkBook != null){
168 
				try {
169 
					myWorkBook.close();
170 
				} catch (IOException e) {
171 
					// TODO Auto-generated catch block
172 
					e.printStackTrace();
173 
				}
174 
			}
175 
		}
176 
		return tagListings;
177 
	}
178 
}