Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook.

Prototype

public HSSFWorkbook(InputStream s) throws IOException 

Source Link

Document

Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your InputStream , including all nodes.

Usage

From source file:com.googlecode.bdoc.testsupport.excel.TestExcelExampleTable.java

License:Open Source License

public TestExcelExampleTable() throws FileNotFoundException, IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(DATATYPE_EXAMPLES));
    excelExampleTable = new ExcelExampleTable(workbook.getSheetAt(0), 0, 0);
}

---

From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

@Override
public Map<Integer, List<GovernmentBodyAnnualSummary>> getDataPerMinistry(final String name) {
    final Map<Integer, List<GovernmentBodyAnnualSummary>> map = new TreeMap<>();
    try {//from  w w w.  jav a2 s  .  c o  m
        final HSSFWorkbook myWorkBook = new HSSFWorkbook(
                EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

        for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
            final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

            addMinistryPerYearToMap(name, map, mySheet);
        }

        myWorkBook.close();
    } catch (

    final IOException e) {
        LOGGER.warn("Problem loading", e);
    }

    return map;
}

---

From source file:com.hack23.cia.service.external.esv.impl.EsvApiImpl.java

License:Apache License

@Override
public Map<Integer, GovernmentBodyAnnualSummary> getDataPerGovernmentBody(String name) {
    final Map<Integer, GovernmentBodyAnnualSummary> map = new TreeMap<>();
    try {//from  w w  w. j  a v a  2 s .c o m
        final HSSFWorkbook myWorkBook = new HSSFWorkbook(
                EsvApiImpl.class.getResourceAsStream("/Myndighetsinformation.xls"));

        for (int sheetNr = 0; sheetNr < myWorkBook.getNumberOfSheets(); sheetNr++) {
            final HSSFSheet mySheet = myWorkBook.getSheetAt(sheetNr);

            addDataForYearToMap(name, map, mySheet);
        }
        myWorkBook.close();
    } catch (

    final IOException e) {
        LOGGER.warn("Problem loading", e);
    }

    return map;
}

---

From source file:com.haulmont.mp2xls.object.LocalizationsBatch.java

License:Apache License

protected void readLocalizationFile(File source, String lcFilePath) throws IOException {
    FileInputStream fis = new FileInputStream(source);
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet sheet = wb.getSheetAt(0);//from   w  ww . j  a  va2  s .c  o  m

    if (lcFilePath == null)
        projectDirectory = (String) XlsHelper.getCellValue(sheet.getRow(0).getCell(1));
    else
        projectDirectory = lcFilePath;

    int firstDataRowIndex = 6;
    int headingRow = 5;

    for (int currentRowIndex = firstDataRowIndex; !XlsHelper
            .eof(sheet.getRow(currentRowIndex)); currentRowIndex++) {
        HSSFRow row = sheet.getRow(currentRowIndex);

        String path = (String) XlsHelper.getCellValue(row.getCell(0));
        String param = (String) XlsHelper.getCellValue(row.getCell(1));

        Set<MessagesLocalization> localizations;
        if (messagesLocalizations.keySet().contains(path))
            localizations = messagesLocalizations.get(path);
        else {
            localizations = new HashSet<>();
            messagesLocalizations.put(path, localizations);
        }

        for (int col = 2; XlsHelper.getCellValue(sheet.getRow(headingRow).getCell(col)) != null; col++) {
            String localeId = (String) XlsHelper.getCellValue(sheet.getRow(headingRow).getCell(col));
            Object val = XlsHelper.getCellValue(row.getCell(col));
            String value = val != null ? val.toString() : null;
            localeId = localeId.equals("default") ? null : localeId;
            String relPath = path + (localeId == null ? "" : "_" + localeId) + ".properties";

            MessagesLocalization local = null;
            for (MessagesLocalization l : localizations) {
                if (l.getPath().equals(relPath)) {
                    local = l;
                    break;
                }
            }

            if (local == null) {
                local = new MessagesLocalization(localeId, relPath);
                localizationIds.add(localeId);
                localizations.add(local);
            }

            local.getMessages().put(param, value);
        }
    }
}

---

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

