Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getColumnIndex.

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:com.mycompany.peram_inclassexam.ReadExcelFile.java

public List getAccountListFromExcel() throws FileNotFoundException {
    List accountList = new ArrayList();
    FileInputStream fis = null;/*from   ww  w.  ja v  a  2s. c  om*/
    try {
        fis = new FileInputStream(FILE_PATH);
        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                AccountDetails account = new AccountDetails();
                Row row = (Row) sheet.getRow(j);

                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = (Cell) cellIterator.next();

                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {

                        if (cell.getColumnIndex() == 1) {
                            account.setLastName(cell.getStringCellValue());
                        }

                        if (cell.getColumnIndex() == 2) {
                            account.setAccountNo(cell.getStringCellValue());
                        }

                        if (cell.getColumnIndex() == 0) {
                            account.setFirstName(cell.getStringCellValue());
                        }

                    } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        if (cell.getColumnIndex() == 3) {
                            account.setAccountBalance((int) cell.getNumericCellValue());
                        }
                    }

                }

                accountList.add(account);
            }

        }

        fis.close();

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

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Map<String, String> managementMap = BscKpiCode.getManagementMap(false);
    //Map<String, String> calculationMap = BscKpiCode.getCalculationMap(false);
    int itemCols = 4;
    int mrRow = row;
    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);

        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);

            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                KpiVO kpi = objective.getKpis().get(kx);

                Row contentRow = sh.createRow(row++);
                contentRow.setHeight((short) 4000);

                int cell = 0;

                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(perspective.getName(), perspective.getScore(),
                            perspective.getWeight(), perspective.getTarget(), perspective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(//from ww  w . j  ava 2s . c  o  m
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0 && ox == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("PERSPECTIVES",
                                perspective.getTarget(), perspective.getMin(), perspective.getScore(), "", "",
                                0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }
                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(objective.getName(), objective.getScore(),
                            objective.getWeight(), objective.getTarget(), objective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0 && kx == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("OBJECTIVES",
                                objective.getTarget(), objective.getMin(), objective.getScore(), "", "", 0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }
                for (int i = 0; i < itemCols; i++) {
                    //String content = this.getKpisContent(kpi, managementMap, calculationMap);
                    String content = this.getKpisContent(kpi, managementMap);
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(
                            new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);

                    if (i == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("KPI", kpi.getTarget(),
                                kpi.getMin(), kpi.getScore(), kpi.getCompareType(), kpi.getManagement(),
                                kpi.getQuasiRange());
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                    contentCell1.getColumnIndex());
                        }
                    }

                }

            }

        }

    }

    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);
        sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow() - 1, 0, 3));

        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);
            sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow() - 1, 4, 7));

            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                sh.addMergedRegion(new CellRangeAddress(mrRow + kx, mrRow + kx, 8, 11));
            }

            mrRow += objective.getKpis().size();
        }

    }

    return row++;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private int createDateRange(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context)
        throws Exception {
    String frequency = (String) context.get("frequency");
    String startYearDate = StringUtils.defaultString((String) context.get("startYearDate")).trim();
    String endYearDate = StringUtils.defaultString((String) context.get("endYearDate")).trim();
    String startDate = StringUtils.defaultString((String) context.get("startDate")).trim();
    String endDate = StringUtils.defaultString((String) context.get("endDate")).trim();
    String date1 = startDate;//  w  w  w  .  jav a2 s.c  o  m
    String date2 = endDate;
    if (BscMeasureDataFrequency.FREQUENCY_QUARTER.equals(frequency)
            || BscMeasureDataFrequency.FREQUENCY_HALF_OF_YEAR.equals(frequency)
            || BscMeasureDataFrequency.FREQUENCY_YEAR.equals(frequency)) {
        date1 = startYearDate + "/01/01";
        date2 = endYearDate + "/12/" + SimpleUtils.getMaxDayOfMonth(Integer.parseInt(endYearDate), 12);
    }
    Map<String, Object> headContentMap = new HashMap<String, Object>();
    this.fillHeadContent(context, headContentMap);

    XSSFCellStyle cellStyleLabel = wb.createCellStyle();
    cellStyleLabel.setFillForegroundColor(
            new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor())));
    cellStyleLabel.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFFont cellFontLabel = wb.createFont();
    cellFontLabel.setBold(false);
    cellFontLabel
            .setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor())));
    cellStyleLabel.setFont(cellFontLabel);
    cellStyleLabel.setWrapText(true);
    //cellStyleLabel.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyleLabel.setBorderBottom(BorderStyle.THIN);
    cellStyleLabel.setBorderTop(BorderStyle.THIN);
    cellStyleLabel.setBorderRight(BorderStyle.THIN);
    cellStyleLabel.setBorderLeft(BorderStyle.THIN);

    int cols = 4 + vision.getPerspectives().get(0).getObjectives().get(0).getKpis().get(0).getDateRangeScores()
            .size();
    int cell = 0;
    for (int i = 0; i < cols; i++) {
        String content = "Frequency: " + BscMeasureDataFrequency.getFrequencyMap(false).get(frequency)
                + " Date range: " + date1 + " ~ " + date2 + "\n"
                + StringUtils.defaultString((String) headContentMap.get("headContent"));
        Row headRow = sh.createRow(row);
        headRow.setHeight((short) 700);
        Cell headCell1 = headRow.createCell(cell);
        headCell1.setCellValue(content);
        headCell1.setCellStyle(cellStyleLabel);
    }

    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));

    row++;

    int kpiCols = 4;
    int kpiRows = 2;
    for (PerspectiveVO perspective : vision.getPerspectives()) {
        for (ObjectiveVO objective : perspective.getObjectives()) {
            for (KpiVO kpi : objective.getKpis()) {
                cell = 0;

                for (int r = 0; r < kpiRows; r++) {
                    Row contentRow = sh.createRow(row++);
                    contentRow.setHeight((short) 400);

                    for (int c = 0; c < kpiCols; c++) {
                        XSSFCellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setFillForegroundColor(
                                new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        XSSFFont cellFont = wb.createFont();
                        cellFont.setBold(false);
                        cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                        cellStyle.setFont(cellFont);
                        cellStyle.setWrapText(true);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                        cellStyle.setBorderTop(BorderStyle.THIN);
                        cellStyle.setBorderRight(BorderStyle.THIN);
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                        Cell contentCell1 = contentRow.createCell(c);
                        contentCell1.setCellValue(kpi.getName());
                        contentCell1.setCellStyle(cellStyle);

                    }

                    cell = 4;
                    if (r == 0) { // date

                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue(dateRangeScore.getDate());
                            contentCell1.setCellStyle(cellStyle);
                        }

                    }
                    if (r == 1) { // score

                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(
                                    SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue(
                                    "      " + BscReportSupportUtils.parse2(dateRangeScore.getScore()));
                            contentCell1.setCellStyle(cellStyle);

                            byte[] imgBytes = BscReportSupportUtils.getByteIcon(kpi, dateRangeScore.getScore());
                            if (null != imgBytes) {
                                SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(),
                                        contentCell1.getColumnIndex());
                            }

                        }

                    }

                }

                sh.addMergedRegion(new CellRangeAddress(row - 2, row - 1, 0, kpiCols - 1));

            }
        }
    }

    return row++;
}

