Unfortunately, Logicapps is not capable to work on macro included excel files. I think they did this on purpose but why don’t let us choose.
Downloading file from SharePoint online with .netcore is another headache. Microsoft developed a temporary solution, it works, but I didn’t used it. Instead, I copied file from SharePoint to azure storage with Logic apps easily.
Anyway, I had to process a data in excel and update SQL data.
So I developed scripts as POC. I am publishing it just for giving an example how to use. For professional purposes, you need to update code with exception handling, put connection parameters out of code etc.
Function app basically works with an HTTP trigger.
I used DocumentFormat.OpenXML to uses excel file. Off course you can choose other methods. My data were in specific cells. so I preferred this one. Just don’t forget that the easiest way to pull data is to get them as a string and convert manually. Otherwise, you have to deal with Excel cell formattings. Also in my case, cell.cellType function was returning null.
Happy coding!
#r "Newtonsoft.Json"
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.File;
using Microsoft.WindowsAzure.Storage.Auth;
public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string name = req.Query["name"];
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
dynamic data = JsonConvert.DeserializeObject(requestBody);
name = name ?? data?.name;
Console.WriteLine("start");
string accountName = "xxx";
string accountKey = "xxx";
StorageCredentials credentials = new StorageCredentials(accountName, accountKey);
CloudStorageAccount storageAccount = new CloudStorageAccount(credentials, true);
CloudFileShare share = fileClient.GetShareReference("xxx");
CloudFileDirectory root = share.GetRootDirectoryReference();
CloudFileDirectory dir = root.GetDirectoryReference("xxx");
CloudFile file = dir.GetFileReference("xxx");
Stream filestream = new MemoryStream();
//Download the file to memory stream
file.DownloadToStreamAsync(filestream).Wait();
//process excel
processExcel(filestream);
return name != null
? (ActionResult)new OkObjectResult($"Hello, {name}")
: new BadRequestObjectResult("Please pass a name on the query string or in the request body");
}
private static void processExcel(Stream stream)
{
// Open a SpreadsheetDocument based on a stream.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, false);
//read excel
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "xxx").FirstOrDefault();
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(theSheet.Id);
Worksheet worksheet = worksheetPart.Worksheet;
string connectionString = @"xxx";
//connect to SQL
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
string cellValue;
for (uint i = 1; i < 10; i++)
{
Cell celldate = GetCell(worksheet, "D", i + 9);
cellValue = string.Empty;
cellValue = celldate.InnerText;
String datestr = cellValue;
Cell cell = GetCell(worksheet, "E", i + 9);
cellValue = string.Empty;
cellValue = cell.InnerText;
String value = cellValue;
String query = "xxx";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@date", date);
command.Parameters.AddWithValue("@value", value);
int result = command.ExecuteNonQuery();
}
}
// Close the document handle.
spreadsheetDocument.Close();
}
private static Cell GetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
Row row = GetRow(worksheet, rowIndex);
if (row == null)
return null;
return row.Elements<Cell>().Where(c => string.Compare
(c.CellReference.Value, columnName +
rowIndex, true) == 0).First();
}
// Given a worksheet and a row index, return the row.
private static Row GetRow(Worksheet worksheet, uint rowIndex)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
}