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

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

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

Introduction

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

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:com.clri.servlet.ExcelUpload.java

private int processExcelFile(File file, String uploadType, int type, int category) {

    int count = 0;
    Connection connection = null;
    MajorProductionsDAO majorProductionsDAO = new MajorProductionsDAO();
    MajorCustomersDAO majorCustomersDAO = new MajorCustomersDAO();
    DataBaseConnection dbcon = new DataBaseConnection();
    MajorProductions majorProductions = new MajorProductions();
    MajorCustomers majorCustomers = new MajorCustomers();
    majorCustomers.setType(type);//w  ww .java  2 s.c o m
    majorProductions.setType(type);
    majorProductions.setCategory(category);
    majorCustomers.setCategory(category);
    try {
        connection = dbcon.openConnection();
        // Creating Input Stream 
        FileInputStream myInput = new FileInputStream(file);

        // Create a workbook using the File System 
        XSSFWorkbook myWorkBook = new XSSFWorkbook(myInput);

        // Get the first sheet from workbook 
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        /**
         * We now need something to iterate through the cells.*
         */
        Iterator<Row> rowIter = mySheet.rowIterator();
        int rowCount = 0;
        while (rowIter.hasNext()) {
            XSSFRow row = (XSSFRow) rowIter.next();
            if (rowCount != 0) {
                if (CommonConstants.CUSTOMER_UPLOAD.equalsIgnoreCase(uploadType)) {
                    count += majorCustomersDAO.insertMajorCustomers(connection,
                            getCustomers(row, majorCustomers));
                } else {
                    count += majorProductionsDAO.insertMajorProductions(connection,
                            getProductions(row, majorProductions));
                }

            }
            rowCount++;
        }
    } catch (IOException e) {
    } finally {
        DBUtils.closeConnection(connection);
    }
    return count;

}

From source file:com.cms.utils.DataUtil.java

License:Open Source License

/**
 *
 * @param file//from  www  .j  a  v a 2  s.  c o  m
 * @param iSheet
 * @param iBeginRow
 * @param iFromCol
 * @param iToCol
 * @param rowBack
 * @param lstValidateCells
 * @return
 */
public static List isValidExcells(String mimeType, File file, int iSheet, int iBeginRow, int iFromCol,
        int iToCol, int rowBack, List<ValidateCells> lstValidateCells) {
    String fileName = file.getName();
    fileName = removeDotInteger(fileName);
    boolean isCopySheet = true;
    File fileError = null;
    Map<String, String> mapsNameError = new HashMap<>();
    List lst = null;
    try {
        if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx
            lst = ExcelReaderXLSX.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack);
        } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
            lst = ExcelReader.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack);
        }
        //If lst null return
        if (lst == null) {
            return lst;
        }
        String error = "";
        ValidateCells validateCells = null;
        int index = 0;
        if (iBeginRow == 0) {
            index = 1;
        } else {
            index = 0;
        }
        int rowErr = 0;
        Object[] temp;
        List<String> lstReturn = Lists.newArrayList();
        for (int i = index, size = lst.size(); i < size; i++) {
            temp = (Object[]) lst.get(i);
            if (checkObjectNull(temp)) {
                //                    lst.remove(temp);
                //                    i--;
                //                    continue;
                if (i == 0) {
                    lst = Lists.newArrayList();
                }
                break;
            }
            error = "";
            for (int j = 0; j < lstValidateCells.size(); j++) {
                validateCells = lstValidateCells.get(j);
                if (validateCells.getPattern() != null) {
                    error += DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(),
                            validateCells.isIsNotNull(), validateCells.getPattern());
                } else {
                    lstReturn = DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(),
                            validateCells.isIsNotNull(), validateCells.getLength());
                    error += lstReturn.get(0);
                    temp[j] = lstReturn.get(1);
                }
            }
            if (!isStringNullOrEmpty(error)) {
                rowErr = i + iBeginRow;
                mapsNameError.put(rowErr + "", error);
            }
        }

        if (!mapsNameError.isEmpty()) {
            //
            FileInputStream flieInput = new FileInputStream(file);
            XSSFWorkbook workbookIp = null;
            String fileCreate = fileName + "_Error.xlsx";
            FileOutputStream fileOut = new FileOutputStream(fileCreate);
            XSSFWorkbook workbookEp = new XSSFWorkbook();
            XSSFSheet worksheetEp = workbookEp.createSheet("Thong_Tin_Loi");
            XSSFCellStyle cellStyle = null;
            //
            if (isCopySheet) {
                if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx
                    workbookIp = new XSSFWorkbook(flieInput);
                } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls
                    HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(flieInput);
                    workbookIp = ExcelReaderXLSX.convertWorkbookHSSFToXSSF(hSSFWorkbook);
                }
                XSSFSheet worksheetIp = workbookIp.getSheetAt(iSheet);
                ExcelReaderXLSX.copySheets(worksheetEp, worksheetIp, iToCol);
                //
                cellStyle = CommonUtils.styleCell(workbookEp);
                isCopySheet = false;
            }
            for (Map.Entry<String, String> entrySet : mapsNameError.entrySet()) {
                String key = entrySet.getKey();
                String value = entrySet.getValue();
                int row = Integer.valueOf(key);
                XSSFRow row5 = worksheetEp.getRow(row);
                if (row5 != null) {
                    XSSFCell cellB1 = row5.createCell(iToCol + 1);
                    cellB1.setCellValue(value);
                    cellB1.setCellStyle(cellStyle);
                }
            }
            workbookEp.write(fileOut);
            fileOut.flush();
            fileOut.close();
            fileError = new File(fileCreate);
            Resource resource = new FileResource(fileError);
            Page.getCurrent().open(resource, null, false);
            lst = null;
            fileError.deleteOnExit();
        }

    } catch (Exception e) {
        e.printStackTrace();
        ;
        lst = null;
    }
    return lst;
}

From source file:com.cn.util.ExcelImport.java

/**
* ?2007excel/*  www  .  j  a  va 2 s .  c o  m*/
* 
* @param file
* @return
*/
private static List<List<Object>> read2007Excel(InputStream inputStream) throws IOException {
    List<List<Object>> dataList = new ArrayList<>();
    XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = xwb.getSheetAt(0);
    XSSFRow row = null;
    XSSFCell cell = null;
    Object val = null;
    DecimalFormat df = new DecimalFormat("0");// ?
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ?

    for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        List<Object> objList = new ArrayList<>();
        for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell == null) {
                val = null;
                objList.add(val);
                continue;
            }
            switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    val = df.format(cell.getNumericCellValue());
                } else {
                    val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                }
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                val = cell.getBooleanCellValue();
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                val = "";
                break;
            default:
                val = cell.toString();
                break;
            }
            objList.add(val);
        }
        dataList.add(objList);
    }
    return dataList;
}

From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java

License:Apache License

private void readXmlBasedExcel(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data)
        throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        Cell cell = null;//from   ww  w  . j  av a2  s . co m

        List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>();

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }

            Map<String, String> columnData = new HashMap<String, String>();

            for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK);

                columnData.put("column" + (cellIndex + 1), cell.toString());
            }

            sheetData.add(columnData);
        }

        data.put("sheet" + (sheetIndex + 1), sheetData);
    }

}

From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java

License:Apache License

@SuppressWarnings("rawtypes")
private void readXmlBasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(bufferedStream);

    int sheetCount = workbook.getNumberOfSheets();

    for (int index = 0; index < sheetCount; index++) {
        XSSFSheet sheet = workbook.getSheetAt(index);

        Sheet s = new Sheet();

        sheets.add(s);//from   w  w  w .java 2 s.c  o  m

        int lastRowNumber = sheet.getLastRowNum();

        for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);

            Record record = new Record();

            s.addRecord(record);

            Iterator it = row.cellIterator();

            while (it.hasNext()) {
                record.addCellValue(it.next());
            }
        }
    }

}

From source file:com.creditcloud.carinsurance.CarInsuranceFeeServiceBean.java

/**
 * ??/*from ww w  .j  av  a  2s .c om*/
 *
 * @param feeFileExcel
 */
