Example usage for org.apache.poi.ss.usermodel Workbook 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.projectswg.tools.ExcelToIff.java

License:Open Source License

private static void convertWorkbook(String path) {
    File file = new File(path);
    if (!file.exists()) {
        System.err.println(String.format("Could not convert %s as it doesn't exist!", path));
        return;/*from  www.ja v  a  2 s.co m*/
    }

    try {
        Workbook workbook = WorkbookFactory.create(file);
        System.out.println("Converting sheets from workbook " + file.getAbsolutePath());
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            path = file.getAbsolutePath().split("\\.")[0] + "_" + sheet.getSheetName() + ".iff";
            convertSheet(new File(path), sheet);
        }
        System.out.println("Conversion for workbook " + file.getAbsolutePath() + " completed.");
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
}

---

From source file:com.projectswg.tools.ExcelToIff.java

License:Open Source License

private static void convertSheet(String path, String sheetStr) {
    File file = new File(path);
    if (!file.exists()) {
        System.err.println(String.format("Could not convert %s as it doesn't exist!", path));
        return;/*from   ww w  .  ja  v  a2  s .c  o  m*/
    }

    try {
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheet(sheetStr);
        if (sheet == null)
            sheet = workbook.getSheetAt(Integer.valueOf(sheetStr));
        if (sheet == null) {
            System.err.println(String.format("Could not convert %s as there is no sheet name or id that is %s",
                    path, sheetStr));
        }
        System.out
                .println("Converting sheet " + sheet.getSheetName() + " in workbook " + file.getAbsolutePath());
        convertSheet(new File(file.getAbsolutePath().split("\\.")[0] + "_" + sheet.getSheetName() + ".iff"),
                sheet);
        System.out.println("Conversion for sheet " + sheet.getSheetName() + " in workbook "
                + file.getAbsolutePath() + " completed.");
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
}

---

From source file:com.qihang.winter.poi.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 ww .j a  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);
        }
        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) {
        LOGGER.error(e.getMessage(), e);
        return null;
    }
    return wb;
}

---

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * Excel  field  Integer,Long,Double,Date,String,Boolean
 *
 * @param inputstream// w w w .  j a  v a 2 s.  c  o m
 * @param pojoClass
 * @param params
 * @return
 * @throws Exception
 */
public com.qihang.winter.poi.excel.entity.result.ExcelImportResult importExcelByIs(InputStream inputstream,
        Class<?> pojoClass, com.qihang.winter.poi.excel.entity.ImportParams params) throws Exception {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel import start ,class is {}", pojoClass);
    }
    List<T> result = new ArrayList<T>();
    Workbook book = null;
    boolean isXSSFWorkbook = true;
    if (!(inputstream.markSupported())) {
        inputstream = new PushbackInputStream(inputstream, 8);
    }
    if (POIFSFileSystem.hasPOIFSHeader(inputstream)) {
        book = new HSSFWorkbook(inputstream);
        isXSSFWorkbook = false;
    } else if (POIXMLDocument.hasOOXMLHeader(inputstream)) {
        book = new XSSFWorkbook(OPCPackage.open(inputstream));
    }
    createErrorCellStyle(book);
    Map<String, PictureData> pictures;
    for (int i = 0; i < params.getSheetNum(); i++) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(" start to read excel by is ,startTime is {}", new Date().getTime());
        }
        if (isXSSFWorkbook) {
            pictures = com.qihang.winter.poi.util.PoiPublicUtil
                    .getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book);
        } else {
            pictures = com.qihang.winter.poi.util.PoiPublicUtil
                    .getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book);
        }
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(" end to read excel by is ,endTime is {}", new Date().getTime());
        }
        result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug(" end to read excel list by pos ,endTime is {}", new Date().getTime());
        }
    }
    String excelName = "";
    if (params.isNeedSave()) {
        excelName = saveThisExcel(params, pojoClass, isXSSFWorkbook, book);
    }
    return new com.qihang.winter.poi.excel.entity.result.ExcelImportResult(result, verfiyFail, book, excelName);
}

---

From source file:com.qualogy.qafe.service.DocumentServiceImpl.java

License:Apache License

private DocumentOutput handleExcel2003(DocumentParameter parameter) throws IOException {
    DocumentOutput out = null;//from  w ww  .ja  v a  2 s  .  co  m
    String uuid = UUIDHelper.generateUUID();
    POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(parameter.getData()));
    Workbook workbook = WorkbookFactory.create(fs);
    Sheet sheet = workbook.getSheetAt(0);
    out = handleExcelData(sheet, parameter.isFirstFieldHeader());
    out.setUuid(uuid);
    return out;
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.POIExcerpterAndMerger.java

License:Apache License