protected void initWorkbook() {
    try {/*from   w w w . ja  va  2  s.c o  m*/
        templateWorkbook = new HSSFWorkbook(reportTemplate.getDocumentContent());
        resultWorkbook = new HSSFWorkbook(reportTemplate.getDocumentContent());
    } catch (IOException e) {
        throw wrapWithReportingException(
                "An error occurred while parsing xls template " + reportTemplate.getDocumentName(), e);
    }

    for (int sheetNumber = 0; sheetNumber < templateWorkbook.getNumberOfSheets(); sheetNumber++) {
        HSSFSheet templateSheet = templateWorkbook.getSheetAt(sheetNumber);
        HSSFSheet resultSheet = resultWorkbook.getSheetAt(sheetNumber);
        templateToResultSheetsMapping.put(templateSheet, resultSheet);

        initMergeRegions(templateSheet);
        copyCharts(resultSheet);
        removeMergedRegions(resultSheet);
        cleanupCells(resultSheet);
    }

    copyPicturesToResultWorkbook();

    initNamedStyleCache();
}

---

From source file:com.helger.masterdata.tools.MainReadPackageTypeCodeListExcel.java

License:Apache License

public static void main(final String[] args) throws Exception {
    final String sBaseName = "rec21_Rev9e_2012";
    final String sSource = "http://www.unece.org/cefact/recommendations/rec21/" + sBaseName + ".xls";
    final String sRevision = "9";

    // Ideally don't change anything from here on
    final File f = new File("src/test/resources/" + sBaseName + ".xls");
    final Workbook aWB = new HSSFWorkbook(FileUtils.getInputStream(f));
    final Sheet aSheet = aWB.getSheetAt(0);
    final Iterator<Row> it = aSheet.rowIterator();

    // Skip 3 rows
    for (int i = 0; i < 3; ++i)
        it.next();//from  w  w  w.j  a va  2 s. co  m

    final IMicroDocument aDoc = new MicroDocument();
    final IMicroElement eRoot = aDoc.appendElement("root");
    final IMicroElement eHeader = eRoot.appendElement("header");
    eHeader.appendElement("source").appendText(sSource);
    eHeader.appendElement("revision").appendText(sRevision);

    final IMicroElement eBody = eRoot.appendElement("body");
    while (it.hasNext()) {
        final Row aRow = it.next();
        final String sStatus = ExcelReadUtils.getCellValueString(aRow.getCell(0));
        final EUNCodelistStatus[] aStatus = EUNCodelistStatus.getFromTextOrUnchanged(sStatus);
        final String sCode = ExcelReadUtils.getCellValueString(aRow.getCell(1));
        final String sName = ExcelReadUtils.getCellValueString(aRow.getCell(2));
        final String sDescription = ExcelReadUtils.getCellValueString(aRow.getCell(3));
        final String sNumericCode = _convertNumericCodes(ExcelReadUtils.getCellValueString(aRow.getCell(4)));

        // Avoid reading empty lines
        if (StringHelper.hasText(sCode)) {
            final IMicroElement eItem = eBody.appendElement("item");
            eItem.setAttribute("status", EUNCodelistStatus.getAsString(aStatus));
            eItem.setAttribute("code", sCode);
            eItem.appendElement("name").appendElement("text").setAttribute("locale", "en").appendText(sName);
            if (StringHelper.hasText(sDescription))
                eItem.appendElement("description").appendElement("text").setAttribute("locale", "en")
                        .appendText(sDescription);
            eItem.setAttribute("numericcodes", sNumericCode);
        }
    }

    MicroWriter.writeToStream(aDoc,
            FileUtils.getOutputStream("src/main/resources/codelists/" + sBaseName + ".xml"));
    s_aLogger.info("Done");
}

---

From source file:com.helger.masterdata.tools.MainReadPostalCodeListExcel.java

License:Apache License

