问题描述
我需要使用Azure Logic应用程序从Excel电子表格中解析和提取列信息
I need to parse and extract Column information from an Excel spreadsheet using Azure Logic Apps
我已经为Logic App设置了从Outlook检索最新的未读电子邮件的功能.此外,我的Logic App会执行FOR EACH读取所有附件(从未读的电子邮件中读取),并确保它们是Excel文件(基于文件扩展名).
I have already setup the ability for my Logic App to retrieve the latest unread Emails from my Outlook. Also, my Logic App does a FOR EACH to read all attachments (from unread emails) and make sure they are Excel files (based on filename extension).
我有一个基本的Excel文件,其中包含3列产品,说明,价格",我需要解析每行(仅产品和价格)列.
I have a basic Excel file that contains 3 columns "Product, Description, Price" I need to parse each row (only Product and Price) column.
我将添加将解析的内容存储到Azure托管的SQL表中的功能.
I will add the ability to store that parsed into into my SQL table hosted on Azure.
推荐答案
在这里,您也可以使用HTTP请求.
Here you go, you could use HTTP Request as well.
using ExcelDataReader;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Extensions.Logging;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Nancy.Json;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace ConvertExcelToJSon
{
public static class Function1
{
[FunctionName("ConvertToJson")]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
dynamic data = JsonConvert.DeserializeObject(requestBody);
string blobName = data?.blobName;
string[] splitBlob = blobName.Split('/');
Stream blob = await GetBlobStreamAsync(splitBlob[1], splitBlob[2] + "/" + splitBlob[3]);
DataSet ds = CreateDataSet(blob);
List<Simple> simpleList = new List<Simple>();
foreach (DataTable table in ds.Tables)
{
return (ActionResult)new OkObjectResult(DataTableToJSON(table));
}
return blobName != null
? (ActionResult)new OkObjectResult($"Hello, {blobName}")
: new BadRequestObjectResult("Please pass a name on the query string or in the request body");
}
public static string DataTableToJSON(DataTable table)
{
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
foreach (DataRow row in table.Rows)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
foreach (DataColumn col in table.Columns)
{
dict[col.ColumnName] = (Convert.ToString(row[col]));
}
list.Add(dict);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
return serializer.Serialize(list);
}
public static string AppSetting(this string Key)
{
string ret = string.Empty;
if (Environment.GetEnvironmentVariable(Key) != null)
{
ret = Environment.GetEnvironmentVariable(Key);
}
return ret;
}
public static async Task<Stream> GetBlobStreamAsync(string containerName, string blobName)
{
Stream myBlob = new MemoryStream();
if (CloudStorageAccount.TryParse("AzureWebJobsStorage".AppSetting(), out CloudStorageAccount storageAccount))
{
CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = cloudBlobClient.GetContainerReference(containerName);
CloudBlob myBloab = container.GetBlobReference(blobName);
await myBloab.DownloadToStreamAsync(myBlob);
myBlob.Seek(0, SeekOrigin.Begin);
}
return myBlob;
}
public static DataSet CreateDataSet(Stream stream)
{
DataSet ds;
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
}
});
return ds;
}
}
}
这篇关于如何在Azure Logic应用中解析Excel电子表格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!