Modify-Excel

1.0.2

Modify-Excel.ps1

                                
<#PSScriptInfo

.VERSION 1.0.2

.GUID 89982a47-1a96-4d98-b207-1bcbf0deffe4

.AUTHOR c.de.

.COMPANYNAME

.COPYRIGHT

.TAGS

excel,ComObject,unicode,utf,utf16

.LICENSEURI

.PROJECTURI

.ICONURI

.EXTERNALMODULEDEPENDENCIES 

.REQUIREDSCRIPTS

.EXTERNALSCRIPTDEPENDENCIES

.RELEASENOTES

.PRIVATEDATA

#>

<# 

.DESCRIPTION 

 Write to Excel daily tasks 

#> 

Param()

<#

.SYNOPSIS

This function is used for updating, saving excel data at pre-defined intervals.

.DESCRIPTION

Used for updating certain excel cells with unicode-hardcoded characters in main PS code file.

Script selects a random unicode-translated message to be written in the excel task list.

.PARAMETER addSheet

Switch option to add an extra worksheet to write your information on.

.EXAMPLES

PS> Modify-Excel <-- adds your scheduled data to your spreadsheet (dd/MM/yyyy and relevant comment)

PS> Modify-Excel -addSheet <-- adds your scheduled data on additional worksheet in the end of main excel area

#>

Function Modify-Excel([switch]$addSheet) {

$defaultdir = "$env:USERPROFILE\Documents\Timesheet_1709_2020.xlsx";

$xls = New-Object -ComObject Excel.Application; $xls.Visible = $false;

$xlShiftDown = -4121;

$fileModify = $xls.Workbooks.Open($defaultdir);

# select latest/current working month ..

$spreadSheet =  $fileModify.ActiveSheet; # $fileModify.Worksheets | ?{ $_.Name -ilike "*2020" } | Select -Last 1

if ($addSheet) {

Try {

# Create an additional worksheet..

$Currentmonth = ([System.DateTime]::Now).ToLongDateString().Split(' ')[1];

$Nextmonth = ([System.DateTime]::Now.AddMonths(1)).ToLongDateString().Split(' ')[1];

$Currentyear = ([System.DateTime]::Now).ToLongDateString().Split(' ')[2];

$lastSheet = ($fileModify.Worksheets).Count();

Write-Host "Added worksheet #$($lastSheet + 1) to file.. after ws `'$(($fileModify.Worksheets | Select -Last 1).Name)`'"

$lastwsheet = $fileModify.Worksheets | Select -Last 1;

$addedsheet = $fileModify.Worksheets.Add($lastwsheet);

$addedsheet.Name = "Days_$($Nextmonth)_$Currentyear";

$lastwsheet.Move($addedsheet);

$addedsheet.Select();

$f=0;

for ($r=3; $r -lt 34; $r++) {

 $valueDate = (([System.DateTime]::Now)).ToString("dd/MM/yyyy");

 $addedSheet.Cells.Item($r,1) = (($valueDate -as [System.DateTime]).AddDays($f++));

 }

}

Catch { Write-Warning "Error while adding to excel, duplicate sheet/error in name."; return $($null);}

}

# Create/Print daily input, UTF-16 encoded chars.

# input 1 - unicode string, label in code

[string]$el_syst_part1 += [Int[]]@(924,942,957,965,956,945) | %{$_ -as [char]};

[string]$el_syst_part2 += [Int[]]@(49) | %{$_ -as [char]};

# input 2 - unicode string, label in code

[string]$el_dat_part1  += [Int[]]@(924,942,957,973,956,945) | %{$_ -as [char]};

[string]$el_dat_part2  += [Int[]]@(51) | %{$_ -as [char]};

# input 3 - unicode string, label in code

[string]$an_dat_proc_part1 += [Int[]]@(931,951,956,949,943,969,963,951) | %{$_ -as [char]};

[string]$an_dat_proc_part2 += [Int[]]@(51,50) | %{$_ -as [char]};

# Create complete resulting strings..

[string]$el_syst = @($($el_syst_part1 -replace ' ',''),$($el_syst_part2 -replace ' ','')) -join ' ';

[string]$el_dat = @($($el_dat_part1 -replace ' ',''),$($el_dat_part2 -replace ' ','')) -join ' ';

[string]$an_dat_proc = @($($an_dat_proc_part1 -replace ' ',''),$($an_dat_proc_part2 -replace ' ','')) -join ' ';

$dailyTask = [string[]]@($($el_syst),

                         $($el_dat),

             $($an_dat_proc))[$(Get-Random -Maximum 3)];                         

$dayissued = $([System.DateTime]::Now.ToShortDateString().Split('/')[0] -as [int]);

$spreadSheet.Cells.Item($($dayissued) + 2,3) = $dailyTask;

$spreadSheet.Cells.Item($($dayissued) + 2,4) = $dailyTask;

# Add to relevant cells.

$spreadSheet.Columns.Item('C').columnWidth = 40;

$spreadSheet.Columns.Item('D').columnWidth = 40;

$fileModify.Save();

$fileModify.Close();

$xls.Quit();

}

