Set-format

[Razzbiz]

I am writing a script that compares the input from 2 excel-imports using compare-object, exports it back to excel and marks the specific cell with changes. I am having trouble solving the last part - marking specific data cells in the excel output. I need a way to mark the changed data and feed that info to "set-format" (?) and then I need to mark the specific cells, not just the entire row or column. Is this possible?

[jhoneill]

Yes, it's possible. You might find it easier to use Compare-Worksheet, but if that doesn't work the way you want it to, then Set-Format will do the job - it's just a question of saying which sheet, specifying the cell(s) as a range, and saying what formatting you want to be applied.

[Razzbiz]

Thanks for the input. Running the following code marks all the changed rows with green, but only the first field with red, what am I doing wrong? There should be at least one red field for each row.

$InputA = "C:\Users\Rasmus Eilertsen\OneDrive - HERAX\D - Project\ACT.MIG1\Compare_tool\test_input\Personnel_16_12_2018 - small2.xlsx"
$InputB = "C:\Users\Rasmus Eilertsen\OneDrive - HERAX\D - Project\ACT.MIG1\Compare_tool\test_input\Personnel_28_11_2018 - small2.xlsx"
$InputSheetA = "Export Worksheet"
$InputSheetB = "Export Worksheet"

$comparePropsResult = Compare-worksheet $InputA $InputB -WorkSheetName $InputSheetA, $InputSheetB -key  "PERSONNELL_PK" -BackgroundColor lightGreen -FontColor Red -PassThru -show #Only data from difference object

[jhoneill]

I've just tested this , and I can't reproduce it. It possible for you to share a couple of sheets which should be marked up, but aren't ?

[Razzbiz]

I am using 2 excel files for testing:

Personnel_16_12_2018 - small2.xlsx (previous version) Personnel_28_11_2018 - small2.xlsx (new version with changes and additions)

The files can be downloaded here:

https://ufile.io/bmstu https://ufile.io/3z62x

[jhoneill]

OK, getting there.
In the sample data you sent PERSONNELL_PK has duplicates; we need a unique value as the key.. otherwise we can't get a match to compare a row on either side

When I took the made the keys unique everything worked. The row you are getting the red font applied to has a unique key. I need add some more information when it encounters a duplicate key. So thanks.

J

[Razzbiz]

Hi J, Ok, so what can I do if none of my ID fields are entirely unique. They might be if I combine 2 of them. Is that possible?

/R

[jhoneill]

The way things are written at the moment, it needs a column to be a unique key, you could put a formula in the sheet to combine 2 fields, to get a unique compound key. (You can use Set-ExcelColumn, and use -Calculate when you close the file).
It needs a key becuase if a row is inserted or deleted the cells won't match up by rows number. So we have to find some other way to match data items between sheets. I will have a look for ways to specify a compound key as a parameter, but right now, that won't work.

[jhoneill]

