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:com.gsecs.GSECSFrame.java

private void importSemesterMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_importSemesterMenuItemActionPerformed
    // TODO add your handling code here:
    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {/*from www  . ja v  a 2s .  com*/
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<SemesterBean> semesterBeansList = new ArrayList<SemesterBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    SemesterBean semesterBean = new SemesterBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            semesterBean.setSemester((String) getCellValue(nextCell));
                            break;
                        case 1:
                            String[] stringDate = getCellValue(nextCell).toString().split("/");
                            semesterBean
                                    .setStartDate(stringDate[0] + "/" + stringDate[1] + "/20" + stringDate[2]);
                            break;
                        case 2:
                            String[] stringDate2 = getCellValue(nextCell).toString().split("/");
                            semesterBean
                                    .setEndDate(stringDate2[0] + "/" + stringDate2[1] + "/20" + stringDate2[2]);
                            break;

                        }

                    }
                    semesterBeansList.add(semesterBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            SemesterModel model = new SemesterModel();
            SemesterJInternalFrame semesterFrame = new SemesterJInternalFrame();
            semesterFrame.getSemesterMsgLabel().setText("");
            semesterFrame.getAddSemesterButton().setVisible(true);
            semesterFrame.getUpdateSemesterButton().setVisible(false);
            semesterFrame.getDeleteSemesterButton().setVisible(false);
            //schoolFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (SemesterBean semesterBean : semesterBeansList) {
                try {
                    model.addSemester(semesterBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            semesterFrame.loadDataIntoJTable();
            mainPanel.add(semesterFrame);
            semesterFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void ImportUsersMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_ImportUsersMenuItemActionPerformed
    // TODO add your handling code here:

    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {// ww w.  j  a  va2  s.co m
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<UserBean> userBeansList = new ArrayList<UserBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    UserBean userBean = new UserBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            userBean.setLoginId(getCellValue(nextCell).toString());
                            break;
                        case 1:
                            userBean.setPassword((String) getCellValue(nextCell));
                            break;
                        case 2:
                            userBean.setRole((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    userBeansList.add(userBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            UserModel model = new UserModel();
            UserJInternalFrame userFrame = new UserJInternalFrame();
            userFrame.getUsersMsgLabel().setText("");
            userFrame.getUserRegister().setVisible(true);
            userFrame.getUserUpdateButton().setVisible(false);
            userFrame.getUserDeleteButton().setVisible(false);
            //userFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (UserBean userBean : userBeansList) {
                try {
                    model.registerUser(userBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            userFrame.loadDataIntoJTable();
            mainPanel.add(userFrame);
            userFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.gsecs.GSECSFrame.java

private void ImportRoomMenuItemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_ImportRoomMenuItemActionPerformed
    // TODO add your handling code here:
    JFileChooser jf = new JFileChooser();
    jf.setDialogTitle("Please select a excel File to import");
    int result = jf.showOpenDialog(null);
    if (result == JFileChooser.APPROVE_OPTION) {
        String filePath = jf.getSelectedFile().getAbsolutePath();

        try {//from w w  w  .ja v a 2s  . c  o  m
            FileInputStream fis = new FileInputStream(filePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            List<RoomBean> roomBeansList = new ArrayList<RoomBean>();

            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                if (nextRow.getRowNum() != 0) {
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    RoomBean roomBean = new RoomBean();

                    while (cellIterator.hasNext()) {
                        Cell nextCell = cellIterator.next();
                        int columnIndex = nextCell.getColumnIndex();

                        switch (columnIndex) {
                        case 0:
                            roomBean.setRoomNumber(getCellValue(nextCell).toString());
                            break;
                        case 1:
                            roomBean.setCourseCode((String) getCellValue(nextCell));
                            break;
                        case 2:
                            roomBean.setCapacity((String) getCellValue(nextCell));
                            break;

                        }

                    }
                    roomBeansList.add(roomBean);
                }

            }
            workbook.close();
            fis.close();

            mainPanel.removeAll();
            mainPanel.repaint();
            RoomModel model = new RoomModel();
            RoomJInternalFrame roomFrame = new RoomJInternalFrame();
            roomFrame.getRoomMsgLabel().setText("");
            roomFrame.getAddRoomButton().setVisible(true);
            roomFrame.getUpdateRoomButton().setVisible(false);
            roomFrame.getDeleteRoomButton().setVisible(false);
            //userFrame.getSchoolDataTable().setModel(DbUtils.resultSetToTableModel( new GradSchoolModel().getAllSchools()));
            for (RoomBean roomBean : roomBeansList) {
                try {
                    model.addRoom(roomBean);
                } catch (Exception exp) {
                    System.out.println("Exception Raised....." + exp.getMessage());
                }
            }
            roomFrame.loadDataIntoJTable();
            mainPanel.add(roomFrame);
            roomFrame.setVisible(true);
        } catch (Exception exp) {
            JOptionPane.showMessageDialog(null, exp.getMessage());
        }

    }
}

From source file:com.helger.genericode.excel.ExcelSheetToCodeList04Test.java

License:Apache License

@Test
public void testReadExcel() throws URISyntaxException {
    // Where is the Excel?
    final IReadableResource aXls = new ClassPathResource("excel/Simple1.xls");
    assertTrue(aXls.exists());/* ww  w .  ja  v  a2s. c o  m*/

    // Interpret as Excel
    final Workbook aWB = EExcelVersion.XLS.readWorkbook(aXls.getInputStream());
    assertNotNull(aWB);

    // Check whether all required sheets are present
    final Sheet aSheet = aWB.getSheetAt(0);
    assertNotNull(aSheet);

    final ExcelReadOptions<UseType> aReadOptions = new ExcelReadOptions<UseType>().setLinesToSkip(1)
            .setLineIndexShortName(0);
    aReadOptions.addColumn(0, "id", UseType.REQUIRED, "string", true);
    aReadOptions.addColumn(1, "name", UseType.REQUIRED, "string", false);
    final CodeListDocument aCodeList = ExcelSheetToCodeList04.convertToSimpleCodeList(aSheet, aReadOptions,
            "ExampleList", "1.0", new URI("urn:www.helger.com:names:example"),
            new URI("urn:www.helger.com:names:example-1.0"), null);
    final Document aDoc = new Genericode04CodeListMarshaller().write(aCodeList);
    assertNotNull(aDoc);
    final CodeListDocument aCLDoc = new Genericode04CodeListMarshaller().read(aDoc);
    assertNotNull(aCLDoc);
}

From source file:com.helger.genericode.excel.ExcelSheetToCodeList10Test.java

License:Apache License

@Test
public void testReadExcel() throws URISyntaxException {
    // Where is the Excel?
    final IReadableResource aXls = new ClassPathResource("excel/Simple1.xls");
    assertTrue(aXls.exists());/* ww w . j av a2 s .  co  m*/

    // Interpret as Excel
    final Workbook aWB = EExcelVersion.XLS.readWorkbook(aXls.getInputStream());
    assertNotNull(aWB);

    // Check whether all required sheets are present
    final Sheet aSheet = aWB.getSheetAt(0);
    assertNotNull(aSheet);

    final ExcelReadOptions<UseType> aReadOptions = new ExcelReadOptions<UseType>().setLinesToSkip(1)
            .setLineIndexShortName(0);
    aReadOptions.addColumn(0, "id", UseType.REQUIRED, "string", true);
    aReadOptions.addColumn(1, "name", UseType.REQUIRED, "string", false);
    final CodeListDocument aCodeList = ExcelSheetToCodeList10.convertToSimpleCodeList(aSheet, aReadOptions,
            "ExampleList", "1.0", new URI("urn:www.helger.com:names:example"),
            new URI("urn:www.helger.com:names:example-1.0"), null);
    final Document aDoc = new Genericode10CodeListMarshaller().write(aCodeList);
    assertNotNull(aDoc);
    final CodeListDocument aCLDoc = new Genericode10CodeListMarshaller().read(aDoc);
    assertNotNull(aCLDoc);
}

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

License:Apache License

public static void main(final String[] args) {
    final IReadableResource aRes = new ClassPathResource("country2continent.xlsx");
    final Workbook aWB = ExcelReadUtils.readWorkbookFromInputStream(aRes);
    final Sheet aSheet = aWB.getSheetAt(0);
    // Skip one row
    int nRow = 1;
    int nNotFound = 0;
    final Map<Locale, EContinent> aMap = new TreeMap<Locale, EContinent>(new ComparatorLocaleCountry(LOC));
    while (true) {
        final Row aRow = aSheet.getRow(nRow);
        if (aRow == null)
            break;
        final String sContinent = ExcelReadUtils.getCellValueString(aRow.getCell(0));
        if (StringHelper.hasNoText(sContinent))
            break;
        final EContinent eContinent = _findContinent(sContinent);
        final String sCountryName = ExcelReadUtils.getCellValueString(aRow.getCell(1));
        final Locale aCountry = _findCountryComplex(sCountryName);
        if (aCountry == null) {
            System.out.println("No such country: '" + sCountryName + "'");
            ++nNotFound;/*  w  w w. j  a  v  a  2 s  .  c  o m*/
        } else {
            final EContinent eOld = aMap.put(aCountry, eContinent);
            if (eOld != null)
                System.out.println("Country " + aCountry.getDisplayCountry() + " is assigned to "
                        + eContinent.getDisplayText(LOC) + " and " + eOld.getDisplayText(LOC));
        }

        ++nRow;
    }
    System.out.println("Countries not found: " + nNotFound);

    for (final Map.Entry<Locale, EContinent> e : aMap.entrySet()) {
        System.out.println("s_aMap.put (CountryCache.getCountry (\"" + e.getKey().getCountry()
                + "\"), EContinent." + e.getValue().name() + "),");
    }
}

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  ava2  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");
    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();/*from ww  w. j ava 2  s  .  co m*/

    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  ww w  . j  av a  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");
    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.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * ,//from  w ww  .  j a va 2  s  .  c  om
 * 
 * @param fileName
 *            excel, getModelExcel
 * @return 
 * @throws FileNotFoundException 
 */
public Map<String, String> readModelExcel(File file, String modelId) {
    if (modelId == null)
        return null;
    Map<String, String> m = new HashMap<String, String>();
    try {
        InputStream in = new FileInputStream(file);
        Workbook wb;
        try {
            wb = new HSSFWorkbook(in);
        } catch (IllegalArgumentException e) {
            wb = new XSSFWorkbook(in);
        }
        Sheet sheet = wb.getSheetAt(0);
        int total = sheet.getLastRowNum();
        Row row0 = sheet.getRow(0);
        String[] head = new String[row0.getLastCellNum()];
        for (int j = 0; j < row0.getLastCellNum(); j++) {
            String[] str = row0.getCell(j).getStringCellValue().split("/");
            if (str.length == 2) {
                head[j] = str[1];
            } else {
                head[j] = "";
            }
            System.out.println(head[j]);
        }
        Row row = null;
        Cell cell = null;
        for (int i = 1; i < total; i++) {
            m.clear();
            row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                m.put(head[j], cell.getStringCellValue());
                System.out.println(head[j] + "--" + cell.getStringCellValue());
            }
            // ServiceManager.getResourceUpdateService().addResource(modelId,
            // m, 1);
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        file.delete();
    }
    return m;
}