Example usage for org.apache.commons.csv CSVFormat EXCEL

List of usage examples for org.apache.commons.csv CSVFormat EXCEL

  1. HOME
  2. Java
  3. org.apache.commons
  4. org.apache.commons.csv.*
  5. CSVFormat
  6. EXCEL

Introduction

In this page you can find the example usage for org.apache.commons.csv CSVFormat EXCEL.

Prototype

CSVFormat EXCEL

To view the source code for org.apache.commons.csv CSVFormat EXCEL.

Click Source Link

Document

Excel file format (using a comma as the value delimiter).

Usage

From source file:com.kdmanalytics.toif.ui.common.AdaptorConfiguration.java

/**
 * Load configuration data from the specified stream.
 * /*from w ww  .  j  av  a  2 s  .c om*/
 * @param is
 * @throws IOException
 */
private synchronized void load(InputStream is) throws IOException {
    if (!isEmpty()) {
        // If there is already data loaded, we want to merge the new data
        merge(is);
    } else {
        InputStreamReader in = null;
        CSVParser parser = null;
        try {
            in = new InputStreamReader(is);
            CSVFormat format = CSVFormat.EXCEL.withDelimiter(',').withIgnoreEmptyLines();

            parser = new CSVParser(in, format);

            // Set to false once the header is read
            boolean header = true;
            // Number of rows we have loaded so far
            int rcount = data.size();
            // Import all new rows
            for (CSVRecord record : parser) {
                if (header) {
                    parseHeader(record);
                    header = false;
                } else {
                    rcount = parseData(record, rcount);
                }
            }
        } finally {
            if (in != null) {
                in.close();
            }
            if (parser != null) {
                parser.close();
            }
        }
    }
}

From source file:com.kdmanalytics.toif.report.internal.importWizard.TsvImportWizardPage.java

/**
 * Perform the actual load./*from   ww  w .  j  a v a2 s.  c o  m*/
 * 
 * @return
 */
