Subversion Repositories SmartDukaan

Rev

Rev 22231 | Rev 22470 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 22231 Rev 22247
Line 7... Line 7...
7 
import java.util.ArrayList;
 7 
import java.util.ArrayList;
8 
import java.util.Date;
 8 
import java.util.Date;
9 
import java.util.List;
 9 
import java.util.List;
10 
 
 10 
 
11 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 11 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
- 
 
 12 
import org.apache.poi.sl.usermodel.Sheet;
12 
import org.apache.poi.ss.usermodel.CellType;
 13 
import org.apache.poi.ss.usermodel.CellType;
13 
import org.apache.poi.ss.usermodel.Row;
 - 
 
14 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
 14 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
- 
 
 15 
import org.apache.poi.xssf.usermodel.XSSFRow;
15 
import org.apache.poi.xssf.usermodel.XSSFSheet;
 16 
import org.apache.poi.xssf.usermodel.XSSFSheet;
16 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 17 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
17 
import org.slf4j.Logger;
 18 
import org.slf4j.Logger;
18 
import org.slf4j.LoggerFactory;
 19 
import org.slf4j.LoggerFactory;
19 
 
 20 
 
Line 38... Line 39...
38 
	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
 39 
	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
39
40
40 
	public static void main(String[] args) throws Throwable{
 41 
	public static void main(String[] args) throws Throwable{
41 
		parse(null);
 42 
		parse(null);
42 
	}
 43 
	}
43 
	public static List<TagListingModel> parse(InputStream inputStream) throws ProfitMandiBusinessException {
 44 
	public static List<TagListingModel> parse(InputStream inputStream) throws Throwable {
44
45
45 
		List<TagListingModel> tagListings = new ArrayList<>();
 46 
		List<TagListingModel> tagListings = new ArrayList<>();
46 
		XSSFWorkbook myWorkBook = null;
 47 
		XSSFWorkbook myWorkBook = null;
47 
		try{
 48 
		try{
48 
			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
 49 
			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
49 
			myWorkBook = new XSSFWorkbook (inputStream);
 50 
			myWorkBook = new XSSFWorkbook (inputStream);
50
51
51 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
 52 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
52 
			// Return first sheet from the XLSX workbook
53 
			// Return first sheet from the XLSX workbook
53 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
 54 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
- 
 
 55 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
54
56
55 
			for(int rowNumber = mySheet.getFirstRowNum(); rowNumber <= mySheet.getLastRowNum(); rowNumber++){
 57 
			for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){
56 
				Row row = mySheet.getRow(rowNumber);
 58 
				XSSFRow row = mySheet.getRow(rowNumber);
57 
				if(rowNumber == 0 && row != null){
 59 
				LOGGER.info("row {}", row);
58 
					if(row.getPhysicalNumberOfCells() == 11){
 60 
				TagListingModel tagListing = new TagListingModel();
59 
						if(!(TAG_ID.equals(row.getCell(0).toString()) || row.getCell(0).getCellTypeEnum() != CellType.STRING)){
 61 
				if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
60 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1000");
 - 
 
61 
							LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 62 
					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
62 
							throw profitMandiBusinessException;
 - 
 
63 
						}
 63 
				}else{
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");
 64 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
66 
							LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 65 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
67 
							throw profitMandiBusinessException;
 66 
					throw profitMandiBusinessException;
68 
						}
 67 
				}
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");
 - 
 
71 
							LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
72 
							throw profitMandiBusinessException;
 - 
 
73 
						}
 68
74 
						if(!(MOP.equals(row.getCell(8).toString()) || row.getCell(8).getCellTypeEnum() != CellType.STRING)){
 69 
				if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){
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 
						}
 - 
 
79 
						if(!(SUPPORT_PRICE.equals(row.getCell(9).toString()) || row.getCell(9).getCellTypeEnum() != CellType.STRING)){
 - 
 
80 
							ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(8).toString(), "TGLSTNG_VE_1008");
 - 
 
81 
							LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 70 
					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
82 
							throw profitMandiBusinessException;
 - 
 
83 
						}
 - 
 
84 
						if(!(START_DATE.equals(row.getCell(10).toString()) || row.getCell(10).getCellTypeEnum() != CellType.STRING)){
 - 
 
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{
 71 
				}else{
90 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, row.getPhysicalNumberOfCells(), "TGLSTNG_VE_1011");
 72 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
91 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
92 
						throw profitMandiBusinessException;
 - 
 
93 
					}
 - 
 
94 
					//Headers are OK. Lets break now and iterate through first row.
 - 
 
95 
					break;
 - 
 
96 
				}else if(rowNumber == 0 && row == null){
 - 
 
97 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, "", "TGLSTNG_VE_1012");
 - 
 
98 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 73 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
99 
					throw profitMandiBusinessException;
 74 
					throw profitMandiBusinessException;
100 
					// missing headers in first row
 - 
 
101 
				}
 75 
				}
