Subversion Repositories SmartDukaan

Rev

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