public static void main(final String[] args) throws Exception {
    final String sSource = "http://en.wikipedia.org/wiki/List_of_postal_codes";
    final String sRevision = "20130209";

    final File f = new File("src/test/resources/" + sRevision + "PostalCodes.xls");
    final Workbook aWB = new HSSFWorkbook(FileUtils.getInputStream(f));
    final Sheet aSheet = aWB.getSheetAt(0);
    final Iterator<Row> it = aSheet.rowIterator();

    // Skip 1 row
    it.next();//  w  w  w.j a v  a2s.c om

    final IMicroDocument aDoc = new MicroDocument();
    final IMicroElement eRoot = aDoc.appendElement(PostalCodeListReader.ELEMENT_ROOT);
    final IMicroElement eHeader = eRoot.appendElement(PostalCodeListReader.ELEMENT_HEADER);
    eHeader.appendElement(PostalCodeListReader.ELEMENT_SOURCE).appendText(sSource);
    eHeader.appendElement(PostalCodeListReader.ELEMENT_REVISION).appendText(sRevision);

    final IMicroElement eBody = eRoot.appendElement(PostalCodeListReader.ELEMENT_BODY);
    final List<Item> aItems = new ArrayList<Item>();
    int nRow = 0;
    while (it.hasNext()) {
        final Row aRow = it.next();
        ++nRow;
        final String sCountry = ExcelReadUtils.getCellValueString(aRow.getCell(0));
        if (StringHelper.hasNoText(sCountry)) {
            s_aLogger.warn("Line " + nRow + ": No country name present");
            continue;
        }
        final Cell aDateCell = aRow.getCell(1);
        Date aIntroducedDate = null;
        if (aDateCell != null && aDateCell.getCellType() != Cell.CELL_TYPE_BLANK) {
            final Number aNum = ExcelReadUtils.getCellValueNumber(aDateCell);
            final int nYear = aNum.intValue();
            if (nYear > 1800 && nYear < 3000)
                aIntroducedDate = PDTFactory.createLocalDate(nYear, DateTimeConstants.JANUARY, 1).toDate();
            else
                aIntroducedDate = ExcelReadUtils.getCellValueJavaDate(aDateCell);
        }
        final String sISO = ExcelReadUtils.getCellValueString(aRow.getCell(2));
        if (StringHelper.hasNoText(sISO)) {
            s_aLogger.warn("Line " + nRow + ": No ISO code for " + sCountry);
            continue;
        }
        final String sFormat = ExcelReadUtils.getCellValueString(aRow.getCell(3));
        if (NO_CODES.equals(sFormat) || StringHelper.hasNoText(sFormat))
            continue;
        final List<String> aFormats = StringHelper.getExploded("\n", sFormat);
        final String sNote = ExcelReadUtils.getCellValueString(aRow.getCell(4));
        aItems.add(new Item(sCountry, aIntroducedDate, sISO, aFormats, sNote));
    }

    // Convert to map, where the key is the ISO
    final IMultiMapListBased<String, Item> aMap = new MultiHashMapArrayListBased<String, Item>();
    for (final Item aItem : aItems)
        aMap.putSingle(aItem.getISO(), aItem);

    // Sort all sub-lists by introduction date
    for (final List<Item> aSubList : aMap.values()) {
        ContainerHelper.getSortedInline(aSubList, new ComparatorItemValidFrom());
        for (int i = 1; i < aSubList.size(); ++i) {
            final Item aPrevItem = aSubList.get(i - 1);
            final Item aThisItem = aSubList.get(i);
            if (aThisItem.getValidFrom() != null)
                aPrevItem.setValidTo(aThisItem.getValidFrom().minusDays(1));
        }
    }

    // Print sorted by ISO code
    for (final Map.Entry<String, List<Item>> aEntry : ContainerHelper.getSortedByKey(aMap).entrySet()) {
        IMicroElement eCountry = null;
        for (final Item aItem : aEntry.getValue()) {
            if (eCountry == null) {
                // First item - ISO and name only once
                eCountry = eBody.appendElement(PostalCodeListReader.ELEMENT_COUNTRY);
                eCountry.setAttribute(PostalCodeListReader.ATTR_ISO, aItem.getISO());
                eCountry.setAttribute(PostalCodeListReader.ATTR_NAME, aItem.getCountry());
            }

            final IMicroElement ePostalCodes = eCountry.appendElement(PostalCodeListReader.ELEMENT_POSTALCODES);
            if (aItem.getValidFrom() != null)
                ePostalCodes.setAttribute(PostalCodeListReader.ATTR_VALIDFROM,
                        ISODateTimeFormat.date().print(aItem.getValidFrom()));
            if (aItem.getValidTo() != null)
                ePostalCodes.setAttribute(PostalCodeListReader.ATTR_VALIDTO,
                        ISODateTimeFormat.date().print(aItem.getValidTo()));
            for (final String sSingleFormat : aItem.getFormats())
                if (sSingleFormat.startsWith(PREFIX_ONE_CODE))
                    ePostalCodes.appendElement(PostalCodeListReader.ELEMENT_SPECIFIC)
                            .appendText(sSingleFormat.substring(PREFIX_ONE_CODE.length()));
                else {

                    ePostalCodes.appendElement(PostalCodeListReader.ELEMENT_FORMAT).appendText(sSingleFormat);
                }
            if (StringHelper.hasText(aItem.getNote()))
                ePostalCodes.appendElement(PostalCodeListReader.ELEMENT_NOTE).appendText(aItem.getNote());
        }
    }

    MicroWriter.writeToStream(aDoc,
            FileUtils.getOutputStream("src/main/resources/codelists/postal-codes-" + sRevision + ".xml"));
    s_aLogger.info("Done");
}

