Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheet

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheet.

Prototype

@Override
public XSSFSheet getSheet(String name)

Source Link

Document

Get sheet with the given name (case insensitive match)

Usage

From source file:nl.detoren.ijsco.io.ExcelExport.java

License:Open Source License

public void exportGroepen(Groepen groepen) {
    String password = "abcd";
    try {/*from   w w w . ja  v  a2s.c  om*/
        if (groepen == null)
            return;
        // sheetindx geeft index in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte 
        int[] sheetindx = new int[] { -1, -1, -1, -1, 5, -1, 4, -1, 3, -1, 2, -1, 1, -1, 0, -1, -1, -1 };
        // columnsize geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte
        int[] columnsize = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1,
                -1 };
        // pagelngth geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte
        int[] pagelngth = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1,
                -1 };
        int sheet2row = 2;
        int sheet3row = 2;
        FileInputStream file = new FileInputStream("Indeling.xlsm");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFCellStyle style1 = workbook.createCellStyle();
        style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(180, 180, 180)));
        XSSFCellStyle my_style = workbook.createCellStyle();
        XSSFColor my_foreground = new XSSFColor(Color.ORANGE);
        XSSFColor my_background = new XSSFColor(Color.RED);
        my_style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        my_style.setFillForegroundColor(my_foreground);
        my_style.setFillBackgroundColor(my_background);
        XSSFSheet sheet2 = workbook.getSheet("Groepsindeling");
        XSSFSheet sheet3 = workbook.getSheet("Deelnemerslijst");
        updateCell(sheet3, sheet3row, 0, "Naam", style1);
        updateCell(sheet3, sheet3row, 1, "KNSB nr", style1);
        updateCell(sheet3, sheet3row, 2, "rating", style1);
        updateCell(sheet3, sheet3row, 3, "groep", style1);
        sheet3row++;
        for (Groep groep : groepen) {
            logger.log(Level.INFO, "Exporteer groep : " + groep.getNaam());
            XSSFSheet sheet = workbook.cloneSheet(sheetindx[groep.getGrootte()], groep.getNaam());
            updateCell(sheet, 0, 6, groep.getNaam());
            updateCell(sheet2, sheet2row, 1, groep.getNaam());
            sheet2row++;
            updateCell(sheet2, sheet2row, 0, "nr", style1);
            updateCell(sheet2, sheet2row, 1, "Naam", style1);
            updateCell(sheet2, sheet2row, 2, "KNSB nr", style1);
            updateCell(sheet2, sheet2row, 3, "rating", style1);
            sheet2row++;
            for (int i = 0; i < groep.getGrootte(); i++) {
                updateCell(sheet, 3 + i, 2, groep.getSpeler(i).getNaam());
                updateCell(sheet, 3 + i, 3, groep.getSpeler(i).getKnsbnummer());
                updateCell(sheet, 3 + i, 5, groep.getSpeler(i).getRating());
                updateCell(sheet2, sheet2row, 0, i + 1);
                updateCell(sheet2, sheet2row, 1,
                        "'" + sheet.getSheetName() + "'!"
                                + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i),
                        true);
                updateCell(sheet2, sheet2row, 2,
                        "'" + sheet.getSheetName() + "'!"
                                + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i),
                        true);
                updateCell(sheet2, sheet2row, 3,
                        "'" + sheet.getSheetName() + "'!"
                                + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i),
                        true);
                if (groep.getSpeler(i).getNaam() != "Bye") {
                    updateCell(sheet3, sheet3row, 0,
                            "'" + sheet.getSheetName() + "'!"
                                    + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i),
                            true);
                    updateCell(sheet3, sheet3row, 1,
                            "'" + sheet.getSheetName() + "'!"
                                    + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i),
                            true);
                    updateCell(sheet3, sheet3row, 2,
                            "'" + sheet.getSheetName() + "'!"
                                    + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i),
                            true);
                    updateCell(sheet3, sheet3row, 3, groep.getNaam());
                }
                sheet2row++;
                sheet3row++;
            }
            sheet2row++;
            sheet.setForceFormulaRecalculation(true);
            // Set print margins
            XSSFPrintSetup ps = sheet.getPrintSetup();
            ps.setLandscape(true);
            ps.setFitWidth((short) 1);
            sheet.setFitToPage(true);
            sheet.setAutobreaks(false);
            workbook.setPrintArea(workbook.getSheetIndex(sheet), 1, 26, 0, pagelngth[groep.getGrootte()]);
            sheet.setColumnBreak(18);
            sheet.protectSheet(password);
            sheet.enableLocking();
        }
        XSSFSheet sheet4 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (naam)");
        sortSheet(sheet4, 1, 3, 62);
        //XSSFSheet sheet5 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (rating)");
        //sortSheet(sheet5, 1,4);
        sheet2.protectSheet(password);
        sheet3.protectSheet(password);
        sheet4.protectSheet(password);
        //sheet5.protectSheet(password);
        // Remove template sheets
        for (int i = 0; i < 6; i++) {
            workbook.removeSheetAt(0);
        }

        // Close input file
        file.close();
        // Store Excel to new file
        String filename = "Indeling resultaat.xlsm";
        File outputFile = new File(filename);
        FileOutputStream outFile = new FileOutputStream(outputFile);
        workbook.write(outFile);
        // Close output file
        workbook.close();
        outFile.close();
        // And open it in the system editor
        Desktop.getDesktop().open(outputFile);
    } catch (IOException e) {
        logger.log(Level.SEVERE, "Fout bij maken indeling excel : " + e.getMessage());

    }
}

