Returning Excel with a CAP service

[TheWind]

Hello, I am trying this feature in order to download a dynamically generated excel from a UI5 app: https://cap.cloud.sap/docs/node.js/best-practices#custom-streaming-beta

The way I use it is this:

const readable = Readable.from(buffer)
return {
  value: readable,
  $mediaContentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  $mediaContentDispositionFilename: `${fileName}.xlsx`,
}

Where readable is of type stream.Readable and the buffer comes from ExcelJS.

When I trigger it in the browser, it correctly downloads an attachment of type XLSX, but the content is:

{
@odata.context: "$metadata#Edm.Binary"
value: {
  value: "[object Readable]"
  $mediaContentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  $mediaContentDispositionFilename: "DCT - 212 - TEST TAX - SSD 2024-10-05.xlsx"
  }
}

So the contents of the stream are not actually read, but rather, the stream object is somehow converted into a string. I found a bunch of other community posts regarding media data, but only few of them got clear answers. Am I making a mistake here, or is this a bug? The documentation states no requirements for the streamed data, only that the type of the object must be stream.Readable

[catano]

Hi @TheWind 
I tried it with version 8.3 and it works for me.
I added my media field as LargeBinary type in the schema, in my case with a fixed media type, but this can be made dynamic as described in the documentation:

entity Books { // ...
        attachmentXlsx : LargeBinary @Core.MediaType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
}

I see it as Edm.Stream in the metadata.

<EntityType Name="Books">
  <Key>
    <PropertyRef Name="ID"/>
  </Key>
  <Property Name="ID" Type="Edm.Int32" Nullable="false"/>
  <Property Name="attachmentXlsx" Type="Edm.Stream"/>
</EntityType>

I used the custom read logic as follows, I used the createReadStream from Node.js fs to read a static file:

  srv.on('READ', 'Books', (req, next) => {
    if (attachmentIsRequested(req)) {
      const readable = fs.createReadStream('files/test.xlsx');
      return {
        value: readable,
        $mediaContentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        $mediaContentDispositionFilename: 'test.xlsx', // > optional
        $mediaContentDispositionType: 'inline' // > optional
      }
    }
    return next()
  });

By typing the following URL into my browser, I was able to download and open the Excel file, and the content was correct (according to the ID, this should point to an existing record):

http://localhost:4004/odata/v4/galactic/Books(201)/image

It might be worth trying a direct browser link before the UI download link so that it doesn't interfere with the testing.

[WouterLemaire]

You could try it the following way assuming the excel is in your project located:

const data = readFileSync(path.join(__dirname, "../files/template.xlsx"));
            
const template = new XlsxTemplate(data);
    
let sheetNumber = 1;
const values = {
      data: templateData
};
template.substitute(sheetNumber, values);

template.substitute(sheetNumber, inputParameters);

const binaryData = template.generate({type: "uint8array"});
const readableInstanceStream = new Readable({
      read() {
              this.push(binaryData);
              this.push(null);
      }
});

req._.res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
req._.res.setHeader('Content-Disposition', 'attachment; filename=excel.xlsx');
            
return {
        value: readableInstanceStream
};

(this example uses templating using the xslx-template lib) 

[TheWind]

I have tried creating the stream the same way, as in lines 14f, but it get the same issue that the data from the stream is not actually read, but returned as 'value: "[object Readable]"'. For reference, I am using CDS version 8.3.0

[WouterLemaire]

How does your request look like? You have to add /content (or other name depending on the property name that you use for the filecontent)