---

From source file:com.helger.masterdata.tools.MainReadUnitTypeCodeListExcel.java

License:Apache License

public static void main(final String[] args) throws Exception {
    final String sBaseName = "rec20_Rev8e_2012";
    final String sSource = "http://www.unece.org/cefact/recommendations/rec20/" + sBaseName + ".xls";
    final String sRevision = "8";

    // Ideally don't change anything from here on
    final File f = new File("src/test/resources/" + sBaseName + ".xls");
    final Workbook aWB = new HSSFWorkbook(FileUtils.getInputStream(f));
    final Sheet aSheet = aWB.getSheetAt(1);
    final Iterator<Row> it = aSheet.rowIterator();

    // Skip 1 row
    it.next();/*from www  .  ja v a2 s .c o  m*/

    final IMicroDocument aDoc = new MicroDocument();
    final IMicroElement eRoot = aDoc.appendElement("root");
    final IMicroElement eHeader = eRoot.appendElement("header");
    eHeader.appendElement("source").appendText(sSource);
    eHeader.appendElement("revision").appendText(sRevision);

    final IMicroElement eBody = eRoot.appendElement("body");
    final Map<String, String> aSectors = new HashMap<String, String>();
    final Map<String, Integer> aQuantities = new HashMap<String, Integer>();
    while (it.hasNext()) {
        final Row aRow = it.next();
        final String sGroupNumber = ExcelReadUtils.getCellValueString(aRow.getCell(0));
        final String sSector = ExcelReadUtils.getCellValueString(aRow.getCell(1));
        final String sGroupID = ExcelReadUtils.getCellValueString(aRow.getCell(2));
        final String sQuantity = ExcelReadUtils.getCellValueString(aRow.getCell(3));
        final String sLevel = ExcelReadUtils.getCellValueString(aRow.getCell(4));
        final int nLevel = StringParser.parseInt(sLevel.substring(0, 1), -1);
        final String sLevelSuffix = sLevel.length() != 2 ? null : sLevel.substring(1, 2);
        final String sStatus = ExcelReadUtils.getCellValueString(aRow.getCell(5));
        final EUNCodelistStatus[] aStatus = EUNCodelistStatus.getFromTextOrUnchanged(sStatus);
        final String sCommonCode = ExcelReadUtils.getCellValueString(aRow.getCell(6));
        final String sName = ExcelReadUtils.getCellValueString(aRow.getCell(7));
        final String sConversionFactor = ExcelReadUtils.getCellValueString(aRow.getCell(8));
        final String sSymbol = ExcelReadUtils.getCellValueString(aRow.getCell(9));
        final String sDescription = ExcelReadUtils.getCellValueString(aRow.getCell(10));

        // Avoid reading empty lines
        if (StringHelper.hasText(sCommonCode)) {
            aSectors.put(sGroupNumber, sSector);

            Integer aQuantityID = aQuantities.get(sQuantity);
            if (aQuantityID == null) {
                aQuantityID = Integer.valueOf(aQuantities.size() + 1);
                aQuantities.put(sQuantity, aQuantityID);
            }

            final IMicroElement eItem = eBody.appendElement("item");
            eItem.setAttribute("groupnum", sGroupNumber);
            eItem.setAttribute("groupid", sGroupID);
            eItem.setAttribute("quantityid", aQuantityID.intValue());
            eItem.setAttribute("level", nLevel);
            if (StringHelper.hasText(sLevelSuffix))
                eItem.setAttribute("levelsuffix", sLevelSuffix);
            eItem.setAttribute("status", EUNCodelistStatus.getAsString(aStatus));
            eItem.setAttribute("commoncode", sCommonCode);
            eItem.appendElement("name").appendElement("text").setAttribute("locale", "en").appendText(sName);
            eItem.setAttribute("conversion", sConversionFactor);
            eItem.setAttribute("symbol", sSymbol);
            if (StringHelper.hasText(sDescription))
                eItem.appendElement("description").appendElement("text").setAttribute("locale", "en")
                        .appendText(sDescription);
        }
    }

    // sectors
    final IMicroElement eSectors = eRoot.appendElement("sectors");
    for (final Map.Entry<String, String> aEntry : ContainerHelper.getSortedByKey(aSectors).entrySet()) {
        final IMicroElement eSector = eSectors.appendElement("sector");
        eSector.setAttribute("groupnum", aEntry.getKey());
        eSector.appendElement("name").appendElement("text").setAttribute("locale", "en")
                .appendText(aEntry.getValue());
    }

    // quantities
    final IMicroElement eQuantities = eRoot.appendElement("quantities");
    for (final Map.Entry<String, Integer> aEntry : ContainerHelper.getSortedByValue(aQuantities).entrySet()) {
        final IMicroElement eSector = eQuantities.appendElement("quantity");
        eSector.setAttribute("id", aEntry.getValue().intValue());
        eSector.appendElement("name").appendElement("text").setAttribute("locale", "en")
                .appendText(aEntry.getKey());
    }

    MicroWriter.writeToStream(aDoc,
            FileUtils.getOutputStream("src/main/resources/codelists/" + sBaseName + ".xml"));
    s_aLogger.info("Done");
}