From source file:Opm_Package.Excell_Dublicating.java

public void createExcel(ArrayList<Object[]> allobj, int number, String excelFilePath, String sheetName)
        throws IOException {
    FileOutputStream fos = null;//from   w  w  w .j  a va 2 s .co  m
    try {
        XSSFWorkbook workbook = null;
        if (new File(excelFilePath).createNewFile()) {
            workbook = new XSSFWorkbook();
        } else {
            FileInputStream pfs = new FileInputStream(new File(excelFilePath));
            workbook = new XSSFWorkbook(pfs);
        }
        if (workbook.getSheet(sheetName) == null) {
            fos = new FileOutputStream(excelFilePath);
            sheet = workbook.createSheet(sheetName);
            ///
            int rowid2 = sheet.getLastRowNum();
            int x;
            for (x = 0; x < allobj.size(); x++) {//Looping thru the array list to pick the objects...
                rows = sheet.createRow(rowid2++);
                Object[] objectArr = allobj.get(x);
                int cellid = 0;
                for (Object obj : objectArr) {//Looping inside the object...
                    Cell cells = rows.createCell(cellid++);
                    if (obj instanceof String) {
                        cells.setCellValue((String) obj);
                    } else if (obj instanceof Integer) {
                        cells.setCellValue((int) obj);
                    } else if (obj instanceof Double) {
                        cells.setCellValue((double) obj);
                    }
                } // End of for loop for object
            } //End of for loop for arraylist of object....

            workbook.write(fos);
            System.out.println("Excel File Created is : " + excelFilePath + " With sheet name :" + sheetName);
            System.out.println("\n\n");
        }

    } catch (IOException e) {
        throw e;
    } finally {
        if (fos != null) {
            fos.close();
        }
    }
}

From source file:opn.greenwebs.FXMLDocumentController.java

