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

Introduction

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

Prototype

@Override
public HSSFSheet getSheet(String name) 

Source Link

Document

Get sheet with the given name (case insensitive match)

Usage

From source file:com.syncnapsis.utils.data.UniverseEvolutionExcelParser.java

License:Open Source License

/**
 * Erstellen aller Imperien/*from  www .java2  s. c  o  m*/
 * 
 * @param workbook - das Workbook
 * @param key_sheet_empires - der Key fr das Sheet
 */
public static void parseEmpires(HSSFWorkbook workbook, String key_sheet_empires) {
    HSSFSheet sheet_empires = workbook.getSheet(key_sheet_empires);

    HSSFRow row;
    String empirename, playername;
    Player player;
    Empire empire;
    int blocks, colonies, x, y, z, r, level;

    // sheet_empires -> erstelle alle Imperien
    int rowNum = 1;
    while ((row = sheet_empires.getRow(rowNum++)) != null) {
        try {
            empirename = row.getCell(0).getStringCellValue();
            if (empirename == null || empirename.isEmpty())
                break;
            playername = row.getCell(1).getStringCellValue();
            blocks = (int) row.getCell(2).getNumericCellValue();
            colonies = (int) row.getCell(3).getNumericCellValue();
            x = (int) row.getCell(4).getNumericCellValue();
            y = (int) row.getCell(5).getNumericCellValue();
            z = (int) row.getCell(6).getNumericCellValue();
            r = (int) row.getCell(7).getNumericCellValue();
            level = (int) row.getCell(8).getNumericCellValue();

            logger.debug(
                    "creating empire: " + empirename + " [player = " + playername + "]" + " blocks/colonies="
                            + blocks + "/" + colonies + " @ (" + x + "|" + y + "|" + z + ") r=" + r);

            player = getOrCreatePlayer(playername, null, null);
            empire = getOrCreateEmpire(empirename, player, blocks, colonies, x, y, z, r, level);

            if (player.getCurrentEmpire() == null) {
                player.setCurrentEmpire(empire);
                player = playerManager.save(player);
                players.put(playername, player);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error: " + key_sheet_empires + " at line " + rowNum);
        }
    }
    logger.debug("empires created: " + empires.size());
}

---

From source file:com.syncnapsis.utils.data.UniverseEvolutionExcelParser.java

License:Open Source License

/**
 * Erstellen aller Allianzen und der Mitgliedschaften
 * //www.ja  v  a  2s  .c o  m
 * @param workbook - das Workbook
 * @param key_sheet_allianceMemberships - der Key fr das Sheet
 */
public static void parseAlliancesMemberships(HSSFWorkbook workbook, String key_sheet_allianceMemberships) {
    HSSFSheet sheet_allianceMemberships = workbook.getSheet(key_sheet_allianceMemberships);

    HSSFRow row;
    Cell cell;
    String alliancename, empirename, name, fullname;
    Alliance alliance;
    AllianceRank allianceRank;
    Empire empire;
    EmpireRank empireRank;
    int rowNum;

    List<Empire> members;

    // sheet_allianceMemberships -> Allianzen und Mitgliedschaften erstellen
    int colNum = 1;
    while ((cell = sheet_allianceMemberships.getRow(0).getCell(colNum++)) != null) {
        try {
            alliancename = cell.getStringCellValue();

            logger.debug("creating alliance: " + alliancename);

            alliance = getOrCreateAlliance(alliancename);

            logger.debug("alliance has " + alliance.getAllianceMemberRanks().size() + " ranks");

            members = new LinkedList<Empire>();

            // Mitglieder den Rngen zuweisen
            rowNum = 1;
            while ((row = sheet_allianceMemberships.getRow(rowNum++)) != null) {
                empirename = row.getCell(0).getStringCellValue();
                if (empirename == null || empirename.isEmpty())
                    break;

                empire = getOrCreateEmpire(empirename, null);
                name = row.getCell(colNum - 1).getStringCellValue();
                fullname = allianceMemberRanks.get(name + "_full");

                if (fullname != null) {
                    members.add(empire);
                    logger.debug("empire '" + empirename + "' has rank '" + name + "' ('" + fullname + "')");
                    for (AllianceMemberRank rank : alliance.getAllianceMemberRanks()) {
                        if (rank.getRankName().equals(fullname)) {
                            rank.getEmpires().add(empire);
                            break;
                        }
                    }
                }
            }
            for (AllianceMemberRank rank : alliance.getAllianceMemberRanks()) {
                allianceMemberRankManager.save(rank);
            }

            logger.debug("alliance has " + members.size() + " members");

            allianceRank = (AllianceRank) allianceRankManager.getByEntity(alliance.getId());
            for (Empire member : members) {
                empireRank = (EmpireRank) empireRankManager.getByEntity(member.getId());

                allianceRank.setEconomy(allianceRank.getEconomy() + empireRank.getEconomy());
                allianceRank.setMilitary(allianceRank.getMilitary() + empireRank.getMilitary());
                allianceRank.setScience(allianceRank.getScience() + empireRank.getScience());
                allianceRank.setTotal(allianceRank.getTotal() + empireRank.getTotal());
            }
            allianceRank.setNumberOfEmpires(members.size());
            allianceRank.setAverageEconomy(allianceRank.getEconomy() / allianceRank.getNumberOfEmpires());
            allianceRank.setAverageMilitary(allianceRank.getMilitary() / allianceRank.getNumberOfEmpires());
            allianceRank.setAverageScience(allianceRank.getScience() / allianceRank.getNumberOfEmpires());
            allianceRank.setAverageTotal(allianceRank.getTotal() / allianceRank.getNumberOfEmpires());
            allianceRank = (AllianceRank) allianceRankManager.save(allianceRank);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error: " + key_sheet_allianceMemberships + " at column " + colNum);
        }
    }
    logger.debug("alliances created: " + alliances.size());
}

---

From source file:com.syncnapsis.utils.data.UniverseEvolutionExcelParser.java

License:Open Source License

/**
 * Erstellen der Diplomatie zwischen Allianz und Allianz
 * /*from   w  ww.  ja  va2s  .c o  m*/
 * @param workbook - das Workbook
 * @param key_sheet_diplomacy_aa - der Key fr das Sheet
 */
public static void parseDiplomacy_AA(HSSFWorkbook workbook, String key_sheet_diplomacy_aa) {
    HSSFSheet sheet_diplomacy_aa = workbook.getSheet(key_sheet_diplomacy_aa);

    HSSFRow row;
    String contact1, contact2, name1, name2;
    int colNum;
    AllianceAllianceContact allianceAllianceContact;

    // sheet_diplomacy_aa -> Abkommen erstellen
    int rowNum = 1;
    int count = 0;
    while ((row = sheet_diplomacy_aa.getRow(rowNum++)) != null) {
        try {
            colNum = rowNum;

            contact1 = row.getCell(0).getStringCellValue();
            if (contact1 == null || contact1.isEmpty())
                break;

            while ((contact2 = sheet_diplomacy_aa.getRow(0).getCell(colNum++).getStringCellValue()) != null) {
                if (contact2 == null || contact2.isEmpty())
                    break;

                name1 = row.getCell(colNum - 1).getStringCellValue();
                name2 = sheet_diplomacy_aa.getRow(colNum - 1).getCell(rowNum - 1).getStringCellValue();
                if (contactAuthorities.get(name1) == null && contactAuthorities.get(name2) == null)
                    continue;
                if (contactAuthorities.get(name1) == null || contactAuthorities.get(name2) == null)
                    throw new Exception("both authorities must be set: " + contact1 + "(" + name1 + ") <-> "
                            + contact2 + "(" + name2 + ")");

                allianceAllianceContact = (AllianceAllianceContact) RandomModels.createContact(
                        getOrCreateAlliance(contact1), getOrCreateAlliance(contact2),
                        contactAuthorities.get(name1), contactAuthorities.get(name2));
                for (ContactGroup contactGroup : contactGroupManager
                        .getByAlliance(allianceAllianceContact.getContact1().getId())) {
                    if (contactGroup.getName().equals(name1)) {
                        allianceAllianceContact.getContactGroups().add(contactGroup);
                    }
                }
                for (ContactGroup contactGroup : contactGroupManager
                        .getByAlliance(allianceAllianceContact.getContact2().getId())) {
                    if (contactGroup.getName().equals(name2)) {
                        allianceAllianceContact.getContactGroups().add(contactGroup);
                    }
                }
                allianceAllianceContact = allianceAllianceContactManager.save(allianceAllianceContact);
                count++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error: " + key_sheet_diplomacy_aa + ": " + e.getMessage());
        }
    }
    logger.debug("contacts (alliance-alliance) created: " + count);
}

---

From source file:com.syncnapsis.utils.data.UniverseEvolutionExcelParser.java

License:Open Source License

/**
 * Erstellen der Diplomatie zwischen Allianz und Imperium
 * //  w ww  .  ja  v  a  2s. c om
 * @param workbook - das Workbook
 * @param key_sheet_diplomacy_ae - der Key fr das Sheet
 */
public static void parseDiplomacy_AE(HSSFWorkbook workbook, String key_sheet_diplomacy_ae) {
    HSSFSheet sheet_diplomacy_ae = workbook.getSheet(key_sheet_diplomacy_ae);

    HSSFRow row;
    String contact1, contact2, name;
    int colNum;
    EmpireAllianceContact empireAllianceContact;

    // sheet_diplomacy_ae -> Abkommen erstellen
    int rowNum = 1;
    int count = 0;
    while ((row = sheet_diplomacy_ae.getRow(rowNum++)) != null) {
        try {
            colNum = 1;

            contact1 = row.getCell(0).getStringCellValue();
            if (contact1 == null || contact1.isEmpty())
                break;

            while ((contact2 = sheet_diplomacy_ae.getRow(0).getCell(colNum++).getStringCellValue()) != null) {
                if (contact2 == null || contact2.isEmpty())
                    break;

                name = row.getCell(colNum - 1).getStringCellValue();
                if (contactAuthorities.get(name) == null)
                    continue;

                empireAllianceContact = (EmpireAllianceContact) RandomModels.createContact(
                        getOrCreateEmpire(contact1, null), getOrCreateAlliance(contact2),
                        contactAuthorities.get(name), contactAuthorities.get(name));
                for (ContactGroup contactGroup : contactGroupManager
                        .getByEmpire(empireAllianceContact.getContact1().getId())) {
                    if (contactGroup.getName().equals(name)) {
                        empireAllianceContact.getContactGroups().add(contactGroup);
                    }
                }
                for (ContactGroup contactGroup : contactGroupManager
                        .getByAlliance(empireAllianceContact.getContact2().getId())) {
                    if (contactGroup.getName().equals(name)) {
                        empireAllianceContact.getContactGroups().add(contactGroup);
                    }
                }
                empireAllianceContact = empireAllianceContactManager.save(empireAllianceContact);
                count++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error: " + key_sheet_diplomacy_ae + ": " + e.getMessage());
        }
    }
    logger.debug("contacts (empire-alliance) created: " + count);
}

---

From source file:com.syncnapsis.utils.data.UniverseEvolutionExcelParser.java

License:Open Source License

/**
 * Erstellen der Diplomatie zwischen Imperium und Imperium
 * //w ww  .ja  v a  2  s .  co  m
 * @param workbook - das Workbook
 * @param key_sheet_diplomacy_ee - der Key fr das Sheet
 */
public static void parseDiplomacy_EE(HSSFWorkbook workbook, String key_sheet_diplomacy_ee) {
    HSSFSheet sheet_diplomacy_ee = workbook.getSheet(key_sheet_diplomacy_ee);

    HSSFRow row;
    String contact1, contact2, name1, name2;
    int colNum;
    EmpireEmpireContact empireEmpireContact;

    // sheet_diplomacy_ee -> Abkommen erstellen
    int rowNum = 1;
    int count = 0;
    while ((row = sheet_diplomacy_ee.getRow(rowNum++)) != null) {
        try {
            colNum = rowNum;

            contact1 = row.getCell(0).getStringCellValue();
            if (contact1 == null || contact1.isEmpty())
                break;

            while ((contact2 = sheet_diplomacy_ee.getRow(0).getCell(colNum++).getStringCellValue()) != null) {
                if (contact2 == null || contact2.isEmpty())
                    break;

                name1 = row.getCell(colNum - 1).getStringCellValue();
                name2 = sheet_diplomacy_ee.getRow(colNum - 1).getCell(rowNum - 1).getStringCellValue();
                if (contactAuthorities.get(name1) == null && contactAuthorities.get(name2) == null)
                    continue;
                if (contactAuthorities.get(name1) == null || contactAuthorities.get(name2) == null)
                    throw new Exception("both authorities must be set: " + contact1 + "(" + name1 + ") <-> "
                            + contact2 + "(" + name2 + ")");

                empireEmpireContact = (EmpireEmpireContact) RandomModels.createContact(
                        getOrCreateEmpire(contact1, null), getOrCreateEmpire(contact2, null),
                        contactAuthorities.get(name1), contactAuthorities.get(name2));
                for (ContactGroup contactGroup : contactGroupManager
                        .getByEmpire(empireEmpireContact.getContact1().getId())) {
                    if (contactGroup.getName().equals(name1)) {
                        empireEmpireContact.getContactGroups().add(contactGroup);
                    }
                }
                for (ContactGroup contactGroup : contactGroupManager
                        .getByEmpire(empireEmpireContact.getContact1().getId())) {
                    if (contactGroup.getName().equals(name2)) {
                        empireEmpireContact.getContactGroups().add(contactGroup);
                    }
                }
                empireEmpireContact = empireEmpireContactManager.save(empireEmpireContact);
                count++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("error: " + key_sheet_diplomacy_ee + ": " + e.getMessage());
        }
    }
    logger.debug("contacts (empire-empire) created: " + count);
}

---

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRowWithFormat(String sheetName, int row, String[] value, String[] format) {
    try {/*ww  w.  j  a va2  s. c  o m*/
        String formatV = "";
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
            if (format.length >= col) {
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(format[col]));
                //cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
                //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cellA1.setCellStyle(cellStyle);
            }
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

---

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRow(String sheetName, int row, String[] value) {
    try {//from w  w w .ja v a  2  s  . c  om
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

---

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addHeaderRow(String sheetName, String[] columnNames) {
    try {//from  w  w w  .  j a va2  s.c  om
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        HSSFCellStyle cellStyle = setHeaderStyle(workbook);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(0);

        for (int col = 0; col < columnNames.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(columnNames[col]);
            cellA1.setCellStyle(cellStyle);
        }

        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Cols =" + columnNames + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Cols =" + columnNames + " " + e.getMessage());
        e.printStackTrace();
    }

}

---

From source file:com.thingtrack.xbom.parser.XBomParserApplication.java

License:Apache License

private void parseXbom(InputStream file) throws FileNotFoundException, IOException, XbomParsingException {

    //Clear previous importations
    assemblingPartNodes.clear();/*  ww w  .  ja va 2s.  co m*/

    // Load XLS file
    POIFSFileSystem fs = new POIFSFileSystem(file);
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheet(XBOM_SHEET_CAPTION);

    // Parsing assembling parts
    for (int i = 11; i < sheet.getLastRowNum(); i++) {

        HSSFRow row = (HSSFRow) sheet.getRow(i);

        try {
            assemblingPartNodes.add(getAssemblingPart(row));
        }
        //The assembling part parsing has ended
        catch (NoAssemblingPartException e) {
            break;
        }
    }

    if (assemblingPartNodes.size() > 2) {
        //Build tree relationship
        buildTreeRelationship(assemblingPartNodes.get(0),
                assemblingPartNodes.subList(1, assemblingPartNodes.size()));
        loadTreeTable(assemblingPartNodes.get(0));
    }

    assemblingPartNodes.size();
}

---

From source file:com.veeduria.web.cargaarchivo.aut.th.CargaPlanta.java

public void cargarArchivoEmpleados(Path rutaArchivo) {
    //Get first sheet from the workbook
    try {/*from ww  w  . j a  va  2  s.c o  m*/
        StringBuilder strBSql = new StringBuilder();
        HSSFWorkbook workbook = new HSSFWorkbook(Files.newInputStream(rutaArchivo, StandardOpenOption.READ));
        HSSFSheet sheet = workbook.getSheet("EMPLEADOS");
        if (sheet != null) {
            Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = (Row) rowIterator.next();
                if (row.getRowNum() >= 10) {
                    if (row.getCell(0) != null) {
                        for (int i = 0; i < 42; i++) {
                            Cell cell = row.getCell(i);
                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BOOLEAN:
                                strBSql.append(cell.getBooleanCellValue());
                                strBSql.append(",");
                                //  System.out.print(cell.getBooleanCellValue() + "\t\t");
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                strBSql.append(cell.getNumericCellValue());
                                strBSql.append(",");
                                //   System.out.print(cell.getNumericCellValue() + "\t\t");
                                break;
                            case Cell.CELL_TYPE_STRING:
                                strBSql.append(",'");
                                strBSql.append(cell.getStringCellValue());
                                strBSql.append("',");
                                //  System.out.print(cell.getStringCellValue() + "\t\t");
                                break;

                            }

                        }
                        strBSql.replace(strBSql.length() - 1, strBSql.length(), "");
                    }

                }
                strBSql.append(System.getProperty("line.separator"));
            }

            System.out.println("");
        }

        //            FileOutputStream out = new FileOutputStream(new File(System.getProperty("user.home") + File.separator + "test.xls"));
        //            workbook.write(out);
        //            out.close();
    } catch (IOException e) {
        Logger.getLogger(VigilarCarpetaSLBean.class.getName()).log(Level.SEVERE, null, e);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• »