Subversion Repositories SmartDukaan

Rev

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