[PYTHON] Excel tried to summon QR code with black magic

One day

I will be a copy-paste craftsman ╭ (・ ㅂ ・) و ̑̑ Good!

╰ (`• ω •) ╮-= ni <It is troublesome to make various things from the free QR code creation site. I can't create a website that sets parameters and generates a QR code (꒪ ཫ ꒪;)

Prerequisites

• Created as a tool to ease development
• QR code is used in Hololens2
• Works even in places where there is no python environment (Windows only)
• Since Japanese characters are garbled, the base64 converted character string is QR coded.

Development environment

Installation package

Bulk package installation

Used to align the environment

requirements.txt

altgraph==0.17
colorama==0.4.3
future==0.18.2
numpy==1.19.0
pandas==1.0.5
pefile==2019.4.18
Pillow==7.1.2
pip==19.2.3
PyInstaller==3.6
python-dateutil==2.8.1
pytz==2020.1
pywin32-ctypes==0.2.0
qrcode==6.1
setuptools==41.2.0
six==1.15.0
xlrd==1.2.0

Environment creation

• When the development environment is visual studio

1. Create virtual environment --Python environment Right click-> Add environment

--Click OK as it is (you can install the package when creating a virtual environment)

2. Add requirements.txt to your project
3. Right-click on the created virtual environment-> click Install from requirements.txt

Excel data

code

1. Convert cell information to a character string, determine a delimiter, and concatenate
2. Convert the string to base64
3. QR code the base64 string

ExcelToQRCode.py

import sys
import os
import pandas as pd
import qrcode
import base64

def main():
	#Command line arguments
	args = sys.argv
	#Do not process if the argument is less than two
	if len(args) < 2:
		exit()

	#Excel file judgment
	split = os.path.splitext(args[1])
	if split[1] != '.xlsx':
		exit()

	#Get the first sheet
	excel_data = pd.read_excel(args[1], sheet_name = 0, encoding='utf-8')

	param_list = []
	for i, row in excel_data.iterrows():
		if i > 0: 
			temp = ""
			for value in row.iloc[2:8]:
				temp += str(value) + '|'
			temp = temp[:-1]
			full_path = os.getcwd() +'\\' + row.iloc[1] + '.png'
			param_list.append((full_path, temp))

	for index in range(len(param_list)):
		image_qr = qrcode.make(base64.b64encode(param_list[index][1].encode('utf-8')))
		image_qr.save(param_list[index][0])

	if len(param_list) < 1:
		print("There was no QR code to create")
	else:
		print("I created a QR code")

if __name__ == '__main__':
	main()

exe conversion

Type the following command to make it an exe The file size of the exe becomes big, but I close my eyes (˘ω˘)

python

pyinstaller hoge.py --onefile --noconsole

• --onefile --Combine in one file

• --noconsole ――No black screen

How to use

Drag & drop xlsx file to exe

Code to read

Utility.cs

public static class Utility
{
    public static string Base64Decode(string value)
    {
        return System.Text.Encoding.UTF8.GetString(System.Convert.FromBase64String(value));
    }

    public static List<string> GetQRCordData(string value)
    {
        string data = Utility.Base64Decode(value);
        return new List<string>(data.Split(new[] { "|" }, System.StringSplitOptions.None));
    }
}