Last active
April 8, 2016 07:56
-
-
Save zilongshanren/1e31c404ff8440f49713 to your computer and use it in GitHub Desktop.
a helper program to generate random numbers
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import xlwt | |
import random | |
import numpy | |
import json | |
import statistics | |
ezxf = xlwt.easyxf | |
sampleCount = 10 | |
totalSample = 1 | |
input_mean = [] | |
input_deav = [] | |
deviation_mean = 0.1 | |
deviation_std = 0.01 | |
excel_file_name = "" | |
excel_sheet_name = "" | |
def parse_json(filename): | |
global totalSample | |
global input_mean | |
global input_deav | |
global deviation_mean | |
global deviation_mean | |
global deviation_std | |
global deviation_std | |
global excel_file_name | |
global excel_sheet_name | |
json_data = open(filename) | |
data = json.load(json_data) | |
excel_file_name = data["filename"] | |
excel_sheet_name = data["sheetname"] | |
input_mean = data["mean"] | |
input_deav = data["stddev"] | |
deviation_mean = data["meanDeviation"] | |
deviation_std = data["stddevDeviation"] | |
totalSample = len(input_deav) | |
print(totalSample) | |
def write_headers(sheet, sampleCount, style): | |
sheet.write_merge(0, 0, 0, sampleCount-1, '随机数值', style) | |
sheet.write_merge(0, 0, sampleCount, sampleCount, '最大值', style) | |
sheet.write_merge(0, 0, sampleCount+1, sampleCount+1, '最小值', style) | |
sheet.write_merge(0, 0, sampleCount+2, sampleCount+2, '随机数均值', style) | |
sheet.write_merge(0, 0, sampleCount+3, sampleCount+3, '随机数方差', style) | |
sheet.write_merge(0, 0, sampleCount+4, sampleCount+4, '输入的平均值', style) | |
sheet.write_merge(0, 0, sampleCount+5, sampleCount+5, '输入的方差', style) | |
def generate_random(sampleCount, mean, stddev): | |
random_list = [] | |
while True: | |
del random_list[:] | |
for i in range(sampleCount): | |
random_number = random.gauss(mean, stddev) | |
random_list.append(int(random_number)) | |
generated_mean = numpy.mean(random_list) | |
generated_stddev = statistics.stdev(random_list) | |
if generated_mean > (mean - deviation_mean) and generated_mean < (deviation_mean + mean) and (generated_stddev > stddev - deviation_std) and (generated_stddev < stddev + deviation_std): | |
break | |
return random_list | |
def get_raw_random_data(list): | |
new_list = list | |
old_max = max(list) | |
old_min = min(list) | |
for i in range(3): | |
new_list.append(random.randint(1,5) + old_max) | |
new_list.append(old_min - random.randint(1,5)) | |
random.shuffle(new_list) | |
return new_list | |
def write_xls(file_name, sheet_name, heading, totalSample): | |
wb = xlwt.Workbook() | |
ws0 = wb.add_sheet(sheet_name) | |
font = xlwt.Font() | |
font.name = '宋体' | |
font.bold = True | |
headingStyle = xlwt.XFStyle() | |
al = xlwt.Alignment() | |
al.horz = xlwt.Alignment.HORZ_CENTER | |
al.vert = xlwt.Alignment.VERT_CENTER | |
headingStyle.alignment = al | |
headingStyle.font = font | |
write_headers(ws0, sampleCount, headingStyle) | |
# add new colour to palette and set RGB colour value | |
xlwt.add_palette_colour("custom_colour_orange", 0x21) | |
wb.set_colour_RGB(0x21, 253, 181, 44) | |
xlwt.add_palette_colour("custom_colour_blue", 0x22) | |
wb.set_colour_RGB(0x22, 63, 112, 188) | |
xlwt.add_palette_colour("custom_colour_yellow", 0x23) | |
wb.set_colour_RGB(0x23, 255, 255, 60) | |
xlwt.add_palette_colour("custom_colour_green", 0x24) | |
wb.set_colour_RGB(0x24, 165, 206, 91) | |
xlwt.add_palette_colour("custom_colour_dark_green", 0x25) | |
wb.set_colour_RGB(0x25, 99, 175, 87) | |
xlwt.add_palette_colour("custom_colour_light_blue", 0x26) | |
wb.set_colour_RGB(0x26, 99, 174, 237) | |
for i in range(totalSample): | |
# now you can use the colour in styles | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_orange') | |
j = 0 | |
style.num_format_str = "0" | |
style.alignment = al | |
mean = input_mean[i] | |
stddev = input_deav[i] | |
random_list = generate_random(sampleCount, mean, stddev) | |
for num in random_list: | |
ws0.write(i+1, j, num, style) | |
j = j + 1 | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_blue') | |
formular = "Max(A" + str(i+2) + ":" + str(chr(97+sampleCount-1)) + str(i+2) + ")" | |
style.alignment = al | |
ws0.write(i+1, j, xlwt.Formula(formular), style) | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_yellow') | |
formular = "Min(A" + str(i+2) + ":" + str(chr(97+sampleCount-1)) + str(i+2) + ")" | |
style.alignment = al | |
ws0.write(i+1, j+1, xlwt.Formula(formular), style) | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_green') | |
style.num_format_str = "0.0" | |
style.alignment = al | |
formular = "Average(A" + str(i+2) + ":" + str(chr(97+sampleCount-1)) + str(i+2) + ")" | |
ws0.write(i+1, j+2, xlwt.Formula(formular), style) | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_orange') | |
style.num_format_str = "0.00" | |
style.alignment = al | |
formular = "Stdev(A" + str(i+2) + ":" + str(chr(97+sampleCount-1)) + str(i+2) + ")" | |
ws0.write(i+1, j+3, xlwt.Formula(formular), style) | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_green') | |
style.num_format_str = "0.0" | |
style.alignment = al | |
ws0.write(i+1, j+4, mean, style) | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_dark_green') | |
style.num_format_str = "0.00" | |
style.alignment = al | |
ws0.write(i+1, j+5, stddev, style) | |
#write for the remainders | |
style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour_light_blue') | |
raw_random_list = get_raw_random_data(random_list) | |
style.alignment = al | |
j = 0 | |
for num in raw_random_list: | |
ws0.write(i+totalSample+3, j, num, style) | |
j = j + 1 | |
wb.save(file_name) | |
if __name__ == '__main__': | |
parse_json("data.json") | |
headings = ['随机数', '最大值', '最小值', '随机数均值', '随机数方差', '输入的平均值', '输入的方差'] | |
write_xls(excel_file_name, excel_sheet_name, headings, totalSample) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment