Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.xandrev.altafitcalendargenerator.XLSExtractor.java

public HashMap<Integer, ArrayList<TimeTrack>> importExcelSheet(String fileName) {
    HashMap<Integer, String> tmpHours = new HashMap<Integer, String>();
    HashMap<Integer, ArrayList<TimeTrack>> out = new HashMap<Integer, ArrayList<TimeTrack>>();
    init(out);//from ww  w  .j  a va2 s  . c o m
    try {
        Workbook workBook = WorkbookFactory.create(new FileInputStream(fileName));
        Sheet sheet = workBook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        int rowIdx = 0;
        boolean started = false;
        boolean finished = false;
        while (rowIter.hasNext() && !finished) {
            XSSFRow row = (XSSFRow) rowIter.next();
            if (row != null && !started) {
                XSSFCell cell = row.getCell(0);
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    if (value == null || value.isEmpty() || !"HORA".equals(value)) {
                        rowIdx++;
                        started = true;
                        continue;
                    }
                }
                row = (XSSFRow) rowIter.next();
            }

            Iterator<Cell> cellIter = row.cellIterator();
            int cellIndex = 0;
            while (cellIter.hasNext()) {
                XSSFCell cell = (XSSFCell) cellIter.next();
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    installHashMap(tmpHours, out, cellIndex, rowIdx, value);
                }
                cellIndex++;
            }
            rowIdx++;
        }

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    return out;
}

From source file:com.xn.interfacetest.service.impl.TestCaseServiceImpl.java

License:Open Source License

