Subversion Repositories SmartDukaan

Rev

Rev 23568 | Rev 24052 | 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;
22470 ashik.ali 5 
import java.io.OutputStream;
21786 ashik.ali 6 
import java.time.LocalDateTime;
7 
import java.time.ZoneId;
8 
import java.util.ArrayList;
9 
import java.util.Date;
23819 govind 10 
import java.util.Iterator;
11 
import java.util.LinkedHashMap;
21786 ashik.ali 12 
import java.util.List;
23819 govind 13 
import java.util.Map;
14 
import java.util.Map.Entry;
15 
import java.util.Set;
21786 ashik.ali 16 
 
17 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
22470 ashik.ali 18 
import org.apache.poi.ss.usermodel.Cell;
19 
import org.apache.poi.ss.usermodel.CellStyle;
21786 ashik.ali 20 
import org.apache.poi.ss.usermodel.CellType;
22470 ashik.ali 21 
import org.apache.poi.ss.usermodel.Font;
22521 ashik.ali 22 
import org.apache.poi.ss.usermodel.HorizontalAlignment;
22470 ashik.ali 23 
import org.apache.poi.ss.usermodel.Row;
21786 ashik.ali 24 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
22470 ashik.ali 25 
import org.apache.poi.ss.util.CellRangeAddress;
26 
import org.apache.poi.xssf.streaming.SXSSFSheet;
27 
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
22247 amit.gupta 28 
import org.apache.poi.xssf.usermodel.XSSFRow;
21786 ashik.ali 29 
import org.apache.poi.xssf.usermodel.XSSFSheet;
30 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
23568 govind 31 
import org.apache.logging.log4j.Logger;
32 
import org.apache.logging.log4j.LogManager;
21786 ashik.ali 33 
 
34 
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
22470 ashik.ali 35 
import com.spice.profitmandi.common.model.InventoryItemAgingModel;
36 
import com.spice.profitmandi.common.model.InventoryItemAgingValue;
22521 ashik.ali 37 
import com.spice.profitmandi.common.model.ItemCompleteLedgerModel;
21786 ashik.ali 38 
import com.spice.profitmandi.common.model.ProfitMandiConstants;
23017 ashik.ali 39 
import com.spice.profitmandi.common.model.SchemeModel;
21786 ashik.ali 40 
import com.spice.profitmandi.common.model.TagListingModel;
41 
 
42 
public class ExcelUtils {
43 
	private static final String TAG_ID = "Tag Id";
44 
	private static final String TAG_LABEL = "Tag Label";
45 
	private static final String ITEM_ID = "Item Id";
46 
	private static final String BRAND = "Brand";
47 
	private static final String MODEL_NAME = "Model Name";
48 
	private static final String MODEL_NUMBER = "Model Number";
49 
	private static final String COLOR = "Color";
50 
	private static final String SELLING_PRICE = "Selling Price";
22204 amit.gupta 51 
	private static final String MOP = "MOP";
21786 ashik.ali 52 
	private static final String SUPPORT_PRICE = "Support Price";
53 
	private static final String START_DATE = "Start Date";
54 
	private static final String TAG_LISTING = "Tag Listing";
55 
 
23568 govind 56 
	private static final Logger LOGGER = LogManager.getLogger(ExcelUtils.class);
21786 ashik.ali 57 
 
58 
	public static void main(String[] args) throws Throwable{
22470 ashik.ali 59 
		//List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
60 
		//writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
21786 ashik.ali 61 
	}
22924 ashik.ali 62 
	public static List<TagListingModel> parse(InputStream inputStream) throws Exception {
21786 ashik.ali 63 
 
64 
		List<TagListingModel> tagListings = new ArrayList<>();
65 
		XSSFWorkbook myWorkBook = null;
66 
		try{
67 
			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
68 
			myWorkBook = new XSSFWorkbook (inputStream);
69 
 
70 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
71 
			// Return first sheet from the XLSX workbook
72 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
22247 amit.gupta 73 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
21786 ashik.ali 74 
 
22247 amit.gupta 75 
			for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){
76 
				XSSFRow row = mySheet.getRow(rowNumber);
77 
				LOGGER.info("row {}", row);
78 
				TagListingModel tagListing = new TagListingModel();
79 
				if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
80 
					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
81 
				}else{
82 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
22226 amit.gupta 83 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 84 
					throw profitMandiBusinessException;
85 
				}
22247 amit.gupta 86 
 
87 
				if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){
88 
					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
89 
				}else{
90 
					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 
				}
22204 amit.gupta 94 
 
22247 amit.gupta 95 
				if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){
96 
					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
97 
				}else{
98 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
22226 amit.gupta 99 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
22209 amit.gupta 100 
					throw profitMandiBusinessException;
21786 ashik.ali 101 
				}
