1, Foreword
The opportunity is: I asked in the audio tour group, how do the bots in the group check B30? Then the boss replied that the robot hung up recently and couldn't be found. I rubbed one myself.
Although it is impossible to directly obtain the singing data from the server, the B30 query can be realized freely after manual input.
Final effect
Specially made a good icon
2, Information summary
- Simple information processing of Excel
- Simple application of VBA
- Json processing in Python
- Excel worksheet reading in Python
- Python image operation
3, Text
1. Input of singing performance (Excel formula + VBA)
Because excel itself can easily save some records and carry out simple operations, it is decided to directly complete all input work in Excel, and then use Python module to read xlsm files. (xlsm: macro enabled excel Workbook)
The Main worksheet is used to store title and rating information for all songs
Ask the wiki kindly for a fixed number list
Yes, the automatic / one click Update of the fixed number table has not been realized yet. If 616 is updated, you have to do it again.
The good thing is, just copy and paste.
1, Make an entry UI
Calc worksheet for users to fill in and import grades
What elements do you need to provide for general score entry? What song did I play? What is the difficulty level? What's the score? Then calculate the effective ptt of this song, and add it to the grade bank.
Just let me enter the song name. I'm sure I won't do it. Not to mention the trouble, some song titles in area can't be typed by people, and I don't want to enter a score and check whether there is any spelling mistake.. Therefore, a simple search macro is made, and the results can be returned by inputting some song names, and there is a certain fault tolerance.
General idea of search algorithm: compare characters one by one. If they are the same, continue to compare and increase the matching degree. If not, deduct the matching degree and judge whether five consecutive characters are different. If so, interrupt the comparison and jump to the next comparison target. Update the list of matching degree top10 after the first comparison is completed. After all comparisons are completed, the matching degree top10 will be returned, and the most matching song name will be automatically filled in.
(due to the algorithm settings in the bold part, there is only one result in the top10 list in most cases
The difficulty level and score can only be entered manually by the user. After completion, Excel calculates the single ptt through the formula. Specific calculations are made public on the wiki. The formula is as follows:
=MAX(IF(F5>=10000000,HLOOKUP(E5,F2:I3,2,FALSE)+2,IF(F5>=9800000,HLOOKUP(E5,F2:I3,2,FALSE)+1+(F5-9800000)/200000,HLOOKUP(E5,F2:I3,2,FALSE)+(F5-9500000)/300000)),0)
As for the search button and the import button, they are just two rounded rectangles with macros assigned. Because of the existence of macros, documents can only be saved in xlsm format.
A total of 4 macros are used. In addition to search macros, import macros and two easy to maintain one key sorting + index generation are also done. Search efficiency can be improved according to the index.
B30 worksheet. Python will read the data of this worksheet for image generation later
2. Get the music drawing of the corresponding song (Python+apk unpacking)
The direct disadvantage of unpacking is that it will occupy dozens of MB of hard disk, and you need to manually add new music and painting after updating. But I don't want to do it. When it slowly climbs back to the music and painting I want, this advantage is enough.
Download the apk installation package of area and change the suffix zip, we found all the things we need under the songs folder: the music drawings neatly stored according to the song id, and the songlist file.
The problem here is that the song name is different from the song id and needs to be converted.
Simple analysis, the songlist file is written by Json, so it can be used directly. Moreover, it stores all the information of each song, including id and song name (en translation is enough, because the wiki provides English; some song names in other languages are not available).
Therefore, import the json module in Python and process it.
File here_ The path variable is the path of songlist, which involves obtaining the current running path, which will be discussed later. Songlist has no suffix. Don't confuse it with folders. By the way, don't forget to close the door at the end.
filejson = open(file_path,'r',encoding='utf-8') songdic=json.load(filejson) filejson.close()
And import the openxlrd module to open the Workbook (the filename variable points to) and worksheet B30 I need. Use the for loop to traverse the cell(i,1), that is, the cell in column 1, and append it to a list. Modeled on several List, all the data we need in the worksheet can be invoked in Python.
book = xlsx.load_workbook(filename)
sheet = book['B30']
Songs=[]
for i in range(2,32):
Songs.append(sheet.cell(i,1).value)Then, find the song id corresponding to the ith song name in order in Json (here and below, we will use Json to refer to the dictionary songdic generated by songlist) and apply it to another List as usual.
This completes the acquisition of the song id. Later, when you want to get the music and painting, you just need to modify it slightly. To open the picture, you need to import PIL Image module. Code Preview:
for i in song_index:
if(song_dl[i]):
dlstr='dl_'
else:
dlstr=''
#A special folder name that handles songs that need to be downloaded. I found it when unpacking. 616 you did a good job. You did a good job
if(diff[i].lower()=='byd'):
flstr='3.jpg'
else:
flstr='base.jpg'
#Special music drawing for handling byd difficulty
im=Image.open(os.path.join(root_path,'src/songs',dlstr+song_id[i],flstr))#Open corresponding curve drawingNote 1: OS path. join(path1,path2,...) It can help splice and generate a legal path.
Note 2: root_path is the current path.
3. Start processing pictures
The reason why I chose PIL instead of cv2 is that the cv2 module will report an error after the exe is packaged.. It can only be deleted reluctantly, and all background images are preprocessed into 2560 * 1440 + Gaussian blur.
First, paste music and painting on the background
General assumption: on a 2560 * 1440 background, each song occupies 256 * 356 (leaving 100px for the text vertically), in which the music drawing is 240 * 240 (leaving 16px blank), and slightly modified to show the difficulty level and whether PM information is required. (modules to be used: PIL.Image, PIL.ImageFont, PIL.ImageDraw)
Among them, the background randomly selects a picture from a folder (/ src/bgs) (the os module obtains its list, and the random module generates random numbers). As for where so many backgrounds come from, I choose to unpack and get some CG pictures.
Therefore, open the background directly to the output, and all subsequent operations are performed on the output. os.listdir(path) can generate a List and image of all contents under the path Open (path) opens the picture file pointed to by path, random Choice (List) can return a random element in the List.
bg=os.listdir(os.path.join(root_path,'src/bgs')) output=Image.open(os.path.join(root_path,'src/bgs',random.choice(bg)))
Then you can post a song and painting. Since the above is a single variable traversal, you need to use simple remainder (%) and division (/ /) to calculate the coordinates.
for i in song_index:
if(song_dl[i]):
dlstr='dl_'
else:
dlstr=''
#A special folder name that handles songs that need to be downloaded. I found it when unpacking. 616 you did a good job. You did a good job
if(diff[i].lower()=='byd'):
flstr='3.jpg'
else:
flstr='base.jpg'
#Special music drawing for handling byd difficulty
im=Image.open(os.path.join(root_path,'src/songs',dlstr+song_id[i],flstr))#Open corresponding curve drawing
im=im.resize((240,240))#Adjust the size. A graph occupies a total of 256 * 356 space, and the excess is left as blank and data text
output.paste(im,(256*(i%10)+8,350+356*(i//10)+8,256*(i%10+1)-8,350+356*(i//10+1)-108))
Note 1: image Among the two parameters passed by resize ((x, y) [, image. Antialias]), the second is optional, and the first is a binary indicating the size of the scaling result. Therefore, without forcibly specifying the scaling method, two layers of parentheses should be used, such as img resize((256,256)). Otherwise, an error is reported.
Note 2: image The box in paste (icon, box [, mask]) can specify only the binary coordinates of the upper left corner, or the quad coordinates of the upper left corner and the lower right corner at the same time. When using Quad coordinates, if the size of the icon does not match the size marked by the box, an error will be reported directly. In addition, when pasting PNG pictures, you need to pass the third parameter to pass the transparency channel of the mask, such as img paste(myicon,(0,0),myicon). You can also use the transparency channel of another picture file.
II. Slightly modify each song and painting
I want to display the difficulty of the corresponding song directly in the upper right corner of the song drawing. And draw an 8px wide vertical bar rectangle on the right side of the curve drawing, and use the corresponding color of a certain difficulty to assist in transmitting this information. In addition, there is no need to continue to push points for songs that have been PM. I'm too vegetable to theory, so I hope to mark them out in particular.
When unpacking, I turned to the material I wanted and copied it all to / src/tags. In addition, the color of each difficulty is taken (the color number can be directly copied from the QQ screenshot) and written directly into Python. Therefore, it is defined as follows:
clr={'byd':(125,21,43),'ftr':(112,47,99),'prs':(139,163,96),'pst':(62,159,183)}
badge={}
for dif in ['pst','prs','ftr','byd']:
tim=Image.open(os.path.join(root_path,'src/tags',dif+'.png'))
tim=tim.resize((73,22))
badge[dif]=timI have defined both as dictionaries, because the data in Excel is so standard that it can be used directly in a short time. The "append" of the dictionary only needs to assign a value to a key without a value.
Another PM tag is also a direct image Open, no more details.
Because of the display order, the rectangle painted 8px must precede the difficulty tag. Image is used here Putpixel (pos, rgb), where pos is a binary (x,y) and rgb is a triple (r,g,b).
A double-layer cycle is carried out directly. Because there is a big loop for i in index on the outside, it is difficult to obtain a song directly through the previously saved List.
for x in range(256*(i%10+1)-16,256*(i%10+1)-8):
for y in range(350+356*(i//10)+8,350+356*(i//10+1)-108):
output.putpixel((x,y),clr[diff[i]])Then there is the tag, which is still image Paste(), no more details
Then post PM (if you pm, give you a little P flower!). Judge that if the score of this song exceeds 10000000, then image.paste(), the PM mark has a transparency channel, so you have to pass three parameters. It is also a very simple code. I won't repeat it.
III. addition of text information
Then draw text. Three sentences are mainly used:
font=ImageFont.truetype(font_file, size) draw = ImageDraw.Draw(img) draw.text((x,y),text,(r,g,b),font)
Define some fonts in advance, including font files (*. ttc, *. ttf) and font size
Define an ImageDraw object and tell it that you have to draw on img
Call the text method of ImageDraw. This parameter is easy to understand
Among them, the font file uses the same font (Xie Guangguang) as the unpacked settlement score and copies it to / src /
As for the string control of text, you can mention a little: if you are familiar with c but not py, you can format the output string like this:
"%s, %.1f is your score."%(player_name, score)
The smell of home.
In this way, you can complete the drawing of all the text.
End use image Save() to save the picture. For details, just refer to other big guys' blogs (I can't understand).
4. Some small details
Use Tkinter An empty Tk graphic window will pop up when you open a file and specify a path in the FileDialog box. Therefore, add the following code:
root = tkinter.Tk() root.withdraw()#Hide tk generated window #main program root.destroy()#Destroy tk left windows
tk this wave is a pure dialog tool.
image.putpixel() is inefficient when performing a wide range of pixel operations. (and I have to darken almost the whole picture
4, Postscript
The whole project has only been started for three days, and I knew nothing about this content before I started. I even had some very shallow knowledge of Python by myself. Looking back, many places are rough.
1. At present, we can only stay in the "laboratory"
Because from beginning to end, the tester is only me, and I input all kinds of data regularly, so many vulnerabilities are not allowed to appear. For example, if the difficulty is not all lowercase, what will happen if I enter an "FTR"? Or just misspell?
On the one hand, there is no automatic adjustment of input to ensure the normal operation of the program to the greatest extent, on the other hand, there is no error. If one day it collapses, I may not even know why it collapses.
At least go and make a mistake first.
2. Optimize
Because there are only a few hundred songs in the entire music library of area, there is no chance to show the performance problems of many algorithms, and the task can be completed in the whole process. In addition, I can safely and boldly define a pile of games, which may cause a lot of waste.
bot, hurry up. Check ptt. Even if I can calculate B30, can I still calculate R10? I'm dying if I enter so many
Thank you, lowiro.
This project is only for personal learning and communication.
5, Python source code
import tkinter.filedialog
import openpyxl as xlsx
import json
from PIL import Image, ImageFont, ImageDraw
import os
import sys
import random
gen_path = os.path.dirname(os.path.realpath(sys.argv[0]))#After being packaged as exe, you can directly use OS Getcwd() or abspath() cannot be obtained correctly. The solution is copied from the left
clr={'byd':(125,21,43),'ftr':(112,47,99),'prs':(139,163,96),'pst':(62,159,183)}#Four difficult color definitions, easy to use for a while
#Main()
root = tkinter.Tk()
root.withdraw()#Hide tk generated window
player=input('Enter your Player Name:')
print('Select a B30 file:')
filename=tkinter.filedialog.askopenfilename(title='choice B30 surface', filetypes=[('All documents','.*'),('Excel file','.xls'),('Excel2003 file','.xls'),('Excel Macro enabled Workbook','.xlsm')])
print('filename=%s'%(filename))
print('Determine Output Directory:')
savepath = tkinter.filedialog.askdirectory(title='Save path selection')
print('savepath=%s'%(savepath))
#Get the user-defined player name, Excel workbook and export path and echo
badge={}
for dif in ['pst','prs','ftr','byd']:
tim=Image.open(os.path.join(gen_path,'src/tags',dif+'.png'))
tim=tim.resize((73,22))
badge[dif]=tim
#Open the picture file corresponding to each difficulty name for backup and store it in a dictionary
pm=Image.open(os.path.join(gen_path,'src/tags/pm.png'))
#Open the picture marked by PM for standby
book = xlsx.load_workbook(filename)
sheet = book['B30']
#openpyxl module opens a file and saves a variable for B30 worksheet
filejson = open(os.path.join(gen_path,'src/songlist'),'r',encoding='utf-8')
songdic=json.load(filejson)
#Open the songlist file and transfer the json module to the dictionary songdic for backup
filejson.close()
#Please close the door behind you
font=ImageFont.truetype(os.path.join(gen_path,'src/font.ttf'), 42)
titlefont=ImageFont.truetype(os.path.join(gen_path,'src/font.ttf'), 102)
#Font file definition. Write text on the picture and call it directly
bg=os.listdir(os.path.join(gen_path,'src/bgs'))
#Background file list acquisition
Songs=[]
diff=[]
ptt=[]
Sc=[]
#Ten thousand list s are defined. Keep it for later and slowly append
song_index=[]
song_id=[]#Store the folder location corresponding to each song
song_dl=[]#Do you need to download each song
avg=0.0
for i in range(2,32):
Songs.append(sheet.cell(i,1).value)
diff.append(sheet.cell(i,2).value)
ptt.append(sheet.cell(i,3).value)
avg=avg+sheet.cell(i,3).value
Sc.append(sheet.cell(i,4).value)
#Read the B30 worksheet and save it in the list
cnt=0
for songtitle in Songs:
found=False
for i in songdic['songs']:
if(i['title_localized']['en']==songtitle):#Violence searches songdic for the song I want. If you can't find it, it will collapse (cross it out)
song_id.append(i['id'])
song_dl.append(i.get('remote_dl',False))
song_index.append(cnt)
found=True
break
if( not found):
print('ERROR:cannot find ',songtitle)
break
cnt=cnt+1
avg=avg/(cnt)#B30 average PTT calculation
output=Image.open(os.path.join(gen_path,'src/bgs',random.choice(bg)))
#Randomly select a picture in src/bgs / as the background. Size 2560 * 1440
print('Background image fetched. Processing...')
for x in range(1,2560):
for y in range(1,1440):
rgb=output.getpixel((x,y))
output.putpixel((x,y),(int(rgb[0]/1.5),int(rgb[1]/1.5),int(rgb[2]/1.5)))
#The whole is darkened, otherwise the words can't be seen clearly. But the efficiency is very low
print('Process complete. Stitching song & data...')
draw = ImageDraw.Draw(output)#After that, all drawing operations pass through this draw
for i in song_index:
if(song_dl[i]):
dlstr='dl_'
else:
dlstr=''
#A special folder name that handles songs that need to be downloaded. I don't know if I don't unpack. 616 well done, well done
if(diff[i].lower()=='byd'):
flstr='3.jpg'
else:
flstr='base.jpg'
#Special music drawing for handling byd difficulty
im=Image.open(os.path.join(gen_path,'src/songs',dlstr+song_id[i],flstr))#Open corresponding curve drawing
im=im.resize((240,240))#Adjust the size. A graph occupies a total of 256 * 356 space, and the excess is left as blank and data text
output.paste(im,(256*(i%10)+8,350+356*(i//10) + 8256 * (I% 10 + 1) - 8350 + 356 * (I / / 10 + 1) - 108)) # basic coordinate calculation. image.paste(src,(x1,y1,x2,y2))
for x in range(256*(i%10+1)-16,256*(i%10+1)-8):
for y in range(350+356*(i//10)+8,350+356*(i//10+1)-108):
output.putpixel((x,y),clr[diff[i]])#Draw an 8px vertical rectangle on the right of the curve drawing, and fill it with the corresponding difficulty color
output.paste(badge[diff[i]],(256*(i%10+1)-8-60,350+356*(i//10) , 256 * (I% 10 + 1) - 8-60 + 73350 + 356 * (I / / 10) + 22)) # stick the tag corresponding to the difficulty. It is also the basic coordinate calculation.
if(Sc[i]>=10000000):
output.paste(pm,(256*(i%10+1)-8-97,350+356*(i//10 + 1) - 108-73256 * (I% 10 + 1) - 8 + 10350 + 356 * (I / / 10 + 1) - 108 + 10), PM) # if you pm, you will be rewarded with a little P flower
draw.text((256*(i%10)+8,350+356*(i//10 + 1) - 105), '% s%. 6F'% ('ptt: ', PTT [i]), (255230255), font) # draw single PTT
draw.text((256*(i%10)+8,350+356*(i//10 + 1) - 55), '% d% s'% (SC [i],'pts'), (255230255), font) # draw single score
draw.text((80,40),'%s%s'%('Player:',player),(255,230,255),titlefont)#Draw Title: player name
draw.text((80,180),'%s%.6f'%('B30 Average:',avg),(220,200,220),titlefont)#Drawing title: PTT
output.save(os.path.join(savepath,"output.png"),"PNG")#Export to destination path
print("Complete!")
#End Main
root.destroy()#Destroy the opening tk window. tk this wave of pure dialog tools