Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("rawtypes")
private static void writeDataValidations(Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) {
    int templateRowStartIndex = sheetProcessor.getTemplateStartRowIndex();
    int templateRowEndIndex = sheetProcessor.getTemplateEndRowIndex();
    int step = templateRowEndIndex - templateRowStartIndex + 1;
    int rowStartIndex = sheetProcessor.getStartRowIndex();

    Set<Integer> configColIndexSet = new HashSet<Integer>();
    for (Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> fieldIndexMapping : sheetProcessor
            .getFieldMapping().export().entrySet()) {
        if (fieldIndexMapping == null || fieldIndexMapping.getValue() == null) {
            continue;
        }// w ww.  jav a2s .  co m
        for (Entry<Integer, ExcelWriteFieldMappingAttribute> indexProcessorMapping : fieldIndexMapping
                .getValue().entrySet()) {
            if (indexProcessorMapping == null || indexProcessorMapping.getKey() == null) {
                continue;
            }
            configColIndexSet.add(indexProcessorMapping.getKey());
        }
    }

    List<? extends DataValidation> dataValidations = sheet.getDataValidations();
    if (dataValidations != null) {
        for (DataValidation dataValidation : dataValidations) {
            if (dataValidation == null) {
                continue;
            }
            CellRangeAddressList cellRangeAddressList = dataValidation.getRegions();
            if (cellRangeAddressList == null) {
                continue;
            }

            CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
            if (cellRangeAddresses == null || cellRangeAddresses.length == 0) {
                continue;
            }

            CellRangeAddressList newCellRangeAddressList = new CellRangeAddressList();
            boolean validationContains = false;
            for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
                if (cellRangeAddress == null) {
                    continue;
                }
                if (templateRowEndIndex < cellRangeAddress.getFirstRow()
                        || templateRowStartIndex > cellRangeAddress.getLastRow()) {// specify row
                    continue;
                }
                for (Integer configColIndex : configColIndexSet) {
                    if (configColIndex < cellRangeAddress.getFirstColumn()
                            || configColIndex > cellRangeAddress.getLastColumn()) {// specify column
                        continue;
                    }
                    if (templateRowStartIndex == templateRowEndIndex) {
                        newCellRangeAddressList.addCellRangeAddress(rowStartIndex, configColIndex,
                                sheet.getLastRowNum(), configColIndex);
                        validationContains = true;
                    } else {
                        int start = cellRangeAddress.getFirstRow() > templateRowStartIndex
                                ? cellRangeAddress.getFirstRow()
                                : templateRowStartIndex;
                        int end = cellRangeAddress.getLastRow() < templateRowEndIndex
                                ? cellRangeAddress.getLastRow()
                                : templateRowEndIndex;
                        long lastRow = sheet.getLastRowNum();
                        if (lastRow > end) {
                            long count = (lastRow - templateRowEndIndex) / step;
                            int i = templateRowEndIndex;
                            for (; i < count; i++) {
                                newCellRangeAddressList.addCellRangeAddress(start + i * step, configColIndex,
                                        end + i * step, configColIndex);
                                validationContains = true;
                            }
                            long _start = start + i * step;
                            if (_start <= lastRow) {
                                long _end = end + i * step;
                                _end = _end < lastRow ? _end : lastRow;
                                newCellRangeAddressList.addCellRangeAddress((int) _start, configColIndex,
                                        (int) _end, configColIndex);
                                validationContains = true;
                            }
                        }
                    }
                }
            }
            if (validationContains) {
                DataValidation newDataValidation = sheet.getDataValidationHelper()
                        .createValidation(dataValidation.getValidationConstraint(), newCellRangeAddressList);
                sheet.addValidationData(newDataValidation);
            }
        }
    }
}