22247 amit.gupta 102 
				if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
103 
					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
104 
				}else{
105 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");
106 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
107 
					throw profitMandiBusinessException;
108 
				}
109 
				if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){
110 
					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
111 
				}else{
112 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
113 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
114 
					throw profitMandiBusinessException;
115 
				}
22563 amit.gupta 116 
				if(row.getCell(10) != null && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC){
117 
					tagListing.setMaxDiscountPrice(Double.valueOf(row.getCell(10).toString()).floatValue());
118 
				}else{
119 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
120 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
121 
					throw profitMandiBusinessException;
122 
				}
123 
				if(row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))){
22865 amit.gupta 124 
					Date date = row.getCell(11).getDateCellValue();
22247 amit.gupta 125 
					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
126 
					tagListing.setStartDate(startDate);
127 
				}else{
22865 amit.gupta 128 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");
22247 amit.gupta 129 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
130 
					throw  profitMandiBusinessException;
131 
				}
132 
				tagListings.add(tagListing);
21786 ashik.ali 133 
			}
134 
			myWorkBook.close();
22247 amit.gupta 135 
		} catch(IOException ioException){
22206 amit.gupta 136 
			ioException.printStackTrace();
21786 ashik.ali 137 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
22247 amit.gupta 138 
		} finally {
21786 ashik.ali 139 
			if(myWorkBook != null){
140 
				try {
141 
					myWorkBook.close();
142 
				} catch (IOException e) {
143 
					// TODO Auto-generated catch block
144 
					e.printStackTrace();
145 
				}
146 
			}
147 
		}
148 
		return tagListings;
149 
	}
22470 ashik.ali 150 
 
22521 ashik.ali 151 
	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){
22470 ashik.ali 152 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
153 
 
154 
		//CreationHelper createHelper = workbook.getCreationHelper();
155 
 
156 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
157 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
158 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
159 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
160 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
161 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
162 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
163 
		sheet.trackAllColumnsForAutoSizing();
164 
 
165 
 
166 
	    Row rowHeader = sheet.createRow(0);
167 
	    Cell cellItemIdHeader = rowHeader.createCell(0);
168 
		cellItemIdHeader.setCellValue("Item Id");
169 
		Cell cellBrandHeader = rowHeader.createCell(1);
170 
		cellBrandHeader.setCellValue("Brand");
171 
		Cell cellModelNameHeader = rowHeader.createCell(2);
172 
		cellModelNameHeader.setCellValue("Model Name");
173 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
174 
		cellModelNumberHeader.setCellValue("Model Number");
175 
		Cell cellColorHeader = rowHeader.createCell(4);
176 
		cellColorHeader.setCellValue("Color");
177 
		Cell cellTypeHeader = rowHeader.createCell(5);
178 
		cellTypeHeader.setCellValue("Item Type");
179 
		Row rowPriceQuantity = sheet.createRow(1);
180 
		for(int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2){
181 
			Cell cellHeader = rowHeader.createCell(colIndex);
182 
			if(index == 0){
183 
				cellHeader.setCellValue("Less Than "+intervals.get(index)+" Days");
184 
			}else if(index < intervals.size()){
185 
				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
186 
			}else{
187 
				cellHeader.setCellValue("More Than "+intervals.get(index - 1)+" Days");
188 
			}
189 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + 1));
190 
			rowHeader.createCell(colIndex + 1);
191 
			Cell cellPrice = rowPriceQuantity.createCell(colIndex);
192 
			cellPrice.setCellValue("Price");
193 
			Cell cellQuantity = rowPriceQuantity.createCell(colIndex + 1);
194 
			cellQuantity.setCellValue("Quantity");
195 
		}
196 
		Font font = workbook.createFont();
197 
		CellStyle cellStyle = workbook.createCellStyle();
198 
		font.setBold(true);
199 
		//font.setFontHeight((short)16);
200 
		cellStyle.setFont(font);
