Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn More

View all

Sign in to view all badges

Reference code to create a csv file and place in it given path.

Avatar

Avatar
Boost 1
Level 1
keerthana_hn
Level 1

Like

1 like

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
Validate 1
View profile

Avatar
Boost 1
Level 1
keerthana_hn
Level 1

Like

1 like

Total Posts

6 posts

Correct Reply

0 solutions
Top badges earned
Boost 1
Validate 1
View profile
keerthana_hn
Level 1

01-05-2021

Hi all,

 

I just want to create a .csv file programmatically, please share a reference code if anyone has it. 

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Affirm 100
Level 10
asutosh_jena
Level 10

Likes

367 likes

Total Posts

462 posts

Correct Reply

134 solutions
Top badges earned
Affirm 100
Ignite 1
Establish
Give Back 50
Give Back 5
View profile

Avatar
Affirm 100
Level 10
asutosh_jena
Level 10

Likes

367 likes

Total Posts

462 posts

Correct Reply

134 solutions
Top badges earned
Affirm 100
Ignite 1
Establish
Give Back 50
Give Back 5
View profile
asutosh_jena
Level 10

01-05-2021

Hi @keerthana_hn 

 

I have done something like reading all the page title, name and URL and creating a CSV out of it. Hope the below code will give idea.

I have done this using Open CSV API.

 

Please add the below dependency in POM:

<!-- Open CSV Dependencies -->
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>4.1</version>
</dependency>

 

try (ResourceResolver resourceResolver = JcrUtilService.getResourceResolver()) {
PageManager pageManager = resourceResolver.adaptTo(PageManager.class);

AtomicInteger validPageCount = new AtomicInteger();

List<String[]> linkData = new ArrayList<>();
linkData.add(new String[]{URL_HEADING, PAGE_NAME_HEADING, PAGE_TITLE_HEADING});

for (String rootPagePath : brandRootPagePath) {
String brandName = StringUtils.substringBetween(rootPagePath, "/content/project/", "/en_us");

Page rootPage = pageManager != null ? pageManager.getContainingPage(rootPagePath) : null;

SiteMapUtil siteMapUtil = new SiteMapUtil(rootPage);
List<SiteMapUtil.Link> siteMapLinks = siteMapUtil.getLinks();

siteMapLinks.forEach(link -> {
String currentPagePath = link.getPath();
String currentPagePathJcrContent = new StringBuilder(currentPagePath).append(ServiceConstants.SLASH).append(JcrConstants.JCR_CONTENT).toString();
Resource currentPageResource = resourceResolver.getResource(currentPagePathJcrContent);
Page containingPage = pageManager.getContainingPage(currentPageResource);
ValueMap valueMap = containingPage.getProperties();

boolean isValidPage = getValidPage(currentPageResource);

Resource resource = resourceResolver.getResource(new StringBuilder(currentPagePathJcrContent).append(ServiceConstants.SLASH).append(ROOT_NODE).toString());
if ((isValidPage || (resource != null && resource.hasChildren())) && !StringUtils.equalsIgnoreCase(rootPagePath, currentPagePath)
&& !(StringUtils.endsWithIgnoreCase(currentPagePath, new StringBuilder(ServiceConstants.SLASH).append(EXCLUDE_ERRORS_PATH).toString())
|| StringUtils.endsWithIgnoreCase(currentPagePath, new StringBuilder(ServiceConstants.SLASH).append(EXCLUDE_HCP_PATH).toString()))) {
String pageTitle = valueMap.get(PAGE_TITLE_JCR_PROPERTY, StringUtils.EMPTY);
String pageName = valueMap.get(JcrConstants.JCR_TITLE, StringUtils.EMPTY);
pageTitle = StringUtils.isNotBlank(pageTitle) ? pageTitle : pageName;
String pageURL = externalizer.externalLink(resourceResolver, brandName, String.format("%s.html", currentPagePath));

linkData.add(new String[]{pageURL, pageName, pageTitle});
validPageCount.incrementAndGet();
}
});
}

// Write Data to CSV and Download to local file system
return writeDataToCSV(validPageCount, linkData);
} catch (Exception e) {
log.error(":: Generate Report :: Exception -->", e);
}

 