You only need one change to allow a calculated Key. Change line 194 in compareWorksheet.ps1 to read if ($diff -and $FontColor -and (($propList -contains $Key) -or ($key -is [hashtable])) ) { The hash table bit is new. Then you can specify something like this when you run compare. -key @{e={$_.TRIAL_PK + $_.TRIALSSITE_PK}}

line 195 groups the difference rows based on the key; if it finds exactly two rows it takes this to mean it has a row from the "reference" file and matching row from the "difference" file. You can group on a calculated field if you use the @{e={ expression }} syntax - similar to select-object and the format commands bit without the n="Name" which they have.

Let me know if that works, because I will do some more testing and upload the change (plus documentation and a test) if it is helpful

[Razzbiz]

I get this: WARNING: To match rows to set changed cells, you must specify -Key and it must match one of the included properties. But it seems to process allright.

On Thu, 20 Dec 2018 at 13:05, jhoneill notifications@github.com wrote:

You only need one change to allow a calculated Key. Change line 194 in compareWorksheet.ps1 to read if ($diff -and $FontColor -and (($propList -contains $Key) -or ($key -is [hashtable])) ) { The hash table bit is new. Then you can specify something like this when you run compare. -key @{e={$_.TRIALPK + $.TRIALSSITE_PK}}

line 195 groups the difference rows based on the key; if it finds exactly two rows it takes this to mean it has a row from the "reference" file and matching row from the "difference" file. You can group on a calculated field if you use the @{e={ expression }} syntax - similar to select-object and the format commands bit without the n="Name" which they have.

Let me know if that works, because I will do some more testing and upload the change (plus documentation and a test) if it is helpful

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/508#issuecomment-448975942, or mute the thread https://github.com/notifications/unsubscribe-auth/ArjQHxBT-zKZ-iNvTfeQVHzaWVZMfeKQks5u63z-gaJpZM4ZRUIb .

--

-- Med venlig hilsen

Rasmus Eilertsen

Razz Tværvej 24, 4070 Kirke Hyllinge Mobil: +4561686333 Mail: info@razz.biz, Web: http://www.razz.biz

[jhoneill]

If you get the warning that is because the IF line hasn't been changed (and the file saved and reloaded) .

[Razzbiz]

Sorry about that, yes you are right. Now it works without warnings.

On Thu, 20 Dec 2018 at 13:54, jhoneill notifications@github.com wrote:

If you get the warning that is because the IF line hasn't been changed (and the file saved and reloaded) .

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/508#issuecomment-448989625, or mute the thread https://github.com/notifications/unsubscribe-auth/ArjQH9Tq4k1i9k_4L-id4TxLhZooIYriks5u64iCgaJpZM4ZRUIb .

--

-- Med venlig hilsen

Rasmus Eilertsen

Razz Tværvej 24, 4070 Kirke Hyllinge Mobil: +4561686333 Mail: info@razz.biz, Web: http://www.razz.biz

[Razzbiz]

I am trying to save this compare output to a different excel file and the show that. But somewhere along the line, the formatting we just discussed above gets lost. This is how I output the data:

$global:OutputFile = ".\output.xlsx"

$comparePropsResult = Compare-worksheet $InputA $InputB -WorkSheetName $InputSheetA, $InputSheetB -key @{e={$_.TRIALSSITEPK + $.PERSONNELL_PK}} -BackgroundColor lightGreen -FontColor Red -PassThru #Test

foreach ($record in $comparePropsResult) { $record.SideIndicator = "Rightside" }

$WS2 = "Changed"

$ExcelParams = @{ Path = $OutputFile AutoSize = $true AutoFilter = $true WorkSheetname = $WS2 PassThru = $true } $xl2 = $comparePropsResult | Export-Excel @ExcelParams #added to a variable $xl

$ws2 = $xl2.Workbook.Worksheets[$WS2]

Export-Excel -ExcelPackage $xl2 -WorkSheetname $WS2 -Show

On Thu, 20 Dec 2018 at 13:54, jhoneill notifications@github.com wrote:

If you get the warning that is because the IF line hasn't been changed (and the file saved and reloaded) .

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/508#issuecomment-448989625, or mute the thread https://github.com/notifications/unsubscribe-auth/ArjQH9Tq4k1i9k_4L-id4TxLhZooIYriks5u64iCgaJpZM4ZRUIb .

--

-- Med venlig hilsen

Rasmus Eilertsen

Razz Tværvej 24, 4070 Kirke Hyllinge Mobil: +4561686333 Mail: info@razz.biz, Web: http://www.razz.biz

[jhoneill]

OK. What you should have after that is the files referred to in InputA and InputB should be marked up.

You'll have a list of the differences in $output file but that won't be marked up. If you replace the foreach to the end this will mark up that file :-)

$xl = $comparePropsResult  |Select-Object -Property * -ExcludeProperty sideIndicator | export-excel -path \temp\output.xlsx -AutoNameRange  -PassThru

$sheet = $xl.sheet1
$cells = $sheet.cells

$Key1Col = $cells["1:1"].Where({$_.value -eq "trial_pk"}).start.column 
$Key2Col = $cells["1:1"].Where({$_.value -eq "TRIALSSITE_PK"}).Start.column 

$row = 2 
while ($row -lt $xl.Sheet1.Dimension.End.Row) {

    #if both keys match for the current row. 
    if (($cells[($row), $key1Col].value -eq $cells[($row+1), $key1Col].value)  -and  
        ($cells[($row), $key2Col].value -eq $cells[($row+1), $key2Col].value) ) {
        foreach ($c in 1..$Sheet.Dimension.End.Column) {
            if ($cells[1,$c].Value -notlike "_*" -and 
                $cells[$row,$c].value -ne $cells[($row+1),$c].value) {
                $cells[$row,$c,($row+1),$c].Style.Font.Color.SetColor("Red")
            } 
        }
    }
    $row++ 
}

Close-ExcelPackage -ExcelPackage $xl -Show

[Razzbiz]

Thanks for the swift answer, but I don't quite understand. Were do I insert this and what do I replace? Currently my code looks like this:

$global:OutputFile = ".\output.xlsx"

$comparePropsResult = Compare-worksheet $InputA $InputB -WorkSheetName $InputSheetA, $InputSheetB -key @{e={$_.TRIALSSITE_PK + $_.PERSONNELL_PK}} -BackgroundColor lightGreen -FontColor Red -PassThru #Test

foreach ($record in $comparePropsResult)
{
    $record.SideIndicator = "Rightside"
}

$WS2 = "Changed"

$ExcelParams = @{
Path = $OutputFile
AutoSize = $true
AutoFilter = $true
WorkSheetname = $WS2
PassThru = $true
}
$xl2 = $comparePropsResult | Export-Excel @ExcelParams #added to a variable $xl

$ws2 = $xl2.Workbook.Worksheets[$WS2]

Export-Excel -ExcelPackage $xl2 -WorkSheetname $WS2 -Show

Thanks.

[jhoneill]

From your code delete from foreach to the end and paste mine :-)

(From my phone )

[Razzbiz]

Great, it works! How do I get the output to only show reference or difference data? Thanks

[Razzbiz]

How do I get the output to only show reference or difference data? Thanks

[dfinke]

