How to create an Excel (.xlsx) file in Apache Camel?

Question

I am trying to create an Excel file and send it to the SFTP location using the Apache Camel. I can create a PSV from a Java object like this:

Java POJO:

 @CsvRecord(separator = ",", skipFirstLine = true, generateHeaderColumns = true)
public class IexPerson implements Serializable {

   private static final long serialVersionUID = 1234069326527342909L;

    @DataField(pos = 1, columnName = "Person Name")
    private String personName;
    @DataField(pos = 2, columnName = "Gender")
    private String gender;

  // other fields ...

and then I convert a list of IexPersons in the route:

DataFormat iexPersonFormat = new BindyCsvDataFormat(IexPerson.class);

from("direct-get-list-of-persons")
   .setHeader(Exchange.FILE_NAME).simple("Persons_${date:now:yyyyMMdd-HHmmssSSS}_${random(1000,10000000)}.csv")
   .marshal(iexPersonFormat)
   .to("file:///tmp?fileName=${header.CamelFileName}");

This is working fine, but now I need to create an Excel file from the same list and send it to another location. I didn't manage to get it to work. I didn't find anything on the internet that would help me.

Aleksandar Grujic · Accepted Answer · 2022-01-20 09:12:06Z

Posting here for future users who need help with the same topic. I was able to convert the Java Object to a working excel file by using the Apache POI library.

I created a service and inside it a method that converts an object to a file, which I called from my Camel route. This is well explained here.

Here is the code:

  // Create a blank Workbook
  try (Workbook workbook = new XSSFWorkbook()) {

  // Create a blank Sheet
  Sheet sheet = workbook.createSheet("IexPersons");

  // column names
  List <String> columns = new ArrayList<>();
  columns.add("Person Name");
  columns.add("Gender");

  Row headerRow = sheet.createRow(0);

  // Create columns/first row in a file
  for (int i = 0; i < columns.size(); i++) {
    Cell cell = headerRow.createCell(i);
    cell.setCellValue(columns.get(i));
  }

  int rowNum = 1;

  // iterate over the list of persons and for each person write its values to the excel row
  for (IexPerson iexPerson : getListOfPersons()) {
    Row row = sheet.createRow(rowNum++);

    // populate file with the values for each column
    row.createCell(0).setCellValue(iexPerson.getName());
    row.createCell(1).setCellValue(iexPerson.getGender());
  }

  // create file
  FileOutputStream out = new FileOutputStream(new File("iexpersons.xlsx"));

  // write data to file
  workbook.write(out);

  // close the output stream
  out.close();

} catch (IOException e) {
  e.printStackTrace();
}

burki · Accepted Answer · 2021-10-11 11:54:29Z

0

I usually use XSL transformations to create Excel files in the Microsoft Office XML format.

You can find an example XML for an Excel spreadsheet on the linked Wikipedia page.

However, I usually create a simple Excel file that represents what I want and then I save it as "XML Spreadsheet 2003".

The result is the XML file structure that I need to generate with an XSL stylesheet.

answered Oct 11, 2021 at 11:54

burki

7,0671 gold badge19 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to create an Excel (.xlsx) file in Apache Camel?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related