private boolean getValidPage(Resource currentPageResource) {
boolean isValidPage = false;
if (currentPageResource != null && currentPageResource.hasChildren()) {
Iterable<Resource> iterable = currentPageResource.getChildren();
int size = Iterables.size(iterable);
List<String> resourceNameList = new ArrayList<>();
iterable.forEach(resource -> {
String resourceName = resource.getName();
resourceNameList.add(resourceName);
});
isValidPage = !resourceNameList.isEmpty() && resourceNameList.contains(ROOT_NODE) && size > 1;
}
return isValidPage;
}
private boolean writeDataToCSV(AtomicInteger validPageCount, List<String[]> linkData) throws IOException {
boolean generateSuccess = false;
if (validPageCount.get() > 0) {
File file = new File(tempReportPath, getReportFileName());
FileWriter fileWriter = new FileWriter(file);
CSVWriter csvWriter = new CSVWriter(fileWriter, ',', CSVWriter.DEFAULT_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END);
csvWriter.writeAll(linkData);
csvWriter.close();
generateSuccess = true;
}

return generateSuccess;
}
private static String getReportFileName() {
final String currentDate = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
return new StringBuilder("Report").append(currentDate).append(".csv").toString();

 

You can get the file from here and keep it wherever you want:

final String filePath = new StringBuilder(tempReportPath).append(getReportFileName()).toString();

 

Hope this helps!

Thanks 

Answers (1)

Answers (1)

Avatar

Avatar
Establish
MVP
BrianKasingli
MVP

Likes

582 likes

Total Posts

562 posts

Correct Reply

218 solutions
Top badges earned
Establish
Ignite 1
Give Back 5
Give Back 3
Give Back 10
View profile

Avatar
Establish
MVP
BrianKasingli
MVP

Likes

582 likes

Total Posts

562 posts

Correct Reply

218 solutions
Top badges earned
Establish
Ignite 1
Give Back 5
Give Back 3
Give Back 10
View profile
BrianKasingli
MVP

03-05-2021

@keerthana_hn,

Here is an example of a Sling Servlet in AEM exporting a csv file. The example code is borrowed from the ACS AEM Commons Bundle.

/*
 * #%L
 * ACS AEM Commons Bundle
 * %%
 * Copyright (C) 2017 Adobe
 * %%
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * #L%
 */
package com.adobe.acs.commons.reports.internal;

import java.io.IOException;
import java.io.Writer;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Optional;
import java.util.stream.Stream;

import javax.annotation.Nonnull;
import javax.servlet.Servlet;
import javax.servlet.ServletException;

import com.adobe.acs.commons.reports.api.ReportCellCSVExporter;
import com.adobe.acs.commons.reports.api.ReportException;
import com.adobe.acs.commons.reports.api.ReportExecutor;
import com.adobe.acs.commons.reports.api.ResultsPage;
import com.day.cq.commons.jcr.JcrConstants;
import com.day.text.csv.Csv;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.servlets.SlingSafeMethodsServlet;
import org.apache.sling.commons.classloader.DynamicClassLoaderManager;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Servlet for exporting the results of the report to CSV.
 */
@Component(service = { Servlet.class }, property = {
    "sling.servlet.resourceTypes=acs-commons/components/utilities/report-builder/report-page",
    "sling.servlet.selectors=report", "sling.servlet.extensions=csv", "sling.servlet.methods=GET" })
public class ReportCSVExportServlet extends SlingSafeMethodsServlet {

  private static final long serialVersionUID = 2794836639686938093L;
  private static final Logger log = LoggerFactory.getLogger(ReportCSVExportServlet.class);

  @Reference
  private transient DynamicClassLoaderManager dynamicClassLoaderManager;

  @Override
  protected void doGet(@Nonnull SlingHttpServletRequest request, @Nonnull SlingHttpServletResponse response)
      throws ServletException, IOException {
    log.trace("doGet");

    // set response parameters
    response.setContentType("text/csv;charset=UTF-8");
    response.setHeader("Content-disposition",
        "attachment; filename="
            + URLEncoder.encode(request.getResource().getValueMap().get(JcrConstants.JCR_TITLE, "report"), "UTF-8")
            + ".csv");

    Writer writer = null;
    try {
      writer = response.getWriter();

      // write the BOM to indicate this is a UTF-8 file
      writer.write("\uFEFF");

      // initialize the csv
      final Csv csv = new Csv();
      csv.writeInit(writer);

      // write the headers
      List<ReportCellCSVExporter> exporters = writeHeaders(request, csv);

      Resource configCtr = request.getResource().getChild("config");
      if (configCtr != null && configCtr.listChildren().hasNext()) {
        Iterator<Resource> children = configCtr.listChildren();
        while (children.hasNext()) {
          Resource config = children.next();
          if (config != null) {
            updateCSV(config, request, exporters, csv, writer);
            log.debug("Successfully export report with configuration: {}", config);
            break;
          } else {
            log.warn("Unable to export report for configuration: {}", config);
          }
        }
        csv.close();
      } else {
        throw new IOException("No configurations found for " + request.getResource());
      }
    } catch (ReportException e) {
      throw new ServletException("Exception extracting report to CSV", e);
    } finally {
      IOUtils.closeQuietly(writer);
    }
  }

  private List<ReportCellCSVExporter> writeHeaders(SlingHttpServletRequest request, final Csv csv) throws IOException {
    List<String> row = new ArrayList<>();
    List<ReportCellCSVExporter> exporters = new ArrayList<>();
    for (Resource column : request.getResource().getChild("columns").getChildren()) {
      String className = column.getValueMap().get("exporter", String.class);
      if (!StringUtils.isEmpty(className)) {
        try {
          log.debug("Finding ReportCellCSVExporter for {}", className);
          @SuppressWarnings({ "unchecked", "squid:S2658" }) // class name is from a trusted source
          Class<ReportCellCSVExporter> clazz = (Class<ReportCellCSVExporter>) Class.forName(className, true,
              dynamicClassLoaderManager.getDynamicClassLoader());
          ReportCellCSVExporter exporter = column.adaptTo(clazz);
          log.debug("Loaded ReportCellCSVExporter {}", exporter);
          if (exporter != null) {
            exporters.add(exporter);
            row.add(column.getValueMap().get("heading", String.class));
          } else {
            log.warn("Retrieved null ReportCellCSVExporter for {}", className);
          }
        } catch (Exception e) {
          log.warn("Unable to render column due to issue fetching ReportCellCSVExporter " + className, e);
        }
      }
    }
    csv.writeRow(row.toArray(new String[row.size()]));
    return exporters;
  }

  private void updateCSV(Resource config, SlingHttpServletRequest request, List<ReportCellCSVExporter> exporters,
      Csv csv, Writer writer) throws ReportException {
    Class<?> executorClass = ReportExecutorProvider.INSTANCE.getReportExecutor(dynamicClassLoaderManager, config);

    ReportExecutor executor = Optional.ofNullable(request.adaptTo(executorClass))
        .filter(model -> model instanceof ReportExecutor).map(model -> (ReportExecutor) model)
        .orElseThrow(() -> new ReportException("Failed to get report executor"));

    executor.setConfiguration(config);
    log.debug("Retrieved executor {}", executor);

    ResultsPage queryResult = executor.getAllResults();
    Stream<? extends Object> results = queryResult.getResults();
    log.debug("Retrieved {} results", queryResult.getResultSize());

    results.forEach(r -> {
      List<String> row = new ArrayList<>();
      try {
        for (ReportCellCSVExporter exporter : exporters) {
          row.add(exporter.getValue(r));
        }
        csv.writeRow(row.toArray(new String[row.size()]));
        writer.flush();
      } catch (Exception e) {
        log.warn("Exception writing row: " + row, e);
      }
    });

    log.debug("Results written successfully");

  }
}