private StringBuffer readExcel(String path) throws Exception {
    // ?Excel//from  ww  w.  ja  v  a 2 s  . co m
    InputStream excelFile = new FileInputStream(path);

    //?????
    StringBuffer failCaseNumbers = new StringBuffer("");
    try {
        Workbook wb = WorkbookFactory.create(new File(path));
        Sheet sheet = wb.getSheetAt(0);
        // 
        int rowNum = sheet.getLastRowNum() + 1;
        logger.info("rowNum" + rowNum);

        //?1
        Row row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells(); //?
        logger.info("colNum" + colNum);

        // 2,
        for (int i = 1; i < rowNum; i++) {
            TestCaseDto caseDto = new TestCaseDto();
            logger.info("??" + i);
            row = sheet.getRow(i);
            //???
            //?---?
            String number = getCellFormatValue(row.getCell(0)) + "";
            //?
            if (!checkCaseNumberUnique(number)) {
                failCaseNumbers.append("?").append(number)
                        .append("??");
                continue;
            }
            caseDto.setNumber(number);

            //?---??
            caseDto.setName(getCellFormatValue(row.getCell(1)) + "");

            //?---??
            caseDto.setDescription(getCellFormatValue(row.getCell(2)) + "");

            //?---?id,?id?id?
            if (StringUtils.isBlank(getCellFormatValue(row.getCell(3)) + "")
                    || !checkInterfaceIdExist(Long.parseLong(getCellFormatValue(row.getCell(3)) + ""))) {
                failCaseNumbers.append("?").append(number)
                        .append("???id")
                        .append(row.getCell(3)).append("");
                continue;
            }
            caseDto.setInterfaceId(Long.parseLong(getCellFormatValue(row.getCell(3)) + ""));

            //5?---?
            caseDto.setCustomParams(getCellFormatValue(row.getCell(4)) + "");
            caseDto.setCustomParamsType(ParamsGroupTypeEnum.CUSTOM.getId());

            //6?---?
            caseDto.setCustomParamsType(AppendParamEnum.getIdByName(getCellFormatValue(row.getCell(5)) + ""));

            //10-
            if ("SINGLE".equals(getCellFormatValue(row.getCell(9)))
                    || "MUTIPLE".equals(getCellFormatValue(row.getCell(9)))) {
                caseDto.setType(getCellFormatValue(row.getCell(9)) + "");
            } else {
                failCaseNumbers.append("?").append(number)
                        .append("???\"MUTIPLE\"\"SINGLE\"");
                continue;
            }

            caseDto = this.save(caseDto);
            logger.info("?" + caseDto.toString());
            //7?---?
            String assertJson = getCellFormatValue(row.getCell(6)) + "";
            if (StringUtils.isNotBlank(assertJson)) {
                try {
                    //??
                    saveParamsAsserts(assertJson, caseDto);
                } catch (Exception e) {
                    logger.error("?", e);
                    failCaseNumbers.append("?").append(number).append(
                            "???,??");
                }
            }
            //8-?
            String prepareStr = getCellFormatValue(row.getCell(7)) + "";
            if (StringUtils.isNotBlank(prepareStr)) {
                try {
                    saveDataOperate(prepareStr, caseDto.getId(), OperationTypeEnum.PREPARE.getId(),
                            failCaseNumbers);
                    caseDto.setDataPrepare(1);
                    //
                    update(caseDto);
                } catch (Exception e) {
                    logger.error("?sql", e);
                    failCaseNumbers.append("?").append(number).append(
                            "????,??"
                                    + e.getMessage() + "");
                }
            }

            //9-?
            String clearStr = getCellFormatValue(row.getCell(8)) + "";
            if (StringUtils.isNotBlank(clearStr)) {
                try {
                    saveDataOperate(clearStr, caseDto.getId(), OperationTypeEnum.CLEAR.getId(),
                            failCaseNumbers);
                    caseDto.setDataClear(1);
                    //
                    update(caseDto);

                } catch (Exception e) {
                    logger.error("?sql", e);
                    failCaseNumbers.append("?").append(number).append(
                            "????,??"
                                    + e.getMessage() + "");
                }
            } else {
                continue;
            }

        }
    } catch (FileNotFoundException e) {
        logger.error("excel", e);
        throw e;
    } catch (IOException e) {
        logger.error("?excel", e);
        throw e;
    } finally {
        return failCaseNumbers;
    }
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?/*from   w w w  .  ja  v a2  s.  c o  m*/
 * @param FileName ???????excel
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (FileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // jsonkey
        String[] cellNames;
        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);
        // ???
        cellNames = new String[curCellNum];
        // ????JSONkey
        for (int m = 0; m < curCellNum; m++) {
            Cell cell = fisrtRow.getCell(m);
            // ?
            cell.setCellStyle(cellStyle);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            // ?
            cellNames[m] = getCellValue(cell);
        }
        for (String s : cellNames) {
            System.out.print("" + i + " sheet " + s + ",");
        }
        System.out.println();

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);

                cell.setCellStyle(cellStyle);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // ???
                rowMap.put(cellNames[k], getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?/*from  w w w  .  java  2 s  .co  m*/
 * @param FileName ???????excel
 * @param headers list,String-->Arrays.asList();
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (fileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);
                // ???
                rowMap.put(headers.get(k), getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.zlfun.framework.excel.ExcelUtils.java

private static <T> void fill(Class<T> clazz, List<T> result, String fileName, InputStream is) {

    try {//from  w w w .  j ava  2  s .c  o  m
        Workbook excel = null;
        if (fileName.indexOf(".xlsx") > 0) {
            excel = new XSSFWorkbook(is);// Excel2007
        } else if (fileName.indexOf(".xls") > 0) {
            excel = new HSSFWorkbook(is);// Excel2003

        } else {
            return;
        }
        FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = excel.getSheetAt(0);// ?0
        // ????1
        List<String> header = new ArrayList<String>();
        if (sheet.getLastRowNum() >= 0) {
            Row row = sheet.getRow(0);// ?

            for (int i = 0; i < row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);// ??

                if (cell != null) {// ?cellStr

                    header.add(cell.getStringCellValue());
                }

            }

        }

        //
        if (sheet.getLastRowNum() > 1) {

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {

                Row row = sheet.getRow(i);// ?
                if (row == null) {// ??
                    continue;
                }
                Map<String, String> map = genRowMap(row, header, evaluator);
                T t = fill(map, clazz.newInstance());
                result.add(t);
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InstantiationException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
        Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
    } finally {// ?
        if (is != null) {
            try {
                is.close();

            } catch (IOException ex) {
                Logger.getLogger(ExcelUtils.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    return;
}

From source file:com.znsx.cms.service.impl.DeviceManagerImpl.java

public List<Dvr> readDvrWb(Workbook wb, Organ organ) {
    List<Dvr> list = new ArrayList<Dvr>();
    try {/*  w  ww  .  jav  a2s  . co  m*/
        if (wb == null) {
            throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
        }
        Sheet dvrSheet = wb.getSheetAt(0);
        list = readDvrRows(dvrSheet, organ);
    } catch (POIXMLException p) {
        p.printStackTrace();
        throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
    }
    return list;
}

From source file:com.znsx.cms.service.impl.DeviceManagerImpl.java

public List<Camera> readCameraWB(Workbook wb, Organ organ, License license, List<Dvr> dvrs) {
    List<Camera> cList = new ArrayList<Camera>();
    try {//from   www. j av a 2s.c om
        Sheet cameraSheet = wb.getSheetAt(1);
        cList = readCameraRows(cameraSheet, organ, dvrs, license);
    } catch (POIXMLException p) {
        p.printStackTrace();
        throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
    }
    return cList;
}

From source file:com.znsx.cms.service.impl.TmDeviceManagerImpl.java

@Override
public List<FireDetector> readFireDetectorWb(Workbook wb, License license) {
    List<FireDetector> list = new ArrayList<FireDetector>();
    try {//from  ww w .j av a  2  s  .  co m
        if (wb == null) {
            throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
        }
        Sheet sheet = wb.getSheetAt(0);
        list = readFireDetectors(sheet);
    } catch (POIXMLException p) {
        p.printStackTrace();
        throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
    }
    return list;
}

From source file:com.znsx.cms.service.impl.TmDeviceManagerImpl.java

@Override
public List<ControlDeviceLight> readControlDeviceLightWb(Workbook wb, License license) {
    List<ControlDeviceLight> list = new ArrayList<ControlDeviceLight>();
    try {/*  ww  w.  j  a  v a  2s . com*/
        if (wb == null) {
            throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
        }
        Sheet sheet = wb.getSheetAt(0);
        list = readControlDeviceLightWbs(sheet);
    } catch (POIXMLException p) {
        p.printStackTrace();
        throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
    }
    return list;
}

From source file:com.znsx.cms.service.impl.TmDeviceManagerImpl.java

@Override
public List<ControlDeviceWp> readControlDeviceWpWb(Workbook wb, License license) {
    List<ControlDeviceWp> list = new ArrayList<ControlDeviceWp>();
    try {/*  w w w.ja  v  a  2s.  c  o m*/
        if (wb == null) {
            throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
        }
        Sheet sheet = wb.getSheetAt(0);
        list = readControlDeviceWps(sheet);
    } catch (POIXMLException p) {
        p.printStackTrace();
        throw new BusinessException(ErrorCode.EXCEL_FORMAT_INVALID, "Format of excel file invalid !");
    }
    return list;
}