Writing Rest API to Export Data as CSV File - Jersey

The Problem
Sometimes we need support export data as CSV file, so people can download it and open it in excel and do some analysis.

The Data
The data we want to export it as below:
public class SurveyAnswers{
    public String surveyId;
    // some other meta data about survey, user
    public List<QuestionAnswer> answers;
}
// user's choice for this question
public class QuestionAnswer {
    public int questionId;
    public int optionId;
    // other meta data about the question and the option
}

The CSV file would be like this:
SurveyId, dataTime, /*fields about user meta data */, QuestionId1, AnswerId1, QuestionId2, AnswerId2...

The difficult part is that field headers are not fixed due to the list of QuestionAnswer.

Choose CSV Library
I choose Super-CSV, as we want to use its CsvMapWriter. We will convert the data to a hashmap, which contains fields like: QuestionId1, AnswerId1, QuestionId2, AnswerId2 etc.

Check more about superCSV at Writing CSV files

//Update
It's better to use CsvDozerBeanReader for our use case.
https://super-csv.github.io/super-csv/examples_dozer.html

CsvBeanWritable Interface
In most cases, we are going to implement this interface.
/**
 * Check: http://stackoverflow.com/questions/21942042/using-supercsv-to-change-header-values
 *
 */
public interface CsvBeanWritable {
    /**
     * Header in csv file - it can be anything such as 

     * new String[] { "First Name", "Last Name", "Birthday"};
     */
    @JsonIgnore
    public String[] getCsvHeader();
    /**
     * The mapping of bean field to cvv field - order matters

     * new String[] { "firstName", "lastName", "birthDate"};
     */
    @JsonIgnore
    public String[] getCsvMapping();
}

CsvMapWritable Interface
When logic is complex like in our case, we can't use CsvBeanWriter, we will implement CsvMapWritable interface.
CsvMessageBodyWriter will use superCSV CsvMapWriter to write the object as csv data.
public interface CsvMapWritable {
    @JsonIgnore
    public Map<String, Object> getCsvBody();
    @JsonIgnore
    public String[] getCsvHeader();
}
CsvMessageBodyWriter - the Provider
CsvMessageBodyWriter  will marshall object as csv data if the object implements CsvBeanWritable or CsvMapWritable, of the object is a collection of CsvBeanWritable or CsvMapWritable.

We need register it in ResourceConfig or tell jersey to scan the package to find it.
@Component
@Provider
@Produces({CsvMessageBodyWriter.TEXT_CSV, CsvMessageBodyWriter.APPLICATION_EXCEL})
public class CsvMessageBodyWriter<T> implements MessageBodyWriter<T> {
    public static final String TEXT_CSV = "text/csv";
    public static final String APPLICATION_EXCEL = "application/vnd.ms-excel";

    @Override
    public boolean isWriteable(final Class<?> type, final Type genericType, final Annotation[] annotations,
            final MediaType mediaType) {
        return true;
    }
    @Override
    public long getSize(final T data, final Class<?> type, final Type genericType, final Annotation annotations[],
            final MediaType mediaType) {
        return -1;
    }
    @Override
    public void writeTo(final T data, final Class<?> type, final Type genericType, final Annotation[] annotations,
            final MediaType mediaType, final MultivaluedMap<String, Object> httpHeaders,
            final OutputStream entityStream) throws java.io.IOException, javax.ws.rs.WebApplicationException {
        try (AbstractCsvWriter csvWriter = getCsvWriter(data, entityStream)) {
            writeDate(data, csvWriter);
        }
    }
    private AbstractCsvWriter getCsvWriter(final T data, final OutputStream entityStream) {
        AbstractCsvWriter csvWriter = null;
        if (data instanceof CsvBeanWritable) {
            csvWriter = new CsvBeanWriter(new OutputStreamWriter(entityStream), CsvPreference.STANDARD_PREFERENCE);
        } else if (data instanceof CsvMapWritable) {
            csvWriter = new CsvMapWriter(new OutputStreamWriter(entityStream), CsvPreference.STANDARD_PREFERENCE);
        } else if (data instanceof Collection) {
            final Collection<?> collection = (Collection<?>) data;
            csvWriter = getCsvWritterFromCollection(collection, entityStream);
        }
        return csvWriter;
    }


    protected void writeDate(final T data, final AbstractCsvWriter csvWriter) throws IOException {
        if (data instanceof CsvBeanWritable) {
            final CsvBeanWritable writable = (CsvBeanWritable) data;
            csvWriter.writeHeader(writable.getCsvHeader());
            ((CsvBeanWriter) csvWriter).write(writable, writable.getCsvMapping());
        } else if (data instanceof CsvMapWritable) {
            final CsvMapWritable writable = (CsvMapWritable) data;
            csvWriter.writeHeader(writable.getCsvHeader());
            ((CsvMapWriter) csvWriter).write(writable.getCsvBody(), writable.getCsvHeader());
        } else if (data instanceof Collection) {
            writeCollection(data, csvWriter);
        } else {
            throw new XXException("doesn't support download as csv");
        }
    }

    protected void writeCollection(final T data, final AbstractCsvWriter csvWriter) throws IOException {
        final Collection<?> collection = (Collection<?>) data;
        boolean first = true;
        final Iterator<?> it = collection.iterator();
        while (it.hasNext()) {
            final Object obj = it.next();
            if (CsvBeanWritable.class.isAssignableFrom(obj.getClass())) {
                final CsvBeanWritable writable = (CsvBeanWritable) obj;
                if (first) {
                    csvWriter.writeHeader(writable.getCsvHeader());
                    first = false;
                }
                ((CsvBeanWriter) csvWriter).write(writable, writable.getCsvMapping());
            } else if (CsvMapWritable.class.isAssignableFrom(obj.getClass())) {
                final CsvMapWritable writable = (CsvMapWritable) obj;
                if (first) {
                    csvWriter.writeHeader(writable.getCsvHeader());
                    first = false;
                }
                ((CsvMapWriter) csvWriter).write(writable.getCsvBody(), writable.getCsvHeader());
            } else {
                throw new XXException("doesn't support download as csv");
            }
        }
    }
    protected static AbstractCsvWriter getCsvWritterFromCollection(final Collection<?> collection,
            final OutputStream entityStream) {
        AbstractCsvWriter csvWriter = null;
        final Iterator<?> it = collection.iterator();
        while (it.hasNext()) {
            final Object obj = it.next();
            if (CsvMapWritable.class.isAssignableFrom(obj.getClass())) {
                csvWriter = new CsvMapWriter(new OutputStreamWriter(entityStream), CsvPreference.STANDARD_PREFERENCE);
            } else if (CsvBeanWritable.class.isAssignableFrom(obj.getClass())) {
                csvWriter = new CsvBeanWriter(new OutputStreamWriter(entityStream), CsvPreference.STANDARD_PREFERENCE);
            }
        }
        return csvWriter;
    }
}
Add CSV ability to API
 * @param downloadAsFile: downloadAsFile=false, it seems not work in chrome, only work in safari,
 *        not try safari.
@GET
@Produces({MediaType.APPLICATION_JSON, CsvMessageBodyWriter.TEXT_CSV})
public Set getFlatSurveyChoiceResponse(@QueryParam("surveyId") final String surveyId,
        @QueryParam("downloadAsFile") @DefaultValue("true") final boolean downloadAsFile) {
    if (downloadAsFile) {
        servletResponse.addHeader("Content-Disposition", MessageFormat.format("attachment; filename={0}.csv",
                DateUtil.getThreadLocalDateFormat().format(new Date())));
    }
    return service.getSurveyAnswers(surveyId);
}
Using CsvMapWritable in our SurveyAnswers example

Create csv headers for this survey and add it into SurveyHeadersHolder.
for (final Integer questionId : questions) {
    extraHeaders.add(getQuestionIDHeader(questionId));
    extraHeaders.add(getQuestionTitleHeader(questionId));
    extraHeaders.add(getOptionIDHeader(questionId));
    extraHeaders.add(getOptionTextHeader(questionId));
}
SurveyHeadersHolder.INSTANCE.addSurveyHeaders(surveyId, extraHeaders);

