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:cherry.parser.worksheet.RowBasedParser.java

License:Apache License

@Override
public List<TypeDef> parse(Workbook workbook) {
    List<TypeDef> list = new LinkedList<TypeDef>();
    int numOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        list.addAll(parseSheet(sheet));/*from w  w w  . j  a va  2s . c om*/
    }
    return list;
}

From source file:cherry.parser.worksheet.SheetBasedParser.java

License:Apache License

@Override
public List<TypeDef> parse(Workbook workbook) {
    List<TypeDef> list = new LinkedList<TypeDef>();
    int numOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        TypeDef typeDef = parseSheet(sheet);
        if (typeDef != null) {
            list.add(typeDef);/*from  w  ww .  j  ava 2 s  .c o m*/
        }
    }
    return list;
}

From source file:cht.Parser.java

License:Apache License

public static void main(String[] args) throws IOException {

    // TODO get from google drive
    boolean isUnicode = false;
    boolean isRemoveInputFileOnComplete = false;
    int rowNum;/*w ww  .j  a v a  2s  .co  m*/
    int colNum;
    Gson gson = new GsonBuilder().setPrettyPrinting().create();

    Properties prop = new Properties();

    try {
        prop.load(new FileInputStream("config.txt"));
    } catch (IOException ex) {
        ex.printStackTrace();
    }

    String inputFilePath = prop.getProperty("inputFile");
    String outputDirectory = prop.getProperty("outputDirectory");
    System.out.println(outputDirectory);
    // optional
    String unicode = prop.getProperty("unicode");
    String removeInputFileOnComplete = prop.getProperty("removeInputFileOnComplete");

    inputFilePath = inputFilePath.trim();
    outputDirectory = outputDirectory.trim();

    if (unicode != null) {
        isUnicode = Boolean.parseBoolean(unicode.trim());
    }
    if (removeInputFileOnComplete != null) {
        isRemoveInputFileOnComplete = Boolean.parseBoolean(removeInputFileOnComplete.trim());
    }

    Writer out = null;
    FileInputStream in = null;
    final String newLine = System.getProperty("line.separator").toString();
    final String separator = File.separator;
    try {
        in = new FileInputStream(inputFilePath);

        Workbook workbook = new XSSFWorkbook(in);

        Sheet sheet = workbook.getSheetAt(0);

        rowNum = sheet.getLastRowNum() + 1;
        colNum = sheet.getRow(0).getPhysicalNumberOfCells();

        for (int j = 1; j < colNum; ++j) {
            String outputFilename = sheet.getRow(0).getCell(j).getStringCellValue();
            // guess directory
            int slash = outputFilename.indexOf('/');
            if (slash != -1) { // has directory
                outputFilename = outputFilename.substring(0, slash) + separator
                        + outputFilename.substring(slash + 1);
            }

            String outputPath = FilenameUtils.concat(outputDirectory, outputFilename);
            System.out.println("--Writing " + outputPath);
            out = new OutputStreamWriter(new FileOutputStream(outputPath), "UTF-8");
            TreeMap<String, Object> map = new TreeMap<String, Object>();
            for (int i = 1; i < rowNum; i++) {
                try {
                    String key = sheet.getRow(i).getCell(0).getStringCellValue();
                    //String value = "";
                    Cell tmp = sheet.getRow(i).getCell(j);
                    if (tmp != null) {
                        // not empty string!
                        value = sheet.getRow(i).getCell(j).getStringCellValue();
                    }
                    if (!key.equals("") && !key.startsWith("#") && !key.startsWith(".")) {
                        value = isUnicode ? StringEscapeUtils.escapeJava(value) : value;

                        int firstdot = key.indexOf(".");
                        String keyName, keyAttribute;
                        if (firstdot > 0) {// a.b.c.d 
                            keyName = key.substring(0, firstdot); // a
                            keyAttribute = key.substring(firstdot + 1); // b.c.d
                            TreeMap oldhash = null;
                            Object old = null;
                            if (map.get(keyName) != null) {
                                old = map.get(keyName);
                                if (old instanceof TreeMap == false) {
                                    System.out.println("different type of key:" + key);
                                    continue;
                                }
                                oldhash = (TreeMap) old;
                            } else {
                                oldhash = new TreeMap();
                            }

                            int firstdot2 = keyAttribute.indexOf(".");
                            String rootName, childName;
                            if (firstdot2 > 0) {// c, d.f --> d, f
                                rootName = keyAttribute.substring(0, firstdot2);
                                childName = keyAttribute.substring(firstdot2 + 1);
                            } else {// c, d  -> d, null
                                rootName = keyAttribute;
                                childName = null;
                            }

                            TreeMap<String, Object> object = myPut(oldhash, rootName, childName);
                            map.put(keyName, object);

                        } else {// c, d  -> d, null
                            keyName = key;
                            keyAttribute = null;
                            // simple string mode
                            map.put(key, value);
                        }

                    }

                } catch (Exception e) {
                    // just ingore empty rows
                }

            }
            String json = gson.toJson(map);
            // output json
            out.write(json + newLine);
            out.close();
        }
        in.close();

        System.out.println("\n---Complete!---");
        System.out.println("Read input file from " + inputFilePath);
        System.out.println(colNum - 1 + " output files ate generated at " + outputDirectory);
        System.out.println(rowNum + " records are generated for each output file.");
        System.out.println("output file is ecoded as unicode? " + (isUnicode ? "yes" : "no"));
        if (isRemoveInputFileOnComplete) {
            File input = new File(inputFilePath);
            input.deleteOnExit();
            System.out.println("Deleted " + inputFilePath);
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (in != null) {
            in.close();
        }
    }

}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcelCloneByTemplate(TemplateExportParams params,
        Map<Integer, List<Map<String, Object>>> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }/*from   w w  w. j  a v  a  2 s.c  o m*/
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = ExcelCache.getWorkbook(teplateParams.getTemplateUrl(), teplateParams.getSheetNum(), true);
        int oldSheetNum = wb.getNumberOfSheets();
        List<String> oldSheetName = new ArrayList<>();
        for (int i = 0; i < oldSheetNum; i++) {
            oldSheetName.add(wb.getSheetName(i));
        }
        // KEY?
        List<Map<String, Object>> mapList;
        List<Integer> sheetNumList = new ArrayList<>();
        sheetNumList.addAll(map.keySet());
        Collections.sort(sheetNumList);

        //???
        for (Integer sheetNum : sheetNumList) {
            mapList = map.get(sheetNum);
            for (int i = mapList.size(); i > 0; i--) {
                wb.cloneSheet(sheetNum);
            }
        }
        for (int i = 0; i < oldSheetName.size(); i++) {
            wb.removeSheetAt(wb.getSheetIndex(oldSheetName.get(i)));
        }
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        int sheetIndex = 0;
        for (Integer sheetNum : sheetNumList) {
            mapList = map.get(sheetNum);
            for (int i = mapList.size() - 1; i >= 0; i--) {
                tempCreateCellSet.clear();
                if (mapList.get(i).containsKey(SHEET_NAME)) {
                    wb.setSheetName(sheetIndex, mapList.get(i).get(SHEET_NAME).toString());
                }
                parseTemplate(wb.getSheetAt(sheetIndex), mapList.get(i), params.isColForEach());
                sheetIndex++;
            }
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcelByTemplate(TemplateExportParams params, Map<Integer, Map<String, Object>> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }/*  w w w .  j ava  2s  .com*/
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map.get(i), params.isColForEach());
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcelByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet,
        Map<String, Object> map) {
    // step 1. ??
    if (params == null || map == null
            || (StringUtils.isEmpty(params.getTemplateUrl()) && params.getTemplateWb() == null)) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }//from w w  w  . j  a  v  a  2  s .co  m
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        if (params.getTemplateWb() != null) {
            wb = params.getTemplateWb();
        } else {
            wb = getCloneWorkBook();
        }
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map, params.isColForEach());
        }
        if (dataSet != null) {
            // step 4. ?
            dataHandler = params.getDataHandler();
            if (dataHandler != null) {
                needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());
            }
            addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
        }
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

