创作动力:在工控测试类项目中,经常需要对测试数据进行深度计算,其中就包含斜率的计算,为了验证计算的正确与否,需要对excel数据进行二次验证,此时需要一款能够读取excel数据并进行线性拟合计算的斜率的小工具,本项目就应运而生。文章末尾包含源码链接。
功能介绍:1、能够获取Excel中所有sheet页的内容,并赋值到DataTable。
2、对获取到的数据使用最小二乘法来进行线性拟合。
3、对拟合后的数据进行斜率计算。
关键代码如下:
/// <summary>
/// 获取Excel中所有sheet页的内容,并赋值到datatable
/// </summary>
/// <param name="strExcelFilePath">Excel文件路径</param>
/// <returns></returns>
public DataTable OpenEcxelToListDT()
{
List<DataTable> dataTables = new List<DataTable>();
try
{
var package = new ExcelPackage(new System.IO.FileInfo(txt_ExcelPath.Text));
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
ExcelWorkbook workbook = package.Workbook;
foreach (var sheet in workbook.Worksheets)
{
DataTable dt = new DataTable();
ExcelWorksheet workSheet = workbook.Worksheets[sheet.ToString()];
//判断该sheet页中是否有内容
if (workSheet.Dimension != null)
{
//有效内容的起始坐标
ExcelCellAddress start = workSheet.Dimension.Start;
//有效内容的终止坐标
ExcelCellAddress end = workSheet.Dimension.End;
for (int col = start.Column; col <= end.Column; col++)
{
dt.Columns.Add(workSheet.Cells[1, col].Value != null ? workSheet.Cells[1, col].Value.ToString() : col.ToString());
}
for (int row = 2; row <= end.Row; row++)
{
DataRow dataRow = dt.NewRow();
int x = 0;
for (int col = start.Column; col <= end.Column; col++)
{
dataRow[x++] = workSheet.Cells[row, col].Value?.ToString();
}
dt.Rows.Add(dataRow);
}
dt.TableName = workSheet.Name;
}
dataTables.Add(dt);
}
}
catch (Exception ex) { }
return dataTables.Where(o => o.TableName == "Sheet1").FirstOrDefault();
}
/// <summary>
/// 这段代码使用了最小二乘法来进行线性拟合。首先定义了两组数据点 x 和 y,
/// 然后通过调用 CalculateSlope() 函数来计算斜率。
/// 该函数会根据输入的数据点计算平均值、分子和分母等参数,从而得到斜率的结果。
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <returns></returns>
/// <exception cref="ArgumentException"></exception>
private double CalculateSlope(List<double> x, List<double> y)
{
int n = x.Count;
if (n != y.Count || n < 2)
throw new ArgumentException();
double sumX = 0.0;
double sumY = 0.0;
for (int i = 0; i < n; ++i)
{
sumX += x[i];
sumY += y[i];
}
double meanX = sumX / n;
double meanY = sumY / n;
double numerator = 0.0;
double denominator = 0.0;
for (int i = 0; i < n; ++i)
{
numerator += (x[i] - meanX) * (y[i] - meanY);
denominator += Math.Pow((x[i] - meanX), 2);
}
return numerator / denominator;
}