public void bacthUpdateCarInsuranceFeeSatatus(File feeFileExcel) {
    try {
        FileInputStream file = new FileInputStream(feeFileExcel);
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        String insuranceNum = "";
        int currentPeriod = 0;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    currentPeriod = (int) cell.getNumericCellValue();
                    System.out.print((int) cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    insuranceNum = cell.getStringCellValue().trim();
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                }
            }
            CarInsuranceFee fee = carInsuranceFeeDAO.findByInSuranceNumAndCurrentPeriod(insuranceNum,
                    currentPeriod);
            //?
            if (fee != null) {
                updateCarInsuranceFeeSatatus(fee.getId(), CarInsuranceStatus.CLEARED);
            }
            System.out.println("");
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:com.ctb.importdata.ImportSFDataProcessor.java

public static ArrayList<SalesForceLicenseData> readDataFromXLSXFile(String fileName) {
    File sfDataFile = new File(fileName);
    FileInputStream fileInputStream = null;
    ArrayList<SalesForceLicenseData> sfLicenseDataList = null;

    //read the file in to stream
    if (sfDataFile.exists()) {
        //System.out.println("Reading data from .xlsx file started.");
        logger.info("Reading data from .xlsx file : Started :: Timestamp >> "
                + new Date(System.currentTimeMillis()));
        try {/*from w ww .  ja v a  2  s .  c om*/
            fileInputStream = new FileInputStream(sfDataFile);

            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            sfLicenseDataList = new ArrayList<SalesForceLicenseData>();
            SalesForceLicenseData sfld = null;

            if (sheet != null) {
                int totalRows = sheet.getPhysicalNumberOfRows();
                //System.out.println("Total no. of physical rows in file = "+ totalRows);
                logger.info("Total no. of physical rows in file = " + totalRows);
                Row headerRow = sheet.getRow(0);
                Cell headerCell;
                Cell dataCell;
                if (headerRow == null) {
                    //System.out.println("No file header content found.") ;
                    logger.info("No file header content found.");
                } else {
                    int totalHeaderColumns = headerRow.getPhysicalNumberOfCells();
                    //System.out.println("Total no. of header cells = "+ totalHeaderColumns);
                    //logger.info("Total no. of header cells = "+ totalHeaderColumns);
                    for (int rowCtr = 1; rowCtr < totalRows; rowCtr++) {
                        //System.out.println("Row No. >> "+rowCtr);
                        Row dataRow = sheet.getRow(rowCtr);
                        if (dataRow != null) {
                            int totalRowColumns = dataRow.getPhysicalNumberOfCells();
                            //System.out.println("Total no. of current data row cells = "+ totalRowColumns);
                            //logger.info("Total no. of current data row cells = "+ totalRowColumns);
                            logger.info(
                                    "Row No. [" + rowCtr + "] :: Header Column Count = [" + totalHeaderColumns
                                            + "] :: Current Data Row Column Count = [" + totalRowColumns + "]");
                            //Discard dummy rows in spreadsheet if the count of current data row columns not equal to header columns
                            if (totalHeaderColumns == totalRowColumns) {
                                boolean isCustomerIdBlank = dataRow.getCell(0)
                                        .getCellType() == Cell.CELL_TYPE_BLANK ? true : false;
                                boolean isOrgNodeIdBlank = dataRow.getCell(5)
                                        .getCellType() == Cell.CELL_TYPE_BLANK ? true : false;
                                //System.out.println("isCustomerIdBlank >> "+isCustomerIdBlank+" :: isOrgNodeIdBlank >> "+isOrgNodeIdBlank);
                                logger.info("Row No. [" + rowCtr + "] :: isCustomerIdBlank >> "
                                        + isCustomerIdBlank + " :: isOrgNodeIdBlank >> " + isOrgNodeIdBlank);
                                //Condition to skip row for SF data object population if customer id or orgnode id is blank
                                if (!isCustomerIdBlank && !isOrgNodeIdBlank) {
                                    sfld = new SalesForceLicenseData();

                                    // For each row, loop through each column
                                    for (int colCtr = 0; colCtr < totalHeaderColumns; colCtr++) {
                                        //System.out.println("Column No. >> "+colCtr);
                                        headerCell = headerRow.getCell(colCtr);
                                        dataCell = dataRow.getCell(colCtr);
                                        if (dataCell != null) {
                                            //System.out.println("dataCell.getCellType() >> "+dataCell.getCellType());
                                            switch (dataCell.getCellType()) {
                                            case Cell.CELL_TYPE_BOOLEAN:
                                                //Do nothing
                                                System.out.println(dataCell.getBooleanCellValue());
                                                break;

                                            case Cell.CELL_TYPE_NUMERIC:
                                                //System.out.println(dataCell.getNumericCellValue());
                                                populateSFDataNumericColValue(sfld, dataCell, headerCell);
                                                break;

                                            case Cell.CELL_TYPE_STRING:
                                                //System.out.println(dataCell.getStringCellValue());
                                                populateSFDataStrColValue(sfld, dataCell, headerCell);
                                                break;

                                            case Cell.CELL_TYPE_BLANK:
                                                //System.out.println(" ");
                                                populateSFDataBlankColValue(sfld, dataCell, headerCell);
                                                break;

                                            default:
                                                System.out.println(dataCell);
                                                break;
                                            }
                                        }
                                    }

                                    sfLicenseDataList.add(sfld);
                                }
                            }
                        }
                    }

                }
            }

        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException : occurred while procesing :: Filename >> [" + fileName + "]");
            e.printStackTrace(); // unexpected            
        } catch (IOException e) {
            logger.error("IOException : occurred while procesing :: Filename >> [" + fileName + "]");
            e.printStackTrace();
        } finally {
            try {
                if (fileInputStream != null)
                    fileInputStream.close();
            } catch (IOException e) {
                logger.error("IOException : occurred while closing file input stream.");
                e.printStackTrace();
            }
        }
        //System.out.println("Reading data from .xlsx file completed.");
        logger.info("Reading data from .xlsx file : Completed :: Timestamp >> "
                + new Date(System.currentTimeMillis()));
    } else {
        //System.out.println("File does not exists");
        logger.error("File does not exists :: Filename >> [" + fileName + "]");
    }
    return sfLicenseDataList;
}

From source file:com.ddt.driver.EXCELDriver.java

private XSSFSheet getSheetName(String sheetName, XSSFWorkbook workbook) {
    if (sheetName == null) {
        return workbook.getSheetAt(0);
    } else {//from  w  w w.  j a  v a 2 s .com
        return workbook.getSheet(sheetName);
    }
}

From source file:com.denimgroup.threadfix.csv2ssl.parser.FormatParser.java

License:Mozilla Public License

public static Option<String[]> getHeadersExcel(File file) {
    try {//  w ww. j  a  v  a2s .c o m
        FileInputStream fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);

        XSSFSheet ws = wb.getSheetAt(0); // read the first sheet
        int totalRows = ws.getLastRowNum();

        if (totalRows == 0) {
            return Option.failure("No lines found in file " + file.getName());
        }

        XSSFRow row = ws.getRow(0);

        String[] headers = new String[row.getLastCellNum()];

        for (int index = 0; index < row.getLastCellNum(); index++) {
            XSSFCell cell = row.getCell(index);

            assert cell != null : "Got null cell at index " + index;

            headers[index] = cell.toString();
        }

        return Option.success(headers);

    } catch (IOException e) {
        e.printStackTrace();
        return Option.failure("Encountered IOException.");
    }
}