From source file:org.jeecgframework.poi.excel.imports.ExcelImportServer.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,
        Map<String, PictureData> pictures) throws Exception {
    List collection = new ArrayList();
    Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
    List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
    String targetId = null;// ww w  .  ja  va2  s .  c om
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    // 
    int count = 4;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        count++;
        row = rows.next();
        // ???,?,?
        if ((row.getCell(params.getKeyIndex()) == null
                || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
            for (ExcelCollectionParams param : excelCollection) {
                try {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                } catch (Exception e) {
                    // TODO: handle exception
                    Exception f = new Exception("" + count + "" + e.getMessage());
                    throw f;
                }
            }
        } else {
            object = PoiPublicUtil.createObject(pojoClass, targetId);
            try {
                for (int i = row.getFirstCellNum(), le = row.getLastCellNum(); i < le; i++) {
                    Cell cell = row.getCell(i);
                    String titleString = (String) titlemap.get(i);
                    if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
                        if (excelParams.get(titleString) != null
                                && excelParams.get(titleString).getType() == 2) {
                            picId = row.getRowNum() + "_" + i;
                            saveImage(object, picId, excelParams, titleString, pictures, params);
                        } else {
                            try {
                                saveFieldValue(params, object, cell, excelParams, titleString, row);
                            } catch (Exception e) {
                                // TODO: handle exception
                                Exception f = new Exception("" + count + "" + e.getMessage());
                                throw f;
                            }
                        }
                    }
                }

                for (ExcelCollectionParams param : excelCollection) {
                    try {
                        addListContinue(object, param, row, titlemap, targetId, pictures, params);
                    } catch (Exception e) {
                        // TODO: handle exception
                        Exception f = new Exception("" + count + "" + e.getMessage());
                        throw f;
                    }
                }
                collection.add(object);
            } catch (ExcelImportException e) {
                if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                    throw new ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}

From source file:org.joeffice.spreadsheet.csv.SmartCsvReader.java

License:Apache License

public void write(OutputStream output, Workbook workbook) throws IOException {
    SimpleResultSet rs = new SimpleResultSet();
    // TODO use the first row
    for (String header : headers) {
        rs.addColumn(header, Types.VARCHAR, 2000, 0);
    }//from   www . j  a v  a  2s.  c o  m
    Sheet firstSheet = workbook.getSheetAt(0);
    for (int i = 1; i <= firstSheet.getLastRowNum(); i++) {
        Row row = firstSheet.getRow(i);
        String[] rowValues = new String[headers.length];
        for (int j = 0; j < headers.length; j++) {
            Cell cell = row.getCell(j);
            rowValues[j] = cell == null ? "" : cell.getStringCellValue();
        }
        rs.addRow(rowValues);
    }
    Writer writer = new BufferedWriter(new OutputStreamWriter(output, charset));
    try {
        csvMetadata.write(writer, rs);
    } catch (SQLException ex) {
        throw new IOException(ex);
    }
}

From source file:org.joeffice.spreadsheet.SheetComponent.java

License:Apache License

public JTable createTable(Sheet sheet) {
    SheetTableModel sheetTableModel = new SheetTableModel(sheet);
    JTable table = new SheetTable(sheetTableModel);

    table.setDefaultRenderer(Cell.class, new CellRenderer());
    TableCellEditor editor = new org.joeffice.spreadsheet.cell.CellEditor();
    table.setDefaultEditor(Cell.class, editor);
    int columnsCount = sheetTableModel.getColumnCount();
    for (int i = 0; i < columnsCount; i++) {
        TableColumn tableColumn = table.getColumnModel().getColumn(i);
        tableColumn.setCellRenderer(new CellRenderer());
        tableColumn.setCellEditor(editor);
        int widthUnits = sheet.getColumnWidth(i);
        tableColumn.setPreferredWidth(widthUnitsToPixel(widthUnits));
    }//from  w  w  w .  jav  a 2 s  .  c  om

    int rowCount = sheetTableModel.getRowCount();
    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        Row row = sheet.getRow(rowIndex);
        if (row != null) {
            int cellHeight = (int) Math.ceil(sheet.getRow(rowIndex).getHeightInPoints());
            cellHeight += CELL_HEIGHT_MARGINS;
            table.setRowHeight(rowIndex, cellHeight);
        }
    }

    table.setAutoscrolls(true);
    table.setFillsViewportHeight(true);
    JLabel tableHeader = (JLabel) table.getTableHeader().getDefaultRenderer();
    tableHeader.setHorizontalAlignment(SwingConstants.CENTER);

    table.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);
    table.setCellSelectionEnabled(true);

    TableColumnAdjuster tca = new TableColumnAdjuster(table, 20);
    if (sheet.getDefaultColumnWidth() == -1) {
        table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
        tca.setOnlyAdjustLarger(true);
        tca.setLeaveEmptyAsIs(true);
        tca.adjustColumns();
    }

    table.setTransferHandler(new TableTransferHandler());
    table.setDragEnabled(true);
    table.setDropMode(DropMode.ON_OR_INSERT);

    Action cutAction = new ClipboardAction(DefaultEditorKit.cutAction);
    Action copyAction = new ClipboardAction(DefaultEditorKit.copyAction);
    Action pasteAction = new ClipboardAction(DefaultEditorKit.pasteAction);
    table.getActionMap().put(DefaultEditorKit.cutAction, cutAction);
    table.getActionMap().put(DefaultEditorKit.copyAction, copyAction);
    table.getActionMap().put(DefaultEditorKit.pasteAction, pasteAction);

    //table.setIntercellSpacing(new Dimension(0, 0));
    table.putClientProperty("print.printable", Boolean.TRUE);
    Rectangle lastDataCellBounds = table.getCellRect(sheet.getLastRowNum(), sheetTableModel.getLastColumnNum(),
            true);
    table.putClientProperty("print.size", new Dimension(lastDataCellBounds.x + lastDataCellBounds.width,
            lastDataCellBounds.y + lastDataCellBounds.height));
    new SheetListener(table);

    if (!sheet.isDisplayGridlines()) {
        table.setShowGrid(false);
    }
    return table;
}