22486 ashik.ali 201 
		for(int i = 0; i < 8 + (intervals.size() * 2); i++){
22470 ashik.ali 202 
			rowHeader.getCell(i).setCellStyle(cellStyle);
203 
			if(rowPriceQuantity.getCell(i) != null){
204 
				rowPriceQuantity.getCell(i).setCellStyle(cellStyle);
205 
			}
206 
		}
207 
 
208 
		for(int index = 0; index < inventoryItemAgingModels.size(); index++){
209 
			InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);
210 
	    	Row rowValues = sheet.createRow(index + 2);
211 
	    	Cell cellItemId = rowValues.createCell(0);
212 
	    	cellItemId.setCellValue(inventoryItemAgingModel.getItemId());
213 
	    	Cell cellBrand = rowValues.createCell(1);
214 
	    	cellBrand.setCellValue(inventoryItemAgingModel.getBrand());
215 
	    	Cell cellModelName = rowValues.createCell(2);
216 
	    	cellModelName.setCellValue(inventoryItemAgingModel.getModelName());
217 
	    	Cell cellModelNumber = rowValues.createCell(3);
218 
	    	cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());
219 
	    	Cell cellColor = rowValues.createCell(4);
220 
	    	cellColor.setCellValue(inventoryItemAgingModel.getColor());
221 
	    	Cell cellType = rowValues.createCell(5);
222 
	    	cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());
223 
	    	List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
224 
	    	//LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
225 
	    	for(int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2){
226 
	    		Cell cellPrice = rowValues.createCell(colIndex);
227 
	    		InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);
228 
	    		//LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);
229 
	    		Cell cellQuantity = rowValues.createCell(colIndex + 1);
230 
	    		if(inventoryItemAgingValue != null){
231 
	    			cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
232 
	    			cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
233 
	    		}else{
22521 ashik.ali 234 
	    			cellPrice.setCellValue("-");
235 
	    			cellQuantity.setCellValue("-");
22470 ashik.ali 236 
	    		}
237 
	    	}
238 
	    }
239 
 
22486 ashik.ali 240 
		for(int index = 0; index < 8 + (intervals.size() * 2); index++){
22470 ashik.ali 241 
			sheet.autoSizeColumn(index);
242 
		}
243 
 
244 
	    try{
22486 ashik.ali 245 
			workbook.write(outputStream);
22470 ashik.ali 246 
	    	workbook.close();
247 
	    }catch(IOException ioException){
22486 ashik.ali 248 
	    	LOGGER.error("Unable to generate excel file", ioException);
22470 ashik.ali 249 
	    }
250 
	}
251 
 