From source file:com.openitech.db.model.ExcelDataSource.java

License:Apache License

@Override
public boolean loadData(boolean reload, int oldRow) {
    boolean result = false;

    if (isDataLoaded && !reload) {
        return false;
    }/*www  .  ja v a2s .  c o  m*/
    if (sourceFile != null) {
        try {
            Workbook workBook = WorkbookFactory.create(new FileInputStream(sourceFile));
            //        HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(sourceFile));
            Sheet sheet = workBook.getSheetAt(0);
            DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY);
            FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator();

            int lastRowNum = sheet.getLastRowNum();

            boolean isFirstLineHeader = true;

            //count = sheet. - (isFirstLineHeader ? 1 : 0);
            int tempCount = 0;
            for (int j = 0; j <= lastRowNum; j++) {
                //zane se z 0
                Row row = row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }

                // display row number in the console.
                System.out.println("Row No.: " + row.getRowNum());
                if (isFirstLineHeader && row.getRowNum() == 0) {
                    populateHeaders(row);
                    continue;
                }
                tempCount++;

                Map<String, DataColumn> values;
                if (rowValues.containsKey(row.getRowNum())) {
                    values = rowValues.get(row.getRowNum());
                } else {
                    values = new HashMap<String, DataColumn>();
                    rowValues.put(row.getRowNum(), values);
                }

                // once get a row its time to iterate through cells.
                int lastCellNum = row.getLastCellNum();
                for (int i = 0; i <= lastCellNum; i++) {
                    DataColumn dataColumn = new DataColumn();
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        continue;
                    }
                    System.out.println("Cell No.: " + cell.getColumnIndex());
                    System.out.println("Value: " + dataFormatter.formatCellValue(cell));
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                    } else {
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                    }

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: {
                        // cell type numeric.
                        System.out.println("Numeric value: " + cell.getNumericCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:
                        // cell type string.
                        System.out.println("String value: " + cell.getStringCellValue());
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell));
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        // cell type string.
                        System.out.println("String value: " + cell.getBooleanCellValue());
                        dataColumn.setValue(cell.getBooleanCellValue(), Boolean.class);
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        // cell type string.
                        System.out.println(
                                "Formula value: " + dataFormatter.formatCellValue(cell, formulaEvaluator));
                        dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator));
                        break;
                    default:
                        dataColumn.setValue(cell.getStringCellValue(), String.class);
                        break;
                    }

                    values.put(getColumnName(cell.getColumnIndex()).toUpperCase(), dataColumn);

                }
            }

            count = tempCount;

            isDataLoaded = true;
            //se postavim na staro vrstico ali 1
            if (oldRow > 0) {
                absolute(oldRow);
            } else {
                first();
            }

            result = true;
        } catch (Exception ex) {
            Logger.getLogger(ExcelDataSource.class.getName()).log(Level.SEVERE, null, ex);
            result = false;
        }
    }

    return result;
}

