• Tag Archives Excel
  • “Editing” Excel using Powershell

    At work we have a spreadsheet, where we keep track of our mobile phones, and 3G dongles, in it we keep info about the IMEI code, PUK/PIN codes etc.

    Today we found a few SIM cards lying around, the only thing on them was the SIM card number (19 digits). When I went to compare that to the info in the file, I realised that that was not noted in the file, so I had no way to match the SIM with a Phone number, PIN/PUK code.

    So I called the telco, they were willing/able to supply me with an csv file containing the phone number, SIM card number, and PUK code.

    So I took on the task to update our document, and check that the info in our original document was correct, but doing this for 45+ subscriptions, I thought I would write a script, and what better language than PowerShell.

    Here is what I ended up with.

    The CSV file contains 3 headers
    MSISDN1,UICCID,PUK1

    [ps]

    #Location of the Excel file I want to edit
    $FileLoc = "C:\temp\test.xls"
    #Create Excel Com Object, and display it
    $excel = new-object -com Excel.Application
    $excel.visible = $true

    #Open Workbook,
    $workbooks = $excel.workbooks.Open($FileLoc)
    $worksheets = $workbooks.Worksheets
    $worksheet = $worksheets.Item(1)
    #Select the range we want to look at
    #In this example, I am only checking within one Column
    $range = $worksheet.Range("M3", "M150")
    #Load CSV file
    $PhoneInfo = Import-Csv c:\Temp\TlfPUK.csv
    #Run through the colums
    foreach($col in $range.Columns){
    foreach($row in $col.Rows){
    Foreach ($Phone in $PhoneInfo){
    #In the CSV file, MSISDN1 represents the phone number, but with the international dial code in front
    #that is why I use substring to get the last 8 chars and compare it the value of the current cell.
    #If the phone numbers match, do something
    If ( $Phone.MSISDN1.substring($Phone.MSISDN1.length -8,8) -eq $row.Text) {
    #The two colums what will contain the data is set, this is just 4 & 5 columns to the right of the column that contains the phone number
    $ColIMEI = $row.Column + 5
    $ColPUK = $row.Column + 4
    #Here I fill in the SIMCard number (UICCID) and PUK code
    $worksheet.Cells.Item($row.Row, $ColIMEI) = $Phone.UICCID
    $worksheet.Cells.Item($row.Row, $ColPUK) = $Phone.PUK1
    #$Phone.UICCID
    }
    }

    }

    }

    [/ps]



  • Create Excel Spreadsheet

    The other day I was tasked with creating a spreadsheet containing a list of all servers in our network, manager wanted a “sheet” for each computer, and an “index” sheet with links to all the other sheets.

    Since we have more than 150 servers, there was no way I was going to create this list by hand… So I wrote a quick and dirty little Powershell function.

    [sourcecode lang="PosH"]

    Function Fill-Excel {
    BEGIN {
    $xl = new-object -comobject excel.application
    $xl.Visible = $true
    #I specify an existing filename, since creating the link in Excel requires a filename
    $wb = $xl.Workbooks.Open(“C:\Temp\test.xlsx”)
    $ws = $wb.Worksheets.Item(1)
    $xl.ActiveSheet.Name = “List”
    $row = 1
    }

    Process {
    $_
    $q = $_
    $z = $q.ToUpper()
    $xl.Sheets.Add()
    $xl.ActiveSheet.Name = $z
    $wh = $xl.ActiveSheet
    $wh.Cells.Item(1,1) = “=HYPERLINK(`”[test.xlsx]List!A1`”;`”Back To List`”)”
    $ws.Cells.Item($row,1) = “=HYPERLINK(`”[test.xlsx]$z!A1`”;`”$z`”)”
    $row++
    }
    }

    $s= “Computer1″,”CompPUTER2″,”comPUTer3″
    $s | Fill-Excel
    [/sourcecode]

    This will open the xslx filed called test.xlsx under c:\temp and create an index sheet called “list”, and a sheet for each object that is passed to the function, it also creates a Link in A1 that refers back to the index sheet. On the Index sheet a link to each new sheet is created.

    In the above example I “manually” pipe in some text string, but it could also be something like this:

    [sourcecode lang="PosH"]
    #
    #
    $s = Get-QADComputer | Where {$_.OSname -match “Server”} | select name
    [/sourcecode]

    So since it is a function, you can pass virtually any string to it, and it will populate the excel sheet for you.