Excel file upload and read from file

[Eric]

Hi, 

Here I am with yet another question.

I am trying to upload an MS-Excel file, and read from it. Right now I have this:

<SfUploader ID="UploadFiles" AllowedExtensions=".xls, .xlsx" AutoUpload=false DropArea="#DropArea" MaxFileSize="500000">

    <UploaderAsyncSettings RemoveUrl="api/SampleData/Remove">

        <UploaderEvents ValueChange="OnChange"></UploaderEvents>

    </UploaderAsyncSettings>

</SfUploader>

@code {

protected async Task OnChange(UploadChangeEventArgs args)

    {

        foreach (var file in args.Files)

        {

            //Console.WriteLine($"Value is ");

            var path = @"path" + file.FileInfo.Name;

            using (ExcelEngine excelEngine = new ExcelEngine())

            {

                

                //Instantiate the Excel application object

                IApplication application = excelEngine.Excel;

                //Set the default application version

                application.DefaultVersion = ExcelVersion.Xlsx;

                //Load the existing Excel workbook into IWorkbook

                FileStream filestream = new FileStream(path, FileMode.Open, FileAccess.Read);

                IWorkbook workbook = application.Workbooks.Open(filestream);

                //Get the first worksheet in the workbook into IWorksheet

                IWorksheet worksheet = workbook.Worksheets[0];

                filestream.Close();

                file.Stream.Close();

            }

        }

        await Task.CompletedTask;

    }

}

But although it capture the file, I am not able to read the excel, or get any of its properties. It does not give me an error, but let's say I try to get a property like workbook.Worksheets.Count, I get nothing.

There is probably something really obvious and simple that I am missing. Any help would be highly appreciated!

[Sureshkumar P]

Hi Eric, 

 

Greetings from Syncfusion support, 

 

Based on your shared backend code, we suspect that you have used our XlsIo namespace to read the Xlsx files. So, we suggest you check the file path of uploaded file to resolve the filestream issue.  

 

Please find the screen shot here: 

 

 

Please confirm whether you have using our Syncfusion Xlsio package to read the file or not. 

 

Regards, 

Sureshkumar P 

[Eric]

Hi Sureshkumar,

Yes, I am using Syncfusion.XlsIO. 