102 
				//LOGGER.info("rowNumber {}", rowNumber);
 - 
 
103 
				LOGGER.info("row {}", row);
 - 
 
104 
				//LOGGER.info("row {}", row.getLastCellNum());
 - 
 
105 
				if(rowNumber > 0 && row != null && row.getLastCellNum() == 11){
 - 
 
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");
 - 
 
111 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
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");
 - 
 
120 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
121 
						throw profitMandiBusinessException;
 - 
 
122 
					}
 - 
 
123 
 
 76 
 
124 
					if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){
 77 
				if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){
125 
						tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
 78 
					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
126 
					}else{
 79 
				}else{
127 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
 80 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
128 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
129 
						throw profitMandiBusinessException;
 - 
 
130 
					}
 - 
 
131 
					if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
 - 
 
132 
						tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
 - 
 
133 
					}else{
 - 
 
134 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");
 - 
 
135 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
136 
						throw profitMandiBusinessException;
 - 
 
137 
					}
 - 
 
138 
					if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){
 - 
 
139 
						tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
 - 
 
140 
					}else{
 - 
 
141 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
 - 
 
142 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
143 
						throw profitMandiBusinessException;
 - 
 
144 
					}
 - 
 
145 
					if(row.getCell(10) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(10))){
 - 
 
146 
						Date date = row.getCell(10).getDateCellValue();
 - 
 
147 
						LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
 - 
 
148 
						tagListing.setStartDate(startDate);
 - 
 
149 
					}else{
 - 
 
150 
						ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
 - 
 
151 
						LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 - 
 
152 
						throw  profitMandiBusinessException;
 - 
 
153 
					}
 - 
 
154 
					tagListings.add(tagListing);
 - 
 
155 
				} else {
 - 
 
156 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_LISTING, "", "TGLSTNG_VE_1012");
 - 
 
157 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
 81 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
158 
					throw profitMandiBusinessException;
 82 
					throw profitMandiBusinessException;
159 
				}
 83 
				}
- 
 
 84 
				if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
- 
 
 85 
					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
- 
 
 86 
				}else{
- 
 
 87 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");
- 
 
 88 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
- 
 
 89 
					throw profitMandiBusinessException;
160
90 
				}
- 
 
 91 
				if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){
- 
 
 92 
					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
- 
 
 93 
				}else{
- 
 
 94 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
- 
 
 95 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
- 
 
 96 
					throw profitMandiBusinessException;
- 
 
 97 
				}
- 
 
 98 
				if(row.getCell(10) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(10))){
- 
 
 99 
					Date date = row.getCell(10).getDateCellValue();
- 
 
 100 
					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
- 
 
 101 
					tagListing.setStartDate(startDate);
- 
 
 102 
				}else{
- 
 
 103 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
- 
 
 104 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
- 
 
 105 
					throw  profitMandiBusinessException;
- 
 
 106 
				}
- 
 
 107 
				tagListings.add(tagListing);
161 
			}
 108 
			}
162 
			myWorkBook.close();
 109 
			myWorkBook.close();
163 
		}catch(IOException ioException){
 110 
		} catch(IOException ioException){
164 
			ioException.printStackTrace();
 111 
			ioException.printStackTrace();
165 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
 112 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
166 
		}finally{
 113 
		} finally {
167 
			if(myWorkBook != null){
 114 
			if(myWorkBook != null){
168 
				try {
 115 
				try {
169 
					myWorkBook.close();
 116 
					myWorkBook.close();
170 
				} catch (IOException e) {
 117 
				} catch (IOException e) {
171 
					// TODO Auto-generated catch block
 118 
					// TODO Auto-generated catch block