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:das.pf.io.IOExcel.java

License:Open Source License

private void createHeadersConsolidateFile(Workbook consolidadteWb, Path source) {
    try {/* www .  j a v  a 2 s  .com*/
        Workbook wb = WorkbookFactory.create(source.toFile());
        Sheet sheet = wb.getSheetAt(0);
        Sheet consolidaeSheet = consolidadteWb.getSheetAt(0);

        updateMessages("Creando la cabecera de los datos del archivo consolidado");
        createHeaderData(consolidaeSheet, getCellStyleHeaderData(consolidadteWb));

        // seccion para los values USD
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.MTH), 11, 35, 14);
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.QRT), 35, 49, 38);
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.YTD), 49, 54, 52);
        updateMessages(String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.VALUES, TypeUnits.MAT), 54, 59, 57);

        // seccion para los values units
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.MTH), 59, 83, 63);
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.QRT), 83, 97, 87);
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.YTD), 97, 102, 101);
        updateMessages(String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.UNITS, TypeUnits.MAT), 102, 107, 106);
        //            
        //            // seccion para los values units standars
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.MTH.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.MTH), 107, 131, 112);
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.QRT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.QRT), 131, 145, 136);
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.YTD.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.YTD), 145, 150, 150);
        updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                TypeUnits.MAT.name()));
        createHeaderValues(consolidaeSheet, sheet,
                getCellStyleValues(consolidadteWb, TypeValues.U_E, TypeUnits.MAT), 150, 155, 155);
    } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:DB.TopStockDescriptionList.java

