Rev 22470 | Rev 22521 | 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.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.ProfitMandiConstants;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 Throwable {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 && HSSFDateUtil.isCellDateFormatted(row.getCell(10))){Date date = row.getCell(10).getDateCellValue();LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());tagListing.setStartDate(startDate);}else{ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(START_DATE, row.getCell(10).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 OutputStream 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("NA");cellQuantity.setCellValue("NA");}}}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);}return null;}}