前几天笔者隔壁测绘专业的同学去实习了，我就想起来好几月前边学pandas和numpy边做的闭合导线测量的内业计算器，如果你们测站多懒得算，就可以拿去用一下，效果如下：
在给定表填入测量值和已知值后，运行代码
链接:百度网盘 请输入提取码
注意事项，一定要看
1. 由于这是笔者边看边写的，很多地方不尽人意(基本每段有注释)，写到后面发现很多地方能优化，几十行>>几行:( ，但是我懒得搞了，反正就几秒的事，性能什么的一边去吧( *ˊᵕˋ)✩︎‧₊
2. 库一定要安装齐全，且版本不与你的python版本冲突，或者pycharm直接上anaconda环境
3. 更改读取和保存的路径，代码里是笔者的，比如下面的:
4. 如果要增加测站，要手动插入行，比如
至 
5. 还可以打包成exe，看起来更干净
#在py所在目录运行终端:
pyinstaller --onefile --hidden-import=openpyxl.cell._writer cool.py
# onefile: 创建一个exe文件，执行速度慢 --onefile 可以简写为 -F
# onedir: 创建一个含有exe文件的目录(文件夹)，执行速度相比前者大大提高 可以简写为 -D6. 如果你想问为啥不直接在excel里操作，问就是我学啥肯定用啥呀，反正不咋麻烦(||๐_๐)
7. 可以的话点个收藏呗(๑╹ヮ╹๑)ﾉ
代码放在这(加字数)
import numpy as np
import sympy as sy
import pandas as pd
import math
from decimal import Decimal, getcontext
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Alignment,fonts,Border,Side,PatternFill
from openpyxl.utils import get_column_letter
# 快捷定义边框形式
# 定义边框形式(可删除='000000'然后在实参输入字体颜色)
def set_border(t_border, b_border, l_border, r_border,
t_color='000000', b_color='000000', l_color='000000', r_color='000000'):
border = Border(top=Side(border_style=t_border, color=t_color),
bottom=Side(border_style=b_border, color=b_color),
left=Side(border_style=l_border, color=l_color),
right=Side(border_style=r_border, color=r_color))
return border
# 区域边框函数：
# 参数:(工作表, 起始行号, 起始列号, 末尾行号, 末尾列号)
# 1.区域全部单元格细边框/2.区域外围粗边框里面全部单元格细边框
def set_border_1(ws, start_row, start_col, end_row, end_col):
# 遍历区域内的所有单元格并设置边框
for row in range(start_row, end_row + 1):
for col in range(start_col, end_col + 1):
cell = ws.cell(row=row, column=col)
cell.border = set_border('thin', 'thin', 'thin', 'thin', )
def set_border_2(ws, start_row, start_col, end_row, end_col):
set_border_1(ws, start_row, start_col, end_row, end_col)
# 为区域的顶部和底部边缘设置边框
for col in range(start_col, end_col + 1):
ws.cell(row=start_row, column=col).border = set_border('thick', 'thin', 'thin', 'thin')
ws.cell(row=end_row, column=col).border = set_border('thin', 'thick', 'thin', 'thin')
# 为区域的左侧和右侧边缘设置边框
for row in range(start_row, end_row + 1):
ws.cell(row=row, column=start_col).border = set_border('thin', 'thin', 'thick', 'thin')
ws.cell(row=row, column=end_col).border = set_border('thin', 'thin', 'thin', 'thick')
# 对四个角进行处理:
ws.cell(row=start_row, column=start_col).border = set_border('thick', 'thin', 'thick', 'thin')
ws.cell(row=start_row, column=end_col).border = set_border('thick', 'thin', 'thin', 'thick')
ws.cell(row=end_row, column=start_col).border = set_border('thin', 'thick', 'thick', 'thin')
ws.cell(row=end_row, column=end_col).border = set_border('thin', 'thick', 'thin', 'thick')
# 一. 列表度分秒统一转换为秒
def all_change(list):
number1 = list[0]*3600 + list[1]*60 + list[2]
return number1
# 二. 秒转换为列表度分秒
def all_change_back(number1):
list_1 = [number1 // 3600, (number1 % 3600) // 60, (number1 % 3600) % 60]
return list_1
# 三. 列表度分秒全转换为度数
def degree(list):
number1 = list[0] + list[1]/60 + list[2]/3600
return number1
# 四. 相加: 列表度分秒统一换为秒最后回换函数:
def change(list_1, list_2):
number1 = all_change(list_1)
number2 = all_change(list_2)
number3 = number1 + number2
list_3 = all_change_back(number3)
return list_3
# 五. 相加：计算坐标方位角:
def change_s(list_1, list_2):
number1 = all_change(list_1)
number2 = all_change(list_2)
number3 = number1 + number2
if number3 >= 180 * 3600:
number3 -= 180 * 3600
else:
number3 += 180 * 3600
list_3 = all_change_back(number3)
return list_3
# 六. 增量计算值:奇进偶出，四舍六入，保留两位小数(可以简略)
def round_special_to_two_places(num):
# 乘以1000，将小数点后移三位，进行整数运算
num_times_10000 = int(num * 10000)
if num_times_10000 > 0:
# 获取第二位和第三位小数（在乘以1000后，它们变成了整数部分的最后两位和最后一位）
last_digit = num_times_10000 % 10 # 第三位小数（现在是整数最后一位）
second_to_last_digit = num_times_10000 // 10 % 10 # 第二位小数（现在是整数倒数第二位）
# 如果第三位小数是5，则根据第二位小数的奇偶性来决定是否进位
if last_digit == 5:
if second_to_last_digit % 2 == 1: # 如果第二位是奇数，则进位
num_times_10000 += 10
elif last_digit > 5:
num_times_10000 += 10
else:
num_times_10000 = abs(num_times_10000)
last_digit = num_times_10000 % 10
second_to_last_digit = num_times_10000 // 10 % 10
if last_digit == 5:
if second_to_last_digit % 2 == 1:
num_times_10000 += 10
elif last_digit > 5:
num_times_10000 += 10
num_times_10000 = -num_times_10000
# int(num_times_1000 / 10)为去除第三位小数
return round(int(num_times_10000 / 10) / 1000, 3) # 将处理后的整数部分转回浮点数并除以100，保留两位小数
# 七. 增量计算值:将度分秒列表转为三角函数 (由于自带round函数用在这有点bug
def solve_cos(number1, list_1):
number2 = number1 * sy.cos(math.radians(degree(list_1)))
return round_special_to_two_places(number2)
def solve_sin(number1, list_1):
number2 = number1 * sy.sin(math.radians(degree(list_1)))
return round_special_to_two_places(number2)
content = pd.read_excel('C:/sucai/bihedaoxian.xlsx', dtype={'改正数': str})
# 获取测站数: ·在此有优化算法：详见267行openpyxl的操作
# 读取点号这列原本数据，用于复原
data = content.loc[ : , "点号"]
# 计算点号数字所占行数（通过把非数字改为NaN，在计算点号这列数字所占行数
content['点号'] = pd.to_numeric(content['点号'], errors='coerce')
numeric_rows = content['点号'].dropna()
# 还原点号原本数据
content['点号'] = content['点号'].fillna(data)
# 预留：之后发现写了change(list_1, list_2)函数后可大大简化
i = j = sum1 = sum2 = sum3 = 0
for j in range(len(numeric_rows)-2):
sum1 += list(map(int, content.at[j+1, '观测角(左角)° ′ ″'].split()))[0]
for j in range(len(numeric_rows) - 2):
sum2 += list(map(int, content.at[j+1, '观测角(左角)° ′ ″'].split()))[1]
for j in range(len(numeric_rows)-2):
sum3 += list(map(int, content.at[j+1, '观测角(左角)° ′ ″'].split()))[2]
all_sum = [sum1 + ((sum2 + (sum3 // 60)) // 60), (sum2 + (sum3 // 60)) % 60, sum3 % 60]
content.at[len(numeric_rows), '观测角(左角)° ′ ″'] = all_sum
# 在此为了方便计算，默认闭合差<1′ (闭合导线一般), 闭合差以秒为单位，故直接在秒上分类计算
# 理论值:
angle = [(len(numeric_rows)-4) * 180, 0, 0]
# 这里的操作可以看下面有解释
content['改正角° ′ ″'] = content['改正角° ′ ″'].astype(object)
np.array(content.at[len(numeric_rows), '改正角° ′ ″'])
content.at[len(numeric_rows), '改正角° ′ ″'] = str([0, 0, 0]) # 这将存储'[0, 0, 0]'作为字符串
content.at[len(numeric_rows), '改正角° ′ ″'] = angle
# 闭合差
B_H_C = 0
if all_sum[0] < angle[0]:
B_H_C = all_sum[2] - 60
else:
B_H_C = all_sum[2]
all_revise_B_H_C = -B_H_C
content.at[len(numeric_rows), '改正数'] = all_revise_B_H_C
# 由于很烦的负数往下取整，即如-10 // 4 = -3，所以要分类:
if all_revise_B_H_C >= 0:
quotient = all_revise_B_H_C // (len(numeric_rows)-2)
else:
quotient = all_revise_B_H_C // (len(numeric_rows) - 2) + 1
# 由于很烦的取模运算，所以只好这样:
remainder = all_revise_B_H_C - (quotient * (len(numeric_rows)-2))
n = abs(remainder) // 1
# 改正数的额外分配，改正数为正则+1为负则-,同时防止分母为零进行分类:
if remainder == 0:
remainder_s = 0
else:
remainder_s = remainder // abs(remainder)
# 排序(使用python内置的np.argsort()函数):默认为从小到大的下标
number = []
for i in range(len(numeric_rows)-2):
number.append(all_change(list(map(int, content.at[i+1, '观测角(左角)° ′ ″'].split()))))
# 先array读取再argsort得到下标排序
a = np.array(number)
b = np.argsort(a)
# 将整个列的数据类型更改为object,可以存储任何类型的数据，包括字符串化的列表
content['坐标方位角° ′ ″'] = content['坐标方位角° ′ ″'].astype(object)
y = k = q = p = r = t = u = v = x1 = x2 = x3 = x4 = x5 = x6 = x7 = x8 = x9 = x10 = 0
for k in range(n):
content.at[b[len(numeric_rows)-3-k]+1, '改正数'] = quotient + remainder_s
# 转换NaN为字符串nan以便操作:
np.array(content.at[b[len(numeric_rows)-3-k]+1, '改正角° ′ ″'])
content.at[b[len(numeric_rows)-3-k]+1, '改正角° ′ ″'] = str([0, 0, 0]) # 这将存储'[0, 0, 0]'作为字符串
content.at[b[len(numeric_rows)-3-k]+1, '改正角° ′ ″'] \
= change(list(map(int, content.at[b[len(numeric_rows)-3-k]+1, '观测角(左角)° ′ ″'].split())),
[0, 0, quotient + remainder_s])
for q in range(len(numeric_rows)-2-n):
content.at[b[q]+1, '改正数'] = quotient
content.at[b[q]+1, '改正角° ′ ″'] \
= change(list(map(int, content.at[b[q]+1, '观测角(左角)° ′ ″'].split())), [0, 0, quotient])
content.at[0, '坐标方位角° ′ ″'] = list(map(int, content.at[0, '坐标方位角° ′ ″'].split()))
# 由于坐标方位角之后全在同一行上计算，所以这个循环直接包含坐标方位角及之后的所有值的运算
sum4 = sum5 = sum6 = sum7 = sum8 = 0
for x in range(1, len(numeric_rows)-1):
content.at[x, '坐标方位角° ′ ″'] = change_s(content.at[x-1, '坐标方位角° ′ ″'], content.at[x, '改正角° ′ ″'])
sum4 += content.at[x-1, '距离']
content.at[x-1, '增量计算值Δx'] = solve_cos(content.at[x-1, '距离'], content.at[x-1, '坐标方位角° ′ ″'])
sum5 += content.at[x-1, '增量计算值Δx']
content.at[x-1, '增量计算值Δy'] = solve_sin(content.at[x-1, '距离'], content.at[x-1, '坐标方位角° ′ ″'])
sum6 += content.at[x-1, '增量计算值Δy']
# 1. 改正值vx，(可以简略：制作改正数函数)
sum5 = round(sum5, 3)
if (-sum5) >= 0:
quotient1 = (int((-sum5)*1000) // (len(numeric_rows)-2)) * 0.001
else:
quotient1 = (int((-sum5)*1000) // (len(numeric_rows)-2)+1) * 0.001
remainder1 = round(-sum5 - quotient1 * (len(numeric_rows)-2), 3)
n1 = int(abs(remainder1) / 0.001)
if remainder1 == 0:
remainder_s1 = 0
else:
remainder_s1 = (remainder1 // abs(remainder1)) * 0.001
number1 = []
for r in range(len(numeric_rows)-2):
number1.append(content.at[r, '增量计算值Δx'])
c = np.array(number1)
d = np.argsort(c)
# 由于当增量计算值为负值时，当额外分配数分配给正值有多时，要分配给负值为从小到大
# 当额外分配数分配给正值没多时：
if n1 <= sum(1 for x in number1 if x > 0):
for x1 in range(n1):
content.at[d[len(numeric_rows)-3-x1], '改正值vx'] = quotient1 + remainder_s1
for x2 in range(len(numeric_rows)-2-n1):
content.at[d[x2], '改正值vx'] = quotient1
# 当额外分配数分配比正值多时：
else:
for x3 in range(sum(1 for x in number1 if x > 0)):
content.at[d[len(numeric_rows)-3-x3], '改正值vx'] = quotient1 + remainder_s1
for x4 in range(n1-sum(1 for x in number1 if x > 0)):
content.at[d[x4], '改正值vx'] = quotient1 + remainder_s1
for x5 in range(len(numeric_rows)-2-n1):
content.at[d[x4+1+x5], '改正值vx'] = quotient1
# 2. 改正值vy，(可以简略：制作改正数函数)
sum6 = round(sum6, 3)
if (-sum6) >= 0:
quotient2 = (int((-sum6)*1000) // (len(numeric_rows)-2)) * 0.001
else:
quotient2 = (int((-sum6)*1000) // (len(numeric_rows)-2)+1) * 0.001
remainder2 = round(-sum6 - quotient2 * (len(numeric_rows)-2), 3)
n2 = int(abs(remainder2) / 0.001)
if remainder2 == 0:
remainder_s2 = 0
else:
remainder_s2 = (remainder2 // abs(remainder2)) * 0.001
number2 = []
for t in range(len(numeric_rows)-2):
number2.append(content.at[t, '增量计算值Δy'])
e = np.array(number2)
f = np.argsort(e)
# 由于当增量计算值为负值时，当额外分配数分配给正值有多时，要分配给负值为从小到大
# 当额外分配数分配给正值没多时：
if n2 <= sum(1 for x in number2 if x > 0):
for x6 in range(n2):
content.at[f[len(numeric_rows)-3-x6], '改正值vy'] = quotient2 + remainder_s2
for x7 in range(len(numeric_rows)-2-n2):
content.at[f[x7], '改正值vy'] = quotient2
# 当额外分配数分配比正值多时：
else:
for x8 in range(sum(1 for x in number2 if x > 0)):
content.at[f[len(numeric_rows)-3-x8], '改正值vy'] = quotient2 + remainder_s2
for x9 in range(n2-sum(1 for x in number2 if x > 0)):
content.at[f[x9], '改正值vy'] = quotient2 + remainder_s2
for x10 in range(len(numeric_rows)-2-n2):
content.at[f[x9+1+x10], '改正值vy'] = quotient2
# 改正后增量Δx，Δy
for u in range(len(numeric_rows)-2):
content.at[u, '改正后增量Δx'] = content.at[u, '增量计算值Δx'] + content.at[u, '改正值vx']
content.at[u, '改正后增量Δy'] = content.at[u, '增量计算值Δy'] + content.at[u, '改正值vy']
# 坐标值x，y
for v in range(len(numeric_rows)-2):
content.at[v+1, '坐标值x'] = content.at[v, '改正后增量Δx'] + content.at[v, '坐标值x']
content.at[v+1, '坐标值y'] = content.at[v, '改正后增量Δy'] + content.at[v, '坐标值y']
content.at[len(numeric_rows), '距离'] = sum4
content.at[len(numeric_rows), '增量计算值Δx'] = sum5
content.at[len(numeric_rows), '增量计算值Δy'] = sum6
content.at[len(numeric_rows), '改正值vx'] = -sum5
content.at[len(numeric_rows), '改正值vy'] = -sum6
content.at[len(numeric_rows), '改正后增量Δx'] = 0.00
content.at[len(numeric_rows), '改正后增量Δy'] = 0.00
# 去除测量值累加和中括号
content.at[len(numeric_rows), '观测角(左角)° ′ ″'] = ' '.join(str(i) for i in content.at[len(numeric_rows), '观测角(左角)° ′ ″'])
# 去除改正角中括号
for p in range(len(numeric_rows)-2):
content.at[p+1, '改正角° ′ ″'] = ' '.join(str(i) for i in content.at[p+1, '改正角° ′ ″'])
content.at[len(numeric_rows), '改正角° ′ ″'] = ' '.join(str(i) for i in content.at[len(numeric_rows), '改正角° ′ ″'])
# 去除坐标方位角中括号
for y in range(len(numeric_rows)-1):
content.at[y, '坐标方位角° ′ ″'] = ' '.join(str(i) for i in content.at[y, '坐标方位角° ′ ″'])
# 设置点号为整形
# openpyxl设置excel样式: 必须在to_excel生成完工作表后操作
content.loc[0:len(numeric_rows)-1, '点号'] = content.loc[0:len(numeric_rows)-1, '点号'].astype(dtype='int')
content['点号.'] = content['点号']
# 四等导线测量误差计算及检验
content.at[len(numeric_rows)+1, '观测角(左角)° ′ ″'] = (f"fβ=Σβ测-(n-2)*180={B_H_C}″")
content.at[len(numeric_rows)+2, '观测角(左角)° ′ ″'] = (f"fβ容=±24√(n)=±{24 * round(math.sqrt(len(numeric_rows)-2))}″")
content.at[len(numeric_rows)+1, '改正角° ′ ″'] = (f"fx=ΣΔx测={sum5}")
content.at[len(numeric_rows)+2, '改正角° ′ ″'] = (f"fy=ΣΔy测={sum6}")
f_bhc = round(math.sqrt(math.pow(sum5, 2)+math.pow(sum6, 2)), 3)
content.at[len(numeric_rows)+2, '坐标方位角° ′ ″'] = (f"f=√(fx²+fy²)=±{f_bhc}")
content.at[len(numeric_rows)+1, '增量计算值Δx'] = (f"K={abs(f_bhc)}/{sum4}=1/{round(1/abs(f_bhc))*sum4}")
content.at[len(numeric_rows)+2, '增量计算值Δx'] = ("K容=1/4000")
content.set_index('点号', inplace=True)
content.to_excel('C:/sucai/bihedaoxian6.xlsx')
workbook = load_workbook(filename='C:/sucai/bihedaoxian6.xlsx')
worksheet = workbook.active
# 单元格居中
max_rows = worksheet.max_row # 获取最大行: ·优化算法：一开始的计算测站可以用获取最大行推算
max_columns = worksheet.max_column # 获取最大列
# openpyxl的下标从1开始
for y1 in range(1, max_rows + 1):
for y2 in range(1, max_columns + 1):
print(worksheet.cell(y1, y2))
worksheet.cell(y1, y2).alignment = Alignment(horizontal='center', vertical='center')
# 列宽设置：( 未来可期：实现中文和特殊符号存在的列名的自适应列宽，目前只有字母和数字组成的才能搞
worksheet.column_dimensions['B'].width = 28.44
worksheet.column_dimensions['D'].width = 16.33
worksheet.column_dimensions['E'].width = 23.33
worksheet.column_dimensions['G'].width = 15.11
worksheet.column_dimensions['H'].width = 15.11
worksheet.column_dimensions['I'].width = 9.33
worksheet.column_dimensions['J'].width = 9.33
worksheet.column_dimensions['K'].width = 15.11
worksheet.column_dimensions['L'].width = 15.11
worksheet.column_dimensions['M'].width = 10.33
worksheet.column_dimensions['N'].width = 10.33
# 边框设置:
set_border_2(worksheet, 1, 1, max_rows, max_columns)
# 单元格合并:
workbook.save('C:/sucai/bihedaoxian6.xlsx')
print('计算成功！！！等待退出..')
# sleep(10000)
# 使用pyinstaller --onefile --hidden-import=openpyxl.cell._writer cool.py解决打包问题
# onefile: 创建一个exe文件，执行速度慢 --onefile 可以简写为 -F
# onedir: 创建一个含有exe文件的目录(文件夹)，执行速度相比前者大大提高 可以简写为 -D
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
