How to add urlParameters :"$format=xlsx" in odata v2 model read?

[akshaya_p]

I want to append $format=xlsx" in odata v2 model read but im getting 500 internal server error. please help

 aFilters.push(new sap.ui.model.Filter({
          path: 'Req',
          operator: sap.ui.model.FilterOperator.EQ,
          value1: 'xxxxxxxxxxxxxx'
     }));


this.getView().getModel().read("/EntitySet",{
				success:function(s){
				alert("success")
			},
			
			 filters :aFilters,
            urlParameters: "$format=xlsx",
			error:function(e)
			{
				alert("error")
			}
			});

[former_member605181]

Hi Akshaya,

I don't know if this is still an issue but i had the exact same problem. It took some time but i wrote two functions to download an xlsx file from given oData service path with the array of filter values. You should define these in your controller

downloadExcel: function(servicePath, relativePath, filters, requestedFields) {
  // It should download excel file.
  //servicePath 
  //Example "/sap/opu/odata/sap/ZYourService_SRV"
  //relativePath
  //Example "/YourEntitySet"
  //filters 
  //Example [new sap.ui.model.Filter("OverhaulDemands", "EQ", true)]
  //requestedFields
  //Example ['Ebeln', 'Ebelp', 'Etenr', 'Lifnr']
  //Name of the Fields on your oData service which you want to include in your excel
  var expUrl = this.createUrl(servicePath, relativePath, filters, requestedFields);
  sap.m.URLHelper.redirect(expUrl, [true]);
}
createUrl: function(servicePath, relativePath, filters, requestedFields) {
  //this is the function to create the url.
  function handleSingleFilter(oFilter) {
    var result_ = '';
    switch (oFilter.sOperator) {
      case "EQ":
        if ((typeof oFilter.oValue1) != "boolean") result_ += oFilter.sPath + " " + "eq" + " '" + oFilter.oValue1 + "'";
        else result_ += oFilter.sPath + " " + "eq" + " " + oFilter.oValue1;
        break;
        // console.log('EQ');
      case "Contains":
        result_ += "substringof('" + oFilter.oValue1 + "'," + oFilter.sPath + ")";
        break;
        // console.log('Contains');
      case "BT":
        result_ += "(" + oFilter.sPath + " ge '" + oFilter.oValue1 + "' and " + oFilter.sPath + " le '" + oFilter.oValue2 + "')";
        break;
        // console.log('BT');
      case "StartsWith":
        result_ += "startswith(" + oFilter.sPath + ",'" + oFilter.oValue1 + "')";
        break;
        // console.log('StartsWith');
      case "EndsWith":
        result_ += "endswith(" + oFilter.sPath + ",'" + oFilter.oValue1 + "')";
        break;
        // console.log('EndsWith');
      case "LT":
        result_ += oFilter.sPath + " " + "lt" + " '" + oFilter.oValue1 + "'";
        break;
        // console.log('LT');
      case "LE":
        result_ += oFilter.sPath + " " + "le" + " '" + oFilter.oValue1 + "'";
        break;
        // console.log('LE');
      case "GT":
        result_ += oFilter.sPath + " " + "gt" + " '" + oFilter.oValue1 + "'";
        break;
        // console.log('GT');
      case "GE":
        result_ += oFilter.sPath + " " + "ge" + " '" + oFilter.oValue1 + "'";
        break;
        // console.log('GE');
    }
    return result_;
  }
  var flag = false;


  function handleMultiFilter(aFilters, parentBAnd) {
    var multiResult = "";
    for (var i = 0, len = aFilters.length; i < len; i++) {
      console.log(multiResult);
      var oFilter = aFilters[i];
      if (oFilter._bMultiFilter) {
        if (i != 0) {
          multiResult += parentBAnd ? " and " : " or ";
        }
        if (flag) {
          multiResult += "(" + handleMultiFilter(oFilter.aFilters, oFilter.bAnd) + ")";
        } else {
          flag = true;
          multiResult += handleMultiFilter(oFilter.aFilters, oFilter.bAnd);
        }
      } else {
        if (i != 0) multiResult += parentBAnd ? " and " : " or ";
        multiResult += handleSingleFilter(oFilter);
      }
    }
    return multiResult;
  }
  var urlParams = "";
  var mainResult = "";
  var selectParams = "";
  if (requestedFields.length > 0) {
    selectParams = "&$select=" + requestedFields.toString();
  }
  if (filters.length > 0) {
    urlParams = "?$filter=";
    mainResult = servicePath + relativePath + urlParams + handleMultiFilter(filters, false) + "&$format=xlsx" + selectParams;
  } else {
    urlParams = "?$format=xlsx" + selectParams;
    mainResult = servicePath + relativePath + urlParams + handleMultiFilter(filters, false);
  }
  console.log(mainResult);
  return mainResult;
}

[nalamabhiram]

Hi Akshaya,

I assume you are expecting to download the data in xlsx format.

if my assumption is correct, then you can form the url and try url redirect.

Some thing like below

------Start--------

var URL;

URL = this.getView().getModel().sServiceUrl + "/EntitySet"+"?$format=xlsx&$filter=((Req eq 'xxxxxxxxxxxxxx'))";

sap.m.URLHelper.redirect(URL, true);

------END--------

As you are expecting file to be downloaded, if somehow if you successfully executed oData read operation and got success response...How will you manage to generate XLXS file from the success parameter passed "s" for success method.So,I hope URL redirect will be easy.

Regards,

Abhiram.

[former_member44086]

$format=json or $format=xml will work. Once you get the JSON format, you can then write the data to excel in CSV format

[akshaya_p]

is there no way to get excel format by POST?