CODEWITHSUNDEEP
.net-coreasp.net-apicontroller
Kaustubh Badrike
Kaustubh Badrike

Reputation: 565

How to process Excel file in memory?

I am trying to create an API that will accept the representation of an Excel file from the client. I wish to return a List<List<string>> as JSON array after processing the first sheet. However, I cannot write the file to disk, and all processing must happen in-memory. What are the ways in which this can be achieved?

I've tried referring to various solutions on the internet but all of them involve writing the file to disk and then using that file for further processing. I'm open to solutions that involve

  1. Accepting base-64 representation of the file from the POST request body
  2. Accepting file as part of multipart/form-data request
  3. Any other standard request formats that accept files

The only condition is that the API should return a JSON array representation of the spreadsheet.

Upvotes: 2

Views: 2348

Answers (1)

Umesh Lohote
Umesh Lohote

Reputation: 66

Here I am sending a file as part of multipart/form-data request to the API which written in .NET core.

which support .xlsx , .xls and .csv format

use ExcelDataReader and ExcelDataReader.DataSet NuGet packages for reading excel and convert in the dataset.

Here one problem i faced and solution in .NET core.

By default, ExcelDataReader throws a NotSupportedException "No data is available for encoding 1252." on .NET Core.

To fix, add a dependency to the package System.Text.Encoding.CodePages and then add code to register the code page in starting of API

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); This is required to parse strings in binary BIFF2-5 Excel documents encoded with DOS-era code pages. These encodings are registered by default in the full .NET Framework, but not on .NET Core.

       public ActionResult ExcelOrCsvToArray()
        {
            if (Request.Form.Files.Count > 0)
            {
                IFormFile file = Request.Form.Files[0];
                string fileName = file.FileName;
                string fileContentType = file.ContentType;
                System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
                Stream stream = file.OpenReadStream();
                try
                {
                    if (fileName.EndsWith(".csv"))
                    {
                        using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
                        {
                            var result = SetAsDataSet(reader);
                            DataTable table = result.Tables[0];
                            return new OkObjectResult(table);
                        }
                    }
                    else
                    {
                        using (var reader = ExcelReaderFactory.CreateReader(stream))
                        {
                            var result = SetAsDataSet(reader);
                            DataTable table = result.Tables[0];
                            return new OkObjectResult(table);
                        }
                    }
                }
                catch (Exception e)
                {
                    return new BadRequestObjectResult(e);
                }
            }
            else
            {
                return new BadRequestResult();
            }
        }

        private DataSet SetAsDataSet(IExcelDataReader reader)
        {
            var result = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true,
                }
            });
            return result;
        }

Upvotes: 3

Related Questions