/**
 * Excel  field  Integer,Long,Double,Date,String,Boolean
 *//* w w w .  ja v  a  2 s. c  o m*/
public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass, ImportParams params,
        boolean needMore) throws Exception {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel import start ,class is {}", pojoClass);
    }
    List<T> result = new ArrayList<T>();
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    ExcelImportResult importResult;
    try {
        byte[] buffer = new byte[1024];
        int len;
        while ((len = inputstream.read(buffer)) > -1) {
            baos.write(buffer, 0, len);
        }
        baos.flush();

        InputStream userIs = new ByteArrayInputStream(baos.toByteArray());
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Excel clone success");
        }
        Workbook book = WorkbookFactory.create(userIs);

        boolean isXSSFWorkbook = !(book instanceof HSSFWorkbook);
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Workbook create success");
        }
        importResult = new ExcelImportResult();
        createErrorCellStyle(book);
        Map<String, PictureData> pictures;
        for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex() + params.getSheetNum(); i++) {
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug(" start to read excel by is ,startTime is {}", new Date());
            }
            if (isXSSFWorkbook) {
                pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i),
                        (XSSFWorkbook) book);
            } else {
                pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i),
                        (HSSFWorkbook) book);
            }
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug(" end to read excel by is ,endTime is {}", new Date());
            }
            result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug(" end to read excel list by sheet ,endTime is {}", new Date());
            }
            if (params.isReadSingleCell()) {
                readSingleCell(importResult, book.getSheetAt(i), params);
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug(" read Key-Value ,endTime is {}", System.currentTimeMillis());
                }
            }
        }
        if (params.isNeedSave()) {
            saveThisExcel(params, pojoClass, isXSSFWorkbook, book);
        }
        importResult.setList(result);
        if (needMore) {
            InputStream successIs = new ByteArrayInputStream(baos.toByteArray());
            try {
                Workbook successBook = WorkbookFactory.create(successIs);
                importResult.setWorkbook(removeSuperfluousRows(successBook, failRow, params));
                importResult.setFailWorkbook(removeSuperfluousRows(book, successRow, params));
                importResult.setFailList(failCollection);
                importResult.setVerfiyFail(verifyFail);
            } finally {
                successIs.close();
            }
        }
    } finally {
        IOUtils.closeQuietly(baos);
    }

    return importResult;
}

