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:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java

License:Open Source License

/**
 * Test - write data of complex schema and analyze result The
 * implementation, this test based on, does not work correctly at the
 * moment.//from   w  w  w  . j  av a  2  s .  com
 * 
 * @throws Exception , if an error occurs
 */
@Test
public void testWriteNotNestedProperties() throws Exception {

    TransformationExample example = TransformationExamples.getExample(TransformationExamples.SIMPLE_COMPLEX);
    // alternative the data could be generated by iterating through the
    // exempleproject's sourcedata
    List<String> header = Arrays.asList("id", "name");
    List<String> firstDataRow = Arrays.asList("id0", "name0");

    // set instances to xls instance writer
    XLSInstanceWriter writer = new XLSInstanceWriter();
    IContentType contentType = Platform.getContentTypeManager()
            .getContentType("eu.esdihumboldt.hale.io.xls.xls");
    writer.setParameter(InstanceTableIOConstants.SOLVE_NESTED_PROPERTIES, Value.of(false));

    File tmpFile = tmpFolder.newFile("excelNotNestedProperties.xls");

    writer.setInstances(example.getSourceInstances());
    // write instances to a temporary XLS file
    writer.setTarget(new FileIOSupplier(tmpFile));
    writer.setContentType(contentType);
    IOReport report = writer.execute(null);
    assertTrue(report.isSuccess());

    Workbook wb = WorkbookFactory.create(tmpFile);
    Sheet sheet = wb.getSheetAt(0);

    checkHeader(sheet, header);

    checkSheetName(sheet, "person");

    checkFirstDataRow(sheet, firstDataRow);
}

---

From source file:eu.esdihumboldt.hale.io.xls.ui.XLSInstanceImportConfigurationPage.java

License:Open Source License

/**
 * @see eu.esdihumboldt.hale.ui.HaleWizardPage#onShowPage(boolean)
 *//*from   w ww .  ja v  a  2  s  .  co  m*/
@Override
protected void onShowPage(boolean firstShow) {

    if (!firstShow) {
        setErrorMessage(null);
    }

    try {
        Workbook wb = WorkbookFactory.create(getWizard().getProvider().getSource().getInput());
        int numberOfSheets = wb.getNumberOfSheets();
        String[] items = new String[numberOfSheets];
        for (int i = 0; i < numberOfSheets; i++) {
            items[i] = wb.getSheetAt(i).getSheetName();
        }
        sheetSelection.setItems(items);
    } catch (Exception e) {
        setErrorMessage("Cannot load Excel file!");
        setPageComplete(false);
        return;
    }
    super.onShowPage(firstShow);
    sheetSelection.select(0);
    setPageComplete(true);
}

---

From source file:eu.esdihumboldt.hale.io.xls.ui.XLSSchemaTypePage.java

License:Open Source License

@Override
protected void onShowPage(boolean firstShow) {

    URI newLocation = getWizard().getProvider().getSource().getLocation();
    if (!firstShow && newLocation != null && !newLocation.equals(oldLocation)) {
        sheetNum = 0;/*from  w  w  w .  j ava 2s.c  o  m*/
    }

    try {
        Workbook wb = WorkbookFactory.create(getWizard().getProvider().getSource().getInput());

        int numberOfSheets = wb.getNumberOfSheets();
        if (sheetNum >= numberOfSheets) {
            sheetNum = 0;
        }
        ArrayList<String> items = new ArrayList<String>();
        for (int i = 0; i < numberOfSheets; i++) {
            items.add(wb.getSheetAt(i).getSheetName());
            // only add items if there is a header (no empty sheet)
            Row row = wb.getSheetAt(i).getRow(0);
            if (row == null && newLocation != null && !newLocation.equals(oldLocation)) {
                sheetNum++;
            }
        }

        sheet.setItems(items.toArray(new String[items.size()]));
        sheet.select(sheetNum);

        // try to update
        update(sheetNum);

        super.onShowPage(firstShow);

        // Overwrite super string field editor value
        Sheet sheet = wb.getSheetAt(sheetNum);
        setStringFieldEditorValue(sheet.getSheetName());

        oldLocation = newLocation;

    } catch (OldExcelFormatException e) {
        // the setup is not in a valid state
        clearPage();
        clearSuperPage();
        setErrorMessage(
                "Old excel format detected (format 5.0/7.0 (BIFF5)). Please convert the excel file to BIFF8 from Excel versions 97/2000/XP/2003.");
        setPageComplete(false);
    } catch (Exception e) {
        clearPage();
        clearSuperPage();
        setErrorMessage("Excel file cannot be loaded!");
        setPageComplete(false);
    }
}

---

