read and write an excel document using apache camel

Question

I am trying to read an excel document, massage it, and then write the document in a camel route. I can read the excel document ok, I can write to its cells or update it. I'm having trouble writing the stream to the camel message exchange and getting an excel document output.

what am I doing wrong? thank you!!!

so, the idea is that I have an excel template I import. I have data coming in from the exchange that will be put on the spreadsheet cells on 2nd sheet, then I need to send that file to a destination.

public class XlsxProcessor implements Processor {  

    private Logger log = Logger.getLogger(XlsxProcessor.class.getName());

    private static final String template = "DataloaderTemplate.xlsx";
/* collect data from in msg exchange to populate excel spreadsheet */       
    @Override
    public void process(Exchange exchange) throws Exception {

        log.info("Importing Excel Spreedsheet Start...");

/*        List<Map<String, String>> rows = (List<Map<String, String>>)exchange.getIn().getBody();

        for (Map<String, String> row : rows) {

          StringBuilder line = new StringBuilder();
          Iterator<String> rowItr = row.values().iterator();

          while (rowItr.hasNext()) {
            String value = rowItr.next();
            line.append(value);
           if (rowItr.hasNext()) {
               line.append(",");
           }
          }
          line.append("\n");
          log.info("row values: " + line.toString() );
        }
*/        
        InputStream xlsxStream = null;
        ClassLoader classLoader = this.getClass().getClassLoader();
        URL url = this.getClass().getClassLoader().getResource("/excel/" + template);
        log.info("Located Resource URL: " + url );

        if (url != null ){
            log.info("open input stream");
            xlsxStream = url.openStream(); 
            } else {
                log.error("Excel APM Upload Template URI cannot be located!");
            }
                log.info("Open WorkBook!");
                XSSFWorkbook workbook = new XSSFWorkbook(xlsxStream);
                XSSFSheet worksheet = workbook.getSheetAt(1);
                XSSFRow row;

                int rowid = 2;  //data entry starts at row 3 sheet 1 ( 0, 1, 2 )
//                for (String key : keyid) {
                   row = worksheet.createRow(rowid++);
                   int cellid = 0;
//                   for (Object obj : objectArr) {
                     for ( int i = 0; i < 10; i++){
                         XSSFCell cell = (XSSFCell) row.createCell(i);
                         cell.setCellValue(String.valueOf(i));
                   }
//                }

         ByteArrayOutputStream bos = new ByteArrayOutputStream();
         try {
              workbook.write(bos);
             } catch (IOException e) {
             } finally {
             try {
                  bos.close();
                 } catch (IOException e) {
              }
            }
            byte[] xlsx = bos.toByteArray();

            exchange.getIn().setBody(xlsx);

        log.info("APM Predix Excel Spreedsheet End...");
}

which goes to the camel context to send file to destination

                <process ref="xlsxProcessor" />
                <to uri="{{EndpointTest}}fileName=/${header.aircraftMetadata.outputPath}/${header.aircraftMetadata.shipNumber}-${header.enginePosition}_${header.messageDateTime}.xlsx&amp;charset={{outputEncoding}}" />
                <log message="Sending data packet: ${header.aircraftMetadata.outputPath}/${header.aircraftMetadata.shipNumber}-${header.enginePosition}_${header.messageDateTime}.xlsx" />

The output looks like a binary file all garbaged up it is not an excel file. I think it is because of the way it is sent to the message exchange?

GLMills · Accepted Answer · 2018-10-08 21:50:22Z

1

well, it is operator error. in my route I was using charset={{outputEncoding}} where encoding is set to US ASCII.

in the context route do not use charset encoding. This produces an Excel file output.

         <to uri="{{EndpointTest}}fileName=/${header.aircraftMetadata.outputPath}/${header.aircraftMetadata.shipNumber}-${header.enginePosition}_${header.messageDateTime}.xlsx" />

answered Oct 8, 2018 at 21:50

GLMills

6381 gold badge16 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

read and write an excel document using apache camel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related