public static void writeToFileExcel(String fileName, List<TopStockDescription> tsdData) {
    int SHORTNAME = 1;
    int LONGNAME = 0;
    int TEXT = 2;

    try {/*from  www . j av a2 s. co m*/
        FileInputStream fileIn = new FileInputStream(fileName);

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

        for (TopStockDescription tsd : tsdData) {
            if (tsd.getContentText().isEmpty())
                continue;
            //?  ? or Create
            int rowInt = 1;
            while (true) {
                Row row = sheet.getRow(rowInt);

                if (row == null) {// THE END OF THE ROW not found
                    //Create
                    row = sheet.createRow(rowInt);
                    Cell cellShortName = row.createCell(SHORTNAME);
                    cellShortName.setCellType(Cell.CELL_TYPE_STRING);
                    cellShortName.setCellValue(tsd.getShortName());

                    String longName = tsd.getShortName();
                    int endIndex = tsd.getContentText().indexOf(tsd.getShortName());
                    if (endIndex > 0 && endIndex < tsd.getContentText().length())
                        longName = tsd.getContentText().substring(0, endIndex - 1).trim();

                    Cell cellLongName = row.createCell(LONGNAME);
                    cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    cellLongName.setCellValue(longName);

                    Cell cellText = row.createCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                Cell cellShortName = row.getCell(SHORTNAME);
                String shortName = cellShortName.getRichStringCellValue().getString();
                if (shortName.equalsIgnoreCase(tsd.getShortName())) {//If Match, Update
                    //Cell cellLongName = row.getCell(LONGNAME);
                    //cellLongName.setCellType(Cell.CELL_TYPE_STRING);
                    //cellLongName.setCellValue(tsd.getLongName());

                    Cell cellText = row.getCell(TEXT);
                    cellText.setCellType(Cell.CELL_TYPE_STRING);
                    cellText.setCellValue(tsd.getContentText());
                    break;
                }
                rowInt++;
            }
        }

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();
        fileIn.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception ex) {
        Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:de.ingrid.iplug.excel.service.SheetsService.java

License:EUPL

/**
 * Create sheets./*from  w  ww.  j  av a  2 s  .  c  om*/
 * 
 * @param inputStream
 * @return Created sheets.
 * @throws IOException
 */
public static Sheets createSheets(final InputStream inputStream) throws IOException {
    // sheets
    final Sheets sheets = new Sheets();
    // create workbook
    final Workbook workbook = new HSSFWorkbook(inputStream);
    final FormulaEvaluator eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
        final org.apache.poi.ss.usermodel.Sheet poiSheet = workbook.getSheetAt(sheetNum);
        // ingrid sheet
        final Sheet sheet = new Sheet();
        sheet.setSheetIndex(sheetNum);
        sheets.addSheet(sheet);
        final Values values = new Values();
        sheet.setValues(values);
        for (final org.apache.poi.ss.usermodel.Row poiRow : poiSheet) {
            boolean hasValues = false;
            final Map<Point, Comparable<? extends Object>> valuesInCell = new HashMap<Point, Comparable<? extends Object>>();
            for (final Cell poiCell : poiRow) {

                Comparable<? extends Object> value = null;
                switch (poiCell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    value = new Boolean(poiCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(poiCell)) {
                        value = getFormattedDateString(poiCell);
                    } else {
                        value = new Double(poiCell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = poiCell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value = calculateFormula(poiCell, eval);
                    break;
                default:
                    value = "";
                    break;
                }
                // trim strings
                if (value instanceof String) {
                    value = ((String) value).trim();
                }
                // only add if at least one value does exist in row
                if (!value.equals("")) {
                    hasValues = true;
                    // ingrid column
                    if (sheet.getColumn(poiCell.getColumnIndex()) == null) {
                        final Column column = new Column(poiCell.getColumnIndex());
                        sheet.addColumn(column);
                    }
                }

                // ingrid point and value
                final Point point = new Point(poiCell.getColumnIndex(), poiCell.getRowIndex());
                valuesInCell.put(point, value);
            }
            // ingrid row
            // ! only add if at least one value does exist
            if (hasValues) {
                final Row row = new Row(poiRow.getRowNum());
                sheet.addRow(row);
                for (final Point point : valuesInCell.keySet()) {
                    //
                    if (sheet.getColumn(point.getX()) != null) {
                        values.addValue(point, valuesInCell.get(point));
                    }
                }
            }
        }
    }

    return sheets;
}

From source file:de.interactive_instruments.ShapeChange.SBVR.SbvrRuleLoader.java

License:Open Source License

/**
 * @param sbvrXls//www  . ja va2 s. c o m
 * @return mapping of schema package name to SBVR rules that apply to
 *         classes in this schema *
 *         <ul>
 *         <li>key: class name</li>
 *         <li>value: mapping of schema package name to SBVR rule info
 *         <ul>
 *         <li>key: schema package name (
 *         {@value #UNSPECIFIED_SCHEMA_PACKAGE_NAME} if no schema package
 *         name has been provided)</li>
 *         <li>value: list of SBVR rules that apply to classes in that
 *         schema (the list is sorted according to lexical order on a) the
 *         class name and b) the rule text)</li>
 *         </ul>
 *         </ul>
 */
private TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> parseSBVRRuleInfos(Workbook sbvrXls) {

    TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> rules = new TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>>();

    if (sbvrXls == null)
        return null;

    Sheet rulesSheet = null;

    for (int i = 0; i < sbvrXls.getNumberOfSheets(); i++) {

        String sheetName = sbvrXls.getSheetName(i);

        if (sheetName.equalsIgnoreCase("Constraints")) {
            rulesSheet = sbvrXls.getSheetAt(i);
            break;
        }
    }

    if (rulesSheet == null) {

        result.addError(this, 3);
        return null;
    }

    // read header row to determine which columns contain relevant
    // information
    Map<String, Integer> fieldIndexes = new HashMap<String, Integer>();

    Row header = rulesSheet.getRow(rulesSheet.getFirstRowNum());

    if (header == null) {
        result.addError(this, 4);
        return null;
    }

    boolean classNameFound = false;
    boolean commentsFound = false;
    boolean ruleNameFound = false;
    boolean ruleTextFound = false;
    boolean schemaPackageFound = false;

    for (short i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {

        Cell c = header.getCell(i, Row.RETURN_BLANK_AS_NULL);

        if (c == null) {
            // this is allowed
        } else {

            String value = c.getStringCellValue();

            if (value.equalsIgnoreCase(SbvrRuleInfo.CLASS_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.CLASS_COLUMN_NAME, (int) i);
                classNameFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.COMMENT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.COMMENT_COLUMN_NAME, (int) i);
                commentsFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME, (int) i);
                schemaPackageFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME, (int) i);
                ruleTextFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_NAME_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_NAME_COLUMN_NAME, (int) i);
                ruleNameFound = true;
            }
        }
    }

    // if (fieldIndexes.size() != 5) {
    if (!ruleNameFound && !ruleTextFound) {
        // log message that required fields were not found
        result.addError(this, 5);
        return null;
    }

    /*
     * Read rule content
     */
    for (int i = rulesSheet.getFirstRowNum() + 1; i <= rulesSheet.getLastRowNum(); i++) {

        Row r = rulesSheet.getRow(i);
        int rowNumber = i + 1;

        if (r == null) {
            // ignore empty rows
            continue;
        }

        SbvrRuleInfo sri = new SbvrRuleInfo();

        // get rule name (required)
        Cell c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_NAME_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 6, "" + rowNumber);
            continue;
        } else {
            String cellValue = c.getStringCellValue();
            if (cellValue != null) {
                if (cellValue.contains(":")) {
                    sri.setName(cellValue.substring(cellValue.lastIndexOf(":") + 1));
                } else {
                    sri.setName(cellValue);
                }
            }
        }

        // get rule text (required)
        c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 7, "" + rowNumber);
            continue;
        } else {
            sri.setText(c.getStringCellValue());
        }

        // get comment (optional)
        if (commentsFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.COMMENT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c != null) {
                sri.setComment(c.getStringCellValue());
            }
        }

        // get schema package (optional)
        if (schemaPackageFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                sri.setSchemaPackageName(UNSPECIFIED_SCHEMA_PACKAGE_NAME);
            } else {
                sri.setSchemaPackageName(c.getStringCellValue());
            }
        }

        /*
         * get class name (optional when loading from excel because later we
         * can still try parsing it from the rule text)
         */
        if (classNameFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.CLASS_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                /*
                 * then after this we'll try to parse the class name from
                 * the rule text
                 */
            } else {
                sri.setClassName(c.getStringCellValue());
            }
        }

        if (sri.getClassName() == null) {

            /*
             * try parsing the main class name from the rule text
             */
            result.addInfo(this, 10, sri.getName());

            String mainClassName = parseClassNameFromRuleText(sri.getText());

            if (mainClassName == null) {
                result.addWarning(this, 8, sri.getName());
                continue;
            } else {
                sri.setClassName(mainClassName);
            }
        }

        List<SbvrRuleInfo> rulesList;
        TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName;

        if (rules.containsKey(sri.getClassName())) {

            rulesBySchemaPackageName = rules.get(sri.getClassName());

            if (rulesBySchemaPackageName.containsKey(sri.getSchemaPackageName())) {
                rulesList = rulesBySchemaPackageName.get(sri.getSchemaPackageName());
            } else {
                rulesList = new ArrayList<SbvrRuleInfo>();
                rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
            }

        } else {

            rulesBySchemaPackageName = new TreeMap<String, List<SbvrRuleInfo>>();
            rules.put(sri.getClassName(), rulesBySchemaPackageName);

            rulesList = new ArrayList<SbvrRuleInfo>();
            rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
        }

        rulesList.add(sri);
    }

    // now sort all lists contained in the map
    for (TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName : rules.values()) {
        for (List<SbvrRuleInfo> rulesList : rulesBySchemaPackageName.values()) {

            Collections.sort(rulesList, new Comparator<SbvrRuleInfo>() {

                @Override
                public int compare(SbvrRuleInfo o1, SbvrRuleInfo o2) {

                    int classNameComparison = o1.getClassName().compareTo(o2.getClassName());

                    if (classNameComparison != 0) {
                        return classNameComparison;
                    } else {
                        return o1.getText().compareTo(o2.getText());
                    }
                }
            });
        }
    }

    return rules;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformerTest.java

