Wolfram Cloud Connector for Excel

Anthony Zupnik, Wolfram Research

Join the Conversation #WolframTechConf

Table of Contents

What is CloudConnector for Excel?

Installing CloudConnector for Excel

The Wolfram Function

The WolframAPI Function

Live Coding

Other ways to interact with Excel

What is CloudConnector for Excel?

A plugin for Excel on Windows

Calls the Wolfram Cloud from Excel

Native Wolfram Language

Wolfram APIs

Available to download now

Installing CloudConnector for Excel

https://www.wolfram.com/cloud-connector-for-excel/

Preferences

Testing

The Wolfram Function

Call native Wolfram Language

Requires a Cloud ID

Examples

Current Date

=Wolfram("CurrentDate")

Random Data

=Wolfram(“RandomWord”,5)

DayRange

DayRange[Now,DayPlus[Now, 10],"BusinessDay"]

=Wolfram(“DayRange[Now, DayPlus[Now, 10], “”BusinessDay””]&”)

DayRange with a Reference

=Wolfram(“DayRange[Now, DayPlus[Now, #], “”BusinessDay””]&”, B2)

The WolframAPI Function

Deploy API Functions and access from Excel

Can use Public and Private APIs

Example Spreadsheet

SystemOpen[FileNameJoin[{NotebookDirectory[],"Spreadsheets","wolfram-cloud-connector-for-excel-sample.xlsx"}]]

Business Day Example

CloudDeploy[APIFunction[{"x"->"Integer"},DayRange[Now,DayPlus[Now, #x],"BusinessDay"]&,AllowedCloudExtraParameters->All],"MyDayRangeFunction",Permissions->"Public"]

=WolframAPI(“MyDayRangeFunction”,Parameter(“x”,C2))

Employee Rota

CloudDeploy[
	APIFunction[
		{
			"StartDate"->"ExcelDate"|"ComputedDate",
			"EndDate"->"ExcelDate"|"ComputedDate",
			"Names"->RepeatingElement["String"]
		},
		With[
			{names=#Names,days=DayRange[#StartDate,#EndDate,"BusinessDay",HolidayCalendar->(Entity["United Kingdom", Entity["Country","UnitedKingdom"], "Entity[\"Country\", \"UnitedKingdom\"]", "country"])]},
			Table[{days[[i]]["DayName"],days[[i]],names[[Mod[i,Length[names]]+1]]},{i,Length[days]}]
		]&,
		AllowedCloudExtraParameters->All
	],
	"WTC2019/EmployeeRota",
	Permissions->"Public"
]

SystemOpen[FileNameJoin[{NotebookDirectory[],"EmployeeRota","EmployeeRota.xlsx"}]]

Building an Example

A Documentation Walkthrough

https://reference.wolfram.com/language/CloudConnectorForExcel/tutorial/Overview

Other ways to interact with Excel

Import/Export

Import xlsx

haloweenSheet=FileNameJoin[{NotebookDirectory[],"spreadsheets","excel-halloween-costume-donations-chart.xlsx"}];

SystemOpen[haloweenSheet]

TextGrid[Import[haloweenSheet][[1]]]

Import[haloweenSheet,"Images"]

Export xlsx

img=PieChart[Range[5],ChartStyle->"Pastel"]

Export["image.xlsx",{Range[5]}, "Images"->img]

SystemOpen["image.xlsx"]

ExcelLink

Excel -> Mathematica

SystemOpen[FileNameJoin[{NotebookDirectory[],}]]

Mathematica -> Excel

<<ExcelLink`

ExcelWrite["A1","=Sum(1,2,3)"]

ExcelRead["A1"]

NetLink

<<NetLink`

LoadNETAssembly["Microsoft.Office.Interop.Excel"];
LoadNETAssembly["System.Runtime.InteropServices"];

$ExcelInstance=NETNew["Microsoft.Office.Interop.Excel.ApplicationClass"];

$ExcelInstance@Version

$ExcelInstance@Workbooks@Add[]

ExcelWriteNew[rng_String,value_]:=
	NETBlock[
            Module[{range},
                range=$ExcelInstance@Range[rng];
	        	range@Value2=value]
        	]

ExcelWriteNew["A1","=5+5"]

ExcelReadNew[rng_String]:=$ExcelInstance@Range[rng]@Value2

ExcelReadNew["A1"]

ExcelDriver Paclet

PacletDirectoryAdd["C:\\Users\\anthonyz\\Desktop\\exceldriver"]

<<ExcelDriver`

InstallExcel[]

wb=ExcelNewWorkbook[]

ExcelDriverWrite["A1","=1+10"]

ExcelDriverRead["A1"]

ExcelDriverWrite[{{1,2},{3,3}},"=2+2"]

Concluding Remarks

CloudConnector allows for native WL and APIFunction calls

Available to download now

Multiple way to interact with Microsoft Excel

Blog next week???

Thank you for listening!