can't update sheet

[Jethro Torres]

Hello so we have our app using angular spreadsheet, we're trying to update a file. An example will be provided in the attachment. 

Using the Angular Spreadsheet component we can't seem to update the file.

1. Make changes on the front end side.
2. Encode the file to base64.
3. Then send the encoded file to our backend.
4. View it again - changes are not not applied.

But then I tried,  doing the update using purely backend.

1. Upload the file.
2. View the file using your component.
3. Download the file.
4. Make changes using LibreOffice or MS Excel.
5. Encode to base64 using this https://base64.guru/converter/encode/file
6. Use the base64 from that, the file is updated with all the changes I made.

We're using the base64 to view the file.

Attachment: file_example_XLS_50_66150ad7.zip

[Jethro Torres]

Upon further checking - I noticed that there's something off with how the angular sheet is encoding the file to base64. I tried to decode the base64 coming for the angular sheet component and it shows the mime-type as application/zip. However when I tried to edit the file using LibreOffice calc then use an online file to base64 encoder I got this mime-type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. There's also a huge difference when I string compared the base64 generated by angular sheets and by the online one. I will also attach the base64 strings.

Attachment: testfiles_7ec9b1d7.zip

[Babu Periyasamy]

Hi Jethro Torres,

We have thoroughly investigated your reported query, utilizing the details and Excel file you provided. To replicate your reported issue, we created a sample scenario. In this scenario, we initiated a fetch call to the server to load the Excel file which is stored in our server under the 'Files' folder onto the spreadsheet. This was achieved using the 'openFromJson' method, triggered by a button click.

Subsequently, we made modifications to the loaded file within the spreadsheet and saved the altered data using the 'saveAsJson' method. Upon clicking another button, the modified data was sent back to the server via a fetch call.

In our 'Save' service, we've implemented a mechanism to save the Excel file as a base64 string, specifying the return type in our Save API. We then converted this base64 string into a byte array and saved the Excel file on the server, specifically within the 'Files' folder.

Upon thorough testing, we ensured that the changes made within the spreadsheet were accurately saved to the Excel file in the 'Files' folder. And we are not able to replicate your reported issue.

For your convenience, we have attached the created sample along with the service and code snippet for your reference,

Code snippet:

app.component.ts

openExcelFromServer() {

        // Fetch call to server to load the Excel.

        fetch('https://localhost:7016/Home/Open', {

            method: 'POST',

            headers: {

                'Content-Type': 'application/json',

            },

            body: JSON.stringify({ FileName: 'Sample' }),

        })

            .then((response) => response.json())

            .then((data) => {

                // Load the JSON data into spreadsheet.

                this.spreadsheetObj.openFromJson({ file: data });

            });

    }

   saveExcelToServer() {

        // Convert the spreadsheet workbook to JSON data.

        this.spreadsheetObj.saveAsJson().then((json: {jsonObject: {Workbook: {}}}) => {

            const formData = new FormData();

            formData.append('FileName', "Sample");

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

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

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

            formData.append('PdfLayoutSettings', JSON.stringify({ FitSheetOnOnePage: false }));

            // Using fetch to invoke the save process.

            fetch('https://localhost:7016/Home/Save', {

                method: 'POST',

                body: formData

            }).then((response) => {

                console.log(response);

            });

        });

    };

Controller.cs

//Open File from the server

public IActionResult Open([FromBody] FileOptions options)

{

    OpenRequest open = new OpenRequest();

    string filePath = _env.ContentRootPath.ToString() + \\Files\\ + options.FileName + ".xlsx";

    FileStream fileStream = new FileStream(filePath, FileMode.Open); // Getting the file stream from the file path.

    IFormFile formFile = new FormFile(fileStream, 0, fileStream.Length, "", options.FileName + ".xlsx"); // converting MemoryStream to IFormFile

    open.File = formFile;

    var result = Workbook.Open(open); // Processing the Excel file and return the workbook JSON.

    fileStream.Close();

    return Content(result);

}

 

 

//Save the updated file to the server

public string Save(SaveSettings saveSettings)

{

     try

     {

         // Save the workbook as base64 string.

         string base64String = Workbook.Save<string>(saveSettings);

         // Extracting the base64 data from the string

         string base64Data = base64String.Split(',')[1];

         // Decode the base64 string to obtain the byte array

         byte[] excelBytes = Convert.FromBase64String(base64Data);

         //IWorkbook workbook = application.Workbooks.Open(fileStream);

         string basePath = _env.ContentRootPath + \\Files\\ + saveSettings.FileName + ".xlsx";

         // Save the byte array to a file

         System.IO.File.WriteAllBytes(basePath, excelBytes);

        

         return $"File saved successfully: {saveSettings.FileName}.xlsx";

     }

     catch (Exception ex)

     {

         return ex.Message;

     }

}

Client-side sample link: https://stackblitz.com/edit/angular-mnmcru-bmpdqu?file=src%2Fapp.component.ts

WebAPI application: https://www.syncfusion.com/downloads/support/directtrac/general/ze/WebApplication12092942857

