使用Azure应用服务从Excel文件读取数据的推荐方法?

问题描述

背景
我有一个旧站点，该站点允许授权用户上传产品数据等的Excel电子表格.然后该站点读取Excel工作表并将数据解压缩到SQL Server中.这是一个旧站点，它使用OLE.老了，但是可以用.

Background
I have a legacy site that allows authorised users to upload an Excel spreadsheet of product data etc. The site then reads the Excel worksheets and unpacks the data into SQL server.It's an old site and it uses OLE. Old but it works.

问题
我最近将该网站发布到了Azure App Service ，但是从Excel读取的代码的现有部分不起作用(因为Azure没有正确的驱动程序).

The problem
I've recently published the site to an Azure App Service but the existing section of my code that reads from Excel doesn't work (as Azure doesn't have the right driver).

问题
我很高兴重写此部分代码，但是使用Azure App Service从Excel读取的正确或推荐方法是什么?我并不是在问MIGHT的工作方式，我只是对正确的方法感兴趣.

The question
I'm happy to rewrite this section of code but what is the CORRECT or RECOMMENDED approach for reading from Excel using an Azure App Service?I'm not asking about ways that MIGHT work I'm only interested in the RIGHT way to do this.

推荐"是指:

• 不是不必要的复杂.保持简单.
• 将来可能会保留来自Microsoft的支持

我已经研究了此问题，但未能找到明确的最佳方法.如果您有不同方法的经验或知识，请与我们分享有关最佳方法的结论，我们将不胜感激.

I have researched this issue but have not been able to find a clear statement of the best way to do this. If you have experience or knowledge of different ways of doing this I'd be grateful if you could share your conclusion about the BEST way to do this.

推荐答案

应该有很多方法可以实现，在这里我列出了2个，如下所示:

There should be many ways you can achieve this, and here I list 2 as below:

1.使用由MS发布的 DocumentFormat.OpenXml ，但这有点复杂.演示代码位于此处.

1.Use the DocumentFormat.OpenXml, which is published by MS, but it's a little complicated. The demo code is here.

2.使用 ExcelDataReader ，它非常简单并同时支持.xls and .xlsx.您可以参考文章进行操作(请注意，IsFirstRowAsColumnNames属性已被放弃，您可以在下面查看我的代码以进行此更改).

2.Use ExcelDataReader, which is very simple and supports both .xls and .xlsx. You can refer to this article to do it(note that IsFirstRowAsColumnNames property is abandoned, you can see my code below for this change).

然后我用第二种方法ExcelDataReader编写了一个演示.出于测试目的，我将excel上载到了azure Web应用程序目录中，如下所示:

And I write a demo with the 2nd method ExcelDataReader.Just for test purpose, I uploaded the excel to the azure web app directory like below:

以下是excel内容:

And the following is the excel content:

步骤1:创建一个asp.net MVC项目，然后通过nuget软件包管理器安装最新版本的ExcelDataReader和ExcelDataReader.DataSet.

Step 1: Create an asp.net MVC project, and then install the latest version ExcelDataReader and ExcelDataReader.DataSet via nuget package manager.

第2步:在您的项目中创建一个用于读取Excel文件的ExcelData.cs文件:

Step 2: Create a ExcelData.cs file in your project which used to read excel file:

第3步:在ExcelData.cs中编写以下代码:

Step 3: Write the following code in ExcelData.cs:

using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace WebApplication42
{
    public class ExcelData
    {
        string _path;
        public ExcelData(string path)
        {
            _path = path;
        }

        public IExcelDataReader GetExcelReader()
        {
            FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);
            IExcelDataReader reader = null;
            try
            {
                if (_path.EndsWith(".xls"))
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                if (_path.EndsWith(".xlsx"))
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }

                return reader;
            }
            catch (Exception)
            {
                throw;
            }
        }

        //read the sheets name if you need
        public IEnumerable<string> GetWorksheetNames()
        {
            var reader = this.GetExcelReader();
            var workbook = reader.AsDataSet();
            var sheets = from DataTable sheet in workbook.Tables select sheet.TableName;
            return sheets;
        }

        //read data in a specified sheet
        public IEnumerable<DataRow> GetData(string sheet)
        {

            var reader = this.GetExcelReader();
            var workSheet = reader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    //indicates if use the header values
                    UseHeaderRow = true
                }

            }).Tables[sheet];

            var rows = from DataRow a in workSheet.Rows select a;
            return rows;
        }    

    }
}

第4步:在控制器中，调用read excel方法:

Step 4: In the controller, call the read excel method:

        public ActionResult Excels()
        {
            ViewBag.Message = "the data from excel:";
            string data = "";

            //your excel path after uploaded, here I hardcoded it for test only
            string path = @"D:\home\site\wwwroot\Files\ddd.xls";
            var excelData = new ExcelData(path);
            var people = excelData.GetData("sheet1");

            foreach (var p in people)
            {
                for (int i=0;i<=p.ItemArray.GetUpperBound(0);i++)
                {
                    data += p[i].ToString()+",";
                }

                data += ";";
            }

            ViewBag.Message += data;

            return View();
        }

第5步:发布到天蓝色后，启动网站并查看结果->读取excel中的所有数据:

Step 5: After publish to azure, launch the site and see the results-> all the data in excel are read:

这篇关于使用Azure应用服务从Excel文件读取数据的推荐方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！