License:Open Source License

/**
 * Test method for {@link de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformer#transform(java.util.List, java.io.OutputStream, de.iteratec.iteraplan.model.TypeOfBuildingBlock)}.
 *///  ww w . j  ava2s  .  co m
@Test
public void testTransform() {
    NettoTransformer inst2007op = NettoExcelTransformer.newInstance(createSimpleOverviewPageTableStructure(),
            NettoExcelTransformer.ExcelVersion.EXCEL_VERSION_2007);
    NettoTransformer inst2003sr = NettoExcelTransformer.newInstance(
            createSimpleSpreadsheetReportTableStructure(),
            NettoExcelTransformer.ExcelVersion.EXCEL_VERSION_2003);

    assertNotNull("Can't create netto transformer for overview page table structure with excel 2007",
            inst2007op);
    assertNotNull("Can't create netto transformer for spreadsheet report table structure with excel 2003",
            inst2003sr);

    List<BuildingBlock> sourceList = new ArrayList<BuildingBlock>();
    String firstInfstrElemName = "Infrastructure Element for UnitTest";
    String lastInfstrElemDesc = "Last comment";
    sourceList.add(testDataHelper.createInfrastructureElement(firstInfstrElemName, "Some comment"));
    sourceList.add(
            testDataHelper.createInfrastructureElement("Another Infrastructure Element", "Some more comment"));
    sourceList.add(testDataHelper.createInfrastructureElement("Yet another Infrastructure Element",
            "Even more comment"));
    sourceList
            .add(testDataHelper.createInfrastructureElement("Last Infrastructure Element", lastInfstrElemDesc));

    ByteArrayOutputStream bufferA = new ByteArrayOutputStream();
    ByteArrayOutputStream bufferB = new ByteArrayOutputStream();
    inst2007op.transform(sourceList, bufferA, TypeOfBuildingBlock.INFRASTRUCTUREELEMENT);
    inst2003sr.transform(sourceList, bufferB, TypeOfBuildingBlock.INFRASTRUCTUREELEMENT);

    InputStream in2007 = new ByteArrayInputStream(bufferA.toByteArray());
    InputStream in2003 = new ByteArrayInputStream(bufferB.toByteArray());

    // Excel version
    try {
        assertTrue("Generated excel file is not version 2007.", POIXMLDocument.hasOOXMLHeader(in2007));
        assertTrue("Generated excel file is not version 2003.", POIFSFileSystem.hasPOIFSHeader(in2003));
    } catch (IOException e1) {
        fail("Can't read excel header from buffers.");
    }

    Workbook workbook2007 = null;
    Workbook workbook2003 = null;
    try {
        workbook2007 = WorkbookFactory.create(in2007);
        workbook2003 = WorkbookFactory.create(in2003);
    } catch (Exception e) {
        fail("Can't open generated excel workbook.");
    }

    assertNotNull("Could not create excel workbook instance from generated output (excel 2007).", workbook2007);
    assertNotNull("Could not create excel workbook instance from generated output (excel 2003).", workbook2003);

    assertSame("Number of sheets is not equal 1  (excel 2007).",
            Integer.valueOf(workbook2007.getNumberOfSheets()), Integer.valueOf(1));
    assertSame("Number of sheets is not equal 1  (excel 2003).",
            Integer.valueOf(workbook2003.getNumberOfSheets()), Integer.valueOf(1));

    String stringCellValue2007 = null;
    String stringCellValue2003 = null;
    String stringCellValueDesc2007 = null;
    String stringCellValueDesc2003 = null;
    Sheet sheet2007;
    Sheet sheet2003;
    Row firstDataRow2007;
    Row firstDataRow2003;
    Row lastDataRow2007;
    Row lastDataRow2003;
    Cell nameCell2007;
    Cell nameCell2003;
    Cell descCell2007;
    Cell descCell2003;

    try {
        sheet2007 = workbook2007.getSheetAt(0);
        sheet2003 = workbook2003.getSheetAt(0);

        firstDataRow2007 = sheet2007.getRow(1);
        firstDataRow2003 = sheet2003.getRow(1);

        lastDataRow2007 = sheet2007.getRow(4);
        lastDataRow2003 = sheet2003.getRow(4);

        nameCell2007 = firstDataRow2007.getCell(0);
        nameCell2003 = firstDataRow2003.getCell(0);

        descCell2007 = lastDataRow2007.getCell(1);
        descCell2003 = lastDataRow2003.getCell(1);

        stringCellValue2007 = nameCell2007.getStringCellValue();
        stringCellValue2003 = nameCell2003.getStringCellValue();

        stringCellValueDesc2007 = descCell2007.getStringCellValue();
        stringCellValueDesc2003 = descCell2003.getStringCellValue();
    } catch (Exception e) {
        fail("Wrong structure inside workbook/sheet/row.");
    }

    assertEquals(
            "String in generated excel 2007 workbook does not match the string from the first element in the List of BuildingBlocks.",
            stringCellValue2007, firstInfstrElemName);
    assertEquals(
            "String in generated excel 2003 workbook does not match the string from the first element in the List of BuildingBlocks.",
            stringCellValue2003, firstInfstrElemName);

    assertEquals(
            "String in generated excel 2007 workbook does not match the string from the last element in the List of BuildingBlocks.",
            stringCellValueDesc2007, lastInfstrElemDesc);
    assertEquals(
            "String in generated excel 2003 workbook does not match the string from the last element in the List of BuildingBlocks.",
            stringCellValueDesc2003, lastInfstrElemDesc);
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesExcelExportServiceImpl.java

License:Open Source License

private void adjustColumnWidths(Workbook workbook) {
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        if (!AbstractIntroSheetGenerator.INTRO_SHEET_NAME.equals(sheet.getSheetName())) {
            sheet.autoSizeColumn(TimeseriesExcelImporter.BB_COL_NO, false);
            sheet.setColumnWidth(TimeseriesExcelImporter.DATE_COL_NO, DATE_COL_WIDTH);
            sheet.autoSizeColumn(TimeseriesExcelImporter.VALUE_COL_NO, false);
        }//from   w w  w .j  a  v  a 2  s  . co m
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.exporter.TimeseriesExcelExportServiceImplTest.java

License:Open Source License

private void assertTemplateSheets(Workbook workbook) {
    int actualNumberOfSheets = workbook.getNumberOfSheets();
    assertEquals(4, actualNumberOfSheets);
    for (int i = 0; i < actualNumberOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        assertEquals(EXPECTED_SHEET_NAMES[i], sheet.getSheetName());
    }/*from  w  w w .ja v  a  2s. com*/
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.timeseriesExcel.importer.TimeseriesExcelImporter.java

License:Open Source License

/**
 * Imports data from the workbook into the database.
 * @return True if the import was successful, false otherwise.
 *///from  w w  w  . j av  a  2s .  c  o  m
public boolean importExcel(Workbook workbook) {
    assert (workbook != null);
    for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (!AbstractIntroSheetGenerator.INTRO_SHEET_NAME.equals(sheet.getSheetName())) {
            importTimeseriesSheet(sheet);
        }
    }
    return errorMessages.isEmpty();
}

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportThoughts() throws Exception {
    File tempFile = File.createTempFile("thoughtExport", ".xlsx");
    EntityExportSource<Thought> source = new EntityExportSource<>(getAllIds(), Thought.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, source);//from  w w  w.ja  va 2  s.c  o m

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet sheet = wb.getSheetAt(0);
    assertEquals(Thought.class.getName(), sheet.getSheetName());
    int lastRowNum = sheet.getLastRowNum();
    assertEquals(COUNT, lastRowNum);
    Row firstRow = sheet.getRow(0);

    ArrayList<String> titles = new ArrayList<>();
    firstRow.cellIterator().forEachRemaining(col -> titles.add(col.getStringCellValue()));
    assertThat(titles.size(), greaterThanOrEqualTo(3));
    log.info("Found titles {}", titles);

    String creationTime = PropertyPath.property(Thought.class, t -> t.getCreationTime());
    String name = PropertyPath.property(Thought.class, t -> t.getName());
    String description = PropertyPath.property(Thought.class, t -> t.getDescription());

    assertTrue(titles.contains(creationTime));
    assertTrue(titles.contains(name));
    assertTrue(titles.contains(description));

    int nameColumn = titles.indexOf(name);
    ArrayList<String> names = new ArrayList<String>(COUNT);
    for (int i = 1; i <= COUNT; i++) {
        Row row = sheet.getRow(i);
        names.add(row.getCell(nameColumn).getStringCellValue());
    }
    Collections.sort(names);
    assertEquals("Thought000", names.get(0));
    assertEquals("Thought141", names.get(COUNT - 1));

    Date excelDate = sheet.getRow(1).getCell(titles.indexOf(creationTime)).getDateCellValue();

    Thought thought = PersistentWork.forName(Thought.class, "Thought000");

    Timestamp timestamp = java.sql.Timestamp.valueOf(thought.getCreationTime());
    Date creationDate = new Date(timestamp.getTime());
    assertEquals(creationDate, excelDate);
}

From source file:de.teststory.jspwiki.worksheetplugin.WorksheetPlugin.java

License:Apache License

/**
 * Try to find sheet by id, then by name. Default is first sheet.
 * // www .j  ava  2s.c o  m
 * @param wb
 * @param sheetId
 * @param sheetName
 * @return
 */
protected Sheet findSheet(Workbook wb, int sheetId, String sheetName) {
    Sheet ret = null;
    // sheet ID
    if (sheetId >= 0 && sheetId < wb.getNumberOfSheets()) {
        ret = wb.getSheetAt(sheetId);
    }
    // sheet name
    if (ret == null && sheetName != null) {
        ret = wb.getSheet(sheetName);
    }
    if (ret == null && wb.getNumberOfSheets() > 0) {
        ret = wb.getSheetAt(0);
    }
    return ret;
}