excel process.java

warehouse_import_file_settings

warehouseid       fieldMapping

100.              {productName: "pName", "productId": "pid", subCategory: "subCategory" }


------
@Entity
class warehouseImportFileSettings {
  integer warehouseID
  JSONNode filedMappings
}

// warehouseImportFileSettingsRepository

@Entity
class Product {
  String productName;
  Integer productId;
  String parentCategory;
  String subCategory;
}


WarehouseImportFileSettingsRepository warehouseImportFileSettingsRepository;
// method to handle excel file
public void processFile(Multipart file,  integer warehouseId) {


    // 1. figure out mappings for app to client fields

    String[] appFields = {
        "productName",
        "productId",
        "parentCategory",
        "subCategory",
    }

    warehouseImportFileSettings fileSettings = warehouseImportFileSettingsRepository.findMappings(warehouseId);
    JSONNode clientFieldMappings = fileSettings.getFiledMappings(); // {productName: "pName", "productId": "pid" }
    
    Map<String, String> clientToAppFieldMapping = new HashMap();
    for (String appField: appFields) {
        // if appField is productName 
        String clientField = clientFieldMappings.get(appField);  // clientField will be pName
        if (clientField != null) {
            clientToAppFieldMapping.put(clientField, appField);
        }
    }

    /*
     * {
     *   pName: 'productName',
     *   pId: 'productId'
     * }
     */


     List<String> excelFields = clientToAppFieldMapping.keySet().toList();

    Workbook workbook = WorkbookFactory.create(file);
    Sheet sheet = workbook.getSheetAt(0);
     
    List<ImportProductDTO> productDTOS = new ArrayList<>();


    // figure out file extension 
    String fileExtension = FilenameUtils.getExtension(file.getOriginalFilename());

    if (fileExtension.equals("xlsx")) {
        for (Row row : sheet) {
            ImportProductDTO product = new ImportProductDTO();
            
            for (Cell cell : row) {
                String cellName = CellReference.convertNumToColString(cell.getColumnIndex());
                String appField = clientToAppFieldMapping.get(cellName);
                
                if (appField != null) {
                    switch (appField) {
                        case "productName":
                            product.setProductName(cell.getStringCellValue());
                            break;
                        case "productId":
                            product.setProductId((int) cell.getNumericCellValue());
                            break;
                        case "parentCategory":
                            product.setParentCategory(cell.getStringCellValue());
                            break;
                        case "subCategory":
                            product.setSubCategory(cell.getStringCellValue());
                            break;
                    }
                }
            }
            // Save or process the product object as needed
            productDTOS.add(product);
    } else {
        try (CSVReader reader = new CSVReader(new InputStreamReader(file.getInputStream()))) {
            String[] headers = reader.readNext();
            Map<Integer, String> colIndexToAppField = new HashMap<>();
            for (int i = 0; i < headers.length; i++) {
                String appField = clientToAppFieldMapping.get(headers[i]);
                if (appField != null) {
                    colIndexToAppField.put(i, appField);
                }
            }
            String[] row;
            while ((row = reader.readNext()) != null) {
                ImportProductDTO product = new ImportProductDTO();
                for (int i = 0; i < row.length; i++) {
                    String appField = colIndexToAppField.get(i);
                    if (appField != null) {
                        switch (appField) {
                            case "productName":
                                product.setProductName(row[i]);
                                break;
                            case "productId":
                                product.setProductId(Integer.parseInt(row[i]));
                                break;
                            case "parentCategory":
                                product.setParentCategory(row[i]);
                                break;
                            case "subCategory":
                                product.setSubCategory(row[i]);
                                break;
                        }
                    }
                }
                productDTOS.add(product);
            }
    }

}


// download sample template file using fieldMapping from warehouseImportFileSettings
public void downloadSampleTemplate(Integer warehouseId) {
    warehouseImportFileSettings fileSettings = warehouseImportFileSettingsRepository.findMappings(warehouseId);
    JSONNode clientFieldMappings = fileSettings.getFiledMappings(); // {productName: "pName", "productId": "pid", "subCategory": "subCat" }

    List<String> headers = new ArrayList<>(clientFieldMappings.values());
    
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Sample Template");
    
    Row headerRow = sheet.createRow(0);
    for (int i = 0; i < headers.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(headers.get(i));
        // set default options for dropdowns if needed
        if (headers.get(i).equals("subCategory")) {
            // Assuming you have a predefined list of subcategories
            String[] subCategories = {"Electronics", "Clothing", "Books"};
            DataValidationHelper validationHelper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(subCategories);
            CellRangeAddressList addressList = new CellRangeAddressList(1, 100, 0, 0); // Assuming you want to apply it to the first column
            DataValidation validation = validationHelper.createValidation(constraint, addressList);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }
    }
    
    // Write the workbook to a file or output stream
    try (FileOutputStream fileOut = new FileOutputStream("sample_template.xlsx")) {
        workbook.write(fileOut);
    } catch (IOException e) {
        e.printStackTrace();
    }
}