OpenCSV: How to create CSV file from POJO with custom column headers and custom column positions?

I have created a MappingsBean class where all the columns of the CSV file are specified. Next I parse XML files and create a list of mappingbeans. Then I write that data into CSV file as report.

I am using following annotations:

public class MappingsBean {

    @CsvBindByName(column = "TradeID")
    @CsvBindByPosition(position = 0)
    private String tradeId;

    @CsvBindByName(column = "GWML GUID", required = true)
    @CsvBindByPosition(position = 1)
    private String gwmlGUID;

    @CsvBindByName(column = "MXML GUID", required = true)
    @CsvBindByPosition(position = 2)
    private String mxmlGUID;

    @CsvBindByName(column = "GWML File")
    @CsvBindByPosition(position = 3)
    private String gwmlFile;

    @CsvBindByName(column = "MxML File")
    @CsvBindByPosition(position = 4)
    private String mxmlFile;

    @CsvBindByName(column = "MxML Counterparty")
    @CsvBindByPosition(position = 5)
    private String mxmlCounterParty;

    @CsvBindByName(column = "GWML Counterparty")
    @CsvBindByPosition(position = 6)
    private String gwmlCounterParty;
}

And then I use StatefulBeanToCsv class to write into CSV file:

File reportFile = new File(reportOutputDir + "/" + REPORT_FILENAME);
Writer writer = new PrintWriter(reportFile);
StatefulBeanToCsv<MappingsBean> beanToCsv = new 
                              StatefulBeanToCsvBuilder(writer).build();
beanToCsv.write(makeFinalMappingBeanList());
writer.close();

The problem with this approach is that if I use @CsvBindByPosition(position = 0) to control position then I am not able to generate column names. If I use @CsvBindByName(column = "TradeID") then I am not able to set position of the columns.

Is there a way where I can use both annotations, so that I can create CSV files with column headers and also control column position?

Regards, Vikram Pathania

Solution 1:

I've had similar problem. AFAIK there is no build-in functionality in OpenCSV that will allow to write bean to CSV with custom column names and ordering.

There are two main MappingStrategyies that are available in OpenCSV out of the box:

• HeaderColumnNameMappingStrategy: that allows to map CVS file columns to bean fields based on custom name; when writing bean to CSV this allows to change column header name but we have no control on column order
• ColumnPositionMappingStrategy: that allows to map CSV file columns to bean fields based on column ordering; when writing bean to CSV we can control column order but we get an empty header (implementation returns new String[0] as a header)

The only way I found to achieve both custom column names and ordering is to write your custom MappingStrategy.

First solution: fast and easy but hardcoded

Create custom MappingStrategy:

class CustomMappingStrategy<T> extends ColumnPositionMappingStrategy<T> {
    private static final String[] HEADER = new String[]{"TradeID", "GWML GUID", "MXML GUID", "GWML File", "MxML File", "MxML Counterparty", "GWML Counterparty"};

    @Override
    public String[] generateHeader() {
        return HEADER;
    }
}

And use it in StatefulBeanToCsvBuilder:

final CustomMappingStrategy<MappingsBean> mappingStrategy = new CustomMappingStrategy<>();
mappingStrategy.setType(MappingsBean.class);

final StatefulBeanToCsv<MappingsBean> beanToCsv = new StatefulBeanToCsvBuilder<MappingsBean>(writer)
    .withMappingStrategy(mappingStrategy)
    .build();
beanToCsv.write(makeFinalMappingBeanList());
writer.close()

In MappingsBean class we left CsvBindByPosition annotations - to control ordering (in this solution CsvBindByName annotations are not needed). Thanks to custom mapping strategy the header column names are included in resulting CSV file.

The downside of this solution is that when we change column ordering through CsvBindByPosition annotation we have to manually change also HEADER constant in our custom mapping strategy.

Second solution: more flexible

The first solution works, but it was not good for me. Based on build-in implementations of MappingStrategy I came up with yet another implementation:

class CustomMappingStrategy<T> extends ColumnPositionMappingStrategy<T> {
    @Override
    public String[] generateHeader() {
        final int numColumns = findMaxFieldIndex();
        if (!isAnnotationDriven() || numColumns == -1) {
            return super.generateHeader();
        }

        header = new String[numColumns + 1];

        BeanField beanField;
        for (int i = 0; i <= numColumns; i++) {
            beanField = findField(i);
            String columnHeaderName = extractHeaderName(beanField);
            header[i] = columnHeaderName;
        }
        return header;
    }