22521 ashik.ali 252 
	public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels, OutputStream outputStream){
253 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
254 
 
255 
		//CreationHelper createHelper = workbook.getCreationHelper();
256 
 
257 
		SXSSFSheet sheet = workbook.createSheet("ItemCompleteLeger");
258 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
259 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
260 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
261 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
262 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
263 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
264 
		sheet.trackAllColumnsForAutoSizing();
265 
 
266 
 
267 
	    Row rowHeader = sheet.createRow(0);
268 
	    Cell cellItemIdHeader = rowHeader.createCell(0);
269 
		cellItemIdHeader.setCellValue("Item Id");
270 
		Cell cellBrandHeader = rowHeader.createCell(1);
271 
		cellBrandHeader.setCellValue("Brand");
272 
		Cell cellModelNameHeader = rowHeader.createCell(2);
273 
		cellModelNameHeader.setCellValue("Model Name");
274 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
275 
		cellModelNumberHeader.setCellValue("Model Number");
276 
		Cell cellColorHeader = rowHeader.createCell(4);
277 
		cellColorHeader.setCellValue("Color");
278 
		Cell cellTypeHeader = rowHeader.createCell(5);
279 
		cellTypeHeader.setCellValue("Item Type");
280 
		Cell cellOpeningBalanceHeader = rowHeader.createCell(6);
281 
		cellOpeningBalanceHeader.setCellValue("Opening Balance");
282 
		Cell cellInwardsHeader = rowHeader.createCell(9);
283 
		cellInwardsHeader.setCellValue("Inwards");
284 
		Cell cellOutwardsHeader = rowHeader.createCell(12);
285 
		cellOutwardsHeader.setCellValue("Outwards");
286 
		Cell cellClosingBalanceHeader = rowHeader.createCell(15);
287 
		cellClosingBalanceHeader.setCellValue("Closing Balance");
288 
		Row rowQuantityRateValue = sheet.createRow(1);
289 
 
290 
		for(int index = 6; index < 18; index = index + 3){
291 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
292 
			cellQuantityHeader.setCellValue("Quantity");
293 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
294 
			cellRateHeader.setCellValue("Rate");
295 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
296 
			cellValueHeader.setCellValue("Value");
297 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
298 
		}
299 
		Font font = workbook.createFont();
300 
		CellStyle cellStyle = workbook.createCellStyle();
301 
		font.setBold(true);
302 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
303 
		//font.setFontHeight((short)16);
304 
		cellStyle.setFont(font);
305 
		for(int i = 0; i < 18; i++){
306 
			if(rowHeader.getCell(i) != null){
307 
				rowHeader.getCell(i).setCellStyle(cellStyle);
308 
			}
309 
		}
310 
		int openingQuantityTotal = 0;
311 
		float openingValueTotal = 0;
312 
		int inwardsQuantityTotal = 0;
313 
		float inwardsValueTotal = 0;
314 
		int outwardsQuantityTotal = 0;
315 
		float outwardsValueTotal = 0;
316 
		int closingQuantityTotal = 0;
317 
		float closingValueTotal = 0;
318 
		for(int index = 0; index < itemCompleteLedgerModels.size(); index++){
319 
			ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
320 
	    	Row rowValues = sheet.createRow(index + 2);
321 
	    	Cell cellItemId = rowValues.createCell(0);
322 
	    	cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
323 
	    	Cell cellBrand = rowValues.createCell(1);
324 
	    	cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
325 
	    	Cell cellModelName = rowValues.createCell(2);
326 
	    	cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
327 
	    	Cell cellModelNumber = rowValues.createCell(3);
328 
	    	cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
329 
	    	Cell cellColor = rowValues.createCell(4);
330 
	    	cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
331 
	    	Cell cellType = rowValues.createCell(5);
332 
	    	cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
333 
	    	Cell cellOpeningQuantity = rowValues.createCell(6);
334 
	    	Cell cellOpeningRate = rowValues.createCell(7);
335 
	    	Cell cellOpeningValue = rowValues.createCell(8);
336 
	    	if(itemCompleteLedgerModel.getOpeningLedger() == null){
337 
	    		cellOpeningQuantity.setCellValue("-");
338 
	    		cellOpeningRate.setCellValue("-");
339 
	    		cellOpeningValue.setCellValue("-");
340 
	    	}else{
341 
	    		cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
342 
	    		cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
343 
	    		cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
344 
	    		openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();
345 
	    		openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
346 
	    	}
347 
	    	Cell cellInwardsQuantity = rowValues.createCell(9);
348 
	    	Cell cellInwardsRate = rowValues.createCell(10);
349 
	    	Cell cellInwardsValue = rowValues.createCell(11);
350 
	    	if(itemCompleteLedgerModel.getInwardsLedger() == null){
351 
	    		cellInwardsQuantity.setCellValue("-");
352 
	    		cellInwardsRate.setCellValue("-");
353 
	    		cellInwardsValue.setCellValue("-");
354 
	    	}else{
355 
	    		cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
356 
	    		cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
357 
	    		cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
358 
	    		inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();
359 
	    		inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
360 
	    	}
361 
	    	Cell cellOutwardsQuantity = rowValues.createCell(12);
362 
	    	Cell cellOutwardsRate = rowValues.createCell(13);
363 
	    	Cell cellOutwardsValue = rowValues.createCell(14);
364 
	    	if(itemCompleteLedgerModel.getOutwardsLedger() == null){
365 
	    		cellOutwardsQuantity.setCellValue("-");
366 
	    		cellOutwardsRate.setCellValue("-");
367 
	    		cellOutwardsValue.setCellValue("-");
368 
	    	}else{
369 
	    		cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
370 
	    		cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
371 
	    		cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
372 
	    		outwardsQuantityTotal = outwardsQuantityTotal + itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
373 
	    		outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
374 
	    	}
375 
	    	Cell cellClosingQuantity = rowValues.createCell(15);
376 
	    	Cell cellClosingRate = rowValues.createCell(16);
377 
	    	Cell cellClosingValue = rowValues.createCell(17);
378 
	    	if(itemCompleteLedgerModel.getClosingLedger() == null){
379 
	    		cellClosingQuantity.setCellValue("-");
380 
	    		cellClosingRate.setCellValue("-");
381 
	    		cellClosingValue.setCellValue("-");
382 
	    	}else{
383 
	    		cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
384 
	    		cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
385 
	    		cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
386 
	    		closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();
387 
	    		closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
388 
	    	}
389 
	    }
390 
 
391 
		Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
392 
 
393 
		if(openingQuantityTotal > 0){
394 
			Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
395 
			cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
396 
			Cell cellOpeningValueTotal = rowTotal.createCell(8);
397 
			cellOpeningValueTotal.setCellValue(openingValueTotal);
398 
		}
399 
 
400 
		if(inwardsQuantityTotal > 0){
401 
			Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
402 
			cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
403 
			Cell cellInwardsValueTotal = rowTotal.createCell(11);
404 
			cellInwardsValueTotal.setCellValue(inwardsValueTotal);
405 
		}
406 
 
407 
		if(outwardsQuantityTotal > 0){
408 
			Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
409 
			cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
410 
			Cell cellOutwardsValueTotal = rowTotal.createCell(14);
411 
			cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
412 
		}
413 
 
414 
		if(closingQuantityTotal > 0){
415 
			Cell cellClosingQuantityTotal = rowTotal.createCell(15);
416 
			cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
417 
			Cell cellClosingValueTotal = rowTotal.createCell(17);
418 
			cellClosingValueTotal.setCellValue(closingValueTotal);
419 
		}
420 
 
421 
		for(int index = 0; index < 18; index++){
422 
			sheet.autoSizeColumn(index);
423 
		}
424 
 
425 
	    try{
426 
			workbook.write(outputStream);
427 
	    	workbook.close();
428 
	    }catch(IOException ioException){
429 
	    	LOGGER.error("Unable to generate excel file", ioException);
430 
	    }
431 
	}
