Subversion Repositories SmartDukaan

Rev

Rev 22486 | Rev 22563 | 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;
34 
import com.spice.profitmandi.common.model.TagListingModel;
35 
 
36 
public class ExcelUtils {
37 
	private static final String TAG_ID = "Tag Id";
38 
	private static final String TAG_LABEL = "Tag Label";
39 
	private static final String ITEM_ID = "Item Id";
40 
	private static final String BRAND = "Brand";
41 
	private static final String MODEL_NAME = "Model Name";
42 
	private static final String MODEL_NUMBER = "Model Number";
43 
	private static final String COLOR = "Color";
44 
	private static final String SELLING_PRICE = "Selling Price";
22204 amit.gupta 45 
	private static final String MOP = "MOP";
21786 ashik.ali 46 
	private static final String SUPPORT_PRICE = "Support Price";
47 
	private static final String START_DATE = "Start Date";
48 
	private static final String TAG_LISTING = "Tag Listing";
49 
 
22226 amit.gupta 50 
	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
21786 ashik.ali 51 
 
52 
	public static void main(String[] args) throws Throwable{
22470 ashik.ali 53 
		//List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
54 
		//writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
21786 ashik.ali 55 
	}
22247 amit.gupta 56 
	public static List<TagListingModel> parse(InputStream inputStream) throws Throwable {
21786 ashik.ali 57 
 
58 
		List<TagListingModel> tagListings = new ArrayList<>();
59 
		XSSFWorkbook myWorkBook = null;
60 
		try{
61 
			//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");
62 
			myWorkBook = new XSSFWorkbook (inputStream);
63 
 
64 
			myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
65 
			// Return first sheet from the XLSX workbook
66 
			XSSFSheet mySheet = myWorkBook.getSheetAt(0);
22247 amit.gupta 67 
			LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
21786 ashik.ali 68 
 
22247 amit.gupta 69 
			for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){
70 
				XSSFRow row = mySheet.getRow(rowNumber);
71 
				LOGGER.info("row {}", row);
72 
				TagListingModel tagListing = new TagListingModel();
73 
				if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){
74 
					tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
75 
				}else{
76 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");
22226 amit.gupta 77 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
21786 ashik.ali 78 
					throw profitMandiBusinessException;
79 
				}
22247 amit.gupta 80 
 
81 
				if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){
82 
					tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
83 
				}else{
84 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
85 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
86 
					throw profitMandiBusinessException;
87 
				}
22204 amit.gupta 88 
 
22247 amit.gupta 89 
				if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){
90 
					tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
91 
				}else{
92 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
22226 amit.gupta 93 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
22209 amit.gupta 94 
					throw profitMandiBusinessException;
21786 ashik.ali 95 
				}
22247 amit.gupta 96 
				if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){
97 
					tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
98 
				}else{
99 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");
100 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
101 
					throw profitMandiBusinessException;
102 
				}
103 
				if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){
104 
					tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
105 
				}else{
106 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
107 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
108 
					throw profitMandiBusinessException;
109 
				}
110 
				if(row.getCell(10) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(10))){
111 
					Date date = row.getCell(10).getDateCellValue();
112 
					LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
113 
					tagListing.setStartDate(startDate);
114 
				}else{
115 
					ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
116 
					LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
117 
					throw  profitMandiBusinessException;
118 
				}
119 
				tagListings.add(tagListing);
21786 ashik.ali 120 
			}
121 
			myWorkBook.close();
22247 amit.gupta 122 
		} catch(IOException ioException){
22206 amit.gupta 123 
			ioException.printStackTrace();
21786 ashik.ali 124 
			throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");
22247 amit.gupta 125 
		} finally {
21786 ashik.ali 126 
			if(myWorkBook != null){
127 
				try {
128 
					myWorkBook.close();
129 
				} catch (IOException e) {
130 
					// TODO Auto-generated catch block
131 
					e.printStackTrace();
132 
				}
133 
			}
134 
		}
135 
		return tagListings;
136 
	}
22470 ashik.ali 137 
 
