Is there a way for Calypso to create an excel TABLE report

[[An...]]

I have excel reports working (single part measurement data)

I have a merged text file working but don't use it for anything(Just experimenting).

I have an excel macro that combined multiple excel sheets together. This works but its an extra step and not always reliable.

Can Calypso create an Excel table with subsequent evaluations of multiple measurements?

 

Thanks in advance

[[Ri...]]

It's easiest to just modify a PiWeb Template that adds an export button. What version of Calypso are you using? I will send you an example. 

Edited February 6

[[An...]]

Please sign in to view this quote.

7.6.00

 

That would be appreciated 

[[Ri...]]

TableProtocol_Excel Report.ptxStandardProtocol_Excel Export.ptx

 

Here you go. I created one for the StandardProtocol (singular results) and one for the TableProtocol (multiple results). 

Just click on the Excel button and it will prompt you on where you want to save it. By default it saves it in the XML format, but it is formatted properly for you.

[[An...]]

Forgot to thank you for these! Very helpful. 

An issue I am having is that your two templates work great but when I go to add the button to my templates I get an error message saying "An error occurred while executing the action 'Export table data to Excel'. I have replicated the buttons in your template as I see it and have even copy/pasted but with no success. 

Any ideas?

[[DW...]]

Please sign in to view this quote.

Please sign in to view this username.

 Sounds like you have a bunch of Calypso XLS files (single) that you need to compile into one report. Are you familiar with Microsoft PowerShell? It is a very versatile and powerful programming language, and you can automate very monotonous and repetitive tasks, like compiling data.

Go to your windows search (magnifying glass by the start menu) and type in ISE. You should see PowerShell ISE. Right click, and open as administrator. You will most likely see an editor (blue) area on the right half of the window, and the host (black window) on the left side of the window. You will be placing the code in the right side (blue) and watching the output on the left side (black).

Copy/Paste this into the blue side. Adjust the path to your XLS files (in bold). This script goes to a directory, searches for XLS files, opens them, saves them with an XLSX extension (removes Calypso macros), then strips the header out (A1:H13), and finally iterates through each XLSX file grabbing all the actuals in Column B, rows 1:1000 and compiling them into a single XLSX named "compiledExcel.xlsx" which will be located in the same location. This script can be tweaked more based on your actual needs, but this will get you in the automation game.

 

# Define the directory containing the XLS and XLSX files
$directory = "C:\PATH TO YOUR FILES"

# Create a new Excel application instance
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# Convert .xls files to .xlsx
Get-ChildItem -Path $directory -Filter "*.xls" | ForEach-Object {
    try {
        $workbook = $excel.Workbooks.Open($_.FullName)
        $newFileName = [System.IO.Path]::ChangeExtension($_.FullName, ".xlsx")
        $workbook.SaveAs($newFileName, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
        $workbook.Close($false)
        Write-Host "Converted: $($_.FullName) to $newFileName"
        # Uncomment the next line to delete the original .xls file
        # Remove-Item $_.FullName -Force
    } catch {
        Write-Host "Failed to convert $($_.FullName): $_"
    }
}

# Process .xlsx files
Get-ChildItem -Path $directory -Filter "*.xlsx" | ForEach-Object {
    try {
        $workbook = $excel.Workbooks.Open($_.FullName)
        $worksheet = $workbook.Sheets.Item(1)
        $worksheet.Range("A1:H13").Delete()
        $workbook.Save()
        $workbook.Close()
    } catch {
        Write-Host "Error processing file $($_.FullName): $_"
    }
}

# Compile data into a new Excel file
$destinationExcelFilePath = Join-Path $directory "compiledExcel.xlsx"
if (Test-Path $destinationExcelFilePath) {
    Remove-Item $destinationExcelFilePath -Force
}
$destinationWorkbook = $excel.Workbooks.Add()
$destinationWorksheet = $destinationWorkbook.Sheets.Item(1)

$columnIndex = 1
Get-ChildItem -Path $directory -Filter "*.xlsx" | Sort-Object Name | ForEach-Object {
    $sourceWorkbook = $excel.Workbooks.Open($_.FullName)
    try {
        $sourceWorksheet = $sourceWorkbook.Sheets.Item(1)
        $sourceValues = $sourceWorksheet.Range("B1:B1000").Value2
        $destinationWorksheet.Cells.Item(1, $columnIndex).Resize(1000, 1).Value2 = $sourceValues
        $columnIndex++
    } finally {
        $sourceWorkbook.Close($false)
    }
}

$destinationWorkbook.SaveAs($destinationExcelFilePath)
$destinationWorkbook.Close($true)

# Quit Excel and clean up
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()