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

问题描述

我正在尝试创建一个 API，该 API 将接受来自客户端的 Excel 文件的表示.我希望返回一个 List>处理第一个工作表后作为 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?

我尝试参考互联网上的各种解决方案，但所有这些都涉及将文件写入磁盘，然后使用该文件进行进一步处理.我愿意接受涉及

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 核心编写的 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 核心中遇到的一个问题和解决方案.

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屋！