    private String extractHeaderName(final BeanField beanField) {
        if (beanField == null || beanField.getField() == null || beanField.getField().getDeclaredAnnotationsByType(CsvBindByName.class).length == 0) {
            return StringUtils.EMPTY;
        }

        final CsvBindByName bindByNameAnnotation = beanField.getField().getDeclaredAnnotationsByType(CsvBindByName.class)[0];
        return bindByNameAnnotation.column();
    }
}

You can use this custom strategy in StatefulBeanToCsvBuilder exactly this same as in the first solution (remember to invoke mappingStrategy.setType(MappingsBean.class);, otherwise this solution will not work).

Currently our MappingsBean has to contain both CsvBindByName and CsvBindByPosition annotations. The first to give header column name and the second to create ordering of columns in the output CSV header. Now if we change (using annotations) either column name or ordering in MappingsBean class - that change will be reflected in output CSV file.

Solution 2:

Corrected above answer to match with newer version.

package csvpojo;

import org.apache.commons.lang3.StringUtils;

import com.opencsv.bean.BeanField;
import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.CsvBindByName;
import com.opencsv.exceptions.CsvRequiredFieldEmptyException;

class CustomMappingStrategy<T> extends ColumnPositionMappingStrategy<T> {
    @Override
    public String[] generateHeader(T bean) throws CsvRequiredFieldEmptyException {

super.setColumnMapping(new String[ FieldUtils.getAllFields(bean.getClass()).length]);
        final int numColumns = findMaxFieldIndex();
        if (!isAnnotationDriven() || numColumns == -1) {
            return super.generateHeader(bean);
        }

        String[] header = new String[numColumns + 1];

        BeanField<T> beanField;
        for (int i = 0; i <= numColumns; i++) {
            beanField = findField(i);
            String columnHeaderName = extractHeaderName(beanField);
            header[i] = columnHeaderName;
        }
        return header;
    }

    private String extractHeaderName(final BeanField<T> beanField) {
        if (beanField == null || beanField.getField() == null
                || beanField.getField().getDeclaredAnnotationsByType(CsvBindByName.class).length == 0) {
            return StringUtils.EMPTY;
        }

        final CsvBindByName bindByNameAnnotation = beanField.getField()
                .getDeclaredAnnotationsByType(CsvBindByName.class)[0];
        return bindByNameAnnotation.column();
    }
}

Then call this to generate CSV. I have used Visitors as my POJO to populate, update wherever necessary.

        CustomMappingStrategy<Visitors> mappingStrategy = new CustomMappingStrategy<>();
        mappingStrategy.setType(Visitors.class);
        // writing sample
        List<Visitors> beans2 = new ArrayList<Visitors>();

        Visitors v = new Visitors();
        v.set_1_firstName(" test1");
        v.set_2_lastName("lastname1");
        v.set_3_visitsToWebsite("876");
        beans2.add(v);

        v = new Visitors();
        v.set_1_firstName(" firstsample2");
        v.set_2_lastName("lastname2");
        v.set_3_visitsToWebsite("777");
        beans2.add(v);

        Writer writer = new FileWriter("G://output.csv");
        StatefulBeanToCsv<Visitors> beanToCsv = new StatefulBeanToCsvBuilder<Visitors>(writer)
                .withMappingStrategy(mappingStrategy).withSeparator(',').withApplyQuotesToAll(false).build();
        beanToCsv.write(beans2);
        writer.close();

My bean annotations looks like this

 @CsvBindByName (column = "First Name", required = true)
 @CsvBindByPosition(position=1)
 private String firstName;


 @CsvBindByName (column = "Last Name", required = true)
 @CsvBindByPosition(position=0)
 private String lastName;

Solution 3:

I wanted to achieve bi-directional import/export - to be able to import generated CSV back to POJO and visa versa.

I was not able to use @CsvBindByPosition for this, because in this case - ColumnPositionMappingStrategy was selected automatically. Per documents: this strategy requires that the file does NOT have a header.

What I've used to achieve the goal:

HeaderColumnNameMappingStrategy
mappingStrategy.setColumnOrderOnWrite(Comparator<String> writeOrder)

CsvUtils to read/write csv

import com.opencsv.CSVWriter;
import com.opencsv.bean.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.List;

