Category: Coding

Listing Column Widths in Excel

I hacked Box Spout to support column widths formatting, but wanted a quick way of adding appropriate column widths (yes, automatic width determination would be better … but I didn’t want to spend hours sorting that). Instead of wasting time on automatic column widths, I wrote a simple Excel code module to tell me the appropriate column widths. If your data width might vary, you can add some padding to the ReportColumnWidth function. My data, fortunately, is fixed width.

You will need to save your spreadsheet as a macro-enabled workbook (.xlsm). To add a function to Excel, hit Alt and F11. Select “Insert” => “Module” and paste in the following content and save.

Function iCeiling(iInput)
    iCeiling = Int(iInput)
    If iCeiling <> iInput Then
        iCeiling = Int(iInput) + 1
    End If
End Function

Function ReportColumnWidth(CellID As Range) As Double
 Application.Volatile
 ReportColumnWidth = iCeiling(CellID.ColumnWidth)
End Function

In Excel, use the ReportColumnWidth function to print the width of a column into a cell. This is my row #3.

In row #2, I have a counter that provides the row number for use in Box Spout. Row #4 creates the line needed to set the column width in my code using the concat function.

=CONCAT("$writer->setColumnsWidth(",A3,",",A2,",",A2,");")

Replacing the tab characters with newlines, I now have column widths set based on my data.

GitLab – Using the Docker Executor for Testing

Setting up gitlab-runner to use a Docker executor: You need Docker running on the gitlab-runner host. In my sandbox, I have GitLab running as a Docker container. Instead of installing Docker in Docker, I have mounted the host Docker socket to the GitLab container. You’ll need to add the –privileged flag, and since I’m using Windows … my mount path is funky. But it works.

docker run –detach –hostname gitlab.rushworth.us –publish 443:443 –publish 80:80 –publish 22:22 –name gitlab -v //var/run/docker.sock:/var/run/docker.sock –privileged gitlab/gitlab-ee:latest

Register the runner using “docker-runner register”. I always specify the image in my CI YAML file, so the default image is immaterial … but I’ve encountered groups with an image that mirrors the production servers who set that image as the default.

Edit /etc/gitlab-runner/config.toml and change “privileged = false” to true.

Start the runner (docker-runner start). In the GitLab Admin Area, navigate to Overview => Runners and select the one we just created. When a project is updated, tags can be used to select an appropriate runner. Because most of my testing is done with the shell executor, the runner which uses the shell executor has no tags and the runner which uses the Docker executor is tagged with “runner-docker”. You can require all jobs include a tag to select the appropriate runner (which avoids someone accidentally forgetting a tag and having their project processed through the wrong runner).

An image – you’ll need an image. You can use base images from the Docker Hub registry or create your own image. You can add components in the before_script or use a Dockerfile to build an image from the parent image.

Now we’re ready to use the Docker executor! Create your CI YAML file.

If you are not using the default image, start with “image: <the image you want>”.

We don’t want phpunit in the running image, but I use it for testing. Thus, I need a before_script component to install the phpunit package.

If you’ve used a tag to restrict what is run in your Docker-executor based runner, add the appropriate tag. Include the tester command line.

.gitlab.yml:

image: gitlab.rushworth.us:4567/lisa/ljtestproject-dockerexecutor
stages:
- test

before_script:
# Install dependencies
- bash ci/docker_InstallReqs.sh

test_job:
stage: test
tags:
- runner-docker
script:
- phpunit --configuration phpunit_myapp.xml

Docker_InstallReqs.sh

#!/bin/bash
yum install php-phpunit-PHPUnit

Now when you commit changes to the repository, the Docker-executor based runner will be used for the CI/CD pipeline. A transient Docker container will be created with the image, your before_script will be executed, and then the test script will be run within the container.

 

GitLab SSH Deployment Setup

Preliminary stuff – before setting up SSH deployment in your pipeline, you’ll need a user on the target box with permission to write to the files being published. You will need a public/private key pair.

On the target server, the project needs to be cloned into the deployment directory. The public key will need to be added to authorized_keys (or authorized_keys2 on older versions of Linux) file so the private key can be used for authentication.

To set up your GitLab project for SSH-based deployment, you need to add some variables to the project. In the project, navigate to Settings ==> CI/CD

Expand the “Variables” section. You will need to add the following key/value variable pairs:

Key Value
SSH_KNOWN_HOSTS Output of ssh-keyscan targetserver.example.com
SSH_PRIVATE_KEY Content of your private key
DEPLOYMENT_HOST Target hostname, e.g. targetserver.example.com
DEPLOYMENT_USER Username on target server
DEPLOYMENT_PATH Path to which project will be deployed on target server

