How to read xls file in c#?

Question

Net core API. I am trying to read xls file using ExcelDataReader. In ExcelDataReader I do not want to pass file path but I want to pass stream. I tried below

 [HttpPost("import")]
 public async Task<IActionResult> Import(IFormFile formFile)
 {
   excelReader.IExcelDataReader reader;

            if (formFile == null || formFile.Length <= 0)
            {
                return  Ok("formfile is empty");
            }
            using (var stream = new MemoryStream())
            {
                await formFile.CopyToAsync(stream);
                reader = ExcelReaderFactory.CreateReader(stream);
            }
 }

When I execute I get exception

No data is available for encoding 1252. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.

I am not sure what would be the root cause of the issue? Can someone help me here to fix the issue? Any help would be appreciated. Thank you

Answer 1

You need to add a dependency to the package System.Text.Encoding.CodePages and register the encoding by adding this line to your code:

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

See this note on github

Answer 2

You can read both xls and xlsx.

        [HttpPost]
        public async Task<IActionResult> Import(IFormFile formFile)
        {
            excelReader.IExcelDataReader reader;
            if (formFile == null || formFile.Length <= 0)
            {
                return Ok("formfile is empty");
            }
            var path = Path.GetExtension(formFile.FileName);
            System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
            using (var stream = new MemoryStream())
            {
                await formFile.CopyToAsync(stream);
                if (path.EndsWith(".xls"))
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                if (path.EndsWith(".xlsx"))
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
            }
        }