CODEWITHSUNDEEP
sql-serverexcelazureazure-web-app-service
Mark Challice
Mark Challice

Reputation: 489

Recommended way to read data from an Excel file using an Azure App Service?

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.

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).

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.

By "recommendeded" I mean:

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.

Upvotes: 6

Views: 6104

Answers (2)

Ivan Glasenberg
Ivan Glasenberg

Reputation: 29985

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

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

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).

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:

And the following is the excel content:

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

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

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

    }
}

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

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

Upvotes: 3

Gaurav Madaan
Gaurav Madaan

Reputation: 499

So, I am using https://github.com/dotnetcore/NPOI for Excel Import and have tested on Azure App Service and it is really good. I have tested by importing 50,000 records successfully. But beware, if you want to import about a 100 thousand records, you may get request timeout error as for the long running tasks, one should rather create web jobs/functions. Keep this in mind Azure App Service has a requestTimeout limit of 230s. Considering the following links before you select your implementation will be good.

https://feedback.azure.com/forums/169385-web-apps/suggestions/19309957-allow-a-request-timeout-of-more-then-3-8-minutes

Azure ASP .net WebApp - 500 Error - The request timed out

Upvotes: 0

Related Questions