private File createStockFile(List<ItemDB> list) {
    int nSize = list.size();
    XSSFWorkbook wbs = createStockWorkbook();

    XSSFSheet sheetStock = wbs.getSheet("Digital Version");
    List<XSSFTable> lTables = sheetStock.getTables();
    // Create a FormulaEvaluator to use
    FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper()
            .createFormulaEvaluator();//from   www .j  av  a 2 s.co  m
    File fStock = createFilename("STK", "");
    Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault()));
    Row rowed = sheetStock.getRow(6);
    Cell celled = rowed.getCell(10);
    CellStyle cellStyle = celled.getCellStyle();
    XSSFFont font = sheetStock.getWorkbook().createFont();
    font.setFontHeight(14);
    cellStyle.setFont(font);
    celled.setCellValue(Date.from(instant));
    celled.setCellStyle(cellStyle);
    rowed = sheetStock.getRow(10);
    celled = rowed.getCell(2);
    celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5));
    if (!lTables.isEmpty()) {
        XSSFTable table = lTables.get(0);
        table.getCTTable()
                .setRef(new CellRangeAddress(table.getStartCellReference().getRow(),
                        table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(),
                        table.getEndCellReference().getCol()).formatAsString());
        XSSFRow row;
        XSSFCell cell;
        font = sheetStock.getWorkbook().createFont();
        font.setFontHeight(14);
        int nCellRef = table.getStartCellReference().getRow() + 1;
        for (ItemDB itemdb : list) {
            row = sheetStock.createRow(nCellRef++);
            cell = row.createCell(0);
            cellStyle = cell.getCellStyle();
            cell.setCellValue(itemdb.getDblQty());
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(1);
            cell.setCellValue(itemdb.getStrMfr());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(2);
            cell.setCellValue(itemdb.getStrSKU());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(3);
            cell.setCellValue(itemdb.getStrDescrip());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(4);
            cell.setCellValue(itemdb.getStrSupplier());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(5);
            cell.setCellValue(itemdb.getStrSupPart());
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //cell.setCellStyle(cellStyle);
            cell = row.createCell(6);
            cell.setCellValue(itemdb.getDblSalePrice());
            cell.setCellStyle(cellStyle);
            cell = row.createCell(7);
            cell.setCellValue(itemdb.getDblCost());
            cell.setCellStyle(cellStyle);
            /*cell = row.createCell(8);
            cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            cell.setCellStyle(cellStyle);
            cell = row.createCell(9);
            cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")");
            mainWorkbookEvaluator.evaluateFormulaCell(cell);
            CellStyle style = wbs.createCellStyle();
            style.setDataFormat(wbs.createDataFormat().getFormat("0%"));
            cell.setCellStyle(style);*/
            mainWorkbookEvaluator.evaluateAll();
        }

        try {
            try (FileOutputStream fileOut = new FileOutputStream(fStock)) {
                wbs.write(fileOut);
                return fStock;
            }
        } catch (FileNotFoundException ex) {
            logger.info(ex.getLocalizedMessage());
        } catch (IOException ex) {
            logger.info(ex.getLocalizedMessage());
        }
    }
    return null;
}

From source file:opn.greenwebs.FXMLDocumentController.java

private void inject(XSSFWorkbook wb, Object obj, int row, int col) {
    if (wb == null) {
        System.out.println("wb is null");
    }//ww w .j  a va  2  s  . co  m
    XSSFSheet sheet = wb.getSheet("Digital Version");
    Row rowed = sheet.getRow(row);
    Cell cell = rowed.getCell(col);
    CellStyle cellStyle = cell.getCellStyle();
    XSSFFont font = sheet.getWorkbook().createFont();
    font.setFontHeight(14);
    cellStyle.setFont(font);
    if (obj instanceof String) {
        cell.setCellValue((String) obj);
    } else if (obj instanceof Boolean) {
        cell.setCellValue((Boolean) obj);
    } else if (obj instanceof Date) {
        CreationHelper createHelper = wb.getCreationHelper();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy"));
        cell.setCellValue((Date) obj);
    } else if (obj instanceof Double) {
        cell.setCellValue((Double) obj);
    } else if (obj instanceof Integer) {
        cell.setCellValue((int) obj);
    }
    cell.setCellStyle(cellStyle);
}

From source file:opn.greenwebs.FXMLDocumentController.java

private void injectStock(XSSFWorkbook wbs, Object obj, int row, int col) {

    Row rowed = wbs.getSheet("Digital Version").getRow(row);
    Cell cell = rowed.getCell(col);/*from w  ww. ja  va2  s  .co  m*/
    if (obj instanceof String) {
        cell.setCellValue((String) obj);
    } else if (obj instanceof Boolean) {
        cell.setCellValue((Boolean) obj);
    } else if (obj instanceof Date) {
        CellStyle cellStyle = wbs.getCellStyleAt(col);
        CreationHelper createHelper = wbs.getCreationHelper();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy"));
        cell.setCellValue((Date) obj);
        cell.setCellStyle(cellStyle);
    } else if (obj instanceof Double) {
        cell.setCellValue((Double) obj);
    } else if (obj instanceof Integer) {
        cell.setCellValue((int) obj);
    }
}

From source file:opn.greenwebs.FXMLDocumentController.java