Note: Launch the WebAPI first and then run the client-side sample.

Also, we have prepared the video demonstration for the above information and attached below for your reference,

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Save_as_base64-1717989115

For more information, please refer the below documentation,

https://ej2.syncfusion.com/angular/documentation/spreadsheet/how-to/save-and-open-spreadsheet-data-as-base64-string

Kindly, check the above details and sample. If you are still facing the issue or if we misunderstood your reported issue, please share the below details,

1. Share the details of how you are saving the Spreadsheet data as base64 and save it as Excel in your end, share those details along with the code snippets.
2. If you are facing the issue on doing particular action in Spreadsheet, then please share those details.
3. If possible, please replicate your reported issue on the above shared sample and sent it back to us for further validation.
4. If possible, please share the detailed description of issue that you are facing along with the screenshot or video demonstration which will be more helpful for us to validate further.

Please share the above requested details from your end. Based on that, we will check and provide you the better solution quickly.

[Jethro Torres]

@Babu Periyasamy can you check my reply before yours. I added the generated base64 using the angular sheet component.

[Jethro Torres]

Following up my previous comment -

Upon further checking - I noticed that there's something off with how the angular sheet is encoding the file to base64. I tried to decode the base64 coming for the angular sheet component and it shows the mime-type as application/zip. However when I tried to edit the file using LibreOffice calc then use an online file to base64 encoder I got this mime-type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. There's also a huge difference when I string compared the base64 generated by angular sheets and by the online one. I will also attach the base64 strings.

Attachment: testfiles_7ec9b1d7.zip

[Babu Periyasamy]

Hi Jethro Torres,

Thanks for your update.

We have checked your reported query along with the attached base64 strings and file. To check your reported issue, we have prepared the sample in which we have loaded your provided base64 string into Spreadsheet initially by converting it into a file and opened using the open method of our Spreadsheet.

Subsequently, we made alterations to the data within the Spreadsheet and saved the updated data into blob data. This was done by setting 'needBlobData' as true and 'isFullPost' as false in the 'beforeSave' event. The saved blob data was then converted into a Base64 string using a FileReader.

And then we have checked the updated base64 string by loading it into the spreadsheet on a button click. But the updated data is properly saved and loaded into the Spreadsheet, and we are not able to replicate your reported issue.

Below attached the code snippet and sample along with the video demonstration for your reference,

Code snippet:

created() {

        //Loaded the provided base64 string.

        this.base64toFile();

    }

    //Function to covert the base64 string into file.

    base64toFile(): void {

        let base64String = this.updatedBase64 || this.base64String;

        //Remove the type from base64 string if it contains.

        if (base64String.indexOf('data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,') > -1) {

            base64String = base64String.replace('data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,', '');

        }

        const byteCharacters: string = atob(base64String);

        const byteNumbers: number[] = new Array(byteCharacters.length);

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

            byteNumbers[i] = byteCharacters.charCodeAt(i);

        }

        //Convert to byte array.

        const byteArray: Uint8Array = new Uint8Array(byteNumbers);

        //Convert the byte array to blob.

        const fileBlob: Blob = new Blob([byteArray]);

       //Convert the blob to file to open into Spreadsheet.

        const file: File = new File([fileBlob], 'Sample.xlsx');

        if (this.spreadsheetObj) {

            this.spreadsheetObj.open({ file: file });

        }

    }

    beforeSave(args: BeforeSaveEventArgs): void {

        if (this.isSaveAsBase64) {

            args.needBlobData = true; // Get the spreadsheet data as blob data in the saveComplete event.

            args.isFullPost = false;  // To trigger the saveComplete event.

            this.isSaveAsBase64 = false;

        }

    };

    saveComplete(args: SaveCompleteEventArgs): void {

        // Convert blob data to base64 string.

        let reader: FileReader = new FileReader();

        reader.readAsDataURL(args.blobData);

 

        reader.onloadend = () => {

            this.updatedBase64 = reader.result ? (reader.result as string): '';

        };

    };

    importBase64(): void {

        this.base64toFile();

    }

    exportBase64(): void {

        this.isSaveAsBase64 = true;

        this.spreadsheetObj.save({

            url: 'https://services.syncfusion.com/angular/production/api/spreadsheet/save',

            fileName: 'Worksheet',

            saveType: 'Xlsx',

        });

    }

Sample link: https://stackblitz.com/edit/angular-udarog-e16nxe?file=src%2Fapp.component.ts

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Open_and_save_as_base64-528460682

Kindly, check the above information. And if you’re still facing the issue or if we misunderstood your reported issue, please share the below details,

1. Please share the details of how you are converting the Spreadsheet data into the base64 string in your angular sample along with the code snippets.
2. Also, share the details of how you are decoding and encoding the Spreadsheet data in your angular application.
3. If possible, please replicate your reported issue in the above attached sample and send it back to us for further validation.
4. If possible, please share the detailed description of the issue that you are facing along with the screenshot and video demonstration.

Please share the above requested information from your end. Based on that, we will check and provide you the better solution quickly.