Excel Tasks

This article provides information on how to use the out-of-the-box task library components within MSPComplete

Tasks

• Convert To HTML
• Convert To Image
• Convert To PDF
• Populate Excel Template with CSV Data
• Set Cell Values

Convert To HTML

Converts a Base64 encoded Excel worksheet into HTML format as a string

Inputs

• ExcelWorkbook

  Base64 encoded bytes, using [Convert]::ToBase64String((Get-Content -Encoding Byte -Path FILE_PATH))

• WorksheetName

  Single string; optional

Outputs

• Html

  A string representing the Excel worksheet in HTML format

Version: 1.0

Date: 08 January 2019

Back to top

Convert To Image

Converts a Base64 encoded Excel worksheet into a Base64 encoded image

Inputs

• ExcelWorkbook

  Base64 encoded bytes, using [Convert]::ToBase64String((Get-Content -Encoding Byte -Path FILE_PATH))

• WorksheetName

  Single string; optional

• ImageFormat

  Single string; optional, valid values are jpg, bmp, png, gif, jpeg, tiff. The default value is jpg

Outputs

• Image

  Base64 encoded bytes

  To convert back to a image file, use [Convert]::FromBase64String($Image) | Set-Content -Encoding Byte -Path FILE_PATH

Version: 1.0

Date: 08 January 2019

Back to top

Convert To PDF

Converts a Base64 encoded Excel worksheet into a Base64 encoded PDF

Inputs

• ExcelWorkbook

  Base64 encoded bytes, using [Convert]::ToBase64String((Get-Content -Encoding Byte -Path FILE_PATH))

• WorksheetName

  Single string; optional

• CellRange

  Single string; optional

  Example:

  "A5" selects A5

  "A1:B3" selects A1, A2, A3, B1, B2, B3

Outputs

• Pdf

  Base64 encoded bytes

  To convert back to a pdf file, use [Convert]::FromBase64String($Pdf) | Set-Content -Encoding Byte -Path FILE_PATH

Version: 1.1.1

Date: 29 January 2019

Back to top

Populate Excel Template with CSV Data

Populates a worksheet within an Excel template workbook with data specified in a CSV.

Given data represented in CSV format, this task will populate worksheets within the provided Excel workbook template.

The output will be the populated Excel template bytes encoded in Base64.

Inputs

• ExcelXlsxTemplateBase64EncodedBytes

  Base64 encoded bytes; required

  Excel template encoded in Base64 format

• Csv1String

  Single string (CSV format); required

  CSV data to populate a worksheet in the provided template

• ExcelXlsxTemplateFileUrl

  Single string (URL); required

  URL to the Excel template

• CsvDataWorksheetName

  Single string; optional.

  The name of the worksheet to populate the provided CSV data with.

  If no worksheet name has been provided, the system will create and populate a worksheet called 'data'

• Csv(X)String - Where (X) is 2, 3, 4 or 5

  Same as Csv1String; optional

• CsvDataWorksheet(X)Name - Where (X) is 2, 3, 4 or 5

  Same as CsvDataWorksheet1Name; optional

Outputs

• ExcelWorkbookBase64EncodedBytes

  Base64 encoded bytes

  Updated Excel template in Base64 encoded bytes.

  To convert back to an Excel file, use [Convert]::FromBase64String($ExcelWorkbookBase64EncodedBytes) | Set-Content -Encoding Byte -Path FILE_PATH

Version: 2.0.0

Date: 14 March 2019

Back to top

Set Cell Values

Sets the values of cells in an Excel workbook.

Given a Base64 encoded Excel workbook, this task can set the value in a range of cells for a worksheet from specified inputs or can set values in a range of cells for multiple worksheets via CSV input.

The output will be the updated Excel workbook bytes encoded in Base64.

Inputs

Usage option 1: Set the value of the specified cells on a worksheet

• ExcelWorkbookBase64EncodedBytes

  Single string; required

  The Excel worksheet which has been encoded in Base64

• Cells

  Single string describing the cell/range of cells to update; required

• Value

  Single string; required

• Worksheet

  Single string; required

  The name of the worksheet to set the cell values

Usage option 2: For each row in the CSV file, set the value of the specified cells on a worksheet

• ExcelWorkbookBase64EncodedBytes

  Single string; required

  The Excel worksheet which has been encoded in Base64

• CellValuesCsv

  Required columns:

  • Cells

    String describing the cell/range of cells to update

  • Value

    The value to set in the cells

  • Worksheet

    The name of the worksheet to set the cell values

Outputs

• ProcessedExcelWorkbookBase64EncodedBytes

  Base64 encoded bytes

  The updated (processed) Excel workbook

  To convert back to an Excel file, use [Convert]::FromBase64String($ExcelWorkbookBase64EncodedBytes) | Set-Content -Encoding Byte -Path FILE_PATH

• ExcelSetCellValuesCsv

  String, CSV format

  The CellValuesCsv with additional column 'SetSuccessfully' to indicate if the operation has occurred successfully or not

• ExcelSetCellValuesErrorMessages

  String

  A string containing all the error messages which were generated over the course of this task

Version: 1.0.2

Date: 14 March 2019

Back to top