Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. XSSFWorkbook

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook.

Prototype

public XSSFWorkbook(PackagePart part) throws IOException

Source Link

Document

Constructs a XSSFWorkbook object using Package Part.

Usage

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.reader.DataTableReaderExcel.java

License:Apache License

@Override
public void read(DataTable dataTable) throws Exception {

    workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);

    int rowIndex = 0;
    for (Row row : sheet) {
        log.debug("\nRow: " + rowIndex++);
        Record rec = new Record(dataTable.getRecordDef());
        int colIndex = 0;
        for (FieldDef fieldDef : dataTable.getRecordDef()) {
            log.debug("Col: " + colIndex + ": " + fieldDef.getName() + ": " + fieldDef.getDescription());

            Cell cell = row.getCell(colIndex++);
            readCell(rec, fieldDef, cell);
        }/*  w ww .  ja v a  2  s  . com*/
        dataTable.add(rec);
    }
}

From source file:com.bright.json.PGS.java

License:Open Source License

public static void main(String[] args) throws FileNotFoundException {

    String fileBasename = null;//from  w  w  w . ja v a  2s  . co m

    JFileChooser chooser = new JFileChooser();
    try {
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Excel Spreadsheets", "xls", "xlsx");
        chooser.setFileFilter(filter);
        chooser.setCurrentDirectory(new java.io.File(System.getProperty("user.home")));
        chooser.setDialogTitle("Select the Excel file");

        chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
        chooser.setAcceptAllFileFilterUsed(false);

        if (chooser.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) {
            System.out.println("getCurrentDirectory(): " + chooser.getCurrentDirectory());
            System.out.println("getSelectedFile() : " + chooser.getSelectedFile());

            // String fileBasename =
            // chooser.getSelectedFile().toString().substring(chooser.getSelectedFile().toString().lastIndexOf(File.separator)+1,chooser.getSelectedFile().toString().lastIndexOf("."));
            fileBasename = chooser.getSelectedFile().toString()
                    .substring(chooser.getSelectedFile().toString().lastIndexOf(File.separator) + 1);
            System.out.println("Base name: " + fileBasename);

        } else {
            System.out.println("No Selection ");

        }
    } catch (Exception e) {

        System.out.println(e.toString());

    }
    String fileName = chooser.getSelectedFile().toString();
    InputStream inp = new FileInputStream(fileName);
    Workbook workbook = null;
    if (fileName.toLowerCase().endsWith("xlsx")) {
        try {
            workbook = new XSSFWorkbook(inp);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } else if (fileName.toLowerCase().endsWith("xls")) {
        try {
            workbook = new HSSFWorkbook(inp);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    Sheet nodeSheet = workbook.getSheet("Devices");
    Sheet interfaceSheet = workbook.getSheet("Interfaces");
    System.out.println("Read nodes sheet.");
    // Row nodeRow = nodeSheet.getRow(1);
    // System.out.println(row.getCell(0).toString());
    // System.exit(0);

    JTextField uiHost = new JTextField("demo.brightcomputing.com");
    // TextPrompt puiHost = new
    // TextPrompt("demo.brightcomputing.com",uiHost);
    JTextField uiUser = new JTextField("root");
    // TextPrompt puiUser = new TextPrompt("root", uiUser);
    JTextField uiPass = new JPasswordField("");
    // TextPrompt puiPass = new TextPrompt("x5deix5dei", uiPass);

    JPanel myPanel = new JPanel(new GridLayout(5, 1));
    myPanel.add(new JLabel("Bright HeadNode hostname:"));
    myPanel.add(uiHost);
    // myPanel.add(Box.createHorizontalStrut(1)); // a spacer
    myPanel.add(new JLabel("Username:"));
    myPanel.add(uiUser);
    myPanel.add(new JLabel("Password:"));
    myPanel.add(uiPass);

    int result = JOptionPane.showConfirmDialog(null, myPanel, "Please fill in all the fields.",
            JOptionPane.OK_CANCEL_OPTION);
    if (result == JOptionPane.OK_OPTION) {
        System.out.println("Input received.");

    }

    String rhost = uiHost.getText();
    String ruser = uiUser.getText();
    String rpass = uiPass.getText();

    String cmURL = "https://" + rhost + ":8081/json";
    List<Cookie> cookies = doLogin(ruser, rpass, cmURL);
    chkVersion(cmURL, cookies);

    Map<String, Long> categories = UniqueKeyMap(cmURL, "cmdevice", "getCategories", cookies);
    Map<String, Long> networks = UniqueKeyMap(cmURL, "cmnet", "getNetworks", cookies);
    Map<String, Long> partitions = UniqueKeyMap(cmURL, "cmpart", "getPartitions", cookies);
    Map<String, Long> racks = UniqueKeyMap(cmURL, "cmpart", "getRacks", cookies);
    Map<String, Long> switches = UniqueKeyMap(cmURL, "cmdevice", "getEthernetSwitches", cookies);
    // System.out.println(switches.get("switch01"));
    // System.out.println("Size of the map: "+ switches.size());
    // System.exit(0);
    cmDevice newnode = new cmDevice();
    cmDevice.deviceObject devObj = new cmDevice.deviceObject();
    cmDevice.switchObject switchObj = new cmDevice.switchObject();
    // cmDevice.netObject netObj = new cmDevice.netObject();

    List<String> emptyslist = new ArrayList<String>();

    // Row nodeRow = nodeSheet.getRow(1);
    // Row ifRow = interfaceSheet.getRow(1);
    // System.out.println(nodeRow.getCell(0).toString());
    // nodeRow.getCell(3).getStringCellValue()
    Map<String, ArrayList<cmDevice.netObject>> ifmap = new HashMap<String, ArrayList<cmDevice.netObject>>();
    // Map<String,netObject> helperMap = new HashMap<String,netObject>();
    // Iterator<Row> rows = interfaceSheet.rowIterator ();
    // while (rows. < interfaceSheet.getPhysicalNumberOfRows()) {

    // List<netObject> netList = new ArrayList<netObject>();
    for (int i = 0; i < interfaceSheet.getPhysicalNumberOfRows(); i++) {
        Row ifRow = interfaceSheet.getRow(i);
        if (ifRow.getRowNum() == 0) {
            continue; // just skip the rows if row number is 0
        }

        System.out.println("Row nr: " + ifRow.getRowNum());
        cmDevice.netObject netObj = new cmDevice.netObject();
        ArrayList<cmDevice.netObject> helperList = new ArrayList<cmDevice.netObject>();
        netObj.setBaseType("NetworkInterface");
        netObj.setCardType(ifRow.getCell(3).getStringCellValue());
        netObj.setChildType(ifRow.getCell(4).getStringCellValue());
        netObj.setDhcp((ifRow.getCell(5).getNumericCellValue() > 0.1) ? true : false);
        netObj.setIp(ifRow.getCell(7).getStringCellValue());
        // netObj.setMac(ifRow.getCell(0).toString());
        //netObj.setModified(true);
        netObj.setName(ifRow.getCell(1).getStringCellValue());
        netObj.setNetwork(networks.get(ifRow.getCell(6).getStringCellValue()));
        //netObj.setOldLocalUniqueKey(0L);
        netObj.setRevision("");
        netObj.setSpeed(ifRow.getCell(8, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        netObj.setStartIf("ALWAYS");
        netObj.setToBeRemoved(false);
        netObj.setUniqueKey((long) ifRow.getCell(2).getNumericCellValue());
        //netObj.setAdditionalHostnames(new ArrayList<String>(Arrays.asList(ifRow.getCell(9, Row.CREATE_NULL_AS_BLANK).getStringCellValue().split("\\s*,\\s*"))));
        //netObj.setMac(ifRow.getCell(10, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        netObj.setMode((int) ifRow.getCell(11, Row.CREATE_NULL_AS_BLANK).getNumericCellValue());
        netObj.setOptions(ifRow.getCell(12, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        netObj.setMembers(new ArrayList<String>(Arrays
                .asList(ifRow.getCell(13, Row.CREATE_NULL_AS_BLANK).getStringCellValue().split("\\s*,\\s*"))));
        // ifmap.put(ifRow.getCell(0).getStringCellValue(), new
        // HashMap<String, cmDevice.netObject>());
        // helperMap.put(ifRow.getCell(1).getStringCellValue(), netObj) ;
        // ifmap.get(ifRow.getCell(0).getStringCellValue()).putIfAbsent(ifRow.getCell(1).getStringCellValue(),
        // netObj);
        // ifmap.put(ifRow.getCell(0).getStringCellValue(), helperMap);

        if (!ifmap.containsKey(ifRow.getCell(0).getStringCellValue())) {
            ifmap.put(ifRow.getCell(0).getStringCellValue(), new ArrayList<cmDevice.netObject>());
        }
        helperList = ifmap.get(ifRow.getCell(0).getStringCellValue());
        helperList.add(netObj);
        ifmap.put(ifRow.getCell(0).getStringCellValue(), helperList);

        continue;
    }

    for (int i = 0; i < nodeSheet.getPhysicalNumberOfRows(); i++) {
        Row nodeRow = nodeSheet.getRow(i);
        if (nodeRow.getRowNum() == 0) {
            continue; // just skip the rows if row number is 0
        }

        newnode.setService("cmdevice");
        newnode.setCall("addDevice");

        Map<String, Long> ifmap2 = new HashMap<String, Long>();
        for (cmDevice.netObject j : ifmap.get(nodeRow.getCell(0).getStringCellValue()))
            ifmap2.put(j.getName(), j.getUniqueKey());

        switchObj.setEthernetSwitch(switches.get(nodeRow.getCell(8).getStringCellValue()));
        System.out.println(nodeRow.getCell(8).getStringCellValue());
        System.out.println(switches.get(nodeRow.getCell(8).getStringCellValue()));
        switchObj.setPrt((int) nodeRow.getCell(9).getNumericCellValue());
        switchObj.setBaseType("SwitchPort");

        devObj.setBaseType("Device");
        // devObj.setCreationTime(0L);
        devObj.setCustomPingScript("");
        devObj.setCustomPingScriptArgument("");
        devObj.setCustomPowerScript("");
        devObj.setCustomPowerScriptArgument("");
        devObj.setCustomRemoteConsoleScript("");
        devObj.setCustomRemoteConsoleScriptArgument("");
        devObj.setDisksetup("");
        devObj.setBmcPowerResetDelay(0L);
        devObj.setBurning(false);
        devObj.setEthernetSwitch(switchObj);
        devObj.setExcludeListFull("");
        devObj.setExcludeListGrab("");
        devObj.setExcludeListGrabnew("");
        devObj.setExcludeListManipulateScript("");
        devObj.setExcludeListSync("");
        devObj.setExcludeListUpdate("");
        devObj.setFinalize("");
        devObj.setRack(racks.get(nodeRow.getCell(10).getStringCellValue()));
        devObj.setRackHeight((long) nodeRow.getCell(11).getNumericCellValue());
        devObj.setRackPosition((long) nodeRow.getCell(12).getNumericCellValue());
        devObj.setIndexInsideContainer(0L);
        devObj.setInitialize("");
        devObj.setInstallBootRecord(false);
        devObj.setInstallMode("");
        devObj.setIoScheduler("");
        devObj.setLastProvisioningNode(0L);
        devObj.setMac(nodeRow.getCell(6).getStringCellValue());

        devObj.setModified(true);
        devObj.setCategory(categories.get(nodeRow.getCell(1).getStringCellValue()));
        devObj.setChildType("PhysicalNode");
        //devObj.setDatanode((nodeRow.getCell(5).getNumericCellValue() > 0.1) ? true
        //      : false);
        devObj.setHostname(nodeRow.getCell(0).getStringCellValue());
        devObj.setModified(true);
        devObj.setPartition(partitions.get(nodeRow.getCell(2).getStringCellValue()));
        devObj.setUseExclusivelyFor("Category");

        devObj.setNextBootInstallMode("");
        devObj.setNotes("");
        devObj.setOldLocalUniqueKey(0L);

        devObj.setPowerControl(nodeRow.getCell(7).getStringCellValue());

        // System.out.println(ifmap.get("excelnode001").size());
        // System.out.println(ifmap.get(nodeRow.getCell(0).getStringCellValue()).get(nodeRow.getCell(3).getStringCellValue()).getUniqueKey());

        devObj.setManagementNetwork(networks.get(nodeRow.getCell(3).getStringCellValue()));

        devObj.setProvisioningNetwork(ifmap2.get(nodeRow.getCell(4).getStringCellValue()));
        devObj.setProvisioningTransport("RSYNCDAEMON");
        devObj.setPxelabel("");
        // "rack": 90194313218,
        // "rackHeight": 1,
        // "rackPosition": 4,
        devObj.setRaidconf("");
        devObj.setRevision("");

        devObj.setSoftwareImageProxy(null);
        devObj.setStartNewBurn(false);

        devObj.setTag("00000000a000");
        devObj.setToBeRemoved(false);
        // devObj.setUcsInfoConfigured(null);
        // devObj.setUniqueKey(12345L);

        devObj.setUserdefined1("");
        devObj.setUserdefined2("");

        ArrayList<Object> mylist = new ArrayList<Object>();

        ArrayList<cmDevice.netObject> mylist2 = new ArrayList<cmDevice.netObject>();
        ArrayList<Object> emptylist = new ArrayList<Object>();

        devObj.setFsexports(emptylist);
        devObj.setFsmounts(emptylist);
        devObj.setGpuSettings(emptylist);
        devObj.setFspartAssociations(emptylist);

        devObj.setPowerDistributionUnits(emptyslist);

        devObj.setRoles(emptylist);
        devObj.setServices(emptylist);
        devObj.setStaticRoutes(emptylist);

        mylist2 = ifmap.get(nodeRow.getCell(0).getStringCellValue());

        devObj.setNetworks(mylist2);
        mylist.add(devObj);
        mylist.add(1);
        newnode.setArgs(mylist);

        GsonBuilder builder = new GsonBuilder();
        builder.enableComplexMapKeySerialization();

        // Gson g = new Gson();
        Gson g = builder.create();

        String json2 = g.toJson(newnode);

        // To be used from a real console and not Eclipse

        String message = JSonRequestor.doRequest(json2, cmURL, cookies);
        continue;
    }

    JOptionPane optionPaneF = new JOptionPane("The nodes have been added!");
    JDialog myDialogF = optionPaneF.createDialog(null, "Complete:  ");
    myDialogF.setModal(false);
    myDialogF.setVisible(true);
    doLogout(cmURL, cookies);
    // System.exit(0);
}

From source file:com.bus.util.ExcelFileSaver.java

public ExcelFileSaver(FileInputStream fis, boolean isExcel) {
    try {/*from  w  w  w  .  j  av  a2s.  co  m*/
        wb = new XSSFWorkbook(fis);
    } catch (Exception e) {//Catch exception if any
        System.err.println("Error: " + e.getMessage());
    }
}

From source file:com.camel.action.location.CityAction.java

public void handleFileUpload(FileUploadEvent event) {
    try {//from   w w w  .ja  v  a2s.c  o  m
        List<City> citiesList = new ArrayList<City>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String cityCode = "";
        String cityName = "";
        String countryCode = "";
        String countryName = "";

        InputStream fis = event.getFile().getInputstream();

        //Create Workbook instance for xlsx/xls file input stream
        Workbook workbook = null;
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        }

        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        City city = null;
        while (rowIterator.hasNext()) {
            cityCode = "";
            cityName = "";
            countryCode = "";
            countryName = "";

            row = rowIterator.next();
            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0)
                continue;

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (cityCode.equalsIgnoreCase("")) {
                        cityCode = cell.getStringCellValue().trim();
                    } else if (cityName.equalsIgnoreCase("")) {
                        cityName = cell.getStringCellValue().trim();
                    } else if (countryCode.equalsIgnoreCase("")) {
                        countryCode = cell.getStringCellValue().trim();
                    }
                    break;
                }
            } //end of cell iterator
            if (countryCode.equals("#N/A"))
                continue;

            country = findCountry(countryCode);

            if (country != null) {
                city = new City();
                city.setCityCode(cityCode);
                city.setCityName(cityName);
                city.setCountry(country);

                citiesList.add(city);
            }

        } //end of rows iterator

        //close file input stream
        fis.close();
        for (City c : citiesList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    FacesContext.getCurrentInstance().addMessage(null, message);
    country = null;
}

From source file:com.camel.action.location.CountryAction.java

public void handleFileUpload(FileUploadEvent event) {
    String errorMessage = "";
    try {//  www  .  j a  va 2 s  .c  o m
        List<Country> countriesList = new ArrayList<Country>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String name = "";
        String shortCode = "";
        String cont = "";

        InputStream fis = event.getFile().getInputstream();

        //Create Workbook instance for xlsx/xls file input stream
        Workbook workbook = null;
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        }

        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        Country country = null;
        while (rowIterator.hasNext()) {
            name = "";
            shortCode = "";
            cont = "";

            row = rowIterator.next();

            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0) {
                continue;
            }

            while (cellIterator.hasNext()) {

                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (shortCode.equalsIgnoreCase("")) {
                        shortCode = cell.getStringCellValue().trim();
                    } else if (name.equalsIgnoreCase("")) {
                        name = cell.getStringCellValue().trim();
                    } else if (cont.equalsIgnoreCase("")) {
                        cont = cell.getStringCellValue().trim();
                    }
                    break;

                }

            } //end of cell iterator
            if (cont != null && cont.length() > 3) {
                country = new Country();
                country.setContinet(Continent.valueOf(cont));
                country.setCountryCode(shortCode);
                country.setCountryName(name);
                countriesList.add(country);
            }
        } //end of rows iterator

        fis.close();
        for (Country c : countriesList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        errorMessage = e.getMessage();
    }
    System.out.println("eerrromessage..:" + errorMessage);
    FacesMessage message = null;
    if (errorMessage != null && errorMessage.length() > 3) {
        message = new FacesMessage("ERROR..:", "Country dosn't uploaded![" + errorMessage + "]");
    } else {
        message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    }
    FacesContext.getCurrentInstance().addMessage(null, message);

}

From source file:com.camel.action.location.PortAction.java

public void handleFileUpload(FileUploadEvent event) {
    try {/*from  w  w w.j a va 2  s  .c  om*/
        List<Port> portsList = new ArrayList<Port>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String portCode = "";
        String portName = "";
        String cityCode = "";
        String cityName = "";
        String countryCode = "";
        String countryName = "";
        InputStream fis = event.getFile().getInputstream();

        //Create Workbook instance for xlsx/xls file input stream
        Workbook workbook = null;
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        }

        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        City city = null;
        Port port = null;
        while (rowIterator.hasNext()) {
            portCode = "";
            portName = "";
            cityCode = "";
            cityName = "";
            countryCode = "";
            countryName = "";

            row = rowIterator.next();
            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0)
                continue;

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (portCode.equalsIgnoreCase("")) {
                        portCode = cell.getStringCellValue().trim();
                    } else if (portName.equalsIgnoreCase("")) {
                        portName = cell.getStringCellValue().trim();
                    } else if (cityCode.equalsIgnoreCase("")) {
                        cityCode = cell.getStringCellValue().trim();
                    } else if (cityName.equalsIgnoreCase("")) {
                        cityName = cell.getStringCellValue().trim();
                    } else if (countryCode.equalsIgnoreCase("")) {
                        countryCode = cell.getStringCellValue().trim();
                    } else if (countryName.equalsIgnoreCase("")) {
                        countryName = cell.getStringCellValue().trim();
                    }
                    break;
                }
            } //end of cell iterator
            if (countryCode.equals("#N/A"))
                continue;

            country = findCountry(countryCode);
            city = findCity(cityCode);
            if (country != null && city != null) {
                port = new Port();
                port.setPortType(PortType.SEAPORT);
                port.setPortCode(portCode);
                port.setPortName(portName);
                port.setCity(city);
                port.setCountry(country);
                portsList.add(port);
            }

        } //end of rows iterator

        //close file input stream
        fis.close();
        for (Port c : portsList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    FacesContext.getCurrentInstance().addMessage(null, message);

    country = null;

}

From source file:com.canoo.webtest.plugins.exceltest.AbstractExcelStep.java

License:Open Source License

/**
 * Creates an Workbook for the page. This method should not be used directly,
 * use {@link #getExcelWorkbook()} instead.
 * @param currentPage the page containing the Excel document
 * @return the analyzer/*from www .j av a 2  s  . com*/
 */
private Workbook createWorkbook(final Page currentPage) {
    InputStream is = null;
    try {
        getContext().put(KEY_CURRENT_SHEET, null);

        try {
            is = currentPage.getWebResponse().getContentAsStream();
            final POIFSFileSystem excelFile = new RetryWithCapsPOIFSFileSystem(is);
            return new HSSFWorkbook(excelFile);
        } catch (Exception e) {
            is = currentPage.getWebResponse().getContentAsStream();
            return new XSSFWorkbook(is);
        }
    } catch (final Exception e) {
        final String message = "Could not open Excel file.";
        LOG.debug(message, e);
        throw new StepExecutionException(message, this, e);
    } finally {
        IOUtils.closeQuietly(is);
    }
}

From source file:com.carlos.projects.billing.ExcelToMySQLImporter.java

License:Open Source License

public Long importData(MultipartFile excelFile) throws ImportException {
    XSSFWorkbook workbook;/* w ww  .  ja  v a2 s  . c  o  m*/
    File componentsFile;
    try {
        componentsFile = new File("components-" + new Date().getTime() + ".xlsx");
        excelFile.transferTo(componentsFile);
        workbook = new XSSFWorkbook(componentsFile.getAbsolutePath());
    } catch (IOException e) {
        throw new ImportException(messages.getProperty("import.error"), e);
    }
    workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
    Iterator<Row> rowIterator = workbook.getSheetAt(workbook.getActiveSheetIndex()).iterator();
    Long numberOfImportedItems = 0L;
    log.info("Starting reading from file " + excelFile.getOriginalFilename()
            + " to import components to database");
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        String familyCode = row.getCell(FAMILY_CODE).getStringCellValue().trim();
        //The first row of the excel file is the one with the titles
        if (row.getRowNum() != 0 && StringUtils.isNotBlank(familyCode)) {
            Family family = familyDAO.getById(Family.class, familyCode);
            boolean saveFamily = false;
            if (family == null) {
                family = createFamilyFromRow(row);
                saveFamily = true;
            }
            String componentCode = row.getCell(COMPONENT_CODE).getStringCellValue().trim();
            Component component = componentDAO.getById(Component.class, componentCode);
            boolean addComponent = false;
            if (component == null) {
                addComponent = true;
                component = createComponent(row, family);
                numberOfImportedItems += 1L;
            }
            if (saveFamily) {
                if (addComponent) {
                    family.addComponent(component);
                }
                familyDAO.save(family);
                log.info("Family " + family + " saved into the database");
            } else {
                componentDAO.save(component);
                log.info("Component " + component + " saved into the database");
            }
        }
    }
    closeAndDeleteTemporaryFiles(componentsFile);
    log.info("Components import to database finished");
    return numberOfImportedItems;
}