---

From source file:com.helger.peppol.supplementary.tools.MainCreateCodelistsFilesFromExcel.java

License:Mozilla Public License

public static void main(final String[] args) throws IOException, URISyntaxException {
    // Where is the Excel?
    final IReadableResource aXls = new FileSystemResource(EXCEL_FILE);
    if (!aXls.exists())
        throw new IllegalStateException("The Excel file could not be found!");

    // Interprete as Excel
    final Workbook aWB = new HSSFWorkbook(aXls.getInputStream());

    // Check whether all required sheets are present
    final Sheet aParticipantSheet = aWB.getSheet(SHEET_PARTICIPANT);
    if (aParticipantSheet == null)
        throw new IllegalStateException("The " + SHEET_PARTICIPANT + " sheet could not be found!");
    final Sheet aDocumentSheet = aWB.getSheet(SHEET_DOCUMENT);
    if (aDocumentSheet == null)
        throw new IllegalStateException("The " + SHEET_DOCUMENT + " sheet could not be found!");
    final Sheet aProcessSheet = aWB.getSheet(SHEET_PROCESS);
    if (aProcessSheet == null)
        throw new IllegalStateException("The " + SHEET_PROCESS + " sheet could not be found!");

    // Convert participants
    _emitIdentifierIssuingAgency(aParticipantSheet);

    // Convert document identifiers
    _emitDocumentIdentifiers(aDocumentSheet);

    // Convert processes identifiers
    _emitProcessIdentifier(aProcessSheet);

    // Write all Java source files
    final FileCodeWriter aWriter = new FileCodeWriter(new File("src/main/java"), CCharset.CHARSET_UTF_8_OBJ);
    s_aCodeModel.build(aWriter);/*from   w w  w  .jav  a2 s  .c  o  m*/

    s_aLogger.info("Done creating code");
}

---

From source file:com.helger.peppol.supplementary.tools.MainCreatePredefinedEnumsFromExcel.java

License:Mozilla Public License

public static void main(final String[] args) throws IOException, URISyntaxException {
    // Where is the Excel?
    final IReadableResource aXls = new FileSystemResource(EXCEL_FILE);
    if (!aXls.exists())
        throw new IllegalStateException("The Excel file could not be found!");

    // Interprete as Excel
    final Workbook aWB = new HSSFWorkbook(aXls.getInputStream());

    // Check whether all required sheets are present
    final Sheet aParticipantSheet = aWB.getSheet(SHEET_PARTICIPANT);
    if (aParticipantSheet == null)
        throw new IllegalStateException("The " + SHEET_PARTICIPANT + " sheet could not be found!");
    final Sheet aDocumentSheet = aWB.getSheet(SHEET_DOCUMENT);
    if (aDocumentSheet == null)
        throw new IllegalStateException("The " + SHEET_DOCUMENT + " sheet could not be found!");
    final Sheet aProcessSheet = aWB.getSheet(SHEET_PROCESS);
    if (aProcessSheet == null)
        throw new IllegalStateException("The " + SHEET_PROCESS + " sheet could not be found!");

    // Convert participants
    _emitIdentifierIssuingAgency(aParticipantSheet);

    // Convert document identifiers
    _emitDocumentTypeIdentifiers(aDocumentSheet);

    // Convert processes identifiers
    _emitProcessIdentifier(aProcessSheet);

    // Write all Java source files
    final FileCodeWriter aWriter = new FileCodeWriter(new File("src/main/java"), CCharset.CHARSET_UTF_8_OBJ);
    s_aCodeModel.build(aWriter);//from w  w w.  j  a  v  a  2s .  c  o m

    s_aLogger.info("Done creating code");
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• »