Modify-Excel

#Add a new month, new daily task tab on excel. Modify-Excel -addSheet; 

Start-Process "$env:USERPROFILE\Documents\Timesheet_1709_2020.xlsx";

# SIG # Begin signature block

# MIIEGAYJKoZIhvcNAQcCoIIECTCCBAUCAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB

# gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR

# AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUnut52g64u/WgPrNfc8zmJ42a

# iLigggIvMIICKzCCAdCgAwIBAgIQP671MxE0Op1P87qxbgk5QDAKBggqhkjOPQQD

# AjBbMRMwEQYKCZImiZPyLGQBGRYDa29tMRUwEwYKCZImiZPyLGQBGRYFZXJyb3Ix

# FTATBgNVBAsMDFVzZXJBY2NvdW50czEWMBQGA1UEAwwNQ2hyaXN0b3MgVGVzdDAe

# Fw0yMDEyMjIxMTU2MzVaFw0yMTEyMjIxMjE2MzVaMFsxEzARBgoJkiaJk/IsZAEZ

# FgNrb20xFTATBgoJkiaJk/IsZAEZFgVlcnJvcjEVMBMGA1UECwwMVXNlckFjY291

# bnRzMRYwFAYDVQQDDA1DaHJpc3RvcyBUZXN0MFkwEwYHKoZIzj0CAQYIKoZIzj0D

# AQcDQgAEvhilEJNgpX+c/IGwUJ1x03FS4iEXjpLqD0H2ROqYyqzNF3MbY6MNEuSI

# pU8CcVR608lI+nHmuoib13jmfraLbKN2MHQwDgYDVR0PAQH/BAQDAgeAMBMGA1Ud

# JQQMMAoGCCsGAQUFBwMDMC4GA1UdEQQnMCWgIwYKKwYBBAGCNxQCA6AVDBNjaHJp

# c3Rlc3RAZXJyb3Iua29tMB0GA1UdDgQWBBSnqsSsh8Z1o6DpuYiG20a3dvu1IzAK

# BggqhkjOPQQDAgNJADBGAiEA7yVon21IoGplLfBScCb6bDD+wlFkvpuvdFvx9H/R

# GMkCIQClHwnAY0GT+4CCuxUv+J137OG2CHtrFH88WkVeA8ekITGCAVMwggFPAgEB

# MG8wWzETMBEGCgmSJomT8ixkARkWA2tvbTEVMBMGCgmSJomT8ixkARkWBWVycm9y

# MRUwEwYDVQQLDAxVc2VyQWNjb3VudHMxFjAUBgNVBAMMDUNocmlzdG9zIFRlc3QC

# ED+u9TMRNDqdT/O6sW4JOUAwCQYFKw4DAhoFAKB4MBgGCisGAQQBgjcCAQwxCjAI

# oAKAAKECgAAwGQYJKoZIhvcNAQkDMQwGCisGAQQBgjcCAQQwHAYKKwYBBAGCNwIB

# CzEOMAwGCisGAQQBgjcCARUwIwYJKoZIhvcNAQkEMRYEFFdOPOA12JLdrGsqBaAd

# 20FdjY/fMAsGByqGSM49AgEFAARHMEUCIQDNsT4t9/hVrFvintvMxYtbm0Tp4o4I

# nF6G3GyALE/0LwIgCvmIp4hvqOT5uZjymaAJoJ+c5hRnu9EL5zpNSGoA7fI=

# SIG # End signature block