Download an excel file and read content with azure functions

Question

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.

Answer 1

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.

Answer 2

If you do need to save a temporary file, Azure Functions has a %TEMP% environment variable with a path to a temporary folder. This is a folder that is local to the vm that runs your function and will not be persisted.

However, saving the file locally / in Azure Files is unnecessary. You should be able to get the stream from the response to your get request and pass it straight to OpenXML.

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(originalExcelUrl);
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
using (Stream stream = response.GetResponseStream()) 
{
    var doc = SpreadsheetDocument.Open(stream, true);
    // etc
}