From source file:excelUtils.Helper.java

License:Apache License

public static void main(String args[]) {
    System.out.println(Double.parseDouble("1330455.98"));
    try { //C:\Users\Joe\Desktop\Projects\Rec
        Workbook wb = ExcelUtils.openWorkbook(FileUtils.joinPath("C:", "Users", "Joe", "Desktop", "Projects",
                "Rec", "GS-SDI-Account_Balances_By_Currency-08_36_GMT-20140219-17040-0.html"));
        Cell c = ExcelUtils.getCell(wb.getSheetAt(0), 50, 10, ExcelUtils.CELL_CREATE_NULL_AS_BLANK);
        ExcelUtils.setCellFromString(c, "5555 Canary");
        System.out.println(ExcelUtils.saveWorkbook(wb, "HTMLtest.xls",
                FileUtils.joinPath("C:", "Users", "Joe", "Desktop", "Projects", "Rec"), false, true, true));
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();//  w  ww. j  a v a2  s. c o  m
    }
}

---

From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java

License:EUPL

void checkSheetContents(Workbook workbook, int sheetIndex, List<String>... rows) {
    try (AutoCloseableSoftAssertions softly = new AutoCloseableSoftAssertions()) {
        checkSheetContents(softly, workbook, sheetIndex, rows);
    } catch (SoftAssertionError sae) {
        System.out.println("===========\nACTUAL\n===========");
        printSheet(workbook.getSheetAt(sheetIndex));
        System.out.println("===========\nEXPECTED\n===========");
        printSheet(asList(rows));/*from   w  ww .  j a  v a2  s .  co  m*/
        throw sae;
    }
}

---

From source file:fi.hsl.parkandride.itest.AbstractReportingITest.java

License:EUPL

void checkSheetContents(AutoCloseableSoftAssertions softly, Workbook workbook, int sheetIndex,
        List<String>... rows) {
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    softly.assertThat(sheet.getPhysicalNumberOfRows()).as("Number of rows").isEqualTo(rows.length);
    for (int i = 0; i < rows.length; i++) {
        final List<String> dataFromRow = getDataFromRow(sheet, i);
        softly.assertThat(dataFromRow).as("Row index %d", i).containsExactlyElementsOf(rows[i]);
    }/*from  ww w .  j av a2 s  .  co m*/
}

---

From source file:fi.hsl.parkandride.itest.HubsAndFacilitiesReportITest.java

License:EUPL

private void checkHubsAndFacilities_operatorsAre(Workbook workbook, Operator... operators) {
    final Sheet facilities = workbook.getSheetAt(1);
    final List<String> expectedColumns = newArrayList("Operaattori");
    expectedColumns.addAll(Arrays.stream(operators).map(o -> o.name.fi).collect(toList()));
    assertThat(getDataFromColumn(facilities, 3)).containsExactlyElementsOf(expectedColumns);
}

---

From source file:fi.hsl.parkandride.itest.HubsAndFacilitiesReportITest.java

License:EUPL

private void checkHubsAndFacilities_facilityInfo(Workbook workbook) {
    final Sheet facilities = workbook.getSheetAt(1);
    assertThat(facilities.getPhysicalNumberOfRows()).isEqualTo(2);
    final List<String> facilityInfo = getDataFromRow(facilities, 1);
    assertThat(facilityInfo).containsSequence(facility1.name.fi, String.join(", ", facility1.aliases),
            hub.name.fi, operator1.name.fi, translationService.translate(facility1.status),
            facility1.statusDescription.fi,
            String.format(Locale.ENGLISH, "%.4f", facility1.location.getCentroid().getX()),
            String.format(Locale.ENGLISH, "%.4f", facility1.location.getCentroid().getY()), "", "08:00 - 18:00",
            "08:00 - 18:00", facility1.openingHours.info.fi,
            "" + facility1.builtCapacity.entrySet().stream()
                    .filter(entry -> asList(motorCapacities).contains(entry.getKey()))
                    .mapToInt(entry -> entry.getValue()).sum(),
            "" + facility1.builtCapacity.entrySet().stream()
                    .filter(entry -> asList(bicycleCapacities).contains(entry.getKey()))
                    .mapToInt(entry -> entry.getValue()).sum(),
            "" + facility1.builtCapacity.getOrDefault(CAR, 0),
            "" + facility1.builtCapacity.getOrDefault(DISABLED, 0),
            "" + facility1.builtCapacity.getOrDefault(ELECTRIC_CAR, 0),
            "" + facility1.builtCapacity.getOrDefault(MOTORCYCLE, 0),
            "" + facility1.builtCapacity.getOrDefault(BICYCLE, 0),
            "" + facility1.builtCapacity.getOrDefault(BICYCLE_SECURE_SPACE, 0));
}