From source file:org.meveo.commons.utils.ExcelToCsv.java

License:Apache License

/**
 * Called to convert the contents of the currently opened workbook into
 * a CSV file.//  w w w  .java2 s. c  o m
 */
private void convertToCSV() {
    Sheet sheet = null;
    Row row = null;
    int lastRowNum = 0;
    this.csvData = new ArrayList<ArrayList<String>>();

    log.debug("Converting files contents to CSV format.");

    // and then iterate through them.
    for (int i = 0; i < sheetsIdsToConvert.length; i++) {

        // Get a reference to a sheet and check to see if it contains
        // any rows.
        sheet = this.workbook.getSheetAt(sheetsIdsToConvert[i]);
        if (sheet.getPhysicalNumberOfRows() > 0) {

            // Note down the index number of the bottom-most row and
            // then iterate through all of the rows on the sheet starting
            // from the very first row - number 1 - even if it is missing.
            // Recover a reference to the row and then call another method
            // which will strip the data from the cells and build lines
            // for inclusion in the resylting CSV file.
            lastRowNum = sheet.getLastRowNum();
            for (int j = 0; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
    }
}

From source file:org.mifos.dmt.excel.cleanup.PurgeEmptyRows.java

License:Open Source License

public Workbook processEmptyRows() throws DMTException {
    for (int j = 1; j <= (workbook.getNumberOfSheets() - 1); j++) {
        Sheet sheet = workbook.getSheetAt(j);
        int targetRow = -1;
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);// ww  w. j  av a2 s. c  om
            if (row == null)
                continue;
            Cell cell = row.getCell(0);
            if (cell == null)
                continue;
            String val = cell.toString();
            if (val.equals("EOF")) {
                targetRow = i;
            }
        }
        if (targetRow == -1) {
            logger.error("EOF value missing @ " + sheet.getSheetName());
            throw new DMTException("EOF value missing @ " + sheet.getSheetName());
        }
        cleanUpSheet(sheet, targetRow, sheet.getLastRowNum());
    }
    return workbook;
}

From source file:org.newcashel.meta.model.NCClass.java

License:Apache License

public static void load(HSSFWorkbook wb, LaunchParms launchParm) throws Exception {

    // load the sheet
    Sheet sheet = wb.getSheet("ClassAttributes");
    if (sheet == null) {
        throw new Exception("The ClassAttributes sheet was not found in the MetaBook, terminate load process");
    }//  w w  w.  ja  v  a2 s  . c  o m

    //String[] fieldNames = POIUtil.getFirstRowVals(sheet);

    Class cls = Class.forName("org.newcashel.meta.model.NCClass");
    Class[] parmString = new Class[1];

    Row row = null;

    try {

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            // skip blank rows between class attributes
            row = sheet.getRow(i);

            if (row != null && (POIUtil.getCellValue(row, ATTRIBUTE_OFFSET) == null
                    || POIUtil.getCellValue(row, TYPE_OFFSET).length() < 1))
                continue;

            // get the size of the cell, the length will be the number of atbs in the class
            // determine if the next Cell to the left is a separate Cell or part of a CellRangeAddress
            Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                continue;
            }

            CellRangeAddress cra = getCellRangeAddress(sheet, row.getRowNum(), 0);
            if (cra == null) {
                return;
            }

            // instantiate the NCClass instance
            NCClass ncClass = new NCClass();
            ncClass.setClassName(POIUtil.getCellValue(row, CLASSNAME_OFFSET));
            //logger.info("loading NCClass " + ncClass.getClassName());

            ncClass.setSuperClassName(POIUtil.getCellValue(row, SUPERCLASS_NAME_OFFSET));
            ncClass.setClassParent(POIUtil.getCellValue(row, PARENTCLASS_OFFSET));
            ncClass.setPrimaryKey(POIUtil.getCellValue(row, PRIMARYKEY_OFFSET));
            ncClass.setPercolate(new Boolean(POIUtil.getCellValue(row, PERCOLATE_OFFSET)));
            ncClass.setGroupConstrain(new Boolean(POIUtil.getCellValue(row, GROUP_CONSTRAIN_OFFSET)));

            // not throwing java class errors, may not be significant to load context

            // TODO, if NO_VERIFY_JAVA_CLASS is true, skip validation
            // TODO, if NO_VERF true and blank 

            String javaClassName = POIUtil.getCellValue(row, JAVA_CLASS_OFFSET);
            if (javaClassName.endsWith("Person.class")) {
                System.out.println("OKK");
            }
            Class<?> javaCls = null;
            if (javaClassName != null && javaClassName.length() > 0) {
                ncClass.setJavaClassName(javaClassName);
                try {
                    javaCls = Class.forName(javaClassName);
                } catch (Exception e) {
                    logger.error("Java class specified but cannot be loaded for " + ncClass.getClassName()
                            + ", " + javaClassName);
                }
            } else {
                logger.info("no java class specified for class " + ncClass.getClassName());
            }

            classes.put(ncClass.getClassName(), ncClass);
            logger.info("Adding class " + ncClass.getClassName());

            // loop for all the rows in the cell range
            for (i = cra.getFirstRow(); i <= cra.getLastRow(); i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    return; // range iteration complete
                }
                cell = row.getCell(ATTRIBUTE_OFFSET, Row.RETURN_BLANK_AS_NULL);
                if (cell == null)
                    continue;

                String atbName = POIUtil.getCellValue(row, ATTRIBUTE_OFFSET);
                String version = POIUtil.getCellValue(row, VERSION_OFFSET);

                // if  no version id and the atb has not been set, then set it
                // if a version and it matches the build version, set/overwrite the value 
                Attribute atb = null;

                // if version id is set and matches the launchParm setting, use it else skip 
                // a non-versioned atb may be encountered first, reuse it if received a versioned one
                if (version != null && version.length() > 0) {
                    if (!(launchParm.getVersion().equals(version))) {
                        continue;
                    }
                    logger.debug("add version specific atb " + ncClass.getClassName() + ", " + atbName + ", "
                            + version);
                    // if a default version has already been established, use it else create one
                    atb = ncClass.getAttribute(atbName);
                    if (atb == null) {
                        atb = new Attribute();
                    }
                } else { // no version, use existing if already set to the current version
                    atb = ncClass.getAttribute(atbName);
                    if (atb == null) {
                        atb = new Attribute();
                    } else
                        continue; // already established a version specific atb, ignore non-versioned entry
                }

                // create the Attributes and add to the class instance
                // TODO, verify not null on these required values, user may override Excel edits
                atb.setName(POIUtil.getCellValue(row, ATTRIBUTE_OFFSET));
                atb.setType(POIUtil.getCellValue(row, TYPE_OFFSET));
                atb.setLabel(POIUtil.getCellValue(row, LABEL_OFFSET));
                atb.setIndexName(POIUtil.getPopulatedCellValue(row, INDEXNAME_OFFSET));

                //logger.info("added NCClass atb " + ncClass.getClassName() + ", " + atb.getName());

                // defaults to false
                atb.setStore(UTIL.convertBoolean(POIUtil.getCellValue(row, STORE_OFFSET)));
                /*
                String storeVal = POIUtil.getPopulatedCellValue(row, STORE_OFFSET);
                if (storeVal != null) {
                   atb.setStore(new Boolean(storeVal));
                }
                */

                // analyzed is default value, will tokenize field
                String indexVal = POIUtil.getPopulatedCellValue(row, INDEX_OFFSET);
                if (indexVal != null) {
                    atb.setIndex(indexVal);
                }

                // default is true, don't set unless value is not
                String includeInAll = POIUtil.getPopulatedCellValue(row, INCLUDEINALL_OFFSET);
                if (includeInAll != null && includeInAll.equalsIgnoreCase("no")) {
                    atb.setIncludeInAll(false);
                }

                // default varies, based on the numeric type
                // TODO, verify numeric field
                String precision = POIUtil.getPopulatedCellValue(row, PRECISIONSTEP_OFFSET);
                if (precision != null) {
                    atb.setPrecision(new Integer(precision));
                }

                String dateFormat = POIUtil.getPopulatedCellValue(row, DATEFORMAT_OFFSET);
                if (dateFormat != null) {
                    atb.setDateFormat(dateFormat);
                }

                String fieldDataFormat = POIUtil.getPopulatedCellValue(row, FIELDDATAFORMAT_OFFSET);
                if (fieldDataFormat != null) {
                    atb.setFieldDataFormat(fieldDataFormat);
                }

                atb.setDocValues(UTIL.convertBoolean(POIUtil.getCellValue(row, DOCVALUES_OFFSET)));

                String boost = POIUtil.getPopulatedCellValue(row, BOOST_OFFSET);
                if (boost != null) {
                    atb.setBoost(new Double(boost));
                }

                // defaults to not adding the field to the JSON string
                String nullVal = POIUtil.getPopulatedCellValue(row, NULLVALUE_OFFSET);
                if (nullVal != null) {
                    atb.setNullValue(nullVal);
                }

                String termVector = POIUtil.getPopulatedCellValue(row, TERMVECTOR_OFFSET);
                if (termVector != null) {
                    atb.setTermVector(termVector);
                }

                String analyzer = POIUtil.getPopulatedCellValue(row, ANALYZER_OFFSET);
                if (analyzer != null) {
                    atb.setAnalyzer(analyzer);
                }

                String indexAnalyzer = POIUtil.getPopulatedCellValue(row, INDEX_ANALYZER_OFFSET);
                if (indexAnalyzer != null) {
                    atb.setIndexAnalyzer(indexAnalyzer);
                }

                String searchAnalyzer = POIUtil.getPopulatedCellValue(row, SEARCH_ANALYZER_OFFSET);
                if (searchAnalyzer != null) {
                    atb.setSearchAnalyzer(searchAnalyzer);
                }

                atb.setIgnoreAbove(UTIL.convertAnyNumberToInt(POIUtil.getCellValue(row, IGNOREABOVE_OFFSET)));
                atb.setPositionOffset(
                        UTIL.convertAnyNumberToInt(POIUtil.getCellValue(row, POSITIONGAP_OFFSET)));
                atb.setIgnoreMalformed(UTIL.convertBoolean(POIUtil.getCellValue(row, IGNOREMALFORMED_OFFSET)));
                atb.setCoerceNumber(UTIL.convertBoolean(POIUtil.getCellValue(row, COERCENUMBER_OFFSET)));
                atb.setBinaryCompress(UTIL.convertBoolean(POIUtil.getCellValue(row, BINARYCOMPRESS_OFFSET)));
                atb.setCompressThreshold(
                        UTIL.convertAnyNumberToInt(POIUtil.getCellValue(row, COMPRESSTHRESHOLD_OFFSET)));

                // TODO, all all the others

                //atb.setStore(UTIL.convertBoolean(POIUtil.getCellValue(row, STORE_OFFSET)));

                if (atb.getType().equalsIgnoreCase("SubType")) {
                    subTypes.put(atb.getName(), atb.getLabel());
                } else {
                    // save the attribute
                    ncClass.attributes.put(atb.getName(), atb);
                    ncClass.labels.put(atb.getLabel(), atb);

                    // if java class, verify the field accessibility
                    if (javaCls != null) {
                        Field field = null;
                        Class<?> current = javaCls;
                        while (!(current.getName().equals("java.lang.Object"))) {
                            try {
                                field = current.getDeclaredField(atb.getName());
                                atb.setField(field);
                                //atb.setField(current.getDeclaredField(atb.getName()));
                                break;
                            } catch (Exception e) {
                                //System.out.println("java reflection warning, class/field not found, checking super class " + cls.getName() + ", " + atb.getName());
                                current = current.getSuperclass();
                                continue;
                            }
                        }

                        if (field != null) {
                            field.setAccessible(true);
                        }
                    }
                }
            }
            i--; // continue the loop on the prior row
        }
    } catch (Exception e) {
        String msg = "exception in NCClass load " + e.toString();
        logger.error(msg);
        throw new Exception(msg);
    }
}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.DescriptionCodeLookupFromExcelService.java

