如何处理内存中的Excel文件? [英] How to process Excel file in memory?

问题描述

我正在尝试创建一个API，该API将从客户端接受Excel文件的表示形式.我希望返回一个List< List< string>>.处理完第一张工作表后作为JSON数组.但是，我无法将文件写入磁盘，并且所有处理必须必须在内存中进行 .可以通过什么方式实现这一目标?

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?

我曾尝试参考Internet上的各种解决方案，但所有这些解决方案都涉及将文件写入磁盘，然后使用该文件进行进一步处理.我对涉及的解决方案持开放态度

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. 从POST请求正文中接受文件的base-64表示形式
2. 接受文件作为multipart/form-data请求的一部分
3. 任何其他接受文件的标准请求格式

唯一的条件是API应该返回电子表格的JSON数组表示形式.

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

推荐答案

在这里，我正在将文件作为multipart/form-data请求的一部分发送到以.NET Core编写的API.

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

支持.xlsx，.xls和.csv格式

which support .xlsx , .xls and .csv format

使用ExcelDataReader和ExcelDataReader.DataSet NuGet包读取Excel并在数据集中进行转换.

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

这是我所遇到的一个问题，并且是.NET Core中的解决方案.

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

默认情况下，ExcelDataReader引发NotSupportedException没有数据可用于编码1252."在.NET Core上.

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

要解决此问题，请将依赖项添加到包System.Text.Encoding.CodePages中，然后添加代码以在API的开头注册代码页

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);这是解析使用DOS时代代码页编码的二进制BIFF2-5 Excel文档中的字符串所必需的.默认情况下，这些编码是在完整的.NET Framework中注册的，而不是在.NET Core中注册的.

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;
        }

这篇关于如何处理内存中的Excel文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！