About the path, "Duh", yeah, I missed that it was obviously a placeholder. But now, how do I get the path (if it's that even possible given browser security)? I am more concerned on being able to read from the file uploaded.

Thanks for your help.

[Sureshkumar P]

Hi Eric, 

 

Thanks for your update. 

 

You can get the file path as like below constant path. Because modern browser will not tell you the path on the client, for security reasons.  

 

Please refer the sample code example here:  

public async Task OnChange(UploadChangeEventArgs args) 

    { 

        foreach (var file in args.Files) 

        { 

            var path = @"D:\" + file.FileInfo.Name; 

 

            using (ExcelEngine excelEngine = new ExcelEngine()) 

            { 

 

                //Instantiate the Excel application object 

                IApplication application = excelEngine.Excel; 

 

                //Set the default application version 

                application.DefaultVersion = ExcelVersion.Xlsx; 

 

                //Load the existing Excel workbook into IWorkbook 

                FileStream filestream = new FileStream(path, FileMode.Open, FileAccess.Read); 

                IWorkbook workbook = application.Workbooks.Open(filestream); 

                Console.WriteLine("WorkSheetCount" + workbook.Worksheets.Count); 

 

                //Get the first worksheet in the workbook into IWorksheet 

                IWorksheet worksheet = workbook.Worksheets[0]; 

 

                filestream.Close(); 

                file.Stream.Close(); 

            } 

 

        } 

 

        await Task.CompletedTask; 

    } 

 

 

To know more about the path in client side, please refer the below stack overflow link: https://stackoverflow.com/questions/1130560/get-full-path-of-a-file-with-fileupload-control  

 

We have prepared the sample based on your requirement. please find the sample here: https://www.syncfusion.com/downloads/support/forum/159780/ze/UploaderXlsIo470268572  

 

Regards, 

Sureshkumar P 

[Eric]

Hi Sureshkumar,

Thank you for this.

Maybe I started this the wrong way. Instead of focusing on the path and how to obtain in on the client side, I should had focused on just getting the data from the file, I do not care if it's on the client or server side.

It's this feasible?

Again, thanks a lot for you help and sorry for the trouble.

[Eric]

Looking forward to any tips on this.

[Sureshkumar P]

Hi Eric, 

 

Thanks for your update, 

 

As per our previous update, we cannot able to get the selected file root path in the server and client side. But we can get the saved path through WebRootPath as like below code. So, we suggest you use server-side service to save the selected file to get the saved file path to achieve your requirement.  

 

Please find the code example here: 

 [HttpPost("[action]")] 

        public async void Save(IList<IFormFile> chunkFile, IList<IFormFile> UploadFiles) 

        { 

            long size = 0; 

            try 

            { 

                // for chunk-upload 

                foreach (var file in chunkFile) 

                { 

                    var filename = ContentDispositionHeaderValue 

                                        .Parse(file.ContentDisposition) 

                                        .FileName 

                                        .Trim('"'); 

                    filename = hostingEnv.WebRootPath + $@"\{filename}"; 

                    size += file.Length; 

                     

                    if (!System.IO.File.Exists(filename)) 

                    { 

                        using (FileStream fs = System.IO.File.Create(filename)) 

                        { 

                            file.CopyTo(fs); 

                            fs.Flush(); 

                        } 

                    } 

                    else 

                    { 

                        using (FileStream fs = System.IO.File.Open(filename, FileMode.Append)) 

                        { 

                            file.CopyTo(fs); 

                            fs.Flush(); 

                        } 

                    } 

                    using (ExcelEngine excelEngine = new ExcelEngine()) 

                    { 

 

                        //Instantiate the Excel application object 

                        IApplication application = excelEngine.Excel; 

 

                        //Set the default application version 

                        application.DefaultVersion = ExcelVersion.Xlsx; 

 

                        //Load the existing Excel workbook into IWorkbook 

                        FileStream filestream = new FileStream(filename, FileMode.Open, FileAccess.Read); 

                        IWorkbook workbook = application.Workbooks.Open(filestream); 

 

                        //Get the first worksheet in the workbook into IWorksheet 

                        IWorksheet worksheet = workbook.Worksheets[0]; 

 

                        filestream.Close(); 

                    } 

                } 

            } 

            catch (Exception e) 

            { 

                Response.Clear(); 

                Response.StatusCode = 204; 

                Response.HttpContext.Features.Get<IHttpResponseFeature>().ReasonPhrase = "File failed to upload"; 

                Response.HttpContext.Features.Get<IHttpResponseFeature>().ReasonPhrase = e.Message; 

            } 

 

        } 

 

 

Please find the screen shot here: 

 

 

We have created the sample based on your requirement. please find the sample here: https://www.syncfusion.com/downloads/support/forum/159780/ze/BlazorApp1816845306  

 

Regards, 

Sureshkumar P 

[Eric]

Hi SureshKumar,

Thank you for this.

For several hours I was not being able to do anything with the data, always getting a System.ObjectDisposedException: 'IFeatureCollection has been disposed if I tried to do anything with the data (like save with a ApplicationDbContext context or sending it to another class somewhere else). At the end, changing the Save function, from void to Task did the trick and now it works.

Thanks again!

[Sureshkumar P]

Hi Eric, 

 

Thanks for your update. 

 

Regards, 

Sureshkumar P 

[Eric]

Hi Sureshkumar,

One quick thing to follow up from my issues, and hoping it can help you in any way in the future. I opened an issue request on GitHub for something totally unrelated to what we have discussed, however, while browsing other questions recently posted there, funny coincidently enough I found someone who had asked a question almost identical to the issue I was having, with always getting a System.ObjectDisposedException: 'IFeatureCollection when trying to use context with the database or other functions.

And their reply was:  "Never use async void for web APIs. Use async Task if you don't have any return parameters. Otherwise the server doesn't know when you're done and starts cleaning up immediately." 

The user had already mentioned that changing void to task had done the trick, but still could not understand why. GitHub Post

Hope this helps you somehow.

Best regards,

Eric

[Sureshkumar P]

Hi Eric, 

 

Thanks for your valuable suggestion.  

 

Yes, we should avoid async void. We only use async void for event handlers (or things that are logically event handlers). A controller action is not an event handler. Thus, we should use async Task. 

 

Regards, 

Sureshkumar P 

[Eric]

Hi Sureshkumar,

Right now everything is working great, however, if I try to change to controller to [Authorize], I always get a 401 response. Do you know how I can fix this?

Best regards,

Eric

[Sureshkumar P]

Hi Eric, 

 

Based on your shared information, we suspect that you have missed to add the UseAuthentication before the line of UseAuthorization when authorize the controller. We suggest you call the UseAuthentication to resolve the issue. 

 

To know more about the issue, then please refer the below stack overflow and Microsoft documentation link. 

 

Documentation links: 

1. https://docs.microsoft.com/en-us/aspnet/web-api/overview/security/authentication-filters
2. https://stackoverflow.com/questions/56185834/asp-net-core-api-always-returns-401-unauthorized-whenever-i-send-a-request-with

Regards, 

Sureshkumar P 

[Eric]

Hi Sureshkumar,

About the authentication and authorization, note that this component is far from being the only function on the website (actually right now it's just a tiny part of it). Everything else works fine regarding Authorization, or even Authorization for a specific role. 

In order to double check, I used the function in another controller that I had for other purposes (with several working tasks), and if I add a [AllowAnonymous] to the specific save task, it works. Otherwise, it fails. 

[Berly Christopher]

Hi Eric, 

We would like to inform you that the uploader component allows you to add additional header on file upload, and can be received in the server-side. You can achieve this behaviour using FileSelected / BeforeUpload event and its CurrentRequest argument by configure header as key-value pair.    

   

[index.razor]  

 

@using Syncfusion.Blazor.Inputs  

<SfUploader ID="UploadFiles">  

    <UploaderEvents FileSelected="onFileSelect"></UploaderEvents>  

    <UploaderAsyncSettings SaveUrl="api/SampleData/Save">  

    </UploaderAsyncSettings>  

</SfUploader>  

  

@code {  

  

    private void onFileSelect(SelectedEventArgs args)  

    {  

        var accessToken = "Basic test123";  

        args.CurrentRequest = new List<object> { new { Authorization = accessToken } };  

    }  

  

}  

[SampleDataController.cs]  

[HttpPost("[action]")]  

        public async void Save(IList<IFormFile> UploadFiles)  

        {  

            //to get authorization Header to handle save file on server side    

            var authorizationHeader = Request.Headers["Authorization"];  

            try  

            {  

                foreach (var file in UploadFiles)  

                {  

                    if (UploadFiles != null)  

                    {  

                        var filename = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName.Trim('"');  

                        filename = hostingEnv.WebRootPath + $@"\{filename}";  

                        if (!System.IO.File.Exists(filename))  

                        {  

                            using (FileStream fs = System.IO.File.Create(filename))  

                            {  

                                file.CopyTo(fs);  

                                fs.Flush();  

                            }  

                        }  

                        else  

                        {  

                            Response.Clear();  

                            Response.StatusCode = 204;  

                            Response.HttpContext.Features.Get<IHttpResponseFeature>().ReasonPhrase = "File already exists.";  

                        }  

                    }  

                }  

                Response.Headers.Add("ID", "Failure"); // Assign the custom data in the response header.  

            }  

            catch (Exception e)  

            {  

                Response.Clear();  

                Response.ContentType = "application/json; charset=utf-8";  

                Response.StatusCode = 204;  

                Response.HttpContext.Features.Get<IHttpResponseFeature>().ReasonPhrase = "File failed to upload";  

                Response.HttpContext.Features.Get<IHttpResponseFeature>().ReasonPhrase = e.Message;  

            }  

              

        }  

 

   

Please find the sample from the below link.  

Sample Link: https://www.syncfusion.com/downloads/support/directtrac/281062/ze/Uploader_bearer_token-229593712   

   

Hope this will help for your requirement, please share the details to us if you need any further assistance on this. 

Regards,  

Berly B.C 

[Robert Förster]

Hi,

you dont need to know where the file is on your machine if you use the memorystream.

protected async Task OnChange(UploadChangeEventArgs args)

    {

        foreach (var file in args.Files)

        {

            Console.WriteLine($"Uploaded Excel.");

            

            using (ExcelEngine excelEngine = new ExcelEngine())

            {

                //Instantiate the Excel application object

                IApplication application = excelEngine.Excel;

                //Set the default application version

                application.DefaultVersion = ExcelVersion.Xlsx;

                var filestream = file.Stream;

                // Set the position to the beginning of the stream.

                filestream.Seek(0, SeekOrigin.Begin);

                //Load the existing Excel workbook into IWorkbook

                IWorkbook workbook = application.Workbooks.Open(filestream);

                Console.WriteLine("WorkSheetsCount: " + workbook.Worksheets.Count);

                //Get the first worksheet in the workbook into IWorksheet

                IWorksheet worksheet = workbook.Worksheets[0];

                Console.WriteLine("WorksheetsCount: " + worksheet.Rows.Count());

                var excelRows = worksheet.ExportData<ExcelApplicationImportDto>(2,1,10000,19);

                // Set

                exceldata = excelRows;

                file.Stream.Close();

            }

        }

        await Task.CompletedTask;

    }

[Vignesh Srinivasan]

Hi Eric, 

 

Thanks for sharing your solution. 

 

Please let us know if you need any further assistance. 

 

Regards, 

 

Vignesh Srinivasan. 

[Christoph]

Dear all,

the problem Eric ran into is still not solved (as of 20.4). JWT Authentication is not automatically passed during "Save" or "Delete" by the <SfUploader> component.

To get it working, you need to read the .NET auth-token:

@inject Microsoft.AspNetCore.Components.WebAssembly.Authentication.IAccessTokenProvider TokenProvider

<SfUploader ID="FileUpload" AllowedExtensions=".xls, .xlsx, .csv" AllowMultiple="false" AutoUpload="false">
  <UploaderEvents FileSelected="onFileSelect"></UploaderEvents>
  <UploaderAsyncSettings SaveUrl="api/UCTUpload/save" RemoveUrl="api/UCTUpload/delete"></UploaderAsyncSettings>
</SfUploader>

@code {
  private async Task onFileSelect(SelectedEventArgs args)
  {
    var accessTokenResult = await TokenProvider.RequestAccessToken();
    if (accessTokenResult.TryGetToken(out var token))
    {
      args.CurrentRequest = new List<object> { new { Authorization = "Bearer " + token.Value } };
    }
  }
}

Thus, you don't need to make the controller [AllowAnonymous] and handle the Authorization-Header manually as in Christopher's example above.

TBH, this should be handled by the <SfUploader> component.

Regards,
Christoph

[UdhayaKumar Duraisamy]

You can refer to the below Syncfusion forum regarding your requirement.

https://www.syncfusion.com/forums/157977/file-upload-how-to-pass-custom-header-to-the-documentupload-controller-when-saving-a-file?reply=NtNtQK