protected List<Sheet> identifySheets(int[] sheetNums, Workbook workbook) {
    int maxSheetNumber = workbook.getNumberOfSheets() - 1;

    List<Sheet> sheets = new ArrayList<Sheet>(sheetNums.length);
    for (int sn : sheetNums) {
        if (sn > maxSheetNumber)
            throw new IllegalArgumentException("Sheet not found with index '" + sn + "'");

        sheets.add(workbook.getSheetAt(sn));
    }/*from w ww .jav  a2 s.  com*/
    return sheets;
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptGoesReadOnly() throws Exception {
    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

        Sheet s = wb.createSheet("Test");

        // Numeric formulas
        Row r1 = s.createRow(0);//from  w ww . j av a2  s.c  om
        Cell c1 = r1.createCell(0);
        Cell c2 = r1.createCell(1);
        Cell c3 = r1.createCell(2);
        Cell c4 = r1.createCell(3);

        c1.setCellValue(1);
        c2.setCellValue(2);
        c3.setCellFormula("A1+B1");
        c4.setCellFormula("(A1+B1)*B1");

        // Strings, booleans and errors
        Row r2 = s.createRow(1);
        Cell c21 = r2.createCell(0);
        Cell c22 = r2.createCell(1);
        Cell c23 = r2.createCell(2);
        Cell c24 = r2.createCell(3);

        c21.setCellValue("Testing");
        c22.setCellFormula("CONCATENATE(A2,A2)");
        c23.setCellFormula("FALSE()");
        c24.setCellFormula("A1/0");

        // Ensure the formulas are current
        eval.evaluateAll();

        // Run the excerpt
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        excerpter.excerpt(new int[] { 0 }, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(1, newwb.getNumberOfSheets());

        s = newwb.getSheetAt(0);
        r1 = s.getRow(0);
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType());

        assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001);

        r2 = s.getRow(1);
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType());

        assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue());
        assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue());
        assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue());
    }
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptRemovesUnUsed() throws Exception {
    String[] names = new String[] { "a", "b", "ccc", "dddd", "e", "f", "gg" };

    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        // Create some dummy content
        for (String sn : names) {
            Sheet s = wb.createSheet(sn);
            s.createRow(0).createCell(0).setCellValue(sn);
        }/*from   w  w  w .  java2 s  .  co  m*/

        // Excerpt by index
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        int[] excI = new int[] { 0, 1, 2, 4, 5 };
        excerpter.excerpt(excI, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(5, newwb.getNumberOfSheets());
        assertEquals(names[excI[0]], newwb.getSheetName(0));
        assertEquals(names[excI[1]], newwb.getSheetName(1));
        assertEquals(names[excI[2]], newwb.getSheetName(2));
        assertEquals(names[excI[3]], newwb.getSheetName(3));
        assertEquals(names[excI[4]], newwb.getSheetName(4));

        assertEquals(names[excI[0]], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[1]], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[2]], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[3]], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[4]], newwb.getSheetAt(4).getRow(0).getCell(0).getStringCellValue());

        // Excerpt by name
        String[] excN = new String[] { "b", "ccc", "f", "gg" };
        baos = new ByteArrayOutputStream();
        excerpter.excerpt(excN, tmp, baos);

        newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(4, newwb.getNumberOfSheets());
        assertEquals(excN[0], newwb.getSheetName(0));
        assertEquals(excN[1], newwb.getSheetName(1));
        assertEquals(excN[2], newwb.getSheetName(2));
        assertEquals(excN[3], newwb.getSheetName(3));

        assertEquals(excN[0], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[1], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[2], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[3], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());

        // Can't excerpt by invalid index
        try {
            excerpter.excerpt(new int[] { 0, 10 }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }

        // Can't excerpt by invalid name
        try {
            excerpter.excerpt(new String[] { "a", "invalid" }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }
    }
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testExport() throws IOException {
    ExcelExporter exporter = new ExcelExporter();
    exporter.addTemplate(mdBusiness.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(1, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);//from   ww w.ja  v a  2 s.com
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(attributes.size()));
    assertNull(labelRow.getCell(attributes.size()));
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testFormExport() throws IOException {
    ExcelExporter exporter = new FormExcelExporter(new MdWebAttributeFilter());
    exporter.addTemplate(mdForm.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(1, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);/*from   ww w.  j a  v a 2s .  c  o  m*/
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdFieldDAOIF> fields = mdForm.getSortedFields();

    for (int i = 0; i < fields.size(); i++) {
        MdFieldDAOIF mdField = fields.get(i);
        MdAttributeDAOIF mdAttribute = ((MdWebAttributeDAOIF) mdField).getDefiningMdAttribute();

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdField.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(fields.size()));
    assertNull(labelRow.getCell(fields.size()));
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »