TypeScript (Office Script) to force image compression in excel workbook-CodePudding

Coding gurus

Is it possible to invoke something similar to this VBA code in excel online via Office Script?

Sub test()

    Dim wsh As Worksheet
    Dim shp As Shape

    Set wsh = Worksheets("Sheet1")

    For Each shp In wsh.Shapes
        shp.Select
        SendKeys "%e", True
        SendKeys "~", True
        Application.CommandBars.ExecuteMso "PicturesCompress"
    Next shp

End Sub

Basically, I am dynamically updating an excel online excel sheet with base 64 images into a shape container but I want the downloaded version of the sheet to automatically reduce the quality of the image, I know the 'compress picture' feature is not available in excel online so am looking for a workaround so that when the downloaded excel sheet opens it automatically reduces image quality for all photos.

I have tried changing the preferences for the excel online version of the sheet (in offline mode) to lower quality images but the parameter seems to revert as soon as it goes back online...

Thanks in advance for any and all help!

CodePudding user response：

Ok, pay attention. Also know that I have tested this and it worked for me.

Also, I have no idea of your knowledge on the below so I'm gunna treat you like a muppet and give you step by step instructions.

Go into the Azure Portal and create a new .NET Azure Function and call it ChangeImageQuality. Now, in the code view, make sure you open up the bottom drawer so you can see the logs.

Before you copy in any code, you need to create a new file in the solution called function.proj and it needs to be saved with this content ...

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <TargetFramework>netstandard2.0</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="SixLabors.ImageSharp" Version="2.0.0" /> 
    </ItemGroup>
</Project>

JSON Payload

{
  "Image": "@{variables('Base 64 Image')}",
  "Quality": 10
}

Naturally, you just need to change the variable to your base 64 instance along with the quality you want and then pass the response body into your Run script step.

Like I said, I tested that and it worked perfectly.