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

Avatar

Avatar
Boost 5
Level 2
keerthana_hn
Level 2

Likes

6 likes

Total Posts

32 posts

Correct reply

0 solutions
Top badges earned
Boost 5
Boost 3
Boost 1
Validate 1
View profile

Avatar
Boost 5
Level 2
keerthana_hn
Level 2

Likes

6 likes

Total Posts

32 posts

Correct reply

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

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
Applaud 25
Level 10
asutosh_jena
Level 10

Likes

593 likes

Total Posts

705 posts

Correct reply

207 solutions
Top badges earned
Applaud 25
Give Back 100
Boost 500
Affirm 100
Ignite 1
View profile

Avatar
Applaud 25
Level 10
asutosh_jena
Level 10

Likes

593 likes

Total Posts

705 posts

Correct reply

207 solutions
Top badges earned
Applaud 25
Give Back 100
Boost 500
Affirm 100
Ignite 1
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

645 likes

Total Posts

607 posts

Correct reply

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

Avatar
Establish
MVP
BrianKasingli
MVP

Likes

645 likes

Total Posts

607 posts

Correct reply

239 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");

  }
}