22521 ashik.ali 138 
	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){
22470 ashik.ali 139 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
140 
 
141 
		//CreationHelper createHelper = workbook.getCreationHelper();
142 
 
143 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
144 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
145 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
146 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
147 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
148 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
149 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
150 
		sheet.trackAllColumnsForAutoSizing();
151 
 
152 
 
153 
	    Row rowHeader = sheet.createRow(0);
154 
	    Cell cellItemIdHeader = rowHeader.createCell(0);
155 
		cellItemIdHeader.setCellValue("Item Id");
156 
		Cell cellBrandHeader = rowHeader.createCell(1);
157 
		cellBrandHeader.setCellValue("Brand");
158 
		Cell cellModelNameHeader = rowHeader.createCell(2);
159 
		cellModelNameHeader.setCellValue("Model Name");
160 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
161 
		cellModelNumberHeader.setCellValue("Model Number");
162 
		Cell cellColorHeader = rowHeader.createCell(4);
163 
		cellColorHeader.setCellValue("Color");
164 
		Cell cellTypeHeader = rowHeader.createCell(5);
165 
		cellTypeHeader.setCellValue("Item Type");
166 
		Row rowPriceQuantity = sheet.createRow(1);
167 
		for(int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2){
168 
			Cell cellHeader = rowHeader.createCell(colIndex);
169 
			if(index == 0){
170 
				cellHeader.setCellValue("Less Than "+intervals.get(index)+" Days");
171 
			}else if(index < intervals.size()){
172 
				cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
173 
			}else{
174 
				cellHeader.setCellValue("More Than "+intervals.get(index - 1)+" Days");
175 
			}
176 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + 1));
177 
			rowHeader.createCell(colIndex + 1);
178 
			Cell cellPrice = rowPriceQuantity.createCell(colIndex);
179 
			cellPrice.setCellValue("Price");
180 
			Cell cellQuantity = rowPriceQuantity.createCell(colIndex + 1);
181 
			cellQuantity.setCellValue("Quantity");
182 
		}
183 
		Font font = workbook.createFont();
184 
		CellStyle cellStyle = workbook.createCellStyle();
185 
		font.setBold(true);
186 
		//font.setFontHeight((short)16);
187 
		cellStyle.setFont(font);
22486 ashik.ali 188 
		for(int i = 0; i < 8 + (intervals.size() * 2); i++){
22470 ashik.ali 189 
			rowHeader.getCell(i).setCellStyle(cellStyle);
190 
			if(rowPriceQuantity.getCell(i) != null){
191 
				rowPriceQuantity.getCell(i).setCellStyle(cellStyle);
192 
			}
193 
		}
194 
 
195 
		for(int index = 0; index < inventoryItemAgingModels.size(); index++){
196 
			InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);
197 
	    	Row rowValues = sheet.createRow(index + 2);
198 
	    	Cell cellItemId = rowValues.createCell(0);
199 
	    	cellItemId.setCellValue(inventoryItemAgingModel.getItemId());
200 
	    	Cell cellBrand = rowValues.createCell(1);
201 
	    	cellBrand.setCellValue(inventoryItemAgingModel.getBrand());
202 
	    	Cell cellModelName = rowValues.createCell(2);
203 
	    	cellModelName.setCellValue(inventoryItemAgingModel.getModelName());
204 
	    	Cell cellModelNumber = rowValues.createCell(3);
205 
	    	cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());
206 
	    	Cell cellColor = rowValues.createCell(4);
207 
	    	cellColor.setCellValue(inventoryItemAgingModel.getColor());
208 
	    	Cell cellType = rowValues.createCell(5);
209 
	    	cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());
210 
	    	List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
211 
	    	//LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
212 
	    	for(int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2){
213 
	    		Cell cellPrice = rowValues.createCell(colIndex);
214 
	    		InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);
215 
	    		//LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);
216 
	    		Cell cellQuantity = rowValues.createCell(colIndex + 1);
217 
	    		if(inventoryItemAgingValue != null){
218 
	    			cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
219 
	    			cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
220 
	    		}else{
22521 ashik.ali 221 
	    			cellPrice.setCellValue("-");
222 
	    			cellQuantity.setCellValue("-");
22470 ashik.ali 223 
	    		}
224 
	    	}
225 
	    }
226 
 
22486 ashik.ali 227 
		for(int index = 0; index < 8 + (intervals.size() * 2); index++){
22470 ashik.ali 228 
			sheet.autoSizeColumn(index);
229 
		}
230 
 