public boolean finish() {
    // Check source file
    final String name = editor.getStringValue();
    setErrorMessage("Importing " + name + " into " + project + "...");
    IPath location = new Path(name);
    File file = location.toFile();

    Reader in = null;
    CSVParser parser = null;
    try {
        in = new FileReader(file);
        CSVFormat format = CSVFormat.EXCEL.withDelimiter('\t').withIgnoreEmptyLines();

        parser = new CSVParser(in, format);

        System.err.println("FILE: " + name);

        Map<Integer, String> lookup = new HashMap<Integer, String>();
        boolean header = true;

        for (CSVRecord record : parser) {
            int size = record.size();

            IFile ifile = null;
            String tool = null;
            String description = null;
            int line = 0;
            int offset = 0;
            int trust = 0;
            Boolean status = null;
            int kdmLine = 0;
            String cwe = null;
            String sfp = null;

            // Read the header first
            if (header) {
                System.err.print("  ");
                for (int i = 0; i < size; i++) {
                    if (i > 0)
                        System.err.print(",");
                    String cell = record.get(i);
                    lookup.put(i, cell);
                    System.err.print(cell);
                }
                header = false;
                System.err.println();
                System.err.println("  ------------------------------------------");
            }

            // Otherwise this is a data row
            else {
                for (int i = 0; i < size; i++) {
                    String cell = record.get(i);
                    String colName = lookup.get(i);
                    if ("Resource".equals(colName)) {
                        IFileGroup group = new FileGroup(cell);
                        try {
                            IResource resource = MemberUtil.findMembers(project, group);
                            if (resource != null) {
                                ifile = (IFile) resource;
                            }
                        } catch (CoreException e) {
                            e.printStackTrace();
                        }
                    } else if ("SFP".equals(colName)) {
                        sfp = cell;
                    } else if ("CWE".equals(colName)) {
                        cwe = cell;
                    }
                    // Valid is *old* name for "Citing Status"
                    else if ("Valid".equals(colName)) {
                        if (cell != null && !cell.trim().isEmpty()) {
                            status = Boolean.parseBoolean(cell);
                        }
                    } else if ("Citing Status".equals(colName)) {
                        if (cell != null && !cell.trim().isEmpty()) {
                            status = Boolean.parseBoolean(cell);
                        }
                    } else if ("Trust".equals(colName)) {
                        if (cell != null && !cell.trim().isEmpty()) {
                            try {
                                trust = Integer.parseInt(cell);
                            } catch (NumberFormatException e) {
                            }
                        }
                    } else if ("Confidence".equals(colName)) {
                        if (cell != null && !cell.trim().isEmpty()) {
                            try {
                                trust = Integer.parseInt(cell);
                            } catch (NumberFormatException e) {
                            }
                        }
                    } else if ("Line Number".equals(colName)) {
                        if (cell != null && !cell.trim().isEmpty()) {
                            try {
                                line = Integer.parseInt(cell);
                            } catch (NumberFormatException e) {
                            }
                        }
                    } else if ("KDM Line Number".equals(colName)) {
                        if (cell != null && !cell.trim().isEmpty()) {
                            try {
                                kdmLine = Integer.parseInt(cell);
                            } catch (NumberFormatException e) {
                            }
                        }
                    }
                    // "Generator Tool" is *old* name for "SCA Tool"
                    else if ("Generator Tool".equals(colName)) {
                        tool = cell;
                    } else if ("SCA tool".equalsIgnoreCase(colName)) {
                        tool = cell;
                    } else if ("Weakness Description".equals(colName)) {
                        description = cell;
                    } else {
                        System.err.println("WARNING: Unknown column name '" + colName + "'");
                    }
                }

                System.err.print("  ");
                System.err.print(sfp);
                System.err.print(",");
                System.err.print(cwe);
                System.err.print(",");
                System.err.print(status);
                System.err.print(",");
                System.err.print(trust);
                System.err.print(",");
                System.err.print(ifile);
                System.err.print(",");
                System.err.print(line);
                System.err.print(",");
                System.err.print(kdmLine);
                System.err.print(",");
                System.err.print(tool);
                System.err.print(",");
                System.err.print(description);
                System.err.println();

                if (ifile != null) {
                    // Create an associated finding. This will allow us to
                    // set the citing status for the finding. If the
                    // finding does not actually exist in the database this information
                    // is still stored in case the finding exists in the future.
                    FindingData finding = new FindingData(ifile, tool, description, line, offset, cwe, sfp);
                    if (status != null) {
                        finding.cite(status);
                    }
                }
            }
        }

        try {
            IWorkbenchWindow window = PlatformUI.getWorkbench().getActiveWorkbenchWindow();
            if (window != null) {
                IWorkbenchPage page = window.getActivePage();
                if (page != null) {
                    FindingView view = (FindingView) page.showView("com.kdmanalytics.toif.views.FindingView");
                    view.refresh();
                }

            }
        } catch (PartInitException e) {
            e.printStackTrace();
        }

    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (parser != null) {
            try {
                parser.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (in != null) {
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    // PlatformUI.getWorkbench().getDisplay().asyncExec(new Runnable()
    // {
    // public void run()
    // {
    // final ToifReportImportJob job = new ToifReportImportJob("Import SFP/CWE Data", project,
    // name);
    // job.setUser(true);
    // job.setPriority(Job.BUILD);
    // job.setRule(project);
    // job.schedule();
    // }
    // });

    return true;
}

From source file:com.blackducksoftware.integration.hubdiff.HubDiff.java

public String writeDiffAsCSV(File file) throws IOException {
    if (!file.exists()) {
        file.createNewFile();/*from ww  w .j a va 2s.c  om*/
    }

    CSVPrinter printer = new CSVPrinter(new PrintStream(file), CSVFormat.EXCEL);

    printer.printRecord("HubVersions", swaggerDoc1.getVersion(), " -> ", swaggerDoc2.getVersion());
    printer.printRecord("Operation", "Expected", "Actual", "Field");

    // Log all additions to the API
    for (FieldComparisonFailure added : results.getFieldUnexpected()) {
        printer.printRecord("ADDED", added.getExpected(), added.getActual(), added.getField());
    }
    // Log all changes made to the API
    for (FieldComparisonFailure changed : results.getFieldFailures()) {
        printer.printRecord("CHANGED", changed.getExpected(), changed.getActual(), changed.getField());
    }
    // Log all deletions made to the API
    for (FieldComparisonFailure removed : results.getFieldMissing()) {
        printer.printRecord("REMOVED", removed.getExpected(), removed.getActual(), removed.getField());
    }

    printer.close();
    return FileUtils.readFileToString(file, StandardCharsets.UTF_8);
}

From source file:ca.twoducks.vor.ossindex.report.Assistant.java

/** Convert a CSV file back to a JSON config file.
 * //from w w  w . j  a  v a2  s  . com
 * @param file
 * @return
 * @throws IOException 
 */
private Configuration loadCsv(File file) throws IOException {
    Configuration config = new Configuration();
    Reader in = new FileReader(file);
    Iterable<CSVRecord> records = CSVFormat.EXCEL.withHeader().parse(in);
    for (CSVRecord record : records) {
        String path = record.get("Path");
        String state = record.get("State");
        if (!"UNASSIGNED".equals(state)) {
            String projectName = record.get("Project Name");
            // There could be 3 "projectUri" fields. In order:
            //   SCM,Project,Home
            //
            // SCM should always have a value, others *may*
            String[] projectUris = parseList(record.get("Project URI"));
            String scmUri = projectUris[0];
            String projectUri = null;
            String homeUri = null;
            if (projectUris.length > 1 && !projectUris[1].trim().isEmpty())
                projectUri = projectUris[1].trim();
            if (projectUris.length > 2 && !projectUris[2].trim().isEmpty())
                homeUri = projectUris[2].trim();

            String version = record.get("Version");
            String[] cpes = parseList(record.get("CPEs"));
            String[] projectLicenses = parseList(record.get("Project Licenses"));
            String fileLicense = record.get("File License");
            String projectDescription = record.get("Project Description");
            String digest = record.get("Digest");
            String comment = record.get("Comment");

            String overrideName = null;
            String overrideLicense = null;
            try {
                overrideName = record.get("Override Name");
            } catch (IllegalArgumentException e) {
            }
            try {
                overrideLicense = record.get("Override License");
            } catch (IllegalArgumentException e) {
            }

            // Override applicable fields
            if (overrideName != null && !overrideName.trim().isEmpty()) {
                projectName = overrideName;
                scmUri = null;
                projectUri = null;
                homeUri = null;
                version = null;
                cpes = new String[0];
                projectLicenses = new String[0];
                projectDescription = null;
                if (overrideLicense != null) {
                    projectLicenses = new String[] { overrideLicense };
                }
            }

            // Add the checksum to the file list
            FileConfig fileConfig = config.addFile(digest);

            if (path != null && !path.isEmpty()) {
                File aFile = new File(path);
                File parent = aFile.getParentFile();
                if (parent == null)
                    fileConfig.setName(path);
                else
                    fileConfig.setPath(path);
            }

            if (fileLicense != null && !fileLicense.isEmpty())
                fileConfig.setLicense(fileLicense);
            if (comment != null && !comment.isEmpty())
                fileConfig.setComment(comment);
            if (state != null && !state.isEmpty())
                fileConfig.setState(state);

            ProjectGroup group = config.getGroup(projectName);
            ProjectConfig project = group.getProject(scmUri, version);

            // If the project has not been defined yet then set its values
            if (project.getName() == null) {
                project.setName(projectName);
                if (projectUri != null)
                    project.setProjectUri(projectUri);
                if (homeUri != null)
                    project.setHomeUri(homeUri);

                if (cpes != null) {
                    for (String cpe : cpes) {
                        project.addCpe(cpe);
                    }
                }

                if (projectLicenses != null) {
                    for (String license : projectLicenses) {
                        project.addLicense(license);
                    }
                }

                if (projectDescription != null && !projectDescription.isEmpty())
                    project.setDescription(projectDescription);
            }

            // Add the file to the project
            project.addFile(fileConfig);
        }
    }
    return config;
}

From source file:com.wx3.galacdecks.Bootstrap.java

private void importCards(GameDatastore datastore, String path) throws IOException {
    Reader reader = new FileReader(path);
    CSVParser parser = new CSVParser(reader, CSVFormat.EXCEL.withHeader());
    int count = 0;
    for (CSVRecord record : parser) {
        String id = record.get("id");
        String name = record.get("name");
        String description = record.get("description");
        String flavor = record.get("flavor");
        String unitPrefab = record.get("unitPrefab");
        String summonEffect = record.get("summonEffect");
        String projectile = record.get("projectile");
        String portrait = record.get("portrait");
        Set<EntityTag> tags = new HashSet<EntityTag>();
        for (EntityTag tag : EntityTag.values()) {
            if (record.isSet(tag.toString())) {
                if (record.get(tag).equals("Y")) {
                    tags.add(tag);// w  w  w .j  a v a2  s.  c  o m
                }
            }
        }

        String[] types = record.get("types").split(",");
        for (String type : types) {
            if (type.length() > 0) {
                EntityTag tag = EntityTag.valueOf(type);
                tags.add(tag);
            }
        }

        Map<EntityStat, Integer> stats = new HashMap<EntityStat, Integer>();
        for (EntityStat stat : EntityStat.values()) {
            String statName = stat.toString();
            if (record.isSet(statName)) {
                stats.put(stat, parseIntOrZero(record.get(statName)));
            }
        }

        List<EntityRule> rules = new ArrayList<EntityRule>();
        String ruleField = record.get("rules");
        String[] ruleIds = ruleField.split(",");
        for (String ruleId : ruleIds) {
            if (ruleId.length() > 0) {
                if (!ruleCache.containsKey(ruleId)) {
                    throw new RuntimeException("Unable to find rule '" + ruleId + "'");
                }
                EntityRule rule = ruleCache.get(ruleId);
                rules.add(rule);
            }
        }

        ValidatorScript playValidator = null;
        String playValidatorId = record.get("playValidator");
        if (playValidatorId != null && !playValidatorId.isEmpty()) {
            if (!playValidatorCache.containsKey(playValidatorId)) {
                throw new RuntimeException("Unknown validator '" + playValidatorId + "'");
            }
            playValidator = playValidatorCache.get(playValidatorId);
        }

        ValidatorScript discardValidator = null;
        String discardValidatorId = record.get("discardValidator");
        if (discardValidatorId != null && !discardValidatorId.isEmpty()) {
            if (!discardValidatorCache.containsKey(discardValidatorId)) {
                throw new RuntimeException("Unknown validator '" + discardValidatorId + "'");
            }
            discardValidator = discardValidatorCache.get(discardValidatorId);
        }

        Set<AiHint> aiHints = new HashSet<>();
        String hintField = record.get("aiHints");
        String[] hintIds = hintField.split(",");
        for (String hintId : hintIds) {
            if (hintId.length() > 0) {
                if (!aiHintCache.containsKey(hintId)) {
                    throw new RuntimeException("Unable to find AI Hint '" + hintId + "'");
                }
                AiHint hint = aiHintCache.get(hintId);
                aiHints.add(hint);
            }
        }

        EntityPrototype card = EntityPrototype.createPrototype(id, name, description, flavor, unitPrefab,
                summonEffect, projectile, portrait, tags, rules, playValidator, discardValidator, stats,
                aiHints);
        datastore.createPrototype(card);
        if (cardCache.containsKey(card.getId())) {
            throw new RuntimeException("Duplicate card id: " + card.getId());
        }
        cardCache.put(card.getId(), card);
        ++count;
    }
    logger.info("Imported " + count + " cards");
    parser.close();
}

From source file:com.miovision.oss.awsbillingtools.parser.DetailedLineItemTest.java

private DetailedLineItem givenDetailedLineItemWithTags() {
    try {/*from   w ww  .  ja v  a  2  s  .  co  m*/
        CSVParser csvParser = CSVFormat.EXCEL.parse(new StringReader(TEST_RECORD_WITH_TAGS));
        CSVRecord csvRecord = csvParser.getRecords().get(0);
        return givenDetailedLineItem(csvRecord, Arrays.asList("foo"));
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

From source file:com.miovision.oss.awsbillingtools.parser.DetailedLineItemTest.java

private DetailedLineItem givenDetailedLineItemWithoutTags() {
    try {//from   www.  j a  va 2  s.c om
        CSVParser csvParser = CSVFormat.EXCEL.parse(new StringReader(TEST_RECORD_WITHOUT_TAGS));
        CSVRecord csvRecord = csvParser.getRecords().get(0);
        return givenDetailedLineItem(csvRecord, new ArrayList<>(0));
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

From source file:com.wx3.galacdecks.Bootstrap.java

private void importSystems(GameDatastore datastore, String path) throws IOException {
    Reader reader = new FileReader(path);
    CSVParser parser = new CSVParser(reader, CSVFormat.EXCEL.withHeader());
    int count = 0;
    for (CSVRecord record : parser) {
        String id = record.get("id");
        String name = record.get("name");
        String description = record.get("description");
        String pvp = record.get("pvp");
        boolean usePlayerDecks = true;
        if (record.get("usePlayerDecks").toLowerCase().equals("n")) {
            usePlayerDecks = false;/*from  w  w  w.ja  v a2 s  .  c  om*/
        }

        String ruleField = record.get("rootRules");
        String[] ruleIds = ruleField.split(",");
        GameSystem system = new GameSystem();
        system.id = id;
        system.name = name;
        system.description = description;
        system.usePlayerDecks = usePlayerDecks;
        system.rootRules = new ArrayList<>(Arrays.asList(ruleIds));
        if (pvp.toUpperCase().equals("Y")) {
            system.pvp = true;
        } else {
            system.pvp = false;
        }
        datastore.createSystem(system);

        ++count;
    }
    logger.info("Imported " + count + " systems");
    parser.close();
}

From source file:entity.service.EntryFacadeREST.java

@POST
@Path("/processcsv/{raceid}")
@Consumes(MediaType.APPLICATION_FORM_URLENCODED)
@Produces("application/json; charset=UTF-8")
public Response processCSV(@FormParam("filename") String fileName, @PathParam("raceid") Integer raceid) {
    parameters.initClubs();/*from   w  w  w .j a  va 2s. co m*/
    List<String> invalidLicences = new ArrayList<>();
    invalidLicences.add("nev;rajtszam;licensz");
    try (Reader in = new InputStreamReader(
            new FileInputStream(appParameters.getProperty("uploadFolder") + fileName), "UTF-8")) {
        Iterable<CSVRecord> records = CSVFormat.EXCEL.withSkipHeaderRecord().withDelimiter(';')
                .withHeader(CSV_HEADERS).withNullString("").parse(in);
        int count = 0;
        for (CSVRecord record : records) {
            EntryData ed = new EntryData();
            ed.setPreentry(true);
            ed.setStatus("PRE");
            ed.setName(record.get("name"));
            ed.setRacenum(record.get("racenum"));
            ed.setGender(record.get("gender"));
            ed.setBirthYear(Short.valueOf(record.get("birthyear")));
            ed.setCategory(record.get("category"));
            ed.setFromTown(record.get("fromtown"));
            ed.setClubName(record.get("club"));
            ed.setAgegroup(record.get("agegroup"));
            ed.setPaid(record.get("paid").equals("IGEN"));
            ed.setRemainingpayment(
                    record.get("paid").matches("[1-9]\\d{0,10}") ? Integer.parseInt(record.get("paid")) : 0);
            ed.setLicencenum(record.get("licencenum"));
            if (ed.getLicencenum() != null && !ed.getLicencenum().isEmpty()) {
                if (!licenceFacade.exists(ed.getLicencenum())) {
                    invalidLicences.add(ed.getName() + ";" + ed.getRacenum() + ";" + ed.getLicencenum());
                }
            }
            insertEntry(ed, raceid);
            ++count;
        }
        String invalidLicencesFileName = "invalid_licences_" + fileName;
        Files.write(Paths.get(appParameters.getProperty("uploadFolder"), invalidLicencesFileName),
                invalidLicences, Charset.forName("UTF-8"));
        HashMap<String, Object> params = new HashMap<>();
        params.put("invalidLicences", invalidLicencesFileName);
        JsonObject jsonMsg = JsonBuilder.getJsonMsg(count + " db elnevezs beolvasva!",
                JsonBuilder.MsgType.INFO, params);
        return Response.ok(String.valueOf(jsonMsg)).build();
    } catch (FileNotFoundException ex) {
        HashMap<String, Object> params = new HashMap<>();
        params.put("fileName", fileName);
        JsonObject jsonMsg = JsonBuilder.getJsonMsg("A megadott fjl nem tallhat!",
                JsonBuilder.MsgType.ERROR, params);
        return Response.status(500).entity(jsonMsg).build();
    } catch (IOException ex) {
        HashMap<String, Object> params = new HashMap<>();
        params.put("fileName", fileName);
        JsonObject jsonMsg = JsonBuilder.getJsonMsg("Hiba a CSV fjl beolvassa kzben!",
                JsonBuilder.MsgType.ERROR, params);
        return Response.status(500).entity(jsonMsg).build();
    }
}

From source file:ca.twoducks.vor.ossindex.report.Assistant.java

/** Export the configuration data into a CSV file. The CSV file may not
 * contain complete information, but is much easier for a human to work with.
 * Code will be added to allow conversion from CSV back into the JSON format.
 * /*from   w w w  .  ja va  2  s . c  o  m*/
 * @param dir
 * @throws IOException 
 */
private void exportCsv(File dir) throws IOException {
    File file = new File(dir, "vorindex.csv");
    CSVFormat format = CSVFormat.EXCEL.withRecordSeparator("\n").withCommentMarker('#');
    FileWriter fout = new FileWriter(file);
    CSVPrinter csvOut = new CSVPrinter(fout, format);
    String[] header = { "Path", "State", "Project Name", "Project URI", "Version", "CPEs", "Project Licenses",
            "File License", "Project Description", "Digest", "Comment" };
    csvOut.printRecord((Object[]) header);

    try {
        config.exportCsv(csvOut, includeArtifacts, includeImages);
    } finally {
        fout.close();
        csvOut.close();
    }

}