License:RPL License

private void loadMapOfCodeMaps(String codeTableExcelFilePath) throws FileNotFoundException, IOException {
    log.info("Recache code table maps.");

    mapOfCodeMaps = new HashMap<String, Map<String, Integer>>();

    FileInputStream inputStream = new FileInputStream(new File(codeTableExcelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);

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

        Map<String, Integer> codePkMap = new HashMap<String, Integer>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);/*w  w w .j av  a  2  s.c  o  m*/

            if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING);
            }

            String codeOrDescription = StringUtils
                    .upperCase(row.getCell(row.getLastCellNum() - 1).getStringCellValue());
            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
            codePkMap.put(codeOrDescription, pkId);
        }

        mapOfCodeMaps.put(sheet.getSheetName(), codePkMap);

    }

    workbook.close();
    inputStream.close();
}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.SimpleExcelReaderExample.java

License:RPL License

public static void main(String[] args) throws IOException {
    Map<String, Map<String, Integer>> mapOfCodeMaps = new HashMap<String, Map<String, Integer>>();

    String excelFilePath = "src/test/resources/codeSpreadSheets/PimaCountyAnalyticsCodeTables.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        System.out.println("Sheet Name: " + sheet.getSheetName());

        Map<String, Integer> codePkMap = new HashMap<String, Integer>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);/*from www  . j a v  a 2 s.  co  m*/

            if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING);
            }

            String codeOrDescription = row.getCell(row.getLastCellNum() - 1).getStringCellValue();

            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
            codePkMap.put(codeOrDescription, pkId);
        }

        mapOfCodeMaps.put(sheet.getSheetName(), codePkMap);

    }

    workbook.close();
    inputStream.close();
}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.SqlScriptFromExcelGenerator.java