Implementing CsvMapWritable in SurveyAnswers
@Override
public Map<String, Object> getCsvBody() {
    final Map<String, Object> map = new LinkedHashMap<>();
    map.put(HEADER_SURVEY_ID, surveyId);
    map.put(HEADER_DATE, DateUtil.getThreadLocalDateFormat().format(date));
    for (final SurveyAnswers answer : answers) {
        map.put(getQuestionIDHeader(answer.getQuestionId()), answer.getQuestionId());
        map.put(getQuestionTitleHeader(answer.getQuestionId()), answer.getQuestionText());
        map.put(getOptionIDHeader(answer.getQuestionId()), answer.getOptionId());
        map.put(getOptionTextHeader(answer.getQuestionId()), answer.getOptionText());
    }
    return map;
}

@Override
public String[] getCsvHeader() {
    return SurveyHeadersHolder.INSTANCE.getSurveyHeaders(surveyId);
}
Post a Comment

Labels

Java (159) Lucene-Solr (110) All (60) Interview (59) J2SE (53) Algorithm (37) Eclipse (35) Soft Skills (35) Code Example (31) Linux (26) JavaScript (23) Spring (22) Windows (22) Web Development (20) Tools (19) Nutch2 (18) Bugs (17) Debug (15) Defects (14) Text Mining (14) J2EE (13) Network (13) PowerShell (11) Chrome (9) Continuous Integration (9) How to (9) Learning code (9) Performance (9) UIMA (9) html (9) Design (8) Dynamic Languages (8) Http Client (8) Maven (8) Security (8) Trouble Shooting (8) bat (8) blogger (8) Big Data (7) Google (7) Guava (7) JSON (7) Problem Solving (7) ANT (6) Coding Skills (6) Database (6) Scala (6) Shell (6) css (6) Algorithm Series (5) Cache (5) IDE (5) Lesson Learned (5) Miscs (5) Programmer Skills (5) System Design (5) Tips (5) adsense (5) xml (5) AIX (4) Code Quality (4) GAE (4) Git (4) Good Programming Practices (4) Jackson (4) Memory Usage (4) OpenNLP (4) Project Managment (4) Python (4) Spark (4) Testing (4) ads (4) regular-expression (4) Android (3) Apache Spark (3) Become a Better You (3) Concurrency (3) Eclipse RCP (3) English (3) Firefox (3) Happy Hacking (3) IBM (3) J2SE Knowledge Series (3) JAX-RS (3) Jetty (3) Restful Web Service (3) Script (3) regex (3) seo (3) .Net (2) Android Studio (2) Apache (2) Apache Procrun (2) Architecture (2) Batch (2) Build (2) Building Scalable Web Sites (2) C# (2) C/C++ (2) CSV (2) Career (2) Cassandra (2) Distributed (2) Fiddler (2) Google Drive (2) Gson (2) Html Parser (2) Http (2) Image Tools (2) JQuery (2) Jersey (2) LDAP (2) Life (2) Logging (2) Software Issues (2) Storage (2) Text Search (2) xml parser (2) AOP (1) Application Design (1) AspectJ (1) Bit Operation (1) Chrome DevTools (1) Cloud (1) Codility (1) Data Mining (1) Data Structure (1) ExceptionUtils (1) Exif (1) Feature Request (1) FindBugs (1) Greasemonkey (1) HTML5 (1) Httpd (1) I18N (1) IBM Java Thread Dump Analyzer (1) JDK Source Code (1) JDK8 (1) JMX (1) Lazy Developer (1) Mac (1) Machine Learning (1) Mobile (1) My Plan for 2010 (1) Netbeans (1) Notes (1) Operating System (1) Perl (1) Problems (1) Product Architecture (1) Programming Life (1) Quality (1) Redhat (1) Redis (1) Review (1) RxJava (1) Solutions logs (1) Team Management (1) Thread Dump Analyzer (1) Troubleshooting (1) Visualization (1) boilerpipe (1) htm (1) ongoing (1) procrun (1) rss (1)

Popular Posts