From source file:com.celtris.exparse.parser.ExcelReader.java

License:Apache License

public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction)
        throws IOException, InstantiationException, IllegalAccessException {

    FileInputStream file = new FileInputStream(new File(absolutePath));

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook);

    Iterator<Sheet> sheetIterator = workbook.iterator();
    List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets());
    int sheetCount = 0;
    while (sheetIterator.hasNext()) {
        sheetCount++;/*from   w w w .  j  a v  a 2 s  . c o  m*/

        ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass);
        Sheet sheet = sheetIterator.next();
        Iterator<Row> rowIterator = sheet.iterator();

        int rowCount = 0;

        // Evaluating header
        if (headerExtraction) {
            if (rowIterator.hasNext()) {

                rowCount++;

                Field[] fields = excelModelClass.getFields();
                List<String> heaaderStr = new ArrayList<String>(fields.length);

                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    String cellStrValue = cell.getStringCellValue();

                    heaaderStr.add(cellStrValue);
                }
                excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName());
            }
        }

        while (rowIterator.hasNext()) {
            rowCount++;
            Row row = rowIterator.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            List<String> rowStr = new ArrayList<String>(excelParser.parameterCount());
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellStrValue = "";
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        objFormulaEvaluator.evaluate(cell);
                        cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                    } else {

                        cellStrValue = Double.toString(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellStrValue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case BLANK:

                default:
                    break;
                }
                rowStr.add(cellStrValue);
            }

            excelParser.processRow(rowStr, rowCount, sheet.getSheetName());
        }

        SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(),
                sheetCount);
        sheetDataList.add(sheetData);
    }

    file.close();
    workbook.close();
    return sheetDataList;
}

From source file:com.centurylink.mdw.drools.Excel2007Parser.java

License:Apache License

public void parseFile(InputStream inStream) {
    try (XSSFWorkbook workbook = new XSSFWorkbook(inStream)) {
        if (useFirstSheet) {
            XSSFSheet sheet = workbook.getSheetAt(0);
            processSheet(sheet, listeners.get(DEFAULT_RULESHEET_NAME));
        } else {//from  w  w w .j ava  2s . co  m
            for (String sheetName : listeners.keySet()) {
                XSSFSheet sheet = workbook.getSheet(sheetName);
                processSheet(sheet, listeners.get(sheetName));
            }
        }
    } catch (IOException ex) {
        throw new DecisionTableParseException(ex.getMessage(), ex);
    }
}