Misc – Roland Thompson

Using OpenSSL on Windows to create a pfx certificate from a private key and cert file

Creating an PFX from a private key file and crt file on Windows.

Every year the task comes around to renew the SSL certificates for various services, depending on the certificate provider you can’t always download it as a PFX file.

Now for some services that’s fine, but for others – in this case Azure, we need to upload a PFX file.

Now, in this example it was a cert generated by GoDaddy. We get the private key in a file, along with the .crt file. What we need to do is use the 2 and generate the pfx.

We can use the OpenSSL executable, but where to find this on Windows, well if you have Git Desktop installed you can usually find it in the following folder.

C:\Program Files\Git\usr\bin

Now one note with the private key file, it is usually saved in the wrong encoding, so open it up in notepad (yes it will work for this), or your editor of choosing, you need to make sure the encoding type is UTF-8, my originally key file for example was saved as UTF-8 with BOM – it just doesn’t work.

I usually add this folder to my environment PATH, but that’s your choice.

If you have added it to your PATH, then enter the following at the command prompt in the folder you have your cert file (.crt) and private key file (.key)

openssl pkcs12 -export -in yourcert.crt -inkey yourprivatekey.key -out yournewpfx.pfx

If you didn’t add it to the PATH, then you will need to run the above command from the OpenSSL.exe folder.

Either way, replace the bold files with the appropriate ones for you, it should ask you for a password, then create your PFX file for you.

Easy 🙂

c# excel interop – cannot access the file

In a recent project we had the need to create a pivot table in Excel from an existing spreadsheet.

Should be relatively easy… as always never as straight forward as it should be!

I’m not going to show the whole code for creating the pivot, but in essence to create a pivot table you need to

Open the spreadsheet
Select the data range
Create a pivot cache
Create the actual pivot table
Add the Row/Column/Data fields
Save the file.

Easy right, well yes so far. This is the basics of the code:

            Application excelApp = new Application();
            Workbook excelWorkBook = excelApp.Workbooks.Open(file);
            Worksheet excelworksheet = excelWorkBook.ActiveSheet;
            Worksheet sheet2 = excelWorkBook.Sheets.Add();

            try
            {

                sheet2.Name = sheetname;
                excelApp.ActiveWindow.DisplayGridlines = showGridLines;


                Range oRange = excelworksheet.UsedRange;
                PivotCache oPivotCache = excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);  
                PivotCaches pch = excelWorkBook.PivotCaches();

                pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells[1, 1], "PivotTable", Type.Missing, Type.Missing);// Create Pivot table

                PivotTable pvt = sheet2.PivotTables("PivotTable");
                pvt.ShowDrillIndicators = true;
                pvt.InGridDropZones = false;


                // add rows

                // add columns

                // add data fields

                #region Misc

                sheet2.UsedRange.Columns.AutoFit();

                pvt.ColumnGrand = true;
                pvt.RowGrand = true;

                excelApp.DisplayAlerts = false;

                if (removeSourceSheet)
                    excelworksheet.Delete();

                sheet2.Activate();
                sheet2.get_Range("B1", "B1").Select();

                #endregion

                excelWorkBook.SaveAs(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelApp.DisplayAlerts = false;
                excelWorkBook.Close(0);
                excelApp.Quit();

                return "OK";

            }
            catch (Exception ex)
            {
                excelWorkBook.Close(0);
                excelApp.Quit();

                return ex.ToString();

            }

So that’s it really, running it works a treat… unfortunately this is where my problems start.

When running in unattended mode you get something like:

A call to ‘x’ failed with this message: Microsoft Excel cannot access the file ‘<filename>’. There are several possible reasons:
The file name or path does not exist.
The file is being use by another program.
The workbook you are trying to save has the same name as a currently open workbook

Now personally I think this is misleading.. as it turns out to be none of those reasons!

Turns out excel interop doesn’t like running in unattended mode, aka non interactive or whatever you would like to call it – but I need it too.

Step in some black voodoo magic 🙂

Turns out you need to make sure 2 folders exist for it to work, and there are:

  C:\Windows\System32\config\systemprofile\Desktop
  C:\Windows\SysWOW64\config\systemprofile\Desktop

Now, when it runs it works perfectly. It should be noted this isn’t support by Microsoft, so use at your own risk – and it could stop working at any point in time!

Happy new year!

Happy new year everyone!

One of the (many) things I’ve decided to try and do this year is post a bit more, it’s only when I have logged in I noticed I did no posts at all last year, here’s hoping I manage a bit more!

Along with more posting, my other aims are to try and get some more of my Arduino/Raspberry Pi projects actually finished!

Have a good new year everyone!