Could you say more shit what you're trying to do and provide examples?

[Razzbiz]

Hello again; Love your typo ;-) If I run the current code: `

$InputA = "C:\Users\Rasmus Eilertsen\OneDrive - HERAX\D - Project\ACT.MIG1\Compare_tool\test_input\Personnel_16_12_2018 - small2.xlsx"
$InputB = "C:\Users\Rasmus Eilertsen\OneDrive - HERAX\D - Project\ACT.MIG1\Compare_tool\test_input\Personnel_28_11_2018 - small2.xlsx"
$InputSheetA = "Export Worksheet"
$InputSheetB = "Export Worksheet"

$global:OutputFile = ".\output.xlsx"

$comparePropsResult = Compare-worksheet $InputA $InputB -WorkSheetName $InputSheetA, $InputSheetB -key @{e={$_.TRIALSSITE_PK + $_.PERSONNELL_PK}} -BackgroundColor lightGreen -FontColor Red -PassThru #Both sides

$xl = $comparePropsResult  |Select-Object -Property * -ExcludeProperty sideIndicator | export-excel -path $OutputFile -AutoNameRange  -PassThru

$sheet = $xl.sheet1
$cells = $sheet.cells

$Key1Col = $cells["1:1"].Where({$_.value -eq "TRIALSSITE_PK"}).start.column 
$Key2Col = $cells["1:1"].Where({$_.value -eq "PERSONNELL_PK"}).Start.column 

$row = 2 
while ($row -lt $xl.Sheet1.Dimension.End.Row) {

    #if both keys match for the current row. 
    if (($cells[($row), $key1Col].value -eq $cells[($row+1), $key1Col].value)  -and  
        ($cells[($row), $key2Col].value -eq $cells[($row+1), $key2Col].value) ) {
        foreach ($c in 1..$Sheet.Dimension.End.Column) {
            if ($cells[1,$c].Value -notlike "_*" -and 
                $cells[$row,$c].value -ne $cells[($row+1),$c].value) {
                $cells[$row,$c,($row+1),$c].Style.Font.Color.SetColor("Red")
            } 
        }
    }
    $row++ 
}

Close-ExcelPackage -ExcelPackage $xl -Show

I get a nice output into an excel sheet with a dataset containing records from both difference and reference marked in red (btw no lightgreenbackground color).

What I would like was a sheet with only the changes in the difference file, marked with formatting. But we remove the sideindicator information when exporting to excel and also export the entire dataset, both difference and reference before adding the formatting. So I need to export only difference, compare data in order to format changes, and apply formatting only to difference data.

[jhoneill]

@Razzbiz Sorry I missed when you first asked that question - looking at the date it must have been early in the Christmas break. I need to find a few minutes to look at this properly. I'll try to find some time later today and get back to you (trying to arrange a new contract at the moment, and running round like crazy)

[Razzbiz]

Hey guys, did you have a chance to look at a solution? KR Rasmus

[Razzbiz]

Is there a way to use ImportExcel to retrieve a list of headers from a specific sheet, in the same way that you can get a list of sheets using Get-ExcelSheetInfo

[jhoneill]

$e = Open-ExcelPackage -Path .\Examples\Planner-Export.xlsx
$e.Plan.cells["1:1"].value
Close-ExcelPackage -NoSave $e 

Will the headers "plan" in the second line is the name of a sheet in planner.xlsx

Every time I look at the other question I read what you are asking in a different way - that's the problem of doing it late at night. Now I think the question is either "Can the code be made to mark up the 'differnce' sheet and leave the 'reference' sheet alone" and a supplementary "Can we set more than font color on difference cells, and leave the row alone."

Or "Can we export the changes to a new file and find a way to show only rows from the difference side in the new file, with the changed cells marked up. "

We can't do the first one at the moment, but it would be easy to code up and a worthwhile enhancement, (in other words, I'll do it when I can). The second one needs a bit of creative coding. A trick I have used in the past is put both sides data into the same sheet, mark up differences, and then go through and delete one sides data. That's not fast on big data sets but I was doing with 500-1000 rows so the time was acceptable.

[Razzbiz]

This is what I meant: "Can we export the changes to a new file and find a way to show only rows from the difference side in the new file, with the changed cells marked up. " and I was thinking about the same approach as you mention in the end. Would you mind sharing the code? Thanks

[Razzbiz]

I managed to solve the last part of removing the reference side data from the output:

` ##-- Remove old rows/records

    for ($i = 2; $i -le $xl2.$WS2.Dimension.End.Row; $i++)
    {
        if (($cells[($i), $SideCol].value -eq "Old"))
        {
            #Write-host "Old row found, Delete row: " $i
            $sheet.DeleteRow($i)
            $i--
        }
    }`

When applying the formatting using the code you provided earlier it's a prerequisite that the 2 datasets are both sorted in the same order. This could be fixed by making sure that the excelfiles are both sorted from the start before importing, but I would like to make this process failsafe by also sorting the imported data. Could you please advice on how to sort an imported excel sheet by a column (this would logically be the Key column) ? Thanks again.