Dear Warren,

We are trying to use a named range in a drop down (data validation list). The problem is that it does not work unless we first save and reopen the file with Excel. There is something wrong with the inital file produced by Aspose Cells. The following code produces a file with this behaviour:

public static void validationListWithNamedRange(String saveAs) {
       try{
           Workbook wb = new Workbook();

           //create first sheet to write the options into
           Worksheet sheet1 = wb.getWorksheets().addSheet("Named Options");
           Cells cells = sheet1.getCells();

           int col = 0;
           for (; col < 5; col++) {
               Cell cell = cells.getCell(0, col);

               cell.setValue("option"+col);
           }

           //create a named range over the options
           cells.createNamedRange("theRange", 0, 0, 0, col - 1);

           //create a new sheet for the dropdown/validation list
           Worksheet sheet2 = wb.getWorksheets().addSheet("Dropdown");
           Validations validations = sheet2.getValidations();
           Validation list = validations.get(validations.add());

           //use the named range as formula
           list.setFormula1("=theRange");
           list.setType(ValidationType.LIST);
           list.setInCellDropDown(true);
           list.setIgnoreBlank(true);
           list.addCellArea(new CellArea(0, 0, 0, 0));


           File file = new File(saveAs);

           if (!file.exists()) {
               file.getParentFile().mkdirs();
           }

           OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
           wb.save(out, FileFormatType.EXCEL2003);

           out.flush();
           out.close();
       }
       catch(Exception e) {
           e.printStackTrace();
       }
   }

Thanks for looking into this issue.

Best regards,
Ulf Ekström

Dear Ulf,

Please try this fix.

---

Warren Zhang
Developer
Aspose Nanjing Team
About Us
Contact Us

Dear Warren,

Your fix works fine. Thanks for the quick support!

Best regards,
Ulf