Save returns a broken xlsx on cloned Web API

[Bertug]

Hi Syncfusion Team,

I've copied your WebAPI/SpreadhseetController and deployed it on AWS Lambda. Open endpoint works perfectly, however I am having issue with Save endpoint.

My save endpoint returns a blob successfully but it is different from your one, and mine ends up downloading a broken xlsx.

Your API endpoint works, but mine doesn't.

<SpreadsheetComponent ref="editorRef"
:allow-save="true"
save-url="https://services.syncfusion.com/vue/production/api/spreadsheet/save"

/>

I dug deeper. Here are my findings:

- First I suspected it was frontend related, but then I run the exact same request and tested it on Postman to confirm that my one returns different blob than yours.

- I double checked with setting up a PHP api script to test the effect with same hardcoded request, only by changing endpoints yours and mine, and I can confirm it's related to the Web API.

My versions:

Frontend:

"@syncfusion/ej2-vue-spreadsheet": "^24.1.47",

Web API:

<ItemGroup>
<PackageReference Include="Amazon.Lambda.AspNetCoreServer.Hosting" Version="1.6.1" />
<PackageReference Include="Syncfusion.XlsIORenderer.Net.Core" Version="24.1.47" />
<PackageReference Include="Syncfusion.EJ2.Spreadsheet.AspNet.Core" Version="24.1.47" />
</ItemGroup>

I also tried 24.1.41, same issue occurs.

What versions does  https://services.syncfusion.com/vue/production/api/spreadsheet/save use? In github versions are pretty old:

    <PackageReference Include="Syncfusion.EJ2.Spreadsheet.AspNet.Core" Version="19.3.0.44" />

    <PackageReference Include="Syncfusion.XlsIORenderer.Net.Core" Version="19.3.0.44" />

My Controller:

using Microsoft.AspNetCore.Mvc;
using Syncfusion.EJ2.Spreadsheet;
using Syncfusion.XlsIO;

namespace Ej2SpreadsheetApi.Controllers;

[Route("api/[controller]")]
[ApiController]
public class SpreadsheetController : ControllerBase
{
[HttpGet]
[Route("Test")]
public IActionResult Test([FromForm] IFormCollection openRequest)
{
return Content("test");
}

[HttpPost]
[Route("Open")]
public IActionResult Open([FromForm] IFormCollection openRequest)
{
OpenRequest open = new OpenRequest();
open.File = openRequest.Files[0];
return Content(Workbook.Open(open));
}

[HttpPost]
[Route("Save")]
public IActionResult Save([FromForm] SaveSettings saveSettings)
{
return Workbook.Save(saveSettings);
}
}

Edit:
I changed my versions to 19.3.0.44. It returns a blob but again broken one.

[Bertug]

Please find the attached repo for web api

Attachment: LambdaWebAPI_4f4cdb2b.zip

[Gobalakrishnan S]

Hi Bertug,

We have validated your shared image and Web API in our end. In the Save Action, Workbook.Save(saveSettings) method will return the file stream as a result (not return as blob). So that, Save action result displays the XML command as an output on the Postman application. You can convert the file stream as blob on the Save Action itself using C# and uploading into Amazon S3 Buckets.

Also, you can get the spreadsheet data as blob in the Client Vue application end. To achieve this, you should set the isFullPost as false and needBlob as true in the beforeSave argument for getting the spreadsheet as Blob on the saveComplete event. The saveComplete event will trigger after successful save action. After receiving the Blob in the saveComplete event, you should post the Blob data to Amazon API end point using Fetch API to perform your file upload to Amazon S3 Buckets. For your convenience, we have prepared the sample and shared code snippet, output screenshot on below.

Vue Stackblitz sample: https://stackblitz.com/edit/z9hdc8-m8xmql?file=src%2FApp.vue

Code snippet:

 

onBeforeSave: function (args) {

      args.isFullPost = false;

      args.needBlobData = true;

    },

    onSaveComplete: function (args) {

      var blobData = args.blobData;

      console.log('Blob', blobData);

    },

Blob received in Save complete event:

If we misunderstood your query, then share the clear requirement details with video and screenshot from your end. This will help us to identify and provide solution earlier from our end.

[Bertug]

Hi Gobalakrishnan,

The issue is, my cloned version of WebAPI/SpreadhseetController and your hosted https://services.syncfusion.com/vue/production/api/spreadsheet/save return different responses. Both are file streams, but mine is broken file stream, your one returns working file stream. I confirmed that by saving the file stream into a file.

In the above Postman images, the only difference is the URLs (one using my hosted version, one using your hosted version: https://services.syncfusion.com/vue/production/api/spreadsheet/save). The request bodies are identical.

My Controller's code is shared with you above as well as my Lambda project. It is identical to your code to WebAPI/SpreadhseetController's code in Github.

I also wrote a php script to make sure that with the same HTTP request (same request body):
- First, I used your endpoint and saved the response to a file. The file works. I can open the file with Libre Office, Numbers.
- Then I changed the endpoint to mine, saved the response to a file. Excel softwares say "broken file".

That's why I think there is something with dependency versions, or you have different code in your "save" method in .NET. (You can see the versions I tried in OP).

Also you can see my .NET project in my previous comment.

As I understand, Syncfusion suggests that I should use my own hosted version rather than using demo endpoint (https://services.syncfusion.com/vue/production/api/spreadsheet/save)

I can share you my Lambda endpoint if you can get in touch with me privately, that's also an option.

[Bertug]

Here is some additional information.

This image explains my issue way better.

- Your Endpoint: https://services.syncfusion.com/vue/production/api/spreadsheet/save

- My Endpoint: cloned version of WebAPI/SpreadhseetController (project attached in previous comment, Controller code shown in OP).

Your endpoint returns a working file stream, I can save it to a file and can open it with Excel softwares. 
My endpoint returns a broken file stream, when I save it to a file and try to open, it says broken file.

[Babu Periyasamy]

Hi Bertug,

We have checked your reported query and as per your update that our hosted save Url is working properly, we have attached the code snippet of the Save method that we are using in our hosted Url.

Kindly, check the attached Save method code snippet file in your end and if you are still facing the issue, please share the below details to proceed further.

1. Kindly, share how you are deploying the Save method in the AWS Lambda.

2. Share the region and timezone of your hosting environment.

3. Share the complete configuration of deploying the WebAPI to the AWS Lambda.

4. As you mentioned getting the broken excel, if there are any particular excel file that is broking, kindly share the Excel file.

5. Also, share the details of what kind of data that you are trying to save.

We suspect that the error is occurring due to the region and timezone change, so please share the above requested details to proceed further.

Attachment: SpreadsheetController_339c2469.zip

[Bertug]

Hi Babu,

Thanks for your prompt answer. I checked your attached controller file and my method is doing the same thing, it's one line method: 

    [HttpPost]

    [Route("Save")]

    public IActionResult Save([FromForm] SaveSettings saveSettings)

    {

        return Workbook.Save(saveSettings);

    }




1. I am running `dotnet lambda deploy-function` in the project directory and I am getting a release .zip, then I am uploading it to AWS Lambda directly within AWS Lambda function's dashboard. (Open method works as expected).
   
2. Please see my second comment in this thread, you'll see my project zip.
3. I tried very simple excel files too and they end up being broken too. I'm attaching the simplest one here (just tested, your endpoint's file stream works, mine doesn't)
4. I started with empty spreadsheet and tried to export.

JSON I got from editor:

{"enableRtl":false,"locale":"en-US","allowEditing":false,"allowOpen":true,"allowSave":true,"enableContextMenu":false,"enableKeyboardShortcut":false,"height":"calc(100vh - 100px)","showFormulaBar":false,"showRibbon":false,"enablePersistence":false,"cellStyle":{"fontFamily":"Calibri","verticalAlign":"bottom","textIndent":"0pt","backgroundColor":"#ffffff","color":"#000000","textAlign":"left","fontSize":"11pt","fontWeight":"normal","fontStyle":"normal","textDecoration":"none","border":"","borderLeft":"","borderTop":"","borderRight":"","borderBottom":""},"showSheetTabs":true,"allowAutoFill":true,"selectionSettings":{"mode":"Multiple"},"enableKeyboardNavigation":true,"allowNumberFormatting":true,"enableClipboard":true,"allowCellFormatting":true,"allowSorting":true,"allowResizing":true,"allowHyperlink":true,"allowUndoRedo":true,"allowFiltering":true,"allowWrap":true,"allowInsert":true,"allowDelete":true,"allowDataValidation":true,"allowFindAndReplace":true,"allowMerge":true,"allowConditionalFormat":true,"allowImage":true,"allowChart":true,"activeSheetIndex":0,"cssClass":"","width":"100%","allowScrolling":true,"scrollSettings":{"enableVirtualization":true,"isFinite":false},"definedNames":[],"password":"","isProtected":false,"filterCollection":[],"sortCollection":[],"saveUrl":"","autoFillSettings":{"showFillOptions":true},"showAggregate":true,"sheets":[{"columns":[{"width":37},{"width":88},{"width":108},{"width":109},{"width":106},{"width":128},{"width":99},{"width":91}],"name":"Sheet1","rows":[{"cells":[{"style":{"fontFamily":"Arial","fontSize":"10pt"},"value":"test"},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}}],"height":21},{"cells":[{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}},{"style":{"fontFamily":"Arial","fontSize":"10pt"}}],"height":21}],"selectedRange":"A1:A1","usedRange":{"rowIndex":1,"colIndex":7},"rowCount":97,"colCount":100,"topLeftCell":"A1","activeCell":"A1","showHeaders":true,"showGridLines":true,"state":"Visible","isProtected":false,"protectSettings":{},"paneTopLeftCell":"A1","frozenRows":0,"frozenColumns":0,"index":0,"id":1,"maxHgts":[{"0":18,"1":18,"2":18,"3":18,"4":18,"5":18,"6":18,"7":18},{"0":18,"1":18,"2":18,"3":18,"4":18,"5":18,"6":18,"7":18}]}]}

Attachment: testexcel.xlsx_d29f0e5a.zip

[Babu Periyasamy]

Hi Bertug,

Currently, we are validating your reported query with the provided details. Additionally, please share the below details from your end, which will be more helpful for us to validate.

1. Confirm whether you have the below highlighted dependencies in your application,

2. Also, share the region and timezone of the AWS Lambda deploying.

Please, share the above details for further validation in our end.

[Bertug]

Hi Babu,

1. Yes, they are installed 
2. eu-west-2. (I am not sure about the timezone but I think it's UTC by default in AWS Lambda functions. If there is a suggested way for me to check, write me the code and I can run it in my lambda environment)

Let me know if there is anything else I can do to help you identify the issue.

[Gobalakrishnan S]

Hi Bertug

Currently, we are facing problem on AWS lambda function configuration in our end. We suspect that the problem is caused by the region and time zone, as these are the only differences between our hosted service and yours.

So, we suggest updating the culture of the Web API to “en-US” culture. This may help to resolve your problem.

Please added the below code snippet to your Startup.CS file along with existing code blocks.

 

using System.Globalization;

 

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)

        {

            if (env.IsDevelopment())

            {

                app.UseDeveloperExceptionPage();

            }

 

            // Set the default culture to en-US

            var cultureInfo = new CultureInfo("en-US");

            CultureInfo.DefaultThreadCurrentCulture = cultureInfo;

            CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;

 

[Bertug]

Hi Gobalakrishnan

I added the CultureInfo like below, however it is still not working. In your tries, when you set cultureInfo to "en-US", did it work?

For AWS, I'm using .NET6, so I have a Program.cs, and it looks like this with added cultureInfo.

Program.cs:

using System.Globalization;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();

builder.Services.AddCors(options =>

{

    options.AddDefaultPolicy(policy =>

    {

        policy.AllowAnyOrigin()

              .AllowAnyMethod()

              .AllowAnyHeader();

    });

});

Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense("license");

builder.Services.AddAWSLambdaHosting(LambdaEventSource.HttpApi);

var app = builder.Build();

app.UseCors();

// Configure the HTTP request pipeline.

app.UseAuthorization();

app.MapControllers();

var cultureInfo = new CultureInfo("en-US");

CultureInfo.DefaultThreadCurrentCulture = cultureInfo;

CultureInfo.DefaultThreadCurrentUICulture = cultureInfo;

app.Run();

[Bertug]

Hi

I'm writing to follow up on the ongoing issue with the "Save" endpoint in the AWS Lambda deployment. It has been a while since our last interaction, and I am keen to know if there have been any developments or new insights on this matter.

I would like to mention that updating the cultureInfo to "en-US", as suggested, has not resolved the issue. Additionally, I'm seeking clarification on your previous message about facing problems with AWS Lambda function configuration on your end. Were you able to replicate the issue I'm experiencing?

Your prompt response would be greatly appreciated.

[Gobalakrishnan S]

Hi Bertug

Currently, we are validating your reported query with the provided details. We will update you with further details soon. 

[Janakiraman Sakthivel]

Hi Bertug,

Previously, we were facing a problem while deploying our ASP.NET Core Web API to AWS Lambda. Now we have resolved this problem and successfully deployed our ASP.NET Core Web API to AWS Lambda, as you mentioned. After this, while importing an MS Excel file, we got an unsupported file dialog, as attached below. And also, while trying to save our spreadsheet data as MS Excel, the file was saved successfully. But while trying to open the saved file, the file is not opened and also shows an error message that "The file is corrupt and cannot be opened" as shown below.

So, currently, we are validating the reason for these import and export problems to find the root cause of the problem. We will validate and share the update with further details ASAP.

[Bertug]

Hi Janakiraman

Sounds like you have replicated the issue. Looking forward to hearing from you.

[Janakiraman Sakthivel]

Hi Bertug,

In the Save action, the Workbook.Save(saveSettings) method will return the file stream as a result. But while sending the file stream result from your AWS lambda server, the file stream gets corrupted. So, we have saved the workbook as a base64 string instead of a file stream result on the server side and sent it to the client side as shown below.

public string Save([FromForm]SaveSettings saveSettings)

{

    return Workbook.Save<string>(saveSettings);

}

On the client side, currently in the shared sample, we have prevented the default save action by assigning the value true to the args.cancel in the beforeSave event. Instead of this, we have handled the save action at the sample level inside the function "performSaveAction".

And on the client side, inside the "performSaveAction" function, we have used the saveAsJson method to save the spreadsheet as JSON data. And by using the fetch call, we got the blob data as a plain text base64 string from the server. And we have converted this plain text base64 string into Excel blob data. And finally, we have saved the file as MS Excel from the base64 string, as shown below.

performSaveAction: function (args) {

      // Convert the spreadsheet workbook to JSON data.

      this.$refs.spreadsheet.saveAsJson().then(Json => {

        this.isSave = true;

        const formData = new FormData();

        // Passing the JSON data to server to perform save operation.

        formData.append('JSONData', JSON.stringify(Json.jsonObject.Workbook));

        formData.append('saveType', 'Xlsx');

        formData.append('fileName', 'Worksheet');

        formData.append(

          'pdfLayoutSettings',

          '{"fitSheetOnOnePage":false,"orientation":"Portrait"}'

        );

        // Using fetch API to invoke the server for save processing.

        fetch('https://lgba7655gj.execute-api.us-east-1.amazonaws.com/Prod/api/spreadsheet/save', {

          method: 'POST',

          body: formData

        }).then(response => {

          if (response.ok) {

            return response.blob();

          } else {

            console.log({ message: response.statusText });

          }

        }).then(data => {

            const reader = new FileReader();

            reader.onload = function () {

                //Converts the result of the file reading operation into a Base64 string

                const textBase64Str = reader.result.toString();

                //Converts the Base64 string into a Excel base64 string

                const excelBase64Str = atob(textBase64Str.replace('data:text/plain;base64,', ''));

                //Converts the Excel base64 string into byte characters

                const byteCharacters = atob(excelBase64Str.replace('data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,', ''));

                const byteArrays = [];

                for (let i = 0; i < byteCharacters.length; i++) {

                    byteArrays.push(byteCharacters.charCodeAt(i));

                }

                const byteArray = new Uint8Array(byteArrays);

                //creates a Blob data from the byte array

                const blobData = new Blob([byteArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

                const blobUrl = URL.createObjectURL(blobData);

                const anchor = document.createElement('a');

                anchor.download = 'Sample.xlsx';

                anchor.rel='nofollow' href = blobUrl;

                document.body.appendChild(anchor);

                anchor.click();

                URL.revokeObjectURL(blobUrl);

                document.body.removeChild(anchor);

            }

            reader.readAsDataURL(data);

        });

      });

    },

    onBeforeSave: function (args) {

      if (this.isSave) {

        args.cancel = true;

        this.isSave = false;

        this.performSaveAction(args);

      }

    }

 

And We have attached the sample for your reference.

Sample link: https://stackblitz.com/edit/z9hdc8-pnvcou?file=src%2FApp.vue

Get back to us for further clarifications.

[Bertug]

Hi Janakiraman,

I just tested it, your suggestion fixes the issue for xlsx exports. This resolves my issue, thanks a lot.

Similar issue happens with Pdf. With https://services.syncfusion.com/vue/production/api/spreadsheet/ I can download pdfs, however with the new lambda I can't. (Also tested old one, it doesn't work either).

Can you please guide me on that as well?

[Babu Periyasamy]

Hi Bertug,

Currently, we are validating your reported query based on the provided details. We will update you the further details soon.

[Janakiraman Sakthivel]

Hi Bertug

We have validated your reported query, and we were able to replicate your problem on our end. In our previously shared sample, we had handled the "performSaveAction" function only for the Excel format files. Now, we have handled the "performSaveAction" function commonly for the PDF type as well. In our previously shared sample with these changes, we were able to download the spreadsheet as a PDF with our demo purpose service and local service.

But while trying to save the spreadsheet as a PDF with the AWS Lambda server, we are facing the below problem on the client side.

To resolve this problem, we saved the workbook as a stream instead of a base64 string on the server side for pdf downloading and sent it to the client side. But we are facing the same problem with this stream of data as well. Then we converted the workbook into a stream, converted this stream into a byte array, and then converted this byte array into a base64 string on the server side and sent this base64 string to the client side for pdf downloading. But with this process, we have also faced the same problem: "Failed to load resource: the server responded with a status of 500 ()" with the AWS server.

However, we are working on this problem on our end to resolve it. And we need some additional time to validate more on this reported problem, and we will update you with further details soon.

[Bertug]

Hi Janakiraman

Are there any updates on this issue?

Thanks

[Janakiraman Sakthivel]

Hi Bertug

As we mentioned already, while using the AWS server in the fetch API to invoke the server for pdf save processing, we faced the problem "Failed to load resource: the server responded with a status of 500 ()". While validating this problem in the AWS log, we found that the DllNotFoundException occurred with SkiaSharp.dll on the AWS server side. Then we installed SkiaSharp.dll properly on our server end. After installing SkiaSharp.dll, we published the AWS lambda and ensured the installed SkiaSharp.dll was included in the published folder as well. But even after installing SkiaSharp.dll, the same exception still occurred on our end.

Because of this DllNotFoundException, we have faced the "Failed to load resource: the server responded with a status of 500 ()" exception on the client side. And currently, we are working on this problem to resolve it in possible ways.

And could you please check the AWS log file on your end to confirm that the same DllNotFoundException occurred with SkiaSharp.dll on your end as well?

If the same problem occurred on your end as well, please install SkiaSharp.dll on your end and publish with AWS Lambda. After publishing, if you face the same kind of problem again then share your AWS service log file to validate the issue. This will help us to proceed further.

[Bertug]

Hi Janakiraman,

You are right, I just checked my AWS logs, I am indeed seeing: 

"System.DllNotFoundException: Unable to load shared library 'libSkiaSharp' or one of its dependencies. In order to help diagnose loading problems, consider setting the LD_DEBUG environment variable: liblibSkiaSharp: cannot open shared object file: No such file or directory"

---------

I now installed the "SkiaSharp.NativeAssets.Linux.NoDependencies", AWS returned success however the returned base64 seems broken.

Request:
{
  "JSONData": "{"enableRtl":false,"locale":"en-US",....},

 "fileName": "Test.pdf"

  "saveType": "Pdf"

  "pdfLayoutSettings": "{"fitSheetOnOnePage":false,"orientation":"Landscape"}"
}

Response:

"data:application/pdf;base64,JVBERi0xLjUNCiWDkv..." - so it returned a successful base64. However, when I save this to a pdf file, I get a broken file.

Does your one work? 

[Janakiraman Sakthivel]

Hi Bertug,

Thank you for your update.

Even after installing "SkiaSharp.NativeAssets.Linux.NoDependencies", the same exception still occurred on our end. And also, we have installed "SkiaSharp.NativeAssets.Linux" and "HarfBuzzSharp.NativeAssets.Linux" to resolve this problem. But still, the same exception occurred on our end. So, could you please share the details of whether you have installed any other dll with "SkiaSharp.NativeAssets.Linux.NoDependencies" on your end to resolve this exception, or have you taken any other actions to resolve this exception? Please share those details. It will help us to resolve the DllNotFoundException exception on our end.

And we will save the workbook as a base64 string instead of a file stream result on the server side and will send that to the client side in our previously shared server-side save function to save the spreadsheet as Excel. We suggest you use that server-side save function only to save the spreadsheet as an Excel process.

And we suggest you use our default save function on the server end to save the spreadsheet as a PDF. And also, we suggest you use the below shared functions on your server end to save the spreadsheet as a PDF. Can you please try below shared type of save methods in your end? Because it may resolve your problem in your end. And could you please share the results you are getting on your end while using the below shared functions on the server end to save the spreadsheet as a PDF?

// Our default save function
[HttpPost]

[Route("Save")]

public IActionResult Save([FromForm] SaveSettings saveSettings)

{

    return Workbook.Save(saveSettings);

}

[HttpPost]

[Route("SavePDF1")]

public Stream SavePDF1([FromForm] SaveSettings saveSettings)

{

    return Workbook.Save<Stream>(saveSettings);

}

[HttpPost]

[Route("SavePDF2")]

public string SavePDF2([FromForm] SaveSettings saveSettings)

{

    Stream stream = Workbook.Save<Stream>(saveSettings);

    using (MemoryStream memoryStream = new MemoryStream())

    {

        // Copy content from stream to memory stream

        stream.CopyTo(memoryStream);

 

        // Convert memory stream to byte array

        byte[] contentBytes = memoryStream.ToArray();

 

        // Convert byte array to Base64 string

        string base64String = Convert.ToBase64String(contentBytes);

 

        return base64String;

    }

}

[Bertug]

Hi Janakiraman,

I only installed "SkiaSharp.NativeAssets.Linux.NoDependencies" and deployed my change and the DllNotFoundException issue got resolved. (I didn't install any other packages)

Also, I want to confirm my issue is resolved and the default way can download pdf as expected.

[HttpPost]

[Route("Save")]

public IActionResult Save([FromForm] SaveSettings saveSettings)

{

    return Workbook.Save(saveSettings);

}

Thank you very much for your support. If there is anything I can do for your AWS issue, let me know.

[Gobalakrishnan S]

Hi Bertug,

Thanks for your update.

Even after installing "SkiaSharp.NativeAssets.Linux.NoDependencies", we are still getting the same error. We tried a new Web API application with the proper dependencies installed, but the problem persisted. We are currently planning to test with different Azure accounts using a new Web API application. We will notify you if we require any assistance with this.

Regards,

Gobalakrishnan S