Save the variables

I am managing both a production and development deployment within the pipeline, so I’ve got prod and dev specific variables. We use the same username for prod and dev; but the hostname, path, and target server public key are different.

If your repository is publicly readable, this is sufficient. If you have a private repository, you’ll need a way to authenticate and fetch the data. In this example, I am using a deployment token. Under Settings Repository, expand the “Deployment Tokens” section and create a deployment token. On my target servers, the remote is added as https://TokenUser:TokenSecret@gitlab.example.com/path/to/project.git instead of just https://gitlab.example.com/path/to/project.git

Once you have defined these variables within the project, use the variables in your CI/CD YAML. In this example, I am deploying PHP code to a web server. Changes to the development branch are deployed to the dev server, and changes to the master branch are deployed to the production server.

In the before_script, I set up the key-based authentication by adding the private key to my runner environment and adding the prod and dev target server’s public key to the runner environment.

- 'which ssh-agent || ( apt-get update -y && apt-get install openssh-client -y )'
- eval $(ssh-agent -s)
- echo "$SSH_PRIVATE_KEY" | tr -d '\r' | ssh-add - > /dev/null
- mkdir -p ~/.ssh
- chmod 700 ~/.ssh
- echo "$SSH_KNOWN_HOSTS_DEV" > ~/.ssh/known_hosts
- echo "$SSH_KNOWN_HOSTS_PROD" >> ~/.ssh/known_hosts
- chmod 644 ~/.ssh/known_hosts

In the deployment component, username and host variables are used to connect to the target server via SSH. The commands run over that SSH session change directory into the deployment target path and use “git pull” to fetch and merge the updated code. This ensures the proper branch is pulled to the production and down-level environments.

production-deployment:
 stage: deploy
  script:
    - ssh $DEPLOYMENT_USER@$DEPLOYMENT_HOST_PROD "cd '$DEPLOYMENT_PATH_PROD'; git pull origin master"
  only:
    - master

development-deployment:
 stage: deploy
 script:
   - ssh $DEPLOYMENT_USER@$DEPLOYMENT_HOST_DEV "cd '$DEPLOYMENT_PATH_DEV'; git pull origin development"
 only:
   - development

Now when I make changes to the project code,

Assuming the tests still pass, the deployment will run

If you click on the deployment component, you can see what changes were pulled to the target server

And, yes, the updated files are on my target server.

 

VSCode Tab Key Not Working

Tab suddenly stop tabbing in VSCode? Try hitting ctrl+m — evidently there’s another ‘mode’ for the tab key where it changes focus instead of tabbing. Very cool and useful when used deliberately. Very “huh?!?!” when you accidentally hit ctrl+m 🙂

From https://code.visualstudio.com/docs/getstarted/keybindings

Ctrl+M Toggle Use of Tab Key for Setting Focus editor.action.toggleTabFocusMode

Of course if that wasn’t your problem … focus mode is turned on now & you’ll want to hit ctrl+m again to change back to tab mode!

Corrupted Spreadsheets From PHPSpreadsheet (andPHPExcel)

I need to deliver Excel files to the browser, so used php://output as the save location. Does exactly what I want except …

Excel says it has a problem with some of the file content. It’s recoverable – click “Yes” and you’ll see all of the spreadsheet data. But no one is going to want to run a repair on every single file they download from my site!

I confirmed the buffer was being cleared, that I didn’t have any extraneous PHP errors getting inserted into the spreadsheet data. My output was clean – it was also corrupt. I’d actually started using the old PHPExcel module, installed and changed over to PHPSpreadsheet because I know PHPExcel is not maintained. But the problem persisted. I started reading through the docs for PHPSpreadsheet to see if I could find a hint.

https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#redirect-output-to-a-clients-web-browser

Caution:

Make sure not to include any echo statements or output any other contents than the Excel file. 
There should be no whitespace before the opening <?php tag and at most one line break after the closing ?> tag 
(which can also be omitted to avoid problems). 
Make sure that your script is saved without a BOM (Byte-order mark) because this counts as echoing output. 
The same things apply to all included files. 
Failing to follow the above guidelines may result in corrupt Excel files arriving at the client browser, 
and/or that headers cannot be set by PHP (resulting in warning messages).

Do I have more than one newline after the closing “?>” tag? Sure do!

Got rid of the extra newline, and the downloaded file is fine.

 

Oracle Query Returns JSON

I’m using Oracle a lot in my new job; and, in the process, I am learning about a lot of neat Oracle database features. Today, I discovered JSON_OBJECT:

