Subversion Repositories SmartDukaan

Rev

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

Rev 22486 Rev 22521
Line 12... Line 12...
12 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 12 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
13 
import org.apache.poi.ss.usermodel.Cell;
 13 
import org.apache.poi.ss.usermodel.Cell;
14 
import org.apache.poi.ss.usermodel.CellStyle;
 14 
import org.apache.poi.ss.usermodel.CellStyle;
15 
import org.apache.poi.ss.usermodel.CellType;
 15 
import org.apache.poi.ss.usermodel.CellType;
16 
import org.apache.poi.ss.usermodel.Font;
 16 
import org.apache.poi.ss.usermodel.Font;
- 
 
 17 
import org.apache.poi.ss.usermodel.HorizontalAlignment;
17 
import org.apache.poi.ss.usermodel.Row;
 18 
import org.apache.poi.ss.usermodel.Row;
18 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
 19 
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
19 
import org.apache.poi.ss.util.CellRangeAddress;
 20 
import org.apache.poi.ss.util.CellRangeAddress;
20 
import org.apache.poi.xssf.streaming.SXSSFSheet;
 21 
import org.apache.poi.xssf.streaming.SXSSFSheet;
21 
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 22 
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
Line 26... Line 27...
26 
import org.slf4j.LoggerFactory;
 27 
import org.slf4j.LoggerFactory;
27 
 
 28 
 
28 
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
 29 
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
29 
import com.spice.profitmandi.common.model.InventoryItemAgingModel;
 30 
import com.spice.profitmandi.common.model.InventoryItemAgingModel;
30 
import com.spice.profitmandi.common.model.InventoryItemAgingValue;
 31 
import com.spice.profitmandi.common.model.InventoryItemAgingValue;
- 
 
 32 
import com.spice.profitmandi.common.model.ItemCompleteLedgerModel;
31 
import com.spice.profitmandi.common.model.ProfitMandiConstants;
 33 
import com.spice.profitmandi.common.model.ProfitMandiConstants;
32 
import com.spice.profitmandi.common.model.TagListingModel;
 34 
import com.spice.profitmandi.common.model.TagListingModel;
33 
 
 35 
 
34 
public class ExcelUtils {
 36 
public class ExcelUtils {
35 
	private static final String TAG_ID = "Tag Id";
 37 
	private static final String TAG_ID = "Tag Id";
Line 131... Line 133...
131 
			}
 133 
			}
132 
		}
 134 
		}
133 
		return tagListings;
 135 
		return tagListings;
134 
	}
 136 
	}
135
137
136 
	public static OutputStream writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){
 138 
	public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){
137 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
 139 
		SXSSFWorkbook workbook = new SXSSFWorkbook();
138
140
139 
		//CreationHelper createHelper = workbook.getCreationHelper();
 141 
		//CreationHelper createHelper = workbook.getCreationHelper();
140
142
141 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
 143 
		SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
Line 214... Line 216...
214 
	    		Cell cellQuantity = rowValues.createCell(colIndex + 1);
 216 
	    		Cell cellQuantity = rowValues.createCell(colIndex + 1);
215 
	    		if(inventoryItemAgingValue != null){
 217 
	    		if(inventoryItemAgingValue != null){
216 
	    			cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
 218 
	    			cellPrice.setCellValue(inventoryItemAgingValue.getPrice());
217 
	    			cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
 219 
	    			cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());
218 
	    		}else{
 220 
	    		}else{
219 
	    			cellPrice.setCellValue("NA");
 221 
	    			cellPrice.setCellValue("-");
220 
	    			cellQuantity.setCellValue("NA");
 222 
	    			cellQuantity.setCellValue("-");
221 
	    		}
 223 
	    		}
222 
	    	}
 224 
	    	}
223 
	    }
 225 
	    }
224
226
225 
		for(int index = 0; index < 8 + (intervals.size() * 2); index++){
 227 
		for(int index = 0; index < 8 + (intervals.size() * 2); index++){
Line 230... Line 232...
230 
			workbook.write(outputStream);
 232 
			workbook.write(outputStream);
231 
	    	workbook.close();
 233 
	    	workbook.close();
232 
	    }catch(IOException ioException){
 234 
	    }catch(IOException ioException){
233 
	    	LOGGER.error("Unable to generate excel file", ioException);
 235 
	    	LOGGER.error("Unable to generate excel file", ioException);
234 
	    }
 236 
	    }
235 
	    return null;
 - 
 
236 
	}
 237 
	}
237
238
- 
 
 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 
	}
238 
}
 419 
}