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

问题描述

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

Office Interop 在此处不起作用，因为 Azure Function 无法使用 Office.

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

鉴于我不知道在 Azure Functions 中临时存储 excel 文件的方法，我使用了 Azure 文件存储.

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

我测试了 Azure 文件存储中的 excel 文件是否正常工作，但是，当我尝试从 MemoryStream 打开 OpenXML.SpreadsheetDocument 时，我收到错误消息，表明文件已损坏.

如果我尝试打开传递文件 Uri (

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

<块引用>

文件包含损坏的数据".

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

<块引用>

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

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

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

#r "Excel.dll"#r "System.Data"使用 System.Net；使用 System.IO；使用 Excel；使用 System.Data;公共静态 HttpResponseMessage 运行(HttpRequestMessage req，TraceWriter 日志){log.Info($"C# HTTP 触发函数处理了一个请求.RequestUri={req.RequestUri}");WebClient 客户端 = new WebClient();byte[] buffer = client.DownloadData("http://amor-webapp-test.azurewebsites.net/Content/abcdefg.xls");MemoryStream 流 = new MemoryStream();stream.Write(buffer, 0, buffer.Length);流.位置= 0;IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);数据集结果 = excelReader.AsDataSet();for (int i = 0; i 

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

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

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

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.

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

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

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.

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.

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

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

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

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

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

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

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