23017 ashik.ali 432 
 
433 
	public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream){
434 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
435 
 
436 
		//CreationHelper createHelper = workbook.getCreationHelper();
437 
 
438 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
439 
		sheet.trackAllColumnsForAutoSizing();
440 
 
441 
 
442 
	    Row rowHeader = sheet.createRow(0);
443 
	    Cell cellSchemeIdHeader = rowHeader.createCell(0);
444 
		cellSchemeIdHeader.setCellValue("Scheme Id");
445 
		Cell cellNameHeader = rowHeader.createCell(1);
446 
		cellNameHeader.setCellValue("Name");
447 
		Cell cellDescriptionHeader = rowHeader.createCell(2);
448 
		cellDescriptionHeader.setCellValue("Description");
449 
		Cell cellSchemeTypeHeader = rowHeader.createCell(3);
450 
		cellSchemeTypeHeader.setCellValue("Scheme Type");
451 
		Cell cellAmountTypeHeader = rowHeader.createCell(4);
452 
		cellAmountTypeHeader.setCellValue("Amount Type");
453 
		Cell cellAmountHeader = rowHeader.createCell(5);
454 
		cellAmountHeader.setCellValue("Amount");
455 
		Cell cellStartDateTimeHeader = rowHeader.createCell(6);
456 
		cellStartDateTimeHeader.setCellValue("Start Date Time");
457 
		Cell cellEndDateTimeHeader = rowHeader.createCell(7);
458 
		cellEndDateTimeHeader.setCellValue("End Date Time");
459 
		Cell cellCreatedAtHeader = rowHeader.createCell(8);
460 
		cellCreatedAtHeader.setCellValue("Created At");
461 
		Cell cellActiveHeader = rowHeader.createCell(9);
462 
		cellActiveHeader.setCellValue("Active");
463 
		Cell cellExpireHeader = rowHeader.createCell(10);
464 
		cellExpireHeader.setCellValue("Expire");
465 
		Cell cellCreatedByHeader = rowHeader.createCell(11);
466 
		cellCreatedByHeader.setCellValue("Created By");
467 
		Cell cellItemIdsHeader = rowHeader.createCell(12);
468 
		cellItemIdsHeader.setCellValue("Item Ids");
469 
		Cell cellRetailerIdsHeader = rowHeader.createCell(13);
470 
		cellRetailerIdsHeader.setCellValue("Retailer Ids");
471 
		//Row rowQuantityRateValue = sheet.createRow(1);
472 
 
473 
		/*for(int index = 6; index < 18; index = index + 3){
474 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
475 
			cellQuantityHeader.setCellValue("Quantity");
476 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
477 
			cellRateHeader.setCellValue("Rate");
478 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
479 
			cellValueHeader.setCellValue("Value");
480 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
481 
		}*/
482 
		Font font = workbook.createFont();
483 
		CellStyle cellStyle = workbook.createCellStyle();
484 
		font.setBold(true);
485 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
486 
		//font.setFontHeight((short)16);
487 
		cellStyle.setFont(font);
488 
		for(int i = 0; i < 14; i++){
489 
			if(rowHeader.getCell(i) != null){
490 
				rowHeader.getCell(i).setCellStyle(cellStyle);
491 
			}
492 
		}
493 
 
494 
		for(int index = 0; index < schemeModels.size(); index++){
495 
			SchemeModel schemeModel = schemeModels.get(index);
496 
			//ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
497 
	    	Row rowValues = sheet.createRow(index + 1);
498 
	    	Cell cellSchemeId = rowValues.createCell(0);
499 
			cellSchemeId.setCellValue(schemeModel.getSchemeId());
500 
			Cell cellName = rowValues.createCell(1);
501 
			cellName.setCellValue(schemeModel.getName());
502 
			Cell cellDescription = rowValues.createCell(2);
503 
			cellDescription.setCellValue(schemeModel.getDescription());
504 
			Cell cellSchemeType = rowValues.createCell(3);
505 
			cellSchemeType.setCellValue(schemeModel.getSchemeType());
506 
			Cell cellAmountType = rowValues.createCell(4);
507 
			cellAmountType.setCellValue(schemeModel.getAmountType());
508 
			Cell cellAmount = rowValues.createCell(5);
509 
			cellAmount.setCellValue(schemeModel.getAmount());
510 
			Cell cellStartDateTime = rowValues.createCell(6);
511 
			cellStartDateTime.setCellValue(schemeModel.getStartDateTime());
512 
			Cell cellEndDateTime = rowValues.createCell(7);
513 
			cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
514 
			Cell cellCreatedAt = rowValues.createCell(8);
515 
			cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
516 
			Cell cellActive = rowValues.createCell(9);
517 
			if(schemeModel.getActiveTimestamp() !=null){
518 
				cellActive.setCellValue(schemeModel.getActiveTimestamp());
519 
			}else{
520 
				cellActive.setCellValue("False");
521 
			}
522 
			Cell cellExpire = rowValues.createCell(10);
523 
			if(schemeModel.getExpireTimestamp() != null){
524 
				cellExpire.setCellValue(schemeModel.getExpireTimestamp());
525 
			}else{
526 
				cellExpire.setCellValue("False");
527 
			}
528 
			Cell cellCreatedBy = rowValues.createCell(11);
529 
			cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
530 
			Cell cellItemIds = rowValues.createCell(12);
23338 ashik.ali 531 
			cellItemIds.setCellValue(schemeModel.getItemStringMap().toString());
23017 ashik.ali 532 
			Cell cellRetailerIds = rowValues.createCell(13);
533 
			cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
23338 ashik.ali 534 
			int maxHeight = Math.max(schemeModel.getItemStringMap().size(), schemeModel.getRetailerIds().size());
23017 ashik.ali 535 
			if(maxHeight > 1){
536 
				rowValues.setHeight((short)(maxHeight * 240));
537 
			}
538 
	    }
539 
 
540 
		for(int index = 0; index < 14; index++){
541 
			sheet.autoSizeColumn(index);
542 
		}
543 
 
544 
	    try{
545 
			workbook.write(outputStream);
546 
	    	workbook.close();
547 
	    }catch(IOException ioException){
548 
	    	LOGGER.error("Unable to generate excel file", ioException);
549 
	    }
550 
	}
