三坐标测量仪Excel数据的读取和处理

博士后出站报告附录

# -*- coding: utf-8 -*-
"""
Created on Mon Apr 20 14:35:13 2015

@author: User
"""
#Excel表格操作：https://blog.csdn.net/weixin_43820813/article/details/124467183
#拟合球面：http://kernel.meizu.com/nonlinear-regression.html
#拟合球面：https://www.2bboy.com/archives/171.html
# https://blog.csdn.net/yingshaobeng9137/article/details/92989963
# https://blog.csdn.net/sunshine_zoe/article/details/78852978
import xlrd
import matplotlib.pyplot as plt
import numpy as np
import os
import math
from mpl_toolkits.mplot3d import Axes3D
from matplotlib.ticker import MultipleLocator
from find_data import load_elecdata
from scipy.interpolate import griddata
from zhbcode import selffunction as sf
from tkinter import _flatten # 将二维列表转换为一维列表

maindir = r'D:\001_CSNS\data\NBSS\Analyzer\Al-base\CMM\20230218-HuiNeng_Second_processing'
datapath = sf.mkdir(maindir+r'\Norm Data Output')
figpath = sf.mkdir(maindir+r'\figs')

data = xlrd.open_workbook(maindir+r'\\20230218_CMM-data.xls')
table = data.sheets()[0] #获取book（excel文件）中一个工作表
R = 2501
t_nominal = 68.5
nrows = table.nrows #获取该sheet的行数
ncols = table.ncols #获取该sheet的列数

#print(nrows+','+columns)

X = []
Y = []
Z = []
i = 9
j = 10
while i+2<=388: # cell返回cell对象；cell_value返回单元格的值；cell_type返回单元格类型
    X.append(float(table.cell_value(i,j)))
    Y.append(float(table.cell_value(i+1,j)))
    Z.append(float(table.cell_value(i+2,j)))
    i += 4
X = np.array(X)
Y = np.array(Y)
Z = np.array(Z)

Z_nominal = R+t_nominal-np.sqrt(R*R-X*X-Y*Y)
Delta_Z = Z-Z_nominal #unit mm

t_devia = (min(Delta_Z)+max(Delta_Z))/2
Delta_Z_norm = Delta_Z-t_devia


name_CMM = ['X','Y','Z','Z_nominal','Delta_Z','Delta_Z_um','Delta_Z_norm','Delta_Z_norm_um']
unit_CMM = _flatten([('mm',)*4+('mm','um')*2])
comment_CMM = _flatten([(f'{R}mm',)*8])
data_CMM = np.c_[X,Y,Z,Z_nominal,Delta_Z,Delta_Z*1e3,Delta_Z_norm,Delta_Z_norm*1e3]
filename = str(0).rjust(3,'0')+f'_R_{R}-CMM.dat'
sf.dataoutput(name_CMM,unit_CMM,comment_CMM,data_CMM,datapath,filename)