SELECT JSON_OBJECT
(KEY 'CDID" VALUE C1.circuit_design_id,
KEY 'NODE_STATUS' VALUE circuit_position.CIRCUIT_NODE_STATUS, KEY "CDID3' VALUE circuit_position.circuit_design_id_3) jsonCircuitResults
FROM circuit C1
LEFT OUTER JOIN circuit_position on C1.circuit_design_id = circuit_position.circuit_design_id
WHERE C1.circuit_design_id = '54535525' and circuit_position.circuit_design_id_3 is not null;

The response is JSON –

 

Using File System As A Git Repository

I’ve used GitLab for quite some time, and as a full featured CI/CD platform that also provides git functionality … it’s awesome. But it’s also serious overkill for someone who wants to coordinate code with another developer or two. Or just keep history for their code. Or a backup. To accomplish this, all you need is drive space. If you’ve got a file server, any folder on the share can be a git repository.

On the server, create a git repository and add something to it.

Z:\Temp\test>git init
Initialized empty Git repository in Z:/Temp/test/.git/

Z:\Temp\test>notepad testfile.txt

Z:\Temp\test>git add testfile.txt

Z:\Temp\test>git commit -m "Initial file upload"
[master (root-commit) 9a3ebe7] Initial file upload
1 file changed, 1 insertion(+)
create mode 100644 testfile.txt

Then on your client, either clone the repo from the drive path

C:\Users\lisa>git clone file://z:/temp/test
Cloning into 'test'...
remote: Enumerating objects: 3, done.
remote: Counting objects: 100% (3/3), done.
remote: Total 3 (delta 0), reused 0 (delta 0)
Receiving objects: 100% (3/3), done.

Or from the UNC path

C:\Users\lisa>git clone file://server01.example.com/data/temp/test
Cloning into 'test'...
remote: Enumerating objects: 3, done.
remote: Counting objects: 100% (3/3), done.
remote: Total 3 (delta 0), reused 0 (delta 0)
Receiving objects: 100% (3/3), done.

I prefer to use the UNC paths – if my drive letter fails to map for some reason, the UNC path is still available.

If you’ve got pre-existing code, there’s a bit of a different process. On the server, create an empty folder and use “git init” to initialize the empty repo. On the client where the code exists, run:

git init
git add *
git commit -m “Initial code upload”
git remote add origin git clone file://server01.example.com/data/temp/test
git push origin master

 

Inspecting An Element

In Firefox’s developer tools — instead of attempting to navigate through the HTML code to find the element, just right-click on it and select “Inspect Element”

You’ll get dropped into the Inspector tab right where you need to be.

There’s also an element picker tool that you can use instead — click on it & then click on the element within the page. Same result.

Setting up gitlab-runner

CLI on the GitLab server:

# Set up the GitLab Repo
curl -L https://packages.gitlab.com/install/repositories/runner/gitlab-runner/script.rpm.sh

# Install package
yum install gitlab-runner

# verify runner is executable
ll `which gitlab-runner`
# If needed, flag it executable – shouldn’t be a problem with RPM installations, but it’s been a problem for me with manual installs
#chmod ugo+x /usr/local/bin/gitlab-runner

# Register a runner
gitlab-runner register

# use URL & token from http://<GITLABSERVER>/admin/runners
# add tags, if you want to use tags to assign runner
# executor: shell (and docker, if you want to use docker executors. The shell executor is the simplest case, so I am starting there)

# start the runner
gitlab-runner start

On the GitLab Web GUI:

Admin section => Overview => Runners. Click pencil to edit the runner and uncheck “Lock to current projects”, and (unless you want to use tagging) check “Run untagged jobs”

** I was getting an error in every pipeline job saying the git command was not found.

For most other commands, you can append to the path in the before_script section of your .gitlab-ci.yml

before_script:
– export PATH=$PATH:/opt/whatever/odd/path/bin

But that doesn’t work in this case because we’re not getting that far: the bootstrap “stuff” cannot fetch the project to see the before script. Git, on my system, was part of the GitLab package. I just created a symlink into a “normal” binary location:

root@gitlab:~# which git
/opt/gitlab/embedded/bin/git
root@gitlab:~# ln -s /opt/gitlab/embedded/bin/git /usr/bin/git

And we’ve got successful test execution:

 

Git: Listing Conflicting Files

To list the unmerged files — where you’ve got merge conflicts to resolve:

git diff --name-only --diff-filter=U

Filters are:

  • Added (A)
  • Copied (C)
  • Deleted (D)
  • Modified (M)
  • Renamed (R)
  • Type changed (T)
  • Unmerged (U)
  • Unknown (X)
  • pairing Broken (B)

(use lower case letters to exclude)