From source file:cn.afterturn.easypoi.excel.imports.ExcelImportService.java

License:Apache License

private Workbook removeSuperfluousRows(Workbook book, List<Row> rowList, ImportParams params) {
    for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex() + params.getSheetNum(); i++) {
        for (int j = rowList.size() - 1; j >= 0; j--) {
            if (rowList.get(j).getRowNum() < rowList.get(j).getSheet().getLastRowNum()) {
                book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1,
                        rowList.get(j).getSheet().getLastRowNum(), -1);
            } else if (rowList.get(j).getRowNum() == rowList.get(j).getSheet().getLastRowNum()) {
                book.getSheetAt(i).createRow(rowList.get(j).getRowNum() + 1);
                book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1,
                        rowList.get(j).getSheet().getLastRowNum() + 1, -1);
            }//  w w w  .j  a v a  2 s. c  o  m
        }
    }
    return book;
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet,
        Map<String, Object> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }/*from  w w w .ja va  2 s. c o m*/
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // ?
        setExcelExportStyler(
                (IExcelExportStyler) teplateParams.getStyle().getConstructor(Workbook.class).newInstance(wb));
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map, params.isColForEach());
        }
        if (dataSet != null) {
            // step 4. ?
            dataHanlder = params.getDataHanlder();
            if (dataHanlder != null) {
                needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
            }
            addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
        }
    } catch (Exception e) {
        e.getMessage();
        return null;
    }
    return wb;
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

public Workbook createExcleByTemplate(TemplateExportParams params, Map<Integer, Map<String, Object>> map) {
    // step 1. ??
    if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }/*from  w w  w.jav a2s  .  c om*/
    Workbook wb = null;
    // step 2. ?Excel,??
    try {
        this.teplateParams = params;
        wb = getCloneWorkBook();
        // step 3. ??
        for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets()
                : params.getSheetNum().length; i < le; i++) {
            if (params.getSheetName() != null && params.getSheetName().length > i
                    && StringUtils.isNotEmpty(params.getSheetName()[i])) {
                wb.setSheetName(i, params.getSheetName()[i]);
            }
            tempCreateCellSet.clear();
            parseTemplate(wb.getSheetAt(i), map.get(i), params.isColForEach());
        }
    } catch (Exception e) {
        e.getMessage();
        return null;
    }
    return wb;
}