private Object getCellData(File fSheet, int row, int coll) {
    try {//from  w  ww. ja  v a2 s .c o m
        XSSFWorkbook book = new XSSFWorkbook(fSheet);
        FormulaEvaluator eval = book.getCreationHelper().createFormulaEvaluator();
        XSSFSheet xSheet = book.getSheet("Digital Version");
        Cell celled = xSheet.getRow(row).getCell(coll);
        if (celled != null) {
            CellValue cellval = eval.evaluate(celled);
            if (cellval == null) {
                //System.out.println("cellval is null at line 918 " + fSheet.getAbsolutePath() + " " + row + " " + coll);
                return "";
            } else {
                switch (cellval.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    logger.info("got a blank");
                    return "";
                case Cell.CELL_TYPE_BOOLEAN:
                    logger.info("got a boolean");
                    return cellval.getBooleanValue();
                case Cell.CELL_TYPE_ERROR:
                    return cellval.getErrorValue();
                case Cell.CELL_TYPE_FORMULA:
                    return cellval.getStringValue();
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(celled)) {
                        return HSSFDateUtil.getJavaDate(cellval.getNumberValue());
                    } else {
                        return cellval.getNumberValue();
                    }
                case Cell.CELL_TYPE_STRING:
                    return cellval.getStringValue();
                default:
                    return "";
                }
            }
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
    }
    return "";
}

From source file:org.ado.minesync.translation.ExportFile.java

License:Open Source License

public void export(InputStream translations, File exportDirectory) throws IOException {

    Map<String, StringsConfigBuilder> map = null;
    XSSFWorkbook workbook = new XSSFWorkbook(translations);
    XSSFSheet sheet = workbook.getSheet("strings.xml");
    boolean init = false;

    for (Row cells : sheet) {

        if (!init) {
            map = createConfigurationBuilders(cells);
            init = true;//from ww w .  ja v  a  2 s.c o m

        } else {
            String code = cells.getCell(CODE_COL).getStringCellValue();

            if (StringUtils.isNotBlank(code)) {
                int i = 1;
                for (String lang : map.keySet()) {
                    Cell cell = cells.getCell(i++);
                    if (containsCode(cells)) {

                        if (isNotEmpty(cell)) {
                            addCode(map, sheet, code, cell);
                        }

                    } else {
                        map.get(lang).addGroup(code);
                    }
                }
            }
        }
    }
    buildStringFiles(exportDirectory, map);
}

From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java

License:Open Source License