From source file:com.openitech.db.model.ExcelDataSource.java

License:Apache License

private void populateHeaders(Row row) {
    columnCount = 0;/*  w ww. ja  va2 s  .  c  om*/
    int lastCellNum = row.getLastCellNum();
    for (int i = 0; i <= lastCellNum; i++) {
        Cell cell = row.getCell(i);
        if (cell == null) {
            continue;
        }

        System.out.println("String value: " + cell.getStringCellValue());

        String header = cell.getStringCellValue();
        columnMapping.put(header, cell.getColumnIndex());
        columnMappingIndex.put(cell.getColumnIndex(), header);
        columnCount++;
    }
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

/**
 * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat}
 * //ww  w.jav  a2s  . com
 * @param cell
 * @param excell
 * @param style
 * @param font
 */
private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle,
        Workbook workbook, Font font, Font invisibleFont) {
    if (excell != null) {

        // [1] format cell
        formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont);

        // [2] set enum
        if (!excell.isConsiderEnum()) {
            if (StringUtils.isNotBlank(excell.getDisplayText())) {
                cell.setCellValue(excell.getDisplayText());
            }
            if (!excell.isMultiSelect() && excell.isNumberValidation()) {
                addNumberValidation(cell);
            }
        } else {
            String[] list = (String[]) excell.getRestriction().getEnumValues()
                    .toArray(new String[excell.getRestriction().getEnumValues().size()]);

            SXSSFSheet sheet = (SXSSFSheet) cell.getSheet();

            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(list);
            CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getColumnIndex());
            DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                    regions);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("Not Applicable", "Can't change the value");
            dataValidation.setShowErrorBox(true);

            try {
                if (isValidEnumList(list)) {
                    sheet.addValidationData(dataValidation);
                } else {
                    Sheet hidden = null;
                    String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText());
                    Workbook wBook = cell.getSheet().getWorkbook();
                    if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) {
                        hidden = wBook.getSheet(hiddenName);
                    } else {
                        hidden = wBook.createSheet(hiddenName);

                        for (int i = 0, length = list.length; i < length; i++) {
                            String name = list[i];
                            Row row = hidden.createRow(i);
                            Cell cell1 = row.createCell(0);
                            cell1.setCellValue(name);
                        }
                        Name namedCell = hidden.getWorkbook().getName(hiddenName);
                        namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName();
                        namedCell.setNameName(hiddenName);
                        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length);
                    }

                    dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createFormulaListConstraint(hiddenName);
                    dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
                    dataValidation.setShowErrorBox(true);

                    cell.getSheet().addValidationData(dataValidation);
                    wBook.setSheetHidden(wBook.getSheetIndex(hidden), true);

                }

            } catch (Exception e) {
                String msg = "Excel creation failed while building cell: " + excell.getDisplayText();
                throw new IllegalStateException(msg, e);
            }

            // cell.setCellValue(excelConfig.getDropDownMsg());
        }

    }

}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void addNumberValidation(Cell cell) {

    if (cell != null) {

        Sheet sheet = cell.getSheet();/*from   w  w  w.  j  ava 2s  .  c o m*/
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createNumericConstraint(ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, "1.00",
                        "1000000000000.00");
        CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                cell.getColumnIndex(), cell.getColumnIndex());
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                addressList);
        validation.setErrorStyle(ErrorStyle.STOP);
        validation.createErrorBox("Error", "Only numeric values are allowed");
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

}