23819 govind 551 
	public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId,int itemId,OutputStream outputStream){
552 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
553 
 
554 
		//CreationHelper createHelper = workbook.getCreationHelper();
555 
 
556 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
557 
		sheet.trackAllColumnsForAutoSizing();
558 
 
559 
 
560 
	    Row rowHeader = sheet.createRow(0);
561 
	    Cell cellItemHeader = rowHeader.createCell(0);
562 
	    cellItemHeader.setCellValue("ITEMID");
563 
		Cell cellIMEIHeader = rowHeader.createCell(1);
564 
		cellIMEIHeader.setCellValue("IMEI");
565 
		Cell cellFOFOIDHeader = rowHeader.createCell(2);
566 
		cellFOFOIDHeader.setCellValue("RETAILERNAME");
567 
		//Row rowQuantityRateValue = sheet.createRow(1);
568 
 
569 
		/*for(int index = 6; index < 18; index = index + 3){
570 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
571 
			cellQuantityHeader.setCellValue("Quantity");
572 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
573 
			cellRateHeader.setCellValue("Rate");
574 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
575 
			cellValueHeader.setCellValue("Value");
576 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
577 
		}*/
578 
		Font font = workbook.createFont();
579 
		CellStyle cellStyle = workbook.createCellStyle();
580 
		font.setBold(true);
581 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
582 
		//font.setFontHeight((short)16);
583 
		cellStyle.setFont(font);
584 
		for(int i = 0; i < 14; i++){
585 
			if(rowHeader.getCell(i) != null){
586 
				rowHeader.getCell(i).setCellStyle(cellStyle);
587 
			}
588 
		}
589 
		LinkedHashMap<String,String> priceAmount=new LinkedHashMap<>(priceDropIMEIfofoId);
590 
		Set<String> keyset=priceAmount.keySet();
591 
		int rownum = 1;
592 
		for (String key : keyset) {
593 
            Row row = sheet.createRow(rownum++);
594 
            String objArr = priceAmount.get(key);
595 
            int cellnum = 0;
596 
            Cell cell = row.createCell(cellnum++);
597 
            cell.setCellValue(itemId);
598 
            Cell cell1 = row.createCell(cellnum++);
599 
            cell1.setCellValue(key);
600 
            Cell cell2 = row.createCell(cellnum++);
601 
            cell2.setCellValue(priceAmount.get(key));
602 
            }
603 
 
604 
		for(int index = 0; index < 14; index++){
605 
			sheet.autoSizeColumn(index);
606 
		}
607 
 
608 
	    try{
609 
			workbook.write(outputStream);
610 
	    	workbook.close();
611 
	    }catch(IOException ioException){
612 
	    	LOGGER.error("Unable to generate excel file", ioException);
613 
	    }
614 
	}
