private void btnsave_Click(object sender, EventArgs e)
{
try
{
path = Environment.CurrentDirectory.ToString() + @"\Excel\" + "\\";
IWorkbook workbook = new XSSFWorkbook(); //建立空白工作簿
ISheet sheet = workbook.CreateSheet("扫描数据"); //在工作簿中：建立空白工作表
IRow row = sheet.CreateRow(0); //在工作表中：建立行，参数为行号，从0计
ICell cell = row.CreateCell(0); //在行中：建立单元格，参数为列号，从0计
cell.SetCellValue("小型化激光雷达扫描数据"); //设置单元格内容
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置单元格的样式：水平对齐居中
IFont font = workbook.CreateFont(); //新建一个字体样式对象
//font.Boldweight = short.MaxValue; //设置字体加粗样式
font.FontName = "宋体";
font.FontHeight = 16;
style.SetFont(font); //使用SetFont方法将字体样式添加到单元格样式中
cell.CellStyle = style; //将新的样式赋给单元格
row.Height = 30 * 20; //设置单元格的高度
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15); //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
sheet.AddMergedRegion(region);
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("实际值");
row1.CreateCell(1).SetCellValue("最大值");
row1.CreateCell(2).SetCellValue("最小值");
row1.CreateCell(3).SetCellValue("平均值");
row1.CreateCell(4).SetCellValue(" ");
row1.CreateCell(5).SetCellValue("最大偏差");
row1.CreateCell(6).SetCellValue("最小偏差");
row1.CreateCell(7).SetCellValue("平均值偏差");
row1.CreateCell(8).SetCellValue("离散度");
row1.CreateCell(9).SetCellValue(" ");
row1.CreateCell(10).SetCellValue("绝对最大偏差");
row1.CreateCell(11).SetCellValue("绝对最小偏差");
row1.CreateCell(12).SetCellValue("绝对平均偏差");
row1.CreateCell(13).SetCellValue("偏移量");
row1.CreateCell(14).SetCellValue("绝对精度");
//设置单元格的宽度
for (int i = 0; i < 15; i++)
{
sheet.SetColumnWidth(i, 13 * 256);//设置单元格的宽度
//row1.GetCell(i).CellStyle = style;//设置单元格格式
}
int len = Recordlist.Count;
//int l_n32criticalno = 0;
int temp = 0;
if (len > 0)
{
//for (int i = 0; i < len; i++) //查找临界值
//{
// if (((RecordStruct)Recordlist[temp]).m_int32realdisc > 500 &&
// ((RecordStruct)Recordlist[temp]).m_int32realdisc < 1500)
// {
// l_n32criticalno = i;
// break;
// }
//}
for (int i = 0; i < len; i++)
{
IRow rowx = sheet.CreateRow(i + 2);
//if (i % 2 == 0)
//{
// temp = (i / 2 + 10); //temp = (i / 2 + l_n32criticalno+1);
//}
//else
//{
// temp = (i - 1) / 2; //temp = (l_n32criticalno - (i - 1) / 2);
//}
temp = i;
rowx.CreateCell(0).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32realdisc);
rowx.CreateCell(1).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32max);
rowx.CreateCell(2).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32min);
rowx.CreateCell(3).SetCellValue(((RecordStruct)Recordlist[temp]).m_int32ave);
rowx.CreateCell(4);
rowx.CreateCell(5);
rowx.CreateCell(6);
rowx.CreateCell(7);
rowx.CreateCell(8);
rowx.CreateCell(9);
rowx.CreateCell(10); //绝对最大偏差
rowx.CreateCell(11); //绝对最小偏差
rowx.CreateCell(12); //绝对平均偏差
rowx.CreateCell(13); //偏移量
rowx.CreateCell(14); //绝对精度
rowx.CreateCell(15); //绝对精度
}
//设置保留小数点的个数
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
IRow row2 = sheet.GetRow(2);
ICell cellx13 = row2.GetCell(13);
cellx13.SetCellFormula("AVERAGE(H3:H" + (len + 2).ToString() + ")"); //偏移量：平均偏差的平均值
cellx13.CellStyle = cellStyle;
if (len > 0)
{
for (int i = 0; i < len; i++)
{
IRow rowx = sheet.GetRow(i + 2);
string rowno = (i + 3).ToString();
ICell cellx5 = rowx.GetCell(5);
cellx5.SetCellFormula("SUM(B" + rowno + ",A" + rowno + "*(-1))"); //最大偏差： 最大值减实际值
ICell cellx6 = rowx.GetCell(6);
cellx6.SetCellFormula("SUM(C" + rowno + ",A" + rowno + "*(-1))"); //最小偏差：最小值减实际值
ICell cellx7 = rowx.GetCell(7);
cellx7.SetCellFormula("SUM(D" + rowno + ",A" + rowno + "*(-1))"); //平均偏差： 平均值减实际值
ICell cellx8 = rowx.GetCell(8);
cellx8.SetCellFormula("SUM(B" + rowno + ",C" + rowno + "*(-1))"); //离散度：最大值减最小值
ICell cellx10 = rowx.GetCell(10);
cellx10.SetCellFormula("SUM(F" + rowno + ",N3*(-1))"); //绝对最大偏差：最大偏差减去偏移量
cellx10.CellStyle = cellStyle;
ICell cellx11 = rowx.GetCell(11);
cellx11.SetCellFormula("SUM(G" + rowno + ",N3*(-1))"); //绝对最小偏差：最小偏差减去偏移量
cellx11.CellStyle = cellStyle;
ICell cellx12 = rowx.GetCell(12);
cellx12.SetCellFormula("SUM(H" + rowno + ",N3*(-1))"); //绝对平均偏差：平均偏差减去偏移量
cellx12.CellStyle = cellStyle;
}
}
//ICell cellx14 = row2.GetCell(14);
//cellx14.SetCellFormula("SUM(MAX(F3:F"+(len+2).ToString()+"),MIN(G3:G"+(len+2).ToString()+")*(-1))"); //绝对精度：最大偏差的最大值减去最小偏差的最小值
IRow row3 = sheet.GetRow(3);
ICell cell314 = row3.GetCell(14);
cell314.SetCellValue("0.5-6m"); //0.5-6
ICell cell315 = row3.GetCell(15);
cell315.SetCellFormula("SUM(MAX(F4:F14),MIN(G4:G14)*(-1))"); //0.5-6 绝对精度：最大偏差的最大值减去最小偏差的最小值
IRow row4 = sheet.GetRow(4);
ICell cell414 = row4.GetCell(14);
cell414.SetCellValue("0.5-8m"); //0.5-8
ICell cell415 = row4.GetCell(15);
cell415.SetCellFormula("SUM(MAX(F4:F18),MIN(G4:G18)*(-1))"); //0.5-8绝对精度：最大偏差的最大值减去最小偏差的最小值
IRow row5 = sheet.GetRow(5);
ICell cell514 = row5.GetCell(14);
cell514.SetCellValue("0-10m"); //0.5-10
ICell cell515 = row5.GetCell(15);
cell515.SetCellFormula("SUM(MAX(F3:F" + (len + 2).ToString() + "),MIN(G3:G" + (len + 2).ToString() + ")*(-1))"); //绝对精度：最大偏差的最大值减去最小偏差的最小值
if (ExcelFormName == null || ExcelFormName.IsDisposed)
{
ExcelFormName = new ExcelDocumentName();
}
ExcelFormName.ShowDialog(this);
ExcelName = ExcelFormName.name;
if (ExcelName != null)
{
path = path + ExcelName + ".xlsx";
FileStream file2007 = new FileStream(path, FileMode.Create);
workbook.Write(file2007);
file2007.Close();
workbook.Close();
MessageBox.Show("数据保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//加载excel
System.Diagnostics.Process.Start(path);
}
}
else
{
MessageBox.Show("记录数据为空，请重新记录！", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch
{
MessageBox.Show("保存数据出错，请重新操作", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public bool g_bRecordFlg = false; //将测距值记录到excel 此时停止更新数据
private void btn_record_disper_Click(object sender, EventArgs e)
{
try
{
g_bRecordFlg = true;
path = Environment.CurrentDirectory.ToString() + @"\Excel\" + "\\";
IWorkbook workbook = new XSSFWorkbook(); //建立空白工作簿
ISheet sheet = workbook.CreateSheet("扫描数据"); //在工作簿中：建立空白工作表
IRow row = sheet.CreateRow(0); //在工作表中：建立行，参数为行号，从0计
ICell cell = row.CreateCell(0); //在行中：建立单元格，参数为列号，从0计
cell.SetCellValue("小型化激光雷达扫描数据"); //设置单元格内容
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置单元格的样式：水平对齐居中
IFont font = workbook.CreateFont(); //新建一个字体样式对象
//font.Boldweight = short.MaxValue; //设置字体加粗样式
font.FontName = "宋体";
font.FontHeight = 16;
style.SetFont(font); //使用SetFont方法将字体样式添加到单元格样式中
cell.CellStyle = style; //将新的样式赋给单元格
row.Height = 30 * 20; //设置单元格的高度
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 15); //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
sheet.AddMergedRegion(region);
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("实际值");
row1.CreateCell(1).SetCellValue("最大值");
row1.CreateCell(2).SetCellValue("最小值");
row1.CreateCell(3).SetCellValue("平均值");
row1.CreateCell(4).SetCellValue(" ");
row1.CreateCell(5).SetCellValue("最大偏差");
row1.CreateCell(6).SetCellValue("最小偏差");
row1.CreateCell(7).SetCellValue("平均值偏差");
row1.CreateCell(8).SetCellValue("离散度");
row1.CreateCell(9).SetCellValue(" ");
row1.CreateCell(10).SetCellValue("绝对最大偏差");
row1.CreateCell(11).SetCellValue("绝对最小偏差");
row1.CreateCell(12).SetCellValue("绝对平均偏差");
row1.CreateCell(13).SetCellValue("偏移量");
row1.CreateCell(14).SetCellValue("绝对精度");
//设置单元格的宽度
for (int i = 0; i < 15; i++)
{
sheet.SetColumnWidth(i, 13 * 256);//设置单元格的宽度
//row1.GetCell(i).CellStyle = style;//设置单元格格式
}
int len = 11;
if (len > 0)
{
for (int i = 0; i < len; i++)
{
IRow rowx = sheet.CreateRow(i + 2);
string l_srealdistxtbox = "txt_RELDISC" + (i + 1).ToString();
object l_orealdistxtbox = FindControlAsName(l_srealdistxtbox);
int l_n32realdis = Convert.ToInt32((((TextBox)l_orealdistxtbox).Text));
rowx.CreateCell(0).SetCellValue(l_n32realdis);
rowx.CreateCell(1).SetCellValue(g_an32ScanMax[i]);
rowx.CreateCell(2).SetCellValue(g_an32ScanMin[i]);
rowx.CreateCell(3).SetCellValue(g_an64ScanAve[i]);
rowx.CreateCell(4);
rowx.CreateCell(5);
rowx.CreateCell(6);
rowx.CreateCell(7);
rowx.CreateCell(8);
rowx.CreateCell(9);
rowx.CreateCell(10); //绝对最大偏差
rowx.CreateCell(11); //绝对最小偏差
rowx.CreateCell(12); //绝对平均偏差
rowx.CreateCell(13); //偏移量
rowx.CreateCell(14); //绝对精度
rowx.CreateCell(15); //绝对精度
}
//设置保留小数点的个数
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
IRow row2 = sheet.GetRow(2);
ICell cellx13 = row2.GetCell(13);
//cellx13.SetCellFormula("AVERAGE(H3:H" + (len + 2).ToString() + ")"); //偏移量：平均偏差的平均值
cellx13.SetCellFormula("AVERAGE(H4:H12)");
cellx13.CellStyle = cellStyle;
if (len > 0)
{
for (int i = 0; i < len; i++)
{
IRow rowx = sheet.GetRow(i + 2);
string rowno = (i + 3).ToString();
ICell cellx5 = rowx.GetCell(5);
cellx5.SetCellFormula("SUM(B" + rowno + ",A" + rowno + "*(-1))"); //最大偏差： 最大值减实际值
ICell cellx6 = rowx.GetCell(6);
cellx6.SetCellFormula("SUM(C" + rowno + ",A" + rowno + "*(-1))"); //最小偏差：最小值减实际值
ICell cellx7 = rowx.GetCell(7);
cellx7.SetCellFormula("SUM(D" + rowno + ",A" + rowno + "*(-1))"); //平均偏差： 平均值减实际值
ICell cellx8 = rowx.GetCell(8);
cellx8.SetCellFormula("SUM(B" + rowno + ",C" + rowno + "*(-1))"); //离散度：最大值减最小值
ICell cellx10 = rowx.GetCell(10);
cellx10.SetCellFormula("SUM(F" + rowno + ",N3*(-1))"); //绝对最大偏差：最大偏差减去偏移量
cellx10.CellStyle = cellStyle;
ICell cellx11 = rowx.GetCell(11);
cellx11.SetCellFormula("SUM(G" + rowno + ",N3*(-1))"); //绝对最小偏差：最小偏差减去偏移量
cellx11.CellStyle = cellStyle;
ICell cellx12 = rowx.GetCell(12);
cellx12.SetCellFormula("SUM(H" + rowno + ",N3*(-1))"); //绝对平均偏差：平均偏差减去偏移量
cellx12.CellStyle = cellStyle;
}
}
IRow row3 = sheet.GetRow(3);
ICell cell314 = row3.GetCell(14);
cell314.SetCellValue("0.05-4m"); //0.05-4
ICell cell315 = row3.GetCell(15);
cell315.SetCellFormula("SUM(MAX(F3:F10),MIN(F3:G10)*(-1))"); //0.05-4 绝对精度：最大偏差的最大值减去最小偏差的最小值
IRow row4 = sheet.GetRow(4);
ICell cell414 = row4.GetCell(14);
cell414.SetCellValue("0.05-6m"); //0.05-6
ICell cell415 = row4.GetCell(15);
cell415.SetCellFormula("SUM(MAX(F3:F13),MIN(G3:G13)*(-1))"); //0.5-6绝对精度：最大偏差的最大值减去最小偏差的最小值
//IRow row5 = sheet.GetRow(5);
//ICell cell514 = row5.GetCell(14);
//cell514.SetCellValue("6-8m"); //0.5-8m
//ICell cell515 = row5.GetCell(15);
//cell515.SetCellFormula("SUM(MAX(F13:F16),MIN(G13:G16)*(-1))"); //绝对精度：最大偏差的最大值减去最小偏差的最小值
if (ExcelFormName == null || ExcelFormName.IsDisposed)
{
ExcelFormName = new ExcelDocumentName();
}
ExcelFormName.ShowDialog(this);
ExcelName = ExcelFormName.name;
if (ExcelName != null)
{
path = path + ExcelName + ".xlsx";
FileStream file2007 = new FileStream(path, FileMode.Create);
workbook.Write(file2007);
file2007.Close();
workbook.Close();
MessageBox.Show("数据保存成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
g_bRecordFlg = false;
//加载excel
System.Diagnostics.Process.Start(path);
}
}
else
{
MessageBox.Show("记录数据为空，请重新记录！", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch
{
MessageBox.Show("保存数据出错，请重新操作", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}