License:RPL License

private static void generatePolulateCodeTableScript(String sqlScriptPath, String excelFilePath,
        boolean isSqlServerInsert) throws FileNotFoundException, IOException {
    Path adamsSqlPath = Paths.get(sqlScriptPath);

    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);
    StringBuilder sb = new StringBuilder();
    sb.append("/*\n "//from  w  ww .j  av  a 2s  .c om
            + "* Unless explicitly acquired and licensed from Licensor under another license, the contents of\n "
            + "* this file are subject to the Reciprocal Public License (\"RPL\") Version 1.5, or subsequent\n "
            + "* versions as allowed by the RPL, and You may not copy or use this file in either source code\n "
            + "* or executable form, except in compliance with the terms and conditions of the RPL\n " + "* \n "
            + "* All software distributed under the RPL is provided strictly on an \"AS IS\" basis, WITHOUT\n "
            + "* WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, AND LICENSOR HEREBY DISCLAIMS ALL SUCH\n "
            + "* WARRANTIES, INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY, FITNESS FOR A\n "
            + "* PARTICULAR PURPOSE, QUIET ENJOYMENT, OR NON-INFRINGEMENT. See the RPL for specific language\n "
            + "* governing rights and limitations under the RPL.\n " + "*\n "
            + "* http://opensource.org/licenses/RPL-1.5\n " + "*\n "
            + "* Copyright 2012-2015 Open Justice Broker Consortium\n " + "*/\n");

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

        if (isSqlServerInsert) {
            sb.append("SET IDENTITY_INSERT dbo." + sheet.getSheetName() + " ON;\n");
        }
        String idColumnName = sheet.getRow(0).getCell(0).getStringCellValue();
        String descriptionColumnName = sheet.getRow(0).getCell(1).getStringCellValue();

        System.out.println("descriptionColumnName: " + descriptionColumnName);
        String baseString = "insert into " + sheet.getSheetName() + " (" + idColumnName + ", "
                + descriptionColumnName + ") values (";
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);

            String description = row.getCell(1).getStringCellValue();
            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();

            String insertString = baseString + "'" + pkId + "', " + "'" + description.replace("'", "''")
                    + "');\n";
            sb.append(insertString);
        }

        if (isSqlServerInsert) {
            sb.append("SET IDENTITY_INSERT dbo." + sheet.getSheetName() + " OFF;\n");
        }
    }

    workbook.close();
    inputStream.close();

    try (BufferedWriter writer = Files.newBufferedWriter(adamsSqlPath)) {
        writer.write(sb.toString());
    }

    System.out.println("Sql script " + sqlScriptPath + " generated. ");
}