When you want to export the user data in more readable and structured format what comes to your mind Excel file, going for Excel file is good option but have you thought about csv, it could be one of the best way to export data in more readable and structured format.
why? because as the name suggest CSV (comma separated values) it is a simple text file which is very easy store and import into spreadsheet hence giving us a more structured information.
there are multiple libraries available using which we can create CSV file, like super-csv, open-csv etc. and I prefer open-csv as it is one of the most easy to use library.
To work with open-csv we are going to need below dependency, version you can choose according to your requirement.
other than this we will be using other dependencies for the example application which I will be using in this blog. you can find example application in the GitHub repo provided at the bottom of this blog.
we will be using data of Student.java class object to store in csv file, Student class is also an entity class.
and CsvGeneratorRestController rest endpoints to send the csv file as response.
Before we go further you must be having a question why I’m using StringWriter instead of FileWriter and file to store the data, well the reason is simple because I don’t want to save the csv file which I’m going to generate in my directory but instead I want to directly send it as a response.
Now let’s create a utility class which contains the static method to create our csv file for list of student objects.
What we will be discussing is more possible scenarios for creating csv.
Creating CSV | mapping bean to csv.
Mapping Bean to CSV with the values in order of columns alphabetical order.
what do I mean by bean’s column name alphabetical order? you will understand it through example.
now to map bean to csv we are using StatefulBeanToCsvBuilder with properties like separator, quote char, header strategy etc. and lastly writing the list of students to StringWriter though StatefulBeanToCsv.
header strategy will decide how your data i.e student class fields will be mapped in csv . Other things you can customise like separator, qoute char etc.
Let’s see how our api work for above csv generation code, for that I have already inserted some student record beforehand.
That’s great it seems to be working fine, but wait did you observe the response carefully the order in which the columns are arranged is not what we have specified in the student class and name of the columns does not seems what we want.
this one of the limitation with above approach that the data coming in csv has the column order of it’s alphabetical order, how should we solve this issue.
Let’s solve the both issue with column order and custom column name, but we will first start with custom column name.
Custom Column Name
it’s pretty easy to solve this issue, you just need to add one annotation on the top of your fields i.e variables of Student class which is
@CsvBindByName(column = "<customName>") and make sure you mapping strategy is HeaderColumnNameMappingStrategy
now let’s try again
greate, as you can see column names are according to what we specified but still we need to arrange proper order for column.
Custom Column Order
to arrange the column in order you want there are are few changes we need to make as the column arrangement is decided by MappingStrategy so we need to change the mapping strategy for column position.
MappingStrategy columnStrategy = new
and at the same time we need to add one more annotation on the top of student class fields which is
@CsvBindByPosition(position = 1) and remove the one used for custom column name, don’t worry I will tell you why we removed it.
we can specify the order number in position attribute.
let’s try to make a call and see the results.
yup, that’s great we are getting order of column as we specified in our Student class, but then what about the header column names can’t we have both of them at the same time by using the both annotation for custom name and custom position?.
Can we have both custom column name and column position?
should it not be possible to use both annotations for custom column name and custom column position at the time to solve this issue?
well that’s not how it works, as you can see with above examples to work with custom column name and custom column order I have used two different MappingStrategy, it means you can have one at time i.e. either custom column name or custom column.
Ahhhhhhh why it’s so complicated isn’t there a way to get both?
there is a way to have both i.e by using CustomMappingStrategy
Custom Mapping Strategy
if we are writing our own custom mapping strategy we can decide on column order as well as the column name at the same time, we just need extend our class with ColumnPositionMappingStrategy and override the generateHeader(T bean) method to achieve our goal .
let’s understand this in more detailed by looking at how to get custom column name in different ways.
- hardcoding the column names
simplest way to have column name with column order is to hardcode the column names in CustomMapping.
2. using reflection api to get column name to have dynamic column name retrieval
but you cannot use the custom name for columns
3. custom code to have custom order as well as custom column name
here you need to user both @CsvBindByName and @BindCsvByPosition on your student class fields.
here the complexity of code will significantly increase, so think before using.
here I’m using full code for your better understanding.
Why things are so complicated ? don’t we have much simpler way?
well there is a much simpler way to process our data to csv, but there is a condition and i.e. first we need to convert the class object fields to String and then write to CSVWriter.
you can follow below code for that.
Let’s try to run above code
works perfectly fine.
I hope it helps, find the below GitHub repo for example application code.