---

From source file:fi.hsl.parkandride.itest.HubsAndFacilitiesReportITest.java

License:EUPL

private void checkHubsAndFacilities_hubInfo(Workbook workbook) {
    final Sheet hubs = workbook.getSheetAt(0);
    assertThat(hubs.getPhysicalNumberOfRows()).isEqualTo(2);

    final List<String> hubInfo = getDataFromRow(hubs, 1);
    assertThat(hubInfo).containsExactly(hub.name.fi,
            String.join(", ",
                    toArray(hub.address.streetAddress.fi, hub.address.postalCode, hub.address.city.fi)),
            String.format(Locale.ENGLISH, "%.4f", hub.location.getX()),
            String.format(Locale.ENGLISH, "%.4f", hub.location.getY()),
            "" + facility1.builtCapacity.entrySet().stream()
                    .filter(entry -> asList(motorCapacities).contains(entry.getKey()))
                    .mapToInt(entry -> entry.getValue()).sum(),
            "" + facility1.builtCapacity.entrySet().stream()
                    .filter(entry -> asList(bicycleCapacities).contains(entry.getKey()))
                    .mapToInt(entry -> entry.getValue()).sum(),
            "" + facility1.builtCapacity.getOrDefault(CAR, 0),
            "" + facility1.builtCapacity.getOrDefault(DISABLED, 0),
            "" + facility1.builtCapacity.getOrDefault(ELECTRIC_CAR, 0),
            "" + facility1.builtCapacity.getOrDefault(MOTORCYCLE, 0),
            "" + facility1.builtCapacity.getOrDefault(BICYCLE, 0),
            "" + facility1.builtCapacity.getOrDefault(BICYCLE_SECURE_SPACE, 0), facility1.name.fi);
}

---

From source file:fi.semantum.strategia.widget.Database.java

License:Open Source License

public static Database load(Main main, String databaseId) {

    Database result = null;//  w ww .  ja va  2 s . co  m

    synchronized (Database.class) {

        try {

            Map<String, EnumerationDatatype> enumerations = new HashMap<String, EnumerationDatatype>();

            try {
                File file = new File(Main.baseDirectory(), "database.xlsx");
                FileInputStream fis = new FileInputStream(file);
                Workbook book = WorkbookFactory.create(fis);
                fis.close();
                Sheet sheet = book.getSheetAt(0);
                for (int rowN = sheet.getFirstRowNum(); rowN <= sheet.getLastRowNum(); rowN++) {
                    Row row = sheet.getRow(rowN);
                    Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
                    if (cell != null) {
                        if ("Monivalinta".equals(cell.toString())) {
                            Cell id = row.getCell(1, Row.RETURN_BLANK_AS_NULL);
                            if (id == null)
                                continue;
                            Cell traffic = row.getCell(2, Row.RETURN_BLANK_AS_NULL);
                            if (traffic == null)
                                continue;
                            int count = row.getLastCellNum() - 3;
                            if (traffic.toString().length() != count)
                                continue;

                            List<String> values = new ArrayList<String>();
                            for (int i = 0; i < count; i++) {
                                Cell val = row.getCell(3 + i, Row.RETURN_BLANK_AS_NULL);
                                if (val != null)
                                    values.add(val.toString());
                            }
                            enumerations.put(id.toString(),
                                    new EnumerationDatatype(result, id.toString(), values, traffic.toString()));

                        }
                    }
                }

            } catch (Exception e) {
            }

            File f = new File(Main.baseDirectory(), databaseId);
            FileInputStream fileIn = new FileInputStream(f);
            ObjectInputStream in = new ObjectInputStream(fileIn);
            result = (Database) in.readObject();
            in.close();
            fileIn.close();

            result.databaseId = databaseId;

            main.setDatabase(result);

            migrate(main, enumerations);
            validate(main);

            result.lastModified = new Date(f.lastModified());

        } catch (IOException i) {

            i.printStackTrace();
            result = create(main, databaseId);

        } catch (ClassNotFoundException c) {

            System.out.println("Database class not found");
            c.printStackTrace();
            result = create(main, databaseId);

        }

        result.touchBackup();

        result.updateTags();

        try {

            if (!Lucene.indexExists(databaseId)) {

                Lucene.startWrite(databaseId);
                for (Base b : result.enumerate()) {
                    Lucene.set(databaseId, b.uuid, b.searchText(result));
                }
                Lucene.endWrite();

            }

        } catch (Throwable t) {

            t.printStackTrace();

        }

    }

    return result;

}

---

• «
• 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
• »