Rev 34534 | Rev 34574 | 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 com.spice.profitmandi.common.exception.ProfitMandiBusinessException;import com.spice.profitmandi.common.model.*;import in.shop2020.model.v1.order.WalletReferenceType;import org.apache.logging.log4j.LogManager;import org.apache.logging.log4j.Logger;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.ss.usermodel.*;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 java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.time.LocalDateTime;import java.time.ZoneId;import java.util.*;import java.util.stream.Collectors;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 String FOFO_ID = "fofoId";private static final String STORE_NAME = "storeName";private static final String EMAIL = "email";private static final String TARGET_VALUE = "targetValue";private static List<Character> ALPHABETS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".chars().mapToObj(c -> (char) c).collect(Collectors.toList());private static final List<WalletReferenceType> BULK_WALLET_REFERENCES = Arrays.asList(WalletReferenceType.SCHEME_OUT,WalletReferenceType.BRAND_FEE, WalletReferenceType.FESTIVE_OFFER, WalletReferenceType.GOODWILL_GESTURE,WalletReferenceType.EOL, WalletReferenceType.ACTIVATION_SCHEME, WalletReferenceType.BRAND_PAYOUT,WalletReferenceType.SALES_MARKETING_SUPPORT, WalletReferenceType.INVESTMENT_PAYOUT,WalletReferenceType.CATEGORY_SPL_PAYOUT, WalletReferenceType.REFERRAL_INCENTIVES, WalletReferenceType.OTHERS,WalletReferenceType.SHOP_BOY_SUPPORT, WalletReferenceType.PREBOOKING_ORDER);private static final Logger LOGGER = LogManager.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();boolean showPartner = inventoryItemAgingModels.get(0).getFofoId() > 0;int nonValueColumns = showPartner ? 9 : 6;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));int rowIndex = 0;Row rowHeader = sheet.createRow(rowIndex++);Row rowPriceQuantity = sheet.createRow(rowIndex++);int i = 0;if (showPartner) {sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 8));}sheet.trackAllColumnsForAutoSizing();if (showPartner) {Cell cellFofoIdHeader = rowHeader.createCell(i++);cellFofoIdHeader.setCellValue("Partner Id");Cell cellStoreCodeHeader = rowHeader.createCell(i++);cellStoreCodeHeader.setCellValue("Store Code");Cell cellStoreNameHeader = rowHeader.createCell(i++);cellStoreNameHeader.setCellValue("Store Name");}Cell cellItemIdHeader = rowHeader.createCell(i++);cellItemIdHeader.setCellValue("Item Id");Cell cellBrandHeader = rowHeader.createCell(i++);cellBrandHeader.setCellValue("Brand");Cell cellModelNameHeader = rowHeader.createCell(i++);cellModelNameHeader.setCellValue("Model Name");Cell cellModelNumberHeader = rowHeader.createCell(i++);cellModelNumberHeader.setCellValue("Model Number");Cell cellColorHeader = rowHeader.createCell(i++);cellColorHeader.setCellValue("Color");Cell cellTypeHeader = rowHeader.createCell(i++);cellTypeHeader.setCellValue("Item Type");for (int index = 0; index <= intervals.size(); index++) {Cell cellHeader = rowHeader.createCell(i++);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, i - 1, i));rowHeader.createCell(i++);Cell cellPrice = rowPriceQuantity.createCell(i - 2);cellPrice.setCellValue("Price");Cell cellQuantity = rowPriceQuantity.createCell(i - 1);cellQuantity.setCellValue("Quantity");}Font font = workbook.createFont();font.setBold(true);CellStyle cellStyle = workbook.createCellStyle();cellStyle.setFont(font);for (int j = 0; j < nonValueColumns + ((intervals.size() + 1)) * 2; j++) {rowHeader.getCell(j).setCellStyle(cellStyle);if (rowPriceQuantity.getCell(j) != null) {rowPriceQuantity.getCell(j).setCellStyle(cellStyle);}}for (InventoryItemAgingModel inventoryItemAgingModel : inventoryItemAgingModels) {i = 0;Row rowValues = sheet.createRow(rowIndex++);if (showPartner) {rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getFofoId());rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getStoreCode());rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getStoreName());}rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getItemId());rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getBrand());rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getModelName());rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getModelNumber());rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getColor());rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getItemType().toString());List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();// LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);for (InventoryItemAgingValue inventoryItemAgingValue : inventoryItemAgingValues) {if (inventoryItemAgingValue != null) {rowValues.createCell(i++).setCellValue(inventoryItemAgingValue.getPrice());rowValues.createCell(i++).setCellValue(inventoryItemAgingValue.getQuantity());} else {rowValues.createCell(i++).setCellValue("-");rowValues.createCell(i++).setCellValue("-");}}}for (int index = 0; index < nonValueColumns + ((intervals.size() + 1) * 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(Map<String, List<ItemCompleteLedgerModel>> itemCompleteLedgerModelsMap, OutputStream outputStream) {SXSSFWorkbook workbook = new SXSSFWorkbook();// CreationHelper createHelper = workbook.getCreationHelper();for (Map.Entry<String, List<ItemCompleteLedgerModel>> itemCompleteLedgerModelsEntry : itemCompleteLedgerModelsMap.entrySet()) {SXSSFSheet sheet = workbook.createSheet(itemCompleteLedgerModelsEntry.getKey());List<ItemCompleteLedgerModel> itemCompleteLedgerModels = itemCompleteLedgerModelsEntry.getValue();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.getCatalogStringMap().toString());Cell cellRetailerIds = rowValues.createCell(13);cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());int maxHeight = Math.max(schemeModel.getCatalogStringMap().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);}}public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId, int itemId, OutputStream outputStream) {SXSSFWorkbook workbook = new SXSSFWorkbook();// CreationHelper createHelper = workbook.getCreationHelper();SXSSFSheet sheet = workbook.createSheet("Schemes");sheet.trackAllColumnsForAutoSizing();Row rowHeader = sheet.createRow(0);Cell cellItemHeader = rowHeader.createCell(0);cellItemHeader.setCellValue("ITEMID");Cell cellIMEIHeader = rowHeader.createCell(1);cellIMEIHeader.setCellValue("IMEI");Cell cellFOFOIDHeader = rowHeader.createCell(2);cellFOFOIDHeader.setCellValue("RETAILERNAME");// 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);}}LinkedHashMap<String, String> priceAmount = new LinkedHashMap<>(priceDropIMEIfofoId);Set<String> keyset = priceAmount.keySet();int rownum = 1;for (String key : keyset) {Row row = sheet.createRow(rownum++);String objArr = priceAmount.get(key);int cellnum = 0;Cell cell = row.createCell(cellnum++);cell.setCellValue(itemId);Cell cell1 = row.createCell(cellnum++);cell1.setCellValue(key);Cell cell2 = row.createCell(cellnum++);cell2.setCellValue(priceAmount.get(key));}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);}}public static void writePriceDropForAllIMEI(Map<String, String> priceDropAmount, OutputStream outputStream) {SXSSFWorkbook workbook = new SXSSFWorkbook();// CreationHelper createHelper = workbook.getCreationHelper();SXSSFSheet sheet = workbook.createSheet("Schemes");sheet.trackAllColumnsForAutoSizing();Row rowHeader = sheet.createRow(0);Cell cellItemHeader = rowHeader.createCell(0);cellItemHeader.setCellValue("Itemdescription");Cell cellIMEIHeader = rowHeader.createCell(1);cellIMEIHeader.setCellValue("IMEI");// 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);}}Set<String> keyset = priceDropAmount.keySet();int rownum = 1;for (String key : keyset) {Row row = sheet.createRow(rownum++);String objArr = priceDropAmount.get(key);int cellnum = 0;Cell cell = row.createCell(cellnum++);cell.setCellValue(priceDropAmount.get(key));Cell cell1 = row.createCell(cellnum++);cell1.setCellValue(key);}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);}}public static void writeDailySaleReportVsTargetForPartner(Map<Integer, String> targetIdAndTargetNameMap,Map<Integer, Float> targetIdAndtargetValuesMap, Map<Integer, Double> targetIdAndsaleValuesMap,Map<Integer, CustomRetailer> targetIdAndCustomRetailerMap, Map<Integer, String> targetIdAndSlabNamesMap,Map<Integer, Double> targetIdAndsaleValuesPercentageMap,Map<Integer, Float> targetIdAndtargetdailyAverageSaleMap,Map<Integer, Double> targetIdAndRemainingTargetMap, Map<Integer, Double> targetIdAndtodayAchievementsMap,Map<Integer, String> targetIdAndSalesHeadMap, OutputStream outputStream) {SXSSFWorkbook workbook = new SXSSFWorkbook();// CreationHelper createHelper = workbook.getCreationHelper();SXSSFSheet sheet = workbook.createSheet("DailySaleReports");sheet.trackAllColumnsForAutoSizing();Row rowHeader = sheet.createRow(0);Cell cellStoreName = rowHeader.createCell(0);cellStoreName.setCellValue("Store Name");Cell cellBusiness = rowHeader.createCell(1);cellBusiness.setCellValue("Business Manager");Cell cellAssistant = rowHeader.createCell(2);cellAssistant.setCellValue("Assistant Manager");Cell cellSchemeName = rowHeader.createCell(3);cellSchemeName.setCellValue("Scheme Name");Cell cellTargetValue = rowHeader.createCell(4);cellTargetValue.setCellValue("Scheme Target");Cell cellMonthlySaleValue = rowHeader.createCell(5);cellMonthlySaleValue.setCellValue("Total Achievement");Cell cellMonthlySaleValuePercentage = rowHeader.createCell(6);cellMonthlySaleValuePercentage.setCellValue("Today Achievement Percentage");Cell cellDailyTargetValue = rowHeader.createCell(7);cellDailyTargetValue.setCellValue("Daily Target");Cell cellTodayAchievement = rowHeader.createCell(8);cellTodayAchievement.setCellValue("Today Achievement");Cell cellRemainingTarget = rowHeader.createCell(9);cellRemainingTarget.setCellValue("Remaining Target");Cell cellEligibility = rowHeader.createCell(10);cellEligibility.setCellValue("Eligibility");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);}}int rownum = 1;for (Integer targetId : targetIdAndTargetNameMap.keySet()) {Row row = sheet.createRow(rownum++);int cellnum = 0;Cell cellPartner = row.createCell(cellnum++);if (targetIdAndCustomRetailerMap.get(targetId) != null) {cellPartner.setCellValue(targetIdAndCustomRetailerMap.get(targetId).getBusinessName());} else {cellPartner.setCellValue("-");}Cell cellBusinessManager = row.createCell(cellnum++);if (targetIdAndSalesHeadMap.get(targetId) != null) {if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {cellBusinessManager.setCellValue("Mohinder");} else {cellBusinessManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));}} else {cellBusinessManager.setCellValue("-");}Cell cellAssistantManager = row.createCell(cellnum++);if (targetIdAndSalesHeadMap.get(targetId) != null) {if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {cellAssistantManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));} else {cellAssistantManager.setCellValue("");}} else {cellAssistantManager.setCellValue("");}Cell cellTargetName = row.createCell(cellnum++);if (targetIdAndTargetNameMap.get(targetId) == null || targetIdAndTargetNameMap.get(targetId) == "") {cellTargetName.setCellValue("");} else {cellTargetName.setCellValue(targetIdAndTargetNameMap.get(targetId));}Cell cellMTDTargetValue = row.createCell(cellnum++);if (targetIdAndtargetValuesMap.get(targetId) != null) {cellMTDTargetValue.setCellValue(targetIdAndtargetValuesMap.get(targetId));} else {cellMTDTargetValue.setCellValue("-");}Cell cellMTDAchievement = row.createCell(cellnum++);if (targetIdAndsaleValuesMap.get(targetId) != null) {cellMTDAchievement.setCellValue(targetIdAndsaleValuesMap.get(targetId));} else {cellMTDAchievement.setCellValue(0);}Cell cellMTDAchievementPercentage = row.createCell(cellnum++);if (targetIdAndsaleValuesPercentageMap.get(targetId) != null) {cellMTDAchievementPercentage.setCellValue(targetIdAndsaleValuesPercentageMap.get(targetId) + "%");} else {cellMTDAchievementPercentage.setCellValue(0 + "%");}Cell cellDailyTargetValue1 = row.createCell(cellnum++);if (targetIdAndtargetdailyAverageSaleMap.get(targetId) != null) {String formatting = FormattingUtils.formatDecimal(targetIdAndtargetdailyAverageSaleMap.get(targetId));cellDailyTargetValue1.setCellValue(formatting);} else {cellDailyTargetValue1.setCellValue(0);}Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);if (targetIdAndtodayAchievementsMap.get(targetId) != null) {cellTodayAchieveMentSaleValue.setCellValue(targetIdAndtodayAchievementsMap.get(targetId));} else {cellTodayAchieveMentSaleValue.setCellValue(0);}Cell cellRemaining = row.createCell(cellnum++);if (targetIdAndRemainingTargetMap.get(targetId) != null) {cellRemaining.setCellValue(targetIdAndRemainingTargetMap.get(targetId));} else {cellRemaining.setCellValue(0);}Cell cellEligible = row.createCell(cellnum++);if (targetIdAndSlabNamesMap.get(targetId) != null) {cellEligible.setCellValue(targetIdAndSlabNamesMap.get(targetId));} else {cellEligible.setCellValue("-");}}for (int index = 0; index < targetIdAndsaleValuesMap.size(); index++) {sheet.autoSizeColumn(index);}try {workbook.write(outputStream);workbook.close();} catch (IOException ioException) {LOGGER.error("Unable to generate excel file", ioException);}}public static List<WalletHistoryModel> parseWalletBulkCredit(InputStream inputStream) throws Exception {List<WalletHistoryModel> walletHistoryModels = new ArrayList<>();try (XSSFWorkbook 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);WalletHistoryModel walletHistoryModel = new WalletHistoryModel();if (row.getCell(1) != null && row.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {walletHistoryModel.setFofoId((int) row.getCell(1).getNumericCellValue());} else {throw new ProfitMandiBusinessException("Invalid Fofo Id", "row number " + rowNumber,"row number " + rowNumber);}if (row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.STRING) {WalletReferenceType referenceType = WalletReferenceType.valueOf(row.getCell(2).getStringCellValue());if (!BULK_WALLET_REFERENCES.contains(referenceType)) {throw new ProfitMandiBusinessException("Invalid Reference type", "row number " + rowNumber,"row number " + rowNumber);}walletHistoryModel.setWalletReferenceType(WalletReferenceType.valueOf(row.getCell(2).getStringCellValue()));} else {throw new ProfitMandiBusinessException("Invalid ReferenceType", "row number " + rowNumber,"row number " + rowNumber);}if (row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {Date businessDate = row.getCell(3).getDateCellValue();LocalDateTime businessDateTime = LocalDateTime.ofInstant(businessDate.toInstant(), ZoneId.systemDefault());walletHistoryModel.setBusinessDate(businessDateTime);;} else {throw new ProfitMandiBusinessException("Invalid Business Date", "row number " + rowNumber,"row number " + rowNumber);}if (row.getCell(4) != null && row.getCell(4).getCellTypeEnum() == CellType.NUMERIC) {double amount = row.getCell(4).getNumericCellValue();if (amount < 0) {throw new ProfitMandiBusinessException("Invalid Amount, only positive values","row number " + rowNumber, "row number " + rowNumber);}walletHistoryModel.setAmount(amount);} else {throw new ProfitMandiBusinessException("Invalid Amount", "row number " + rowNumber,"row number " + rowNumber);}if (row.getCell(5) != null && row.getCell(5).getCellTypeEnum() == CellType.STRING) {String transactionType = row.getCell(5).getStringCellValue().toLowerCase();if (transactionType.equals("dr")) {walletHistoryModel.setAmount(-walletHistoryModel.getAmount());} else if (!transactionType.equals("cr")) {throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,"row number " + rowNumber);}} else {throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,"row number " + rowNumber);}if (row.getCell(6) != null && row.getCell(6).getCellTypeEnum() == CellType.STRING) {String description = row.getCell(6).getStringCellValue();walletHistoryModel.setDescription(description);} else {throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,"row number " + rowNumber);}if (row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) {int reference = (int)row.getCell(7).getNumericCellValue();walletHistoryModel.setReference(reference);} else {throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,"row number " + rowNumber);}walletHistoryModels.add(walletHistoryModel);}return walletHistoryModels;}}public static String toAlphabet(int number) {StringBuffer sb = new StringBuffer();boolean loop = true;while (loop) {sb.append(ALPHABETS.get(number % 26));number = number / 26;loop = number > 0;}return sb.reverse().toString();}public static String getCellValue(Cell cell) {if (cell == null) {return "Null cell";}switch (cell.getCellTypeEnum()) {case STRING:return cell.getStringCellValue().trim();case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {return cell.getDateCellValue().toString();} else {return Double.toString(cell.getNumericCellValue());}case BOOLEAN:return Boolean.toString(cell.getBooleanCellValue());case FORMULA:return cell.getCellFormula();default:return "";}}public static void writePurchaseInvoicesReport(List<PurchaseInvoiceModel> purchaseInvoiceModels, OutputStream outputStream) {SXSSFWorkbook workbook = new SXSSFWorkbook();// CreationHelper createHelper = workbook.getCreationHelper();SXSSFSheet sheet = workbook.createSheet("PurchaseInvoicesReport");sheet.trackAllColumnsForAutoSizing();Row rowHeader = sheet.createRow(0);Cell cellPoIdHeader = rowHeader.createCell(0);cellPoIdHeader.setCellValue("PoId");Cell cellPoDateHeader = rowHeader.createCell(1);cellPoDateHeader.setCellValue("PoDate");Cell cellGrnNoHeader = rowHeader.createCell(2);cellGrnNoHeader.setCellValue("GRN_No");Cell cellGrnDateHeader = rowHeader.createCell(3);cellGrnDateHeader.setCellValue("GRN_Date");Cell cellSupplierNameHeader = rowHeader.createCell(4);cellSupplierNameHeader.setCellValue("Supplier_Name");Cell cellSupplierStateHeader = rowHeader.createCell(5);cellSupplierStateHeader.setCellValue("Supplier_State");Cell cellSupplierGSTINHeader = rowHeader.createCell(6);cellSupplierGSTINHeader.setCellValue("Supplier_GSTIN");Cell cellInvoiceNumberHeader = rowHeader.createCell(7);cellInvoiceNumberHeader.setCellValue("Invoice_Number");Cell cellInvoiceDateHeader = rowHeader.createCell(8);cellInvoiceDateHeader.setCellValue("Invoice_Date");Cell cellSupplierWarehouseNameHeader = rowHeader.createCell(9);cellSupplierWarehouseNameHeader.setCellValue("Warehouse_Name");Cell cellSupplierWarehouseIdHeader = rowHeader.createCell(10);cellSupplierWarehouseIdHeader.setCellValue("Warehouse_Id");Cell cellReceivedFromHeader = rowHeader.createCell(11);cellReceivedFromHeader.setCellValue("Received_By");Cell cellItemIdHeader = rowHeader.createCell(12);cellItemIdHeader.setCellValue("Item_Id");Cell cellBrandHeader = rowHeader.createCell(13);cellBrandHeader.setCellValue("Brand");Cell cellModelNameHeader = rowHeader.createCell(14);cellModelNameHeader.setCellValue("Model_Name");Cell cellModelNumberHeader = rowHeader.createCell(15);cellModelNumberHeader.setCellValue("Model_Number");Cell cellColorHeader = rowHeader.createCell(16);cellColorHeader.setCellValue("Color");Cell cellSystemPriceHeader = rowHeader.createCell(17);cellSystemPriceHeader.setCellValue("System_Price");Cell cellUnitPriceHeader = rowHeader.createCell(18);cellUnitPriceHeader.setCellValue("Unit_Price");Cell cellBasicRateHeader = rowHeader.createCell(19);cellBasicRateHeader.setCellValue("Basic_Rate");Cell cellQuantityHeader = rowHeader.createCell(20);cellQuantityHeader.setCellValue("Quantity");Cell cellInitialQuantityHeader = rowHeader.createCell(21);cellInitialQuantityHeader.setCellValue("Initial_Quantity");Cell cellAmendedQuantityHeader = rowHeader.createCell(22);cellAmendedQuantityHeader.setCellValue("Amended_Quantity");Cell cellTotalBasicValueHeader = rowHeader.createCell(23);cellTotalBasicValueHeader.setCellValue("Total_Basic_Value");Cell cellSGSTHeader = rowHeader.createCell(24);cellSGSTHeader.setCellValue("SGST%");Cell cellSGSTAmountHeader = rowHeader.createCell(25);cellSGSTAmountHeader.setCellValue("SGST_Amount");Cell cellCGSTHeader = rowHeader.createCell(26);cellCGSTHeader.setCellValue("CGST%");Cell cellCGSTAmountHeader = rowHeader.createCell(27);cellCGSTAmountHeader.setCellValue("CGST_Amount");Cell cellIGSTHeader = rowHeader.createCell(28);cellIGSTHeader.setCellValue("IGST%");Cell cellIGSTAmountHeader = rowHeader.createCell(29);cellIGSTAmountHeader.setCellValue("IGST_Amount");Cell cellAmountWithGstHeader = rowHeader.createCell(30);cellAmountWithGstHeader.setCellValue("Total_Invoice_Value_Amount");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 < purchaseInvoiceModels.size(); index++) {PurchaseInvoiceModel purchaseInvoiceModel = purchaseInvoiceModels.get(index);Row rowValues = sheet.createRow(index + 1);Cell cellPoId = rowValues.createCell(0);cellPoId.setCellValue(purchaseInvoiceModel.getPoId());Cell cellPoDate = rowValues.createCell(1);cellPoDate.setCellValue(String.valueOf(purchaseInvoiceModel.getFormattedPoDate()));Cell cellGRNNo = rowValues.createCell(2);cellGRNNo.setCellValue(purchaseInvoiceModel.getGrnNo());Cell cellGRNDate = rowValues.createCell(3);cellGRNDate.setCellValue(String.valueOf(purchaseInvoiceModel.getFormattedGrnDate()));Cell cellSupplierName = rowValues.createCell(4);cellSupplierName.setCellValue(purchaseInvoiceModel.getSupplierName());Cell cellSupplierState = rowValues.createCell(5);cellSupplierState.setCellValue(purchaseInvoiceModel.getSupplierState());Cell cellSupplierGSTIN = rowValues.createCell(6);cellSupplierGSTIN.setCellValue(purchaseInvoiceModel.getSupplierGstIn());Cell cellInvoiceNumber = rowValues.createCell(7);cellInvoiceNumber.setCellValue(purchaseInvoiceModel.getInvoiceNumber());Cell cellInvoiceDate = rowValues.createCell(8);cellInvoiceDate.setCellValue(String.valueOf(purchaseInvoiceModel.getFormattedInvoiceDate()));Cell cellSupplierWarehouseName = rowValues.createCell(9);cellSupplierWarehouseName.setCellValue(purchaseInvoiceModel.getWarehouseName());Cell cellSupplierWarehouseId = rowValues.createCell(10);cellSupplierWarehouseId.setCellValue(purchaseInvoiceModel.getWarehouseId());Cell cellReceivedFrom = rowValues.createCell(11);cellReceivedFrom.setCellValue(purchaseInvoiceModel.getReceivedFrom());Cell cellItemId = rowValues.createCell(12);cellItemId.setCellValue(purchaseInvoiceModel.getItemId());Cell cellBrand = rowValues.createCell(13);cellBrand.setCellValue(purchaseInvoiceModel.getBrand());Cell cellModelName = rowValues.createCell(14);cellModelName.setCellValue(purchaseInvoiceModel.getModelName());Cell cellModelNumber = rowValues.createCell(15);cellModelNumber.setCellValue(purchaseInvoiceModel.getModelNumber());Cell cellColor = rowValues.createCell(16);cellColor.setCellValue(purchaseInvoiceModel.getColor());Cell cellSystemPrice = rowValues.createCell(17);cellSystemPrice.setCellValue(purchaseInvoiceModel.getSystemPrice());Cell cellUnitPrice = rowValues.createCell(18);cellUnitPrice.setCellValue(purchaseInvoiceModel.getUnitPrice());Cell cellBasicRate = rowValues.createCell(19);cellBasicRate.setCellValue(purchaseInvoiceModel.getBasicRate());Cell cellQuantity = rowValues.createCell(20);cellQuantity.setCellValue(purchaseInvoiceModel.getQuantity());Cell cellInitialQuantity = rowValues.createCell(21);cellInitialQuantity.setCellValue(purchaseInvoiceModel.getInitialQty());Cell cellAmendedQuantity = rowValues.createCell(22);cellAmendedQuantity.setCellValue(purchaseInvoiceModel.getAmendedQty());Cell cellTotalBasicValue = rowValues.createCell(23);cellTotalBasicValue.setCellValue(purchaseInvoiceModel.getTotalBasic());Cell cellSGSTPercent = rowValues.createCell(24);cellSGSTPercent.setCellValue(purchaseInvoiceModel.getSgstRate());Cell cellSGSTAmount = rowValues.createCell(25);cellSGSTAmount.setCellValue(purchaseInvoiceModel.getSgstAmount());Cell cellCGSTPercent = rowValues.createCell(26);cellCGSTPercent.setCellValue(purchaseInvoiceModel.getCgstRate());Cell cellCGSTAmount = rowValues.createCell(27);cellCGSTAmount.setCellValue(purchaseInvoiceModel.getCgstAmount());Cell cellIGSTPercent = rowValues.createCell(28);cellIGSTPercent.setCellValue(purchaseInvoiceModel.getIgstRate());Cell cellIGSTAmount = rowValues.createCell(29);cellIGSTAmount.setCellValue(purchaseInvoiceModel.getIgstAmount());Cell cellAmountWithGst = rowValues.createCell(30);cellAmountWithGst.setCellValue(purchaseInvoiceModel.getTotalInvoiceValueAmount());}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);}}}