public class CsvUtils {
    private CsvUtils() {
    }

    public static <T> String convertToCsv(List<T> entitiesList, MappingStrategy<T> mappingStrategy) throws Exception {
        try (Writer writer = new StringWriter()) {
            StatefulBeanToCsv<T> beanToCsv = new StatefulBeanToCsvBuilder<T>(writer)
                    .withMappingStrategy(mappingStrategy)
                    .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
                    .build();
            beanToCsv.write(entitiesList);
            return writer.toString();
        }
    }

    @SuppressWarnings("unchecked")
    public static <T> List<T> convertFromCsv(MultipartFile file, Class clazz) throws IOException {
        try (Reader reader = new BufferedReader(new InputStreamReader(file.getInputStream()))) {
            CsvToBean<T> csvToBean = new CsvToBeanBuilder<T>(reader).withType(clazz).build();
            return csvToBean.parse();
        }
    }
}

POJO for import/export

public class LocalBusinessTrainingPairDTO {
    //this is used for CSV columns ordering on exporting LocalBusinessTrainingPairs
    public static final String[] FIELDS_ORDER = {"leftId", "leftName", "rightId", "rightName"};

    @CsvBindByName(column = "leftId")
    private int leftId;

    @CsvBindByName(column = "leftName")
    private String leftName;

    @CsvBindByName(column = "rightId")
    private int rightId;

    @CsvBindByName(column = "rightName")
    private String rightName;
    // getters/setters omitted, do not forget to add them
}

Custom comparator for predefined String ordering:

public class OrderedComparatorIgnoringCase implements Comparator<String> {
    private List<String> predefinedOrder;

    public OrderedComparatorIgnoringCase(String[] predefinedOrder) {
        this.predefinedOrder = new ArrayList<>();
        for (String item : predefinedOrder) {
            this.predefinedOrder.add(item.toLowerCase());
        }
    }

    @Override
    public int compare(String o1, String o2) {
        return predefinedOrder.indexOf(o1.toLowerCase()) - predefinedOrder.indexOf(o2.toLowerCase());
    }
}

Ordered writing for POJO (answer to initial question)

public static void main(String[] args) throws Exception {
     List<LocalBusinessTrainingPairDTO> localBusinessTrainingPairsDTO = new ArrayList<>();
     LocalBusinessTrainingPairDTO localBusinessTrainingPairDTO = new LocalBusinessTrainingPairDTO();
     localBusinessTrainingPairDTO.setLeftId(1);
     localBusinessTrainingPairDTO.setLeftName("leftName");
     localBusinessTrainingPairDTO.setRightId(2);
     localBusinessTrainingPairDTO.setRightName("rightName");

     localBusinessTrainingPairsDTO.add(localBusinessTrainingPairDTO);

     //Creating HeaderColumnNameMappingStrategy
     HeaderColumnNameMappingStrategy<LocalBusinessTrainingPairDTO> mappingStrategy = new HeaderColumnNameMappingStrategy<>();
     mappingStrategy.setType(LocalBusinessTrainingPairDTO.class);
     //Setting predefined order using String comparator
     mappingStrategy.setColumnOrderOnWrite(new OrderedComparatorIgnoringCase(LocalBusinessTrainingPairDTO.FIELDS_ORDER));
     String csv = convertToCsv(localBusinessTrainingPairsDTO, mappingStrategy);
     System.out.println(csv);
}

Read exported CSV back to POJO (addition to original answer)

Important: CSV can be unordered, as we are still using binding by name:

public static void main(String[] args) throws Exception {
    //omitted code from writing
    String csv = convertToCsv(localBusinessTrainingPairsDTO, mappingStrategy);

    //Exported CSV should be compatible for further import
    File temp = File.createTempFile("tempTrainingPairs", ".csv");
    temp.deleteOnExit();
    BufferedWriter bw = new BufferedWriter(new FileWriter(temp));
    bw.write(csv);
    bw.close();
    MultipartFile multipartFile = new MockMultipartFile("tempTrainingPairs.csv", new FileInputStream(temp));

    List<LocalBusinessTrainingPairDTO> localBusinessTrainingPairDTOList = convertFromCsv(multipartFile, LocalBusinessTrainingPairDTO.class);
}

To conclude:

1. We can read CSV to POJO, regardless of column order - because we are using @CsvBindByName
2. We can control columns order on write using custom comparator