下载Excel文件并使用Azure函数读取内容 [英] Download an excel file and read content with azure functions

问题描述

我正在尝试编写C#Azure函数以使用OpenXml-SDK下载并打开excel文件.

I am trying to write a C# Azure Function to download and open an excel file using the OpenXml-SDK.

Office Interop在这里不起作用，因为Azure功能无法使用Office.

Office Interop doesn't work here because office is not available to the Azure Function.

我正在尝试使用OpenXml-SDK打开和读取文件，该文件似乎需要保存文件的路径，而不是URL或从远程URL下载的Stream.

I am trying to use OpenXml-SDK to open and read the file which seems to require a path to the saved file and not the url or a Stream downloaded from the remote url.

鉴于我不知道将Excel文件临时存储在Azure Functions中的方法，因此我使用了Azure File Storage.

Given I don't know of a way to temporary store the excel file in Azure Functions, I used Azure File Storage.

我将excel文件从url上传到Azure文件存储，但是我无法使用OpenXML-SDK打开excel文件.

I uploaded the excel file from the url to Azure File Storage, however I cannot open the excel file with OpenXML-SDK.

我测试了Azure File Storage中的excel文件是否正常运行，但是，当我尝试从MemoryStream打开OpenXML.SpreadsheetDocument时，出现错误，表明文件已损坏.

I tested the excel file in Azure File Storage is working, however, when I try to open the OpenXML.SpreadsheetDocument form a MemoryStream I get error indicating the file is corrupt.

如果我尝试通过文件Uri( https://docs.microsoft.com/zh-cn/azure/storage/storage-dotnet-how-to-use-files#develop-with-file-存储)，则地址超过了260个字符的限制.

If I try to open the SpreadsheetDocument passing the file Uri (https://docs.microsoft.com/en-us/azure/storage/storage-dotnet-how-to-use-files#develop-with-file-storage) then the address passes the 260 character limit.

我愿意使用OpenXML以外的库，理想情况下，我希望不必存储excel文件.

I'm open to using a library other than OpenXML and ideally I would prefer not to have to store the excel file.

推荐答案

Open XML SDK在Azure Function中可以正常工作.我对它进行了测试.这是完整的代码.

Open XML SDK works fine in Azure Function. I tested it on my side. Here is the full code.

#r "DocumentFormat.OpenXml.dll"
#r "WindowsBase.dll"

using System.Net;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public static HttpResponseMessage Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info($"C# HTTP trigger function processed a request. RequestUri={req.RequestUri}");

    WebClient client = new WebClient();

    byte[] buffer = client.DownloadData("http://amor-webapp-test.azurewebsites.net/Content/hello.xlsx");
    MemoryStream stream = new MemoryStream();
    stream.Write(buffer, 0, buffer.Length);
    stream.Position = 0;
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false))
    {
        WorkbookPart workbookPart = doc.WorkbookPart;
        SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        SharedStringTable sst = sstpart.SharedStringTable;

        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        Worksheet sheet = worksheetPart.Worksheet;

        var cells = sheet.Descendants<Cell>();
        var rows = sheet.Descendants<Row>();

        log.Info(string.Format("Row count = {0}", rows.LongCount()));
        log.Info(string.Format("Cell count = {0}", cells.LongCount()));

        // One way: go through each cell in the sheet
        foreach (Cell cell in cells)
        {
            if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
            {
                int ssid = int.Parse(cell.CellValue.Text);
                string str = sst.ChildElements[ssid].InnerText;
                log.Info(string.Format("Shared string {0}: {1}", ssid, str));
            }
            else if (cell.CellValue != null)
            {
                log.Info(string.Format("Cell contents: {0}", cell.CellValue.Text));
            }
        }
    }

    return req.CreateResponse(HttpStatusCode.OK, "Hello ");
}

要使用Open XML，请确保已在函数文件夹下创建了bin文件夹，并已将DocumentFormat.OpenXml.dll和WindowsBase.dll上载到其中.

To use Open XML, please make sure you have created a bin folder under your function folder and uploaded DocumentFormat.OpenXml.dll and WindowsBase.dll to it.

文件包含损坏的数据".

"File contains corrupted data".

您是否尝试过另一个Excel文件来检查问题是否与特定的Excel文件有关.我建议您创建一个新的简单excel，以再次测试您的代码.

Have you tried another excel file to check whether the issue is related to specific excel file. I suggest you create a new simple excel to test your code again.

它对我的文件不起作用，并带有相同的文件包含损坏的数据"消息."

"It didn't work on my file with the same "File contains corrupted data" message. "

我下载了您的excel文件，发现它是excel文件的较旧版本(.xls).

I download your excel file and found that it is a older version(.xls) of excel file.

要修复该异常，您可以将excel转换为最新版本(.xlsx)或选择另一个excel解析库. ExcelDataReader 可以适用于任何版本的excel文件.您可以通过搜索"ExcelDataReader"来使用NuGet安装此库.以下是如何解析.xls格式的excel文件的示例代码.我在Azure Function上对其进行了测试，效果很好.

To fixed the exception, you could convert the excel to latest version(.xlsx) or choose another excel parse library. ExcelDataReader could work for any versions of excel file. You could install this library using NuGet by searching 'ExcelDataReader'. Following is the sample code of how to parse .xls format excel file. I tested it on Azure Function, it did worked fine.

#r "Excel.dll"
#r "System.Data"

using System.Net;
using System.IO;
using Excel;
using System.Data;

public static HttpResponseMessage Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info($"C# HTTP trigger function processed a request. RequestUri={req.RequestUri}");

    WebClient client = new WebClient();

    byte[] buffer = client.DownloadData("http://amor-webapp-test.azurewebsites.net/Content/abcdefg.xls");
    MemoryStream stream = new MemoryStream();
    stream.Write(buffer, 0, buffer.Length);
    stream.Position = 0;

    IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

    DataSet result = excelReader.AsDataSet();

    for (int i = 0; i < result.Tables.Count; i++)
    {
        log.Info(result.Tables[i].TableName +" has " + result.Tables[i].Rows.Count + " rows.");
    }

    return req.CreateResponse(HttpStatusCode.OK, "Hello ");
}

请在执行上层代码之前将"Excel.dll"文件添加到函数的bin文件夹中.

Please add "Excel.dll" file to the bin folder of your function before executing upper code.

这篇关于下载Excel文件并使用Azure函数读取内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！