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; } }