From source file:com.denimgroup.threadfix.csv2ssl.serializer.RecordToXMLSerializer.java

License:Mozilla Public License

public static String getFromExcel(XSSFWorkbook wb, String... format) {
    StringBuilder builder = getStart();

    int line = Configuration.CONFIG.shouldSkipFirstLine ? 1 : 0;

    XSSFSheet ws = wb.getSheetAt(0); // read the first sheet
    int totalColumns = ws.getRow(0).getLastCellNum();
    int totalRows = ws.getLastRowNum();
    Map<String, String> rowMap = map();

    for (; line <= totalRows; line++) { // we want <= because the index returned from ws.getLastRowNum() is valid
        XSSFRow row = ws.getRow(line);//from   w ww .j  a  v  a 2  s .c o  m

        for (int column = 0; column < totalColumns; column++) {
            XSSFCell cell = row.getCell(column);

            if (cell == null) {
                // cells are null if there's no data in them; this is fine.
                continue;
            }

            String value = cell.toString();

            if (format.length > column) {
                rowMap.put(format[column], value);
            } else {
                System.err.println("format wasn't long enough for column. Column length = " + totalColumns
                        + ", format was " + format.length);
            }
        }

        addRecord(builder, line, rowMap);
        rowMap.clear();
    }

    return writeEnd(builder);
}