231 
	    try{
22486 ashik.ali 232 
			workbook.write(outputStream);
22470 ashik.ali 233 
	    	workbook.close();
234 
	    }catch(IOException ioException){
22486 ashik.ali 235 
	    	LOGGER.error("Unable to generate excel file", ioException);
22470 ashik.ali 236 
	    }
237 
	}
238 
 
22521 ashik.ali 239 
	public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels, OutputStream outputStream){
240 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
241 
 
242 
		//CreationHelper createHelper = workbook.getCreationHelper();
243 
 
244 
		SXSSFSheet sheet = workbook.createSheet("ItemCompleteLeger");
245 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
246 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
247 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
248 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
249 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
250 
		sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
251 
		sheet.trackAllColumnsForAutoSizing();
252 
 
253 
 
254 
	    Row rowHeader = sheet.createRow(0);
255 
	    Cell cellItemIdHeader = rowHeader.createCell(0);
256 
		cellItemIdHeader.setCellValue("Item Id");
257 
		Cell cellBrandHeader = rowHeader.createCell(1);
258 
		cellBrandHeader.setCellValue("Brand");
259 
		Cell cellModelNameHeader = rowHeader.createCell(2);
260 
		cellModelNameHeader.setCellValue("Model Name");
261 
		Cell cellModelNumberHeader = rowHeader.createCell(3);
262 
		cellModelNumberHeader.setCellValue("Model Number");
263 
		Cell cellColorHeader = rowHeader.createCell(4);
264 
		cellColorHeader.setCellValue("Color");
265 
		Cell cellTypeHeader = rowHeader.createCell(5);
266 
		cellTypeHeader.setCellValue("Item Type");
267 
		Cell cellOpeningBalanceHeader = rowHeader.createCell(6);
268 
		cellOpeningBalanceHeader.setCellValue("Opening Balance");
269 
		Cell cellInwardsHeader = rowHeader.createCell(9);
270 
		cellInwardsHeader.setCellValue("Inwards");
271 
		Cell cellOutwardsHeader = rowHeader.createCell(12);
272 
		cellOutwardsHeader.setCellValue("Outwards");
273 
		Cell cellClosingBalanceHeader = rowHeader.createCell(15);
274 
		cellClosingBalanceHeader.setCellValue("Closing Balance");
275 
		Row rowQuantityRateValue = sheet.createRow(1);
276 
 
277 
		for(int index = 6; index < 18; index = index + 3){
278 
			Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
279 
			cellQuantityHeader.setCellValue("Quantity");
280 
			Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
281 
			cellRateHeader.setCellValue("Rate");
282 
			Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
283 
			cellValueHeader.setCellValue("Value");
284 
			sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
285 
		}
286 
		Font font = workbook.createFont();
287 
		CellStyle cellStyle = workbook.createCellStyle();
288 
		font.setBold(true);
289 
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
290 
		//font.setFontHeight((short)16);
291 
		cellStyle.setFont(font);
292 
		for(int i = 0; i < 18; i++){
293 
			if(rowHeader.getCell(i) != null){
294 
				rowHeader.getCell(i).setCellStyle(cellStyle);
295 
			}
296 
		}
297 
		int openingQuantityTotal = 0;
298 
		float openingValueTotal = 0;
299 
		int inwardsQuantityTotal = 0;
300 
		float inwardsValueTotal = 0;
301 
		int outwardsQuantityTotal = 0;
302 
		float outwardsValueTotal = 0;
303 
		int closingQuantityTotal = 0;
304 
		float closingValueTotal = 0;
305 
		for(int index = 0; index < itemCompleteLedgerModels.size(); index++){
306 
			ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
307 
	    	Row rowValues = sheet.createRow(index + 2);
308 
	    	Cell cellItemId = rowValues.createCell(0);
309 
	    	cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
310 
	    	Cell cellBrand = rowValues.createCell(1);
311 
	    	cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
312 
	    	Cell cellModelName = rowValues.createCell(2);
313 
	    	cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
314 
	    	Cell cellModelNumber = rowValues.createCell(3);
315 
	    	cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
316 
	    	Cell cellColor = rowValues.createCell(4);
317 
	    	cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
318 
	    	Cell cellType = rowValues.createCell(5);
319 
	    	cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
320 
	    	Cell cellOpeningQuantity = rowValues.createCell(6);
321 
	    	Cell cellOpeningRate = rowValues.createCell(7);
322 
	    	Cell cellOpeningValue = rowValues.createCell(8);
323 
	    	if(itemCompleteLedgerModel.getOpeningLedger() == null){
324 
	    		cellOpeningQuantity.setCellValue("-");
325 
	    		cellOpeningRate.setCellValue("-");
326 
	    		cellOpeningValue.setCellValue("-");
327 
	    	}else{
328 
	    		cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
329 
	    		cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
330 
	    		cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
331 
	    		openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();
332 
	    		openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
333 
	    	}
334 
	    	Cell cellInwardsQuantity = rowValues.createCell(9);
335 
	    	Cell cellInwardsRate = rowValues.createCell(10);
336 
	    	Cell cellInwardsValue = rowValues.createCell(11);
337 
	    	if(itemCompleteLedgerModel.getInwardsLedger() == null){
338 
	    		cellInwardsQuantity.setCellValue("-");
339 
	    		cellInwardsRate.setCellValue("-");
340 
	    		cellInwardsValue.setCellValue("-");
341 
	    	}else{
342 
	    		cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
343 
	    		cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
344 
	    		cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
345 
	    		inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();
346 
	    		inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
347 
	    	}
348 
	    	Cell cellOutwardsQuantity = rowValues.createCell(12);
349 
	    	Cell cellOutwardsRate = rowValues.createCell(13);
350 
	    	Cell cellOutwardsValue = rowValues.createCell(14);
351 
	    	if(itemCompleteLedgerModel.getOutwardsLedger() == null){
352 
	    		cellOutwardsQuantity.setCellValue("-");
353 
	    		cellOutwardsRate.setCellValue("-");
354 
	    		cellOutwardsValue.setCellValue("-");
355 
	    	}else{
356 
	    		cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
357 
	    		cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
358 
	    		cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
359 
	    		outwardsQuantityTotal = outwardsQuantityTotal + itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
360 
	    		outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
361 
	    	}
362 
	    	Cell cellClosingQuantity = rowValues.createCell(15);
363 
	    	Cell cellClosingRate = rowValues.createCell(16);
364 
	    	Cell cellClosingValue = rowValues.createCell(17);
365 
	    	if(itemCompleteLedgerModel.getClosingLedger() == null){
366 
	    		cellClosingQuantity.setCellValue("-");
367 
	    		cellClosingRate.setCellValue("-");
368 
	    		cellClosingValue.setCellValue("-");
369 
	    	}else{
370 
	    		cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
371 
	    		cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
372 
	    		cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
373 
	    		closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();
374 
	    		closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
375 
	    	}
376 
	    }
377 
 
378 
		Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
379 
 
380 
		if(openingQuantityTotal > 0){
381 
			Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
382 
			cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
383 
			Cell cellOpeningValueTotal = rowTotal.createCell(8);
384 
			cellOpeningValueTotal.setCellValue(openingValueTotal);
385 
		}
386 
 
387 
		if(inwardsQuantityTotal > 0){
388 
			Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
389 
			cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
390 
			Cell cellInwardsValueTotal = rowTotal.createCell(11);
391 
			cellInwardsValueTotal.setCellValue(inwardsValueTotal);
392 
		}
393 
 
394 
		if(outwardsQuantityTotal > 0){
395 
			Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
396 
			cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
397 
			Cell cellOutwardsValueTotal = rowTotal.createCell(14);
398 
			cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
399 
		}
400 
 
401 
		if(closingQuantityTotal > 0){
402 
			Cell cellClosingQuantityTotal = rowTotal.createCell(15);
403 
			cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
404 
			Cell cellClosingValueTotal = rowTotal.createCell(17);
405 
			cellClosingValueTotal.setCellValue(closingValueTotal);
406 
		}
407 
 
408 
		for(int index = 0; index < 18; index++){
409 
			sheet.autoSizeColumn(index);
410 
		}
411 
 
412 
	    try{
413 
			workbook.write(outputStream);
414 
	    	workbook.close();
415 
	    }catch(IOException ioException){
416 
	    	LOGGER.error("Unable to generate excel file", ioException);
417 
	    }
418 
	}
21786 ashik.ali 419 
}