615 
	public static void writePriceDropForAllIMEI(Map<String,String>priceDropAmount,OutputStream outputStream){
616 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
617 
 
618 
		//CreationHelper createHelper = workbook.getCreationHelper();
619 
 
620 
		SXSSFSheet sheet = workbook.createSheet("Schemes");
621 
		sheet.trackAllColumnsForAutoSizing();
622 
 
623 
 
624 
	    Row rowHeader = sheet.createRow(0);
625 
	    Cell cellItemHeader = rowHeader.createCell(0);
626 
	    cellItemHeader.setCellValue("Itemdescription");
627 
		Cell cellIMEIHeader = rowHeader.createCell(1);
628 
		cellIMEIHeader.setCellValue("IMEI");
629 
		//Row rowQuantityRateValue = sheet.createRow(1);
630 
 
631 
		/*for(int index = 6; index < 18; index = index + 3){
632 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
633 
			cellQuantityHeader.setCellValue("Quantity");
634 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
635 
			cellRateHeader.setCellValue("Rate");
636 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
637 
			cellValueHeader.setCellValue("Value");
638 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
639 
		}*/
640 
		Font font = workbook.createFont();
641 
		CellStyle cellStyle = workbook.createCellStyle();
642 
		font.setBold(true);
643 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
644 
		//font.setFontHeight((short)16);
645 
		cellStyle.setFont(font);
646 
		for(int i = 0; i < 14; i++){
647 
			if(rowHeader.getCell(i) != null){
648 
				rowHeader.getCell(i).setCellStyle(cellStyle);
649 
			}
650 
		}
651 
		Set<String> keyset=priceDropAmount.keySet();
652 
		int rownum = 1;
653 
		for (String key : keyset) {
654 
            Row row = sheet.createRow(rownum++);
655 
            String objArr = priceDropAmount.get(key);
656 
            int cellnum = 0;
657 
            Cell cell = row.createCell(cellnum++);
658 
            cell.setCellValue(priceDropAmount.get(key));
659 
            Cell cell1 = row.createCell(cellnum++);
660 
            cell1.setCellValue(key);
661 
            }
662 
 
663 
		for(int index = 0; index < 14; index++){
664 
			sheet.autoSizeColumn(index);
665 
		}
666 
 
667 
	    try{
668 
			workbook.write(outputStream);
669 
	    	workbook.close();
670 
	    }catch(IOException ioException){
671 
	    	LOGGER.error("Unable to generate excel file", ioException);
672 
	    }
673 
	}
21786 ashik.ali 674 
}