From source file:com.plugin.excel.util.ExcelUtil.java

License:Apache License

/**
 * @param srcSheet/*from   w  w  w .j  av a  2s.  c  om*/
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *            -
 */
public static void copyRow(SXSSFSheet srcSheet, SXSSFSheet destSheet, Row srcRow, Row destRow,
        Map<Integer, CellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a
    // merged zone
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        Cell oldCell = srcRow.getCell(j); // ancienne cell
        Cell newCell = destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell
            copyCell(oldCell, newCell, styleMap);
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                // System.out.println("Selected merged region: " +
                // mergedRegion.toString());
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
                        mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                // System.out.println("New merged region: " +
                // newMergedRegion.toString());
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }

}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void formatCellValueForEZPassNY(HSSFWorkbook wb, Cell cell, Object oneCellValue, String vendor)
        throws Exception {
    if (oneCellValue == null) {
        oneCellValue = StringUtils.EMPTY;
        return;//  w w  w  .  j a  v  a2 s . c  o m
    }

    int columnIndex = cell.getColumnIndex();

    if (columnIndex == 3) { // Tag num
        setCellValueTagNumberFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 4) { // Plate num
        setCellValuePlateNumberFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 5) { // Driver
        setCellValueDriverFormat(wb, cell, oneCellValue);
    } else if (oneCellValue instanceof Date || columnIndex == 6 || columnIndex == 10) { // Transaction date, Invoice date
        setCellValueDateFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 7) { // Transaction time
        String timeStr = stripToTimeFormat(oneCellValue);
        cell.setCellValue(timeStr);
    } else if (columnIndex == 8) { // Agency
        setCellValueAgencyFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 9) { // Amount
        setCellValueFeeFormat(wb, cell, oneCellValue, vendor);
        /*} else if (columnIndex == 11) {
           setCellValueUnitNumberFormat(wb, cell, oneCellValue, vendor);*/
    } else {
        cell.setCellValue(oneCellValue.toString().toUpperCase());
    }
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void formatCellValueForEZPassPA(HSSFWorkbook wb, Cell cell, Object oneCellValue, String vendor)
        throws Exception {
    if (oneCellValue == null) {
        oneCellValue = StringUtils.EMPTY;
        return;/*  w w w .jav  a2 s  . c  o m*/
    }

    int columnIndex = cell.getColumnIndex();
    if (columnIndex == 4) { // Plate num
        setCellValuePlateNumberFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 5) { // Driver
        setCellValueDriverFormat(wb, cell, oneCellValue);
    } else if (oneCellValue instanceof Date || columnIndex == 6 || columnIndex == 10) { // Transaction date and time, Invoice date
        setCellValueDateFormat(wb, cell, oneCellValue, vendor);
    } else if (columnIndex == 9) { // Amount
        setCellValueFeeFormat(wb, cell, oneCellValue, vendor);
        /*} else if (columnIndex == 11) {
           setCellValueUnitNumberFormat(wb, cell, oneCellValue, vendor);*/
    } else {
        cell.setCellValue(oneCellValue.toString().toUpperCase());
    }
}