Data input replace for spreadsheet using images in reference

[bearjustbarely]

Trying to create a java code to automate data input in a cell based on text input across a sheet, replacing that text with an image that is in a page titled "Reference"

Created a sample syntax to give an example of what I am looking to create, however I am unfamiliar with coding for java. Used a range for the sample, however if the code needs to exist outside of the range, I have no issue with that.

ARRAYFORMULA(
=ifs(
D2:Y158 = "Y"; Reference!B2;
D2:Y158 = "N"; Reference!B3;
D2:Y158 = "-"; Reference!B4;
true; iferror(1/0)

If someone more familiar knows of a code that can be implemented to automate this with these 3 text inputs as well as other text inputs that I might need to add later, I would be so greatly appreciated. If not, if someone more familiar could help me create one, that would be incredible too.

Thank you,

Bea

[Wenly.Zhang]

Hello,

Thank you for your inquiry.
If you want to replace the cell value in the sheet with the specified image, you can use the following code:

Code: Select all

 String sheetName = "Sheet1";
        Workbook workbook = new Workbook();
        workbook.loadFromFile("F:\\test.xlsx");
        Worksheet sheet = workbook.getWorksheets().get(sheetName);

        int lastRow = sheet.getLastRow();
        int lastColumn = sheet.getLastColumn();
        //specified range
        for (int row = 1; row <= lastRow; row++) {
            for (int col = 1; col <= lastColumn; col++) {
                CellRange range = sheet.getCellRange(row, col);
                String cellValue = range.getText();
      //Specify the image path to replace
                if (cellValue.equals("Y")) {
                      replaceCellWithImage(sheet,range, "F:\\image1.jpg");
                } else if (cellValue.equals("N")) {
                    replaceCellWithImage(sheet, range, "F:\\image2.jpg");
                } else if (cellValue.equals("-")) {
                    replaceCellWithImage(sheet, range, "F:\\image3.jpg");
                }
                workbook.saveToFile("F:\\result.xlsx");

            }

        }
    }
    private static void replaceCellWithImage(Worksheet sheet,CellRange range, String imagePath){
        range.setText("");
        PicturesCollection pictures = sheet.getPictures();
        ExcelPicture picture = pictures.add(range.getRow(), range.getColumn(), imagePath);
        //Set the size of the image based on the width and height of the cells
        int pictureWidth = (int) range.getColumnWidth();
        int pictureHeight = (int) range.getRowHeight();
        picture.setWidth(pictureWidth);
        picture.setHeight(pictureHeight);
    }

If this is not the code you expected, please provide a description or screenshot of your expected results. You could attach them here or send them to us via email ([email protected]). Thanks in advance.
Sincerely,
Wenly
E-iceblue support team