Rev 23017 | Rev 23568 | Go to most recent revision | View as "text/plain" | Blame | Compare with Previous | Last modification | View Log | RSS feed
package com.spice.profitmandi.common.util;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.time.LocalDateTime;import java.time.ZoneId;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;import com.spice.profitmandi.common.model.InventoryItemAgingModel;import com.spice.profitmandi.common.model.InventoryItemAgingValue;import com.spice.profitmandi.common.model.ItemCompleteLedgerModel;import com.spice.profitmandi.common.model.ProfitMandiConstants;import com.spice.profitmandi.common.model.SchemeModel;import com.spice.profitmandi.common.model.TagListingModel;public class ExcelUtils {private static final String TAG_ID = "Tag Id";private static final String TAG_LABEL = "Tag Label";private static final String ITEM_ID = "Item Id";private static final String BRAND = "Brand";private static final String MODEL_NAME = "Model Name";private static final String MODEL_NUMBER = "Model Number";private static final String COLOR = "Color";private static final String SELLING_PRICE = "Selling Price";private static final String MOP = "MOP";private static final String SUPPORT_PRICE = "Support Price";private static final String START_DATE = "Start Date";private static final String TAG_LISTING = "Tag Listing";private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);public static void main(String[] args) throws Throwable{//List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);//writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);}public static List<TagListingModel> parse(InputStream inputStream) throws Exception {List<TagListingModel> tagListings = new ArrayList<>();XSSFWorkbook myWorkBook = null;try{//FileInputStream fileInputStream = new FileInputStream("/home/ashikali/tag_listing1.xlsx");myWorkBook = new XSSFWorkbook (inputStream);myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);// Return first sheet from the XLSX workbookXSSFSheet mySheet = myWorkBook.getSheetAt(0);LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());for(int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++){XSSFRow row = mySheet.getRow(rowNumber);LOGGER.info("row {}", row);TagListingModel tagListing = new TagListingModel();if(row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC){tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID, row.getCell(0).toString(), "TGLSTNG_VE_1010");LOGGER.error("Excel file parse error : ", profitMandiBusinessException);throw profitMandiBusinessException;}if(row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC){tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");LOGGER.error("Excel file parse error : ", profitMandiBusinessException);throw profitMandiBusinessException;}if(row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC){tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");LOGGER.error("Excel file parse error : ", profitMandiBusinessException);throw profitMandiBusinessException;}if(row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC){tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP, row.getCell(8), "TGLSTNG_VE_1010");LOGGER.error("Excel file parse error : ", profitMandiBusinessException);throw profitMandiBusinessException;}if(row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC){tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");LOGGER.error("Excel file parse error : ", profitMandiBusinessException);throw profitMandiBusinessException;}if(row.getCell(10) != null && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC){tagListing.setMaxDiscountPrice(Double.valueOf(row.getCell(10).toString()).floatValue());}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");LOGGER.error("Excel file parse error : ", profitMandiBusinessException);throw profitMandiBusinessException;}if(row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))){Date date = row.getCell(11).getDateCellValue();LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());tagListing.setStartDate(startDate);}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");LOGGER.error("Excel file parse error : ", profitMandiBusinessException);throw profitMandiBusinessException;}tagListings.add(tagListing);}myWorkBook.close();} catch(IOException ioException){ioException.printStackTrace();throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(), "EXL_VE_1000");} finally {if(myWorkBook != null){try {myWorkBook.close();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}return tagListings;}public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels, List<Integer> intervals, OutputStream outputStream){SXSSFWorkbook workbook = new SXSSFWorkbook();//CreationHelper createHelper = workbook.getCreationHelper();SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));sheet.trackAllColumnsForAutoSizing();Row rowHeader = sheet.createRow(0);Cell cellItemIdHeader = rowHeader.createCell(0);cellItemIdHeader.setCellValue("Item Id");Cell cellBrandHeader = rowHeader.createCell(1);cellBrandHeader.setCellValue("Brand");Cell cellModelNameHeader = rowHeader.createCell(2);cellModelNameHeader.setCellValue("Model Name");Cell cellModelNumberHeader = rowHeader.createCell(3);cellModelNumberHeader.setCellValue("Model Number");Cell cellColorHeader = rowHeader.createCell(4);cellColorHeader.setCellValue("Color");Cell cellTypeHeader = rowHeader.createCell(5);cellTypeHeader.setCellValue("Item Type");Row rowPriceQuantity = sheet.createRow(1);for(int index = 0, colIndex = 6; index < intervals.size() + 1; index++, colIndex = colIndex + 2){Cell cellHeader = rowHeader.createCell(colIndex);if(index == 0){cellHeader.setCellValue("Less Than "+intervals.get(index)+" Days");}else if(index < intervals.size()){cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");}else{cellHeader.setCellValue("More Than "+intervals.get(index - 1)+" Days");}sheet.addMergedRegion(new CellRangeAddress(0, 0, colIndex, colIndex + 1));rowHeader.createCell(colIndex + 1);Cell cellPrice = rowPriceQuantity.createCell(colIndex);cellPrice.setCellValue("Price");Cell cellQuantity = rowPriceQuantity.createCell(colIndex + 1);cellQuantity.setCellValue("Quantity");}Font font = workbook.createFont();CellStyle cellStyle = workbook.createCellStyle();font.setBold(true);//font.setFontHeight((short)16);cellStyle.setFont(font);for(int i = 0; i < 8 + (intervals.size() * 2); i++){rowHeader.getCell(i).setCellStyle(cellStyle);if(rowPriceQuantity.getCell(i) != null){rowPriceQuantity.getCell(i).setCellStyle(cellStyle);}}for(int index = 0; index < inventoryItemAgingModels.size(); index++){InventoryItemAgingModel inventoryItemAgingModel = inventoryItemAgingModels.get(index);Row rowValues = sheet.createRow(index + 2);Cell cellItemId = rowValues.createCell(0);cellItemId.setCellValue(inventoryItemAgingModel.getItemId());Cell cellBrand = rowValues.createCell(1);cellBrand.setCellValue(inventoryItemAgingModel.getBrand());Cell cellModelName = rowValues.createCell(2);cellModelName.setCellValue(inventoryItemAgingModel.getModelName());Cell cellModelNumber = rowValues.createCell(3);cellModelNumber.setCellValue(inventoryItemAgingModel.getModelNumber());Cell cellColor = rowValues.createCell(4);cellColor.setCellValue(inventoryItemAgingModel.getColor());Cell cellType = rowValues.createCell(5);cellType.setCellValue(inventoryItemAgingModel.getItemType().toString());List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();//LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);for(int i = 0, colIndex = 6; i < inventoryItemAgingValues.size(); i++, colIndex = colIndex + 2){Cell cellPrice = rowValues.createCell(colIndex);InventoryItemAgingValue inventoryItemAgingValue = inventoryItemAgingValues.get(i);//LOGGER.info("inventoryItemAgingValue {}", inventoryItemAgingValue);Cell cellQuantity = rowValues.createCell(colIndex + 1);if(inventoryItemAgingValue != null){cellPrice.setCellValue(inventoryItemAgingValue.getPrice());cellQuantity.setCellValue(inventoryItemAgingValue.getQuantity());}else{cellPrice.setCellValue("-");cellQuantity.setCellValue("-");}}}for(int index = 0; index < 8 + (intervals.size() * 2); index++){sheet.autoSizeColumn(index);}try{workbook.write(outputStream);workbook.close();}catch(IOException ioException){LOGGER.error("Unable to generate excel file", ioException);}}public static void writeItemCompleteLedgerModels(List<ItemCompleteLedgerModel> itemCompleteLedgerModels, OutputStream outputStream){SXSSFWorkbook workbook = new SXSSFWorkbook();//CreationHelper createHelper = workbook.getCreationHelper();SXSSFSheet sheet = workbook.createSheet("ItemCompleteLeger");sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));sheet.trackAllColumnsForAutoSizing();Row rowHeader = sheet.createRow(0);Cell cellItemIdHeader = rowHeader.createCell(0);cellItemIdHeader.setCellValue("Item Id");Cell cellBrandHeader = rowHeader.createCell(1);cellBrandHeader.setCellValue("Brand");Cell cellModelNameHeader = rowHeader.createCell(2);cellModelNameHeader.setCellValue("Model Name");Cell cellModelNumberHeader = rowHeader.createCell(3);cellModelNumberHeader.setCellValue("Model Number");Cell cellColorHeader = rowHeader.createCell(4);cellColorHeader.setCellValue("Color");Cell cellTypeHeader = rowHeader.createCell(5);cellTypeHeader.setCellValue("Item Type");Cell cellOpeningBalanceHeader = rowHeader.createCell(6);cellOpeningBalanceHeader.setCellValue("Opening Balance");Cell cellInwardsHeader = rowHeader.createCell(9);cellInwardsHeader.setCellValue("Inwards");Cell cellOutwardsHeader = rowHeader.createCell(12);cellOutwardsHeader.setCellValue("Outwards");Cell cellClosingBalanceHeader = rowHeader.createCell(15);cellClosingBalanceHeader.setCellValue("Closing Balance");Row rowQuantityRateValue = sheet.createRow(1);for(int index = 6; index < 18; index = index + 3){Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);cellQuantityHeader.setCellValue("Quantity");Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);cellRateHeader.setCellValue("Rate");Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);cellValueHeader.setCellValue("Value");sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));}Font font = workbook.createFont();CellStyle cellStyle = workbook.createCellStyle();font.setBold(true);cellStyle.setAlignment(HorizontalAlignment.CENTER);//font.setFontHeight((short)16);cellStyle.setFont(font);for(int i = 0; i < 18; i++){if(rowHeader.getCell(i) != null){rowHeader.getCell(i).setCellStyle(cellStyle);}}int openingQuantityTotal = 0;float openingValueTotal = 0;int inwardsQuantityTotal = 0;float inwardsValueTotal = 0;int outwardsQuantityTotal = 0;float outwardsValueTotal = 0;int closingQuantityTotal = 0;float closingValueTotal = 0;for(int index = 0; index < itemCompleteLedgerModels.size(); index++){ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);Row rowValues = sheet.createRow(index + 2);Cell cellItemId = rowValues.createCell(0);cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());Cell cellBrand = rowValues.createCell(1);cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());Cell cellModelName = rowValues.createCell(2);cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());Cell cellModelNumber = rowValues.createCell(3);cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());Cell cellColor = rowValues.createCell(4);cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());Cell cellType = rowValues.createCell(5);cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());Cell cellOpeningQuantity = rowValues.createCell(6);Cell cellOpeningRate = rowValues.createCell(7);Cell cellOpeningValue = rowValues.createCell(8);if(itemCompleteLedgerModel.getOpeningLedger() == null){cellOpeningQuantity.setCellValue("-");cellOpeningRate.setCellValue("-");cellOpeningValue.setCellValue("-");}else{cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());openingQuantityTotal = openingQuantityTotal + itemCompleteLedgerModel.getOpeningLedger().getQuantity();openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();}Cell cellInwardsQuantity = rowValues.createCell(9);Cell cellInwardsRate = rowValues.createCell(10);Cell cellInwardsValue = rowValues.createCell(11);if(itemCompleteLedgerModel.getInwardsLedger() == null){cellInwardsQuantity.setCellValue("-");cellInwardsRate.setCellValue("-");cellInwardsValue.setCellValue("-");}else{cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());inwardsQuantityTotal = inwardsQuantityTotal + itemCompleteLedgerModel.getInwardsLedger().getQuantity();inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();}Cell cellOutwardsQuantity = rowValues.createCell(12);Cell cellOutwardsRate = rowValues.createCell(13);Cell cellOutwardsValue = rowValues.createCell(14);if(itemCompleteLedgerModel.getOutwardsLedger() == null){cellOutwardsQuantity.setCellValue("-");cellOutwardsRate.setCellValue("-");cellOutwardsValue.setCellValue("-");}else{cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());outwardsQuantityTotal = outwardsQuantityTotal + itemCompleteLedgerModel.getOutwardsLedger().getQuantity();outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();}Cell cellClosingQuantity = rowValues.createCell(15);Cell cellClosingRate = rowValues.createCell(16);Cell cellClosingValue = rowValues.createCell(17);if(itemCompleteLedgerModel.getClosingLedger() == null){cellClosingQuantity.setCellValue("-");cellClosingRate.setCellValue("-");cellClosingValue.setCellValue("-");}else{cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());closingQuantityTotal = closingQuantityTotal + itemCompleteLedgerModel.getClosingLedger().getQuantity();closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();}}Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);if(openingQuantityTotal > 0){Cell cellOpeningQuantityTotal = rowTotal.createCell(6);cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);Cell cellOpeningValueTotal = rowTotal.createCell(8);cellOpeningValueTotal.setCellValue(openingValueTotal);}if(inwardsQuantityTotal > 0){Cell cellInwardsQuantityTotal = rowTotal.createCell(9);cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);Cell cellInwardsValueTotal = rowTotal.createCell(11);cellInwardsValueTotal.setCellValue(inwardsValueTotal);}if(outwardsQuantityTotal > 0){Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);Cell cellOutwardsValueTotal = rowTotal.createCell(14);cellOutwardsValueTotal.setCellValue(outwardsValueTotal);}if(closingQuantityTotal > 0){Cell cellClosingQuantityTotal = rowTotal.createCell(15);cellClosingQuantityTotal.setCellValue(closingQuantityTotal);Cell cellClosingValueTotal = rowTotal.createCell(17);cellClosingValueTotal.setCellValue(closingValueTotal);}for(int index = 0; index < 18; index++){sheet.autoSizeColumn(index);}try{workbook.write(outputStream);workbook.close();}catch(IOException ioException){LOGGER.error("Unable to generate excel file", ioException);}}public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream){SXSSFWorkbook workbook = new SXSSFWorkbook();//CreationHelper createHelper = workbook.getCreationHelper();SXSSFSheet sheet = workbook.createSheet("Schemes");sheet.trackAllColumnsForAutoSizing();Row rowHeader = sheet.createRow(0);Cell cellSchemeIdHeader = rowHeader.createCell(0);cellSchemeIdHeader.setCellValue("Scheme Id");Cell cellNameHeader = rowHeader.createCell(1);cellNameHeader.setCellValue("Name");Cell cellDescriptionHeader = rowHeader.createCell(2);cellDescriptionHeader.setCellValue("Description");Cell cellSchemeTypeHeader = rowHeader.createCell(3);cellSchemeTypeHeader.setCellValue("Scheme Type");Cell cellAmountTypeHeader = rowHeader.createCell(4);cellAmountTypeHeader.setCellValue("Amount Type");Cell cellAmountHeader = rowHeader.createCell(5);cellAmountHeader.setCellValue("Amount");Cell cellStartDateTimeHeader = rowHeader.createCell(6);cellStartDateTimeHeader.setCellValue("Start Date Time");Cell cellEndDateTimeHeader = rowHeader.createCell(7);cellEndDateTimeHeader.setCellValue("End Date Time");Cell cellCreatedAtHeader = rowHeader.createCell(8);cellCreatedAtHeader.setCellValue("Created At");Cell cellActiveHeader = rowHeader.createCell(9);cellActiveHeader.setCellValue("Active");Cell cellExpireHeader = rowHeader.createCell(10);cellExpireHeader.setCellValue("Expire");Cell cellCreatedByHeader = rowHeader.createCell(11);cellCreatedByHeader.setCellValue("Created By");Cell cellItemIdsHeader = rowHeader.createCell(12);cellItemIdsHeader.setCellValue("Item Ids");Cell cellRetailerIdsHeader = rowHeader.createCell(13);cellRetailerIdsHeader.setCellValue("Retailer Ids");//Row rowQuantityRateValue = sheet.createRow(1);/*for(int index = 6; index < 18; index = index + 3){Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);cellQuantityHeader.setCellValue("Quantity");Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);cellRateHeader.setCellValue("Rate");Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);cellValueHeader.setCellValue("Value");sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));}*/Font font = workbook.createFont();CellStyle cellStyle = workbook.createCellStyle();font.setBold(true);cellStyle.setAlignment(HorizontalAlignment.CENTER);//font.setFontHeight((short)16);cellStyle.setFont(font);for(int i = 0; i < 14; i++){if(rowHeader.getCell(i) != null){rowHeader.getCell(i).setCellStyle(cellStyle);}}for(int index = 0; index < schemeModels.size(); index++){SchemeModel schemeModel = schemeModels.get(index);//ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);Row rowValues = sheet.createRow(index + 1);Cell cellSchemeId = rowValues.createCell(0);cellSchemeId.setCellValue(schemeModel.getSchemeId());Cell cellName = rowValues.createCell(1);cellName.setCellValue(schemeModel.getName());Cell cellDescription = rowValues.createCell(2);cellDescription.setCellValue(schemeModel.getDescription());Cell cellSchemeType = rowValues.createCell(3);cellSchemeType.setCellValue(schemeModel.getSchemeType());Cell cellAmountType = rowValues.createCell(4);cellAmountType.setCellValue(schemeModel.getAmountType());Cell cellAmount = rowValues.createCell(5);cellAmount.setCellValue(schemeModel.getAmount());Cell cellStartDateTime = rowValues.createCell(6);cellStartDateTime.setCellValue(schemeModel.getStartDateTime());Cell cellEndDateTime = rowValues.createCell(7);cellEndDateTime.setCellValue(schemeModel.getEndDateTime());Cell cellCreatedAt = rowValues.createCell(8);cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());Cell cellActive = rowValues.createCell(9);if(schemeModel.getActiveTimestamp() !=null){cellActive.setCellValue(schemeModel.getActiveTimestamp());}else{cellActive.setCellValue("False");}Cell cellExpire = rowValues.createCell(10);if(schemeModel.getExpireTimestamp() != null){cellExpire.setCellValue(schemeModel.getExpireTimestamp());}else{cellExpire.setCellValue("False");}Cell cellCreatedBy = rowValues.createCell(11);cellCreatedBy.setCellValue(schemeModel.getCreatedBy());Cell cellItemIds = rowValues.createCell(12);cellItemIds.setCellValue(schemeModel.getItemStringMap().toString());Cell cellRetailerIds = rowValues.createCell(13);cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());int maxHeight = Math.max(schemeModel.getItemStringMap().size(), schemeModel.getRetailerIds().size());if(maxHeight > 1){rowValues.setHeight((short)(maxHeight * 240));}}for(int index = 0; index < 14; index++){sheet.autoSizeColumn(index);}try{workbook.write(outputStream);workbook.close();}catch(IOException ioException){LOGGER.error("Unable to generate excel file", ioException);}}}