public String dowmloadTemplate() {
    try {//from   w w  w . j a v a  2s.co m
        ClassLoader classLoader = this.getClass().getClassLoader();
        File file = new File(this.getClass().getResource("/template/participants-template.xlsm").getFile());

        String path = new File(".").getCanonicalPath();
        String real_path = path + "/src/main/resources/template/participants-template.xlsm";
        String path_ = config.getUploadsBaseFolder() + "/participants-template.xlsm";

        FileInputStream fileInput = new FileInputStream(path_);
        XSSFWorkbook wb = new XSSFWorkbook(fileInput);

        DataValidationConstraint constraintCountries = null;
        DataValidationConstraint constraintHighestDegree = null;
        DataValidationHelper validationHelper = null;

        Sheet sheet1 = wb.getSheetAt(0);
        XSSFSheet sheet2 = wb.getSheet("countries");

        String dataValidationCountryName = "countriesLis";

        // se traen los datos desde la DB con los que se desean crear las listas para los data validator y se rellenan los
        // arreglos que permitaran escribir los datos en el template
        List<LocElement> countryList = new ArrayList<>(locElementService.findAll().stream()
                .filter(le -> le.isActive() && (le.getLocElementType() != null)
                        && (le.getLocElementType().getId() == 2))
                .collect(Collectors.toList()));
        Collections.sort(countryList, (c1, c2) -> c1.getName().compareTo(c2.getName()));

        // arreglo usado para escribir la data de countries al template
        String[] countries = new String[countryList.size()];
        for (int i = 0; i < countryList.size(); i++) {
            countries[i] = countryList.get(i).getName() + " - " + countryList.get(i).getIsoAlpha2();
        }

        List<CapdevHighestDegree> highestDegreeList = new ArrayList<>(capdevHighestDegreeService.findAll()
                .stream().filter(h -> h.getName() != null).collect(Collectors.toList()));
        Collections.sort(highestDegreeList, (c1, c2) -> c1.getName().compareTo(c2.getName()));

        // arreglo usado para escribir la data de highest degree al template
        String[] highestDegree = new String[highestDegreeList.size()];
        for (int i = 0; i < highestDegreeList.size(); i++) {
            highestDegree[i] = highestDegreeList.get(i).getId() + "- " + highestDegreeList.get(i).getName()
                    + " (" + highestDegreeList.get(i).getAcronym() + ")";
        }

        validationHelper = sheet1.getDataValidationHelper();

        // se configuran las coordenas donde se desea pegar el data validator en la sheet1 del template
        CellRangeAddressList addressListCountry = new CellRangeAddressList(10, 1000, 3, 3);
        CellRangeAddressList addressListHighestDegree = new CellRangeAddressList(10, 1000, 4, 4);

        // se crean cada uno de los data validator
        this.createDataValidator(wb, sheet2, countries, dataValidationCountryName);

        // se configuran y pegan cada uno de los data validator
        DataValidation dataValidationCountry = this.setDataValidator(dataValidationCountryName,
                validationHelper, addressListCountry, constraintCountries);

        // set de cada data davilidator al sheet1 del template
        sheet1.addValidationData(dataValidationCountry);

        ByteArrayOutputStream fileOut = new ByteArrayOutputStream();
        wb.write(fileOut);
        wb.close();

        inputStream = new ByteArrayInputStream(fileOut.toByteArray());

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return SUCCESS;
}

From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java

License:Open Source License

public void createFile() throws FileNotFoundException {
    try {/*from w  w  w  .  ja  v  a  2  s.c o m*/
        DataValidation dataValidationCountries = null;
        DataValidation dataValidationInstitutions = null;
        DataValidation dataValidationCountryOfInstitutions = null;
        DataValidationConstraint constraintCountries = null;
        DataValidationConstraint constraintInstitutions = null;
        DataValidationConstraint constraintCountryOfInstitutions = null;
        DataValidationHelper validationHelper = null;

        final String path = new File(".").getCanonicalPath();
        final String filePath = "C:\\Users\\logonzalez\\Downloads\\participants-template.xlsm";
        final File file = new File(filePath);
        final FileInputStream fileInput = new FileInputStream(file);
        final XSSFWorkbook wb = new XSSFWorkbook(fileInput);

        final Sheet sheet1 = wb.getSheetAt(0);
        final XSSFSheet sheet2 = wb.getSheet("countries");
        final XSSFSheet sheet3 = wb.getSheet("institutions");

        final String reference = null;
        final String dataValidationCountryName = "countriesLis";
        final String dataValidationInstitutionName = "institutionsList";

        final String[] countries = { "1- Colombia", "2- Brazil", "3- Espenia", "4- Argentina", "5- Aruba",
                "6- Egipto", "7- Panama", "8- Ecuador" };
        final String[] institutions = { "CH- U.chile", "BZ- U.coritiba", "PN- U.panama", "AR- U.de.Palermo",
                "AF- U.delNilo", "EC- U.de.Quito", };

        for (int i = 0; i < countries.length; i++) {
            final Row fila = sheet2.createRow(i);
            final Cell celda = fila.createCell(0);
            final Cell celdaformula = fila.createCell(1);
            // final String formula = "SUM(C1,D1)";
            celda.setCellValue(countries[i]);
            // celdaformula.setCellFormula(formula);
        }

        // sheet2.protectSheet("marlo-ciat");
        // // 3. create named range for an area using AreaReference
        // final Name namedCountry = wb.createName();
        // namedCountry.setNameName(dataValidationCountryName);
        // reference = "countries!$A$1:$A$" + countries.length; // area reference
        // namedCountry.setRefersToFormula(reference);

        for (int i = 0; i < institutions.length; i++) {
            final Row fila = sheet3.createRow(i);
            final Cell celda = fila.createCell(0);
            celda.setCellValue(institutions[i]);

        }

        // final Name namedInstitution = wb.createName();
        // namedInstitution.setNameName(dataValidationInstitutionName);
        // reference = "institutions!$A$1:$A$" + institutions.length; // area reference
        // namedInstitution.setRefersToFormula(reference);
        //
        // sheet3.protectSheet("marlo-ciat");

        validationHelper = sheet1.getDataValidationHelper();
        final CellRangeAddressList addressListCountry = new CellRangeAddressList(11, 1000, 4, 4);
        constraintCountries = validationHelper.createFormulaListConstraint(dataValidationCountryName);
        dataValidationCountries = validationHelper.createValidation(constraintCountries, addressListCountry);
        dataValidationCountries.setSuppressDropDownArrow(true);
        if (dataValidationCountries instanceof XSSFDataValidation) {
            dataValidationCountries.setSuppressDropDownArrow(true);
            dataValidationCountries.setShowErrorBox(true);
        } else {
            dataValidationCountries.setSuppressDropDownArrow(false);
        }

        final CellRangeAddressList addressListInstitution = new CellRangeAddressList(11, 1000, 6, 6);
        constraintInstitutions = validationHelper.createFormulaListConstraint(dataValidationInstitutionName);
        dataValidationInstitutions = validationHelper.createValidation(constraintInstitutions,
                addressListInstitution);
        dataValidationInstitutions.setSuppressDropDownArrow(true);
        if (dataValidationInstitutions instanceof XSSFDataValidation) {
            dataValidationInstitutions.setSuppressDropDownArrow(true);
            dataValidationInstitutions.setShowErrorBox(true);
        } else {
            dataValidationInstitutions.setSuppressDropDownArrow(false);
        }

        final CellRangeAddressList addressListCountryOfInstitution = new CellRangeAddressList(11, 1000, 7, 7);
        constraintCountryOfInstitutions = validationHelper
                .createFormulaListConstraint(dataValidationCountryName);
        dataValidationCountryOfInstitutions = validationHelper.createValidation(constraintCountryOfInstitutions,
                addressListCountryOfInstitution);
        dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true);
        if (dataValidationCountryOfInstitutions instanceof XSSFDataValidation) {
            dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true);
            dataValidationCountryOfInstitutions.setShowErrorBox(true);
        } else {
            dataValidationCountryOfInstitutions.setSuppressDropDownArrow(false);
        }

        sheet1.addValidationData(dataValidationCountries);
        sheet1.addValidationData(dataValidationInstitutions);
        sheet1.addValidationData(dataValidationCountryOfInstitutions);

        FileOutputStream fileOut;

        fileOut = new FileOutputStream("C:\\Users\\logonzalez\\Downloads\\vineet.xlsm");
        wb.write(fileOut);
        fileOut.close();
        wb.close();

    } catch (EncryptedDocumentException | IOException e1) {
        e1.printStackTrace();
    }
}

From source file:org.dhatim.fastexcel.Correctness.java

License:Apache License

@Test
public void singleWorksheet() throws Exception {
    String sheetName = "Worksheet 1";
    String stringValue = "Sample text with chars to escape : < > & \\ \" ' ~        ";
    Date dateValue = new Date();
    LocalDateTime localDateTimeValue = LocalDateTime.now();
    ZoneId timezone = ZoneId.of("Australia/Sydney");
    ZonedDateTime zonedDateValue = ZonedDateTime.ofInstant(dateValue.toInstant(), timezone);
    double doubleValue = 1.234;
    int intValue = 2_016;
    long longValue = 2_016_000_000_000L;
    BigDecimal bigDecimalValue = BigDecimal.TEN;
    byte[] data = writeWorkbook(wb -> {
        Worksheet ws = wb.newWorksheet(sheetName);
        int i = 1;
        ws.value(i, i++, stringValue);// www. j av a2s.  c  o m
        ws.value(i, i++, dateValue);
        ws.value(i, i++, localDateTimeValue);
        ws.value(i, i++, zonedDateValue);
        ws.value(i, i++, doubleValue);
        ws.value(i, i++, intValue);
        ws.value(i, i++, longValue);
        ws.value(i, i++, bigDecimalValue);
        try {
            ws.finish();
        } catch (IOException ex) {
            throw new RuntimeException(ex);
        }
    });

    // Check generated workbook with Apache POI
    XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(data));
    assertThat(xwb.getActiveSheetIndex()).isEqualTo(0);
    assertThat(xwb.getNumberOfSheets()).isEqualTo(1);
    XSSFSheet xws = xwb.getSheet(sheetName);
    @SuppressWarnings("unchecked")
    Comparable<XSSFRow> row = (Comparable) xws.getRow(0);
    assertThat(row).isNull();
    int i = 1;
    assertThat(xws.getRow(i).getCell(i++).getStringCellValue()).isEqualTo(stringValue);
    assertThat(xws.getRow(i).getCell(i++).getDateCellValue()).isEqualTo(dateValue);
    // Check zoned timestamps have the same textual representation as the Dates extracted from the workbook
    // (Excel date serial numbers do not carry timezone information)
    assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime
            .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault())))
                    .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(localDateTimeValue));
    assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime
            .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault())))
                    .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(zonedDateValue));
    assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(doubleValue);
    assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(intValue);
    assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(longValue);
    assertThat(new BigDecimal(xws.getRow(i).getCell(i++).getRawValue())).isEqualTo(bigDecimalValue);
}