/// <summary> </summary> public ExternalCommandResult ExtractCppDoc(Application excelApp) { _excelApp = excelApp; excelApp.ScreenUpdating = false; var sht = excelApp.ActiveSheet as Worksheet; // var rg = excelApp.Selection as Range; rg = rg.Ex_ShrinkeRange(); // 设置工作簿的“常规”样式,以确定单元格的行高 List <CppDocMember> docMembers = ExtractDocMemberFromRange(rg); if (docMembers == null || docMembers.Count == 0) { MessageBox.Show(@"未提取出任何方法,请选择两列多行,且第一个单元格为函数返回值"); return(ExternalCommandResult.Cancelled); } // 将构造好的数据写入 Array arr = GetMembersInfo(docMembers); Range startCell = rg.Cells[1, 3]; Range arrRg = RangeValueConverter.FillRange(sht, startCell.Row, startCell.Column, arr); // 设计表头行的过滤 arrRg.Select(); if (sht.AutoFilterMode) { // 表示已经打开了过滤 // arrRg.AutoFilter(); } // excelApp.ScreenUpdating = true; return(ExternalCommandResult.Succeeded); }
// 开始具体的调试操作 public void Execute(bool containsHeader) { Range topLeftCell = null; var slopes = GetSlopes(containsHeader, ref topLeftCell); if (slopes != null && slopes.Count > 0) { // 1、对横断面数据进行排序 slopes.Sort(Comparison); // 2、 求分段的面积 var segs = GetArea(slopes); // 4、将结果写回 Excel var slopesArr = SegmentData <float, double> .ConvertToArr(segs); var mileArr = segs.Select(r => $"K{Math.Floor(r.Start / 1000)}+{(r.Start % 1000).ToString("000")}~K{Math.Floor(r.End / 1000)}+{(r.End % 1000).ToString("000")}").ToArray(); var lengthArr = segs.Select(r => (r.End - r.Start).ToString()).ToArray(); var lengthArr2 = segs.Select(r => "长度" + (r.End - r.Start).ToString()).ToArray(); // slopesArr = slopesArr.InsertVector <object, object, string>(false, new[] { mileArr, lengthArr }, new [] { 1f, 1.1f }); var sht = _excelApp.ActiveSheet; RangeValueConverter.FillRange(sht, topLeftCell.Row, topLeftCell.Column + 3, slopesArr, false); } }
/// <summary> /// 将桩号数值转换为对应的字符 /// </summary> /// <param name="app"></param> /// <param name="selectedRange"></param> public static void ConvertStationFromString(Application app, Range selectedRange) { var firstFillCell = selectedRange.Ex_CornerCell(CornerIndex.UpRight).Offset[0, 1]; var arr = RangeValueConverter.GetRangeValue <object>(selectedRange.Value) as object[, ]; if (arr == null) { return; } var row = arr.GetLength(0); var col = arr.GetLength(1); var newArray = new object[row, col]; for (int r = 0; r < row; r++) { for (int c = 0; c < col; c++) { if (arr[r, c] is string) { var str = arr[r, c] as string; var num = GetStationFromString(str); newArray[r, c] = num; } else { newArray[r, c] = arr[r, c]; } } } // 写入到 工作表中 RangeValueConverter.FillRange(app.ActiveSheet, firstFillCell.Row, firstFillCell.Column, newArray); }
/// <summary> 将所有表格中记录的数据导出到指定Excel工作簿的多个工作表中 </summary> /// <param name="sheet_Infos"></param> /// <returns></returns> private static bool ExportDataToExcel(List <WorkSheetData> sheet_Infos, out string errMsg) { Application excelApp = null; errMsg = null; bool succ = true; try { Workbook wkbk; succ = GetExcelWorkbook(out wkbk); if (wkbk != null) { excelApp = wkbk.Application; excelApp.ScreenUpdating = false; // foreach (var tpInfo in sheet_Infos) { var sht = SQUtils.GetOrCreateWorkSheet(wkbk, tpInfo.SheetName); if (sht != null) { RangeValueConverter.FillRange(sht, startRow: 1, startCol: 1, arr: tpInfo.Data, colPrior: false); sht.UsedRange.EntireColumn.AutoFit(); sht.Activate(); var w = excelApp.ActiveWindow as Window; w.SplitColumn = 0; w.SplitRow = 1; w.FreezePanes = true; } else { errMsg = $"未找到工作表:{tpInfo.SheetName}"; succ = false; } wkbk.Save(); } } else { errMsg = $"未能打开或者创建 Excel 工作簿"; } } catch (Exception ex) { errMsg = ex.Message; } finally { if (excelApp != null) { excelApp.Visible = true; excelApp.ScreenUpdating = true; } } return(succ); }
public void Main(Application excelApp) { double[] xValues; double[] yValues; // 获取原始数据 Worksheet sht = excelApp.ActiveSheet; // Range valueRange = RangeValueConverter.GetRange(sht, 1, 1, 117, 2); var value = valueRange.Value; double[] vDate = ArrayConstructor.GetColumn <double>(RangeValueConverter.GetRangeValue <double>(value, false, 0), 0); double[,] vValue = RangeValueConverter.GetRangeValue <double>(value, false, 1); // 其他计算参数 int type = (int)sht.Cells[1, 4].Value; int newCount = (int)sht.Cells[2, 4].Value; // var sp = new SpeedMode(XdateNum.Select(r => (double)r).ToArray(), Y); var sp = new SpeedMode(vDate, ArrayConstructor.GetColumn(vValue, 0)); SpeedMode.ShrinkResult res; switch (type) { case 1: { res = sp.ShrinkByIdAverage(newCount); if (res == SpeedMode.ShrinkResult.Succeed) { // 绘制数据 RangeValueConverter.FillRange(sht, 1, 5, sp.GetX(), colPrior: true); RangeValueConverter.FillRange(sht, 1, 6, sp.GetY(), colPrior: true); } break; } case 2: { res = sp.ShrinkByXAxis(newCount); if (res == SpeedMode.ShrinkResult.Succeed) { // 绘制数据 RangeValueConverter.FillRange(sht, 1, 7, sp.GetX(), colPrior: true); RangeValueConverter.FillRange(sht, 1, 8, sp.GetY(), colPrior: true); } else { MessageBox.Show(sp.ErrorMessage); } break; } } }
private void buttonOk_Click(object sender, EventArgs e) { try { double[] srcX, srcY, interpX; if (GetSplineSrc(out srcX, out srcY, out interpX)) { double[] interpY = null; if (radioButton_Spline.Checked) { interpY = Interpolation.SplineInterpolation(srcX, srcY, interpX); } else if (radioButton_Linear.Checked) { interpY = Interpolation.LinearInterpolation(srcX, srcY, interpX); } if (interpY != null) { // 将结果写入 Excel 表格中 Range destCell; if (rangeGetorD.Range == null) { Range rg = destCell = rangeGetorI.Range.Cells[1]; destCell = rg.Offset[0, 1]; } else { destCell = rangeGetorD.Range.Cells[1]; } Worksheet sht = _exApp.ActiveSheet; RangeValueConverter.FillRange(sht, destCell.Row, destCell.Column, interpY, true); } } else { MessageBox.Show(@"无法找到有效的数据源", @"出错", MessageBoxButtons.OK, MessageBoxIcon.Error); } } catch (Exception ex) { MessageBox.Show(ex.Message + "\r\n" + "\r\n", // + ex.StackTrace @"出错", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> </summary> public ExternalCommandResult QuantityList(Application excelApp) { var sele = excelApp.Selection as Range; sele = sele.Ex_ShrinkeRange(); var sht = excelApp.ActiveSheet; var v = RangeValueConverter.GetRangeValue <object>(sele.Value, false, 0) as object[, ]; List <string> comb = new List <string>(); string cellV; string lastP = v[0, 0].ToString(); for (int r = 0; r < v.GetLength(0); r++) { if (v[r, 0] != null) { cellV = v[r, 0].ToString(); cellV.Trim(); if (cellV.StartsWith("-")) { comb.Add(lastP + cellV); } else { comb.Add(cellV); lastP = cellV; } } else { comb.Add(null); lastP = null; } } // var desti = sele.Offset[0, 1].Cells[1] as Range; var arr = comb.ToArray(); RangeValueConverter.FillRange(sht, desti.Row, desti.Column, arr); return(ExternalCommandResult.Succeeded); }
/// <summary> /// 以只考虑数据点个数的方式进行缩减 /// </summary> /// <param name="srcX">数据源中的X数据</param> /// <param name="srcY">数据源中的Y数据</param> /// <param name="newCount">缩减后的新数据点个数</param> /// <param name="srcD">要将缩减后的数据放置在哪里,此属性中只包含一个单元格,表示整个缩减后的曲线的左上角单元格</param> public static void ShrinkByPointCount(Range srcX, Range srcY, int newCount, Range srcD) { double[] x = GetColumnData(srcX); double[] y = GetColumnData(srcY); if (x.Length > 2 && x.Length == y.Length) { // var sp = new SpeedMode(x, y); SpeedMode.ShrinkResult res = sp.ShrinkByIdAverage(newCount); if (res == SpeedMode.ShrinkResult.Succeed) { Worksheet sht = srcD.Worksheet; // 绘制数据 RangeValueConverter.FillRange(sht, srcD.Row, srcD.Column, sp.GetX(), colPrior: true); RangeValueConverter.FillRange(sht, srcD.Row, srcD.Column + 1, sp.GetY(), colPrior: true); } } else { throw new ArgumentException(@"X与Y数据点的个数必须相同而且至少为2。"); } }
private static void SaveDataToExcel(string[,] data) { var filePath = Utils.ChooseSaveFile(title: "将数据保存到Excel中", filter: "Excel文件(*.xls)| *.xls"); if (filePath != null) { bool fileExists = File.Exists(filePath); Workbook wkbk = null; if (fileExists) { wkbk = Interaction.GetObjectFromFile <Workbook>(filePath); } else { var app = new Application(); wkbk = app.Workbooks.Add(); } if (wkbk != null) { Worksheet sht = wkbk.Worksheets[1]; RangeValueConverter.FillRange(sht, startRow: 1, startCol: 1, arr: data, colPrior: true); wkbk.Application.Windows[wkbk.Name].Visible = true; if (fileExists) { wkbk.Save(); } else { wkbk.SaveAs(Filename: filePath, FileFormat: XlFileFormat.xlAddIn8); } wkbk.Application.Visible = true; } } }
public void Execute(bool containsHeader) { Range topLeftCell = null; var slopes = GetSlopes(true, ref topLeftCell); if (slopes != null && slopes.Count > 0) { // 1、对横断面数据进行排序,排序后集合中不会有重复的里程 slopes = Sort_SumDuplicate(slopes); // 2、 去掉有测量值的定位断面 var sortedSections = new Stack <MileageInfo>(); double duplicateMileage = double.Epsilon; for (int i = slopes.Count - 1; i >= 0; i--) { var slp = slopes[i]; if (slp.Mileage == duplicateMileage) { // 说明出现重复里程,此时从众多重复里程中仅保留有测量值的那个里程断面 if (slp.Type == MileageInfoType.Measured) { // 替换掉原集合中的值 sortedSections.Peek().Override(slp); } } else { // 说明与上一个桩号不重复 sortedSections.Push(slp); duplicateMileage = slp.Mileage; } } // 3、 进行插值,此时 sortedSlopes 中,较小的里程位于堆的上面, // 而且集合中只有“定位”与“测量”两种断面,并没有“插值”断面 var allSections = new List <MileageInfo>(); var count = sortedSections.Count; var smallestSection = sortedSections.Pop(); allSections.Add(smallestSection); // var lastSec = smallestSection; var lastType = smallestSection.Type; for (int i = 1; i < count; i++) { var slp = sortedSections.Pop(); if (slp.Type != lastType) { // 说明从定位断面转到了测量断面,或者从测量断面转到了定位断面,此时要进行断面插值 var interpMile = (lastSec.Mileage + slp.Mileage) / 2; var interpSec = new MileageInfo(interpMile, MileageInfoType.Interpolated, 0); allSections.Add(interpSec); } // allSections.Add(slp); lastSec = slp; lastType = slp.Type; } // 4、将结果写回 Excel var slopesArr = MileageInfo.ConvertToArr(allSections); var sht = _excelApp.ActiveSheet; ; RangeValueConverter.FillRange(sht, topLeftCell.Row, topLeftCell.Column + 3, slopesArr, false); } }
/// <summary> </summary> public ExternalCommandResult QuantityList(Application excelApp) { excelApp.ScreenUpdating = false; int rowNum = 0; try { Range rg = excelApp.Selection as Range; var shtDes = excelApp.ActiveSheet as Worksheet; var wkbkDes = excelApp.ActiveWorkbook; Range dest = (rg.Cells[1] as Range).Offset[0, 8]; //// 规范数据源 //var wkbk = // excelApp.Workbooks.Open(@"C:\Users\Administrator\Desktop\徐敏 工程量清单\格式.xlsx", // ReadOnly: false); Worksheet shtsrc = wkbkDes.Sheets["格式列表"]; ItemSources = GetStandardSource(shtsrc.UsedRange).ToArray(); // 要匹配的数据 var arrV = RangeValueConverter.GetRangeValue <object>(rg.Value) as object[, ]; var items = new List <Item>(); for (int r = 0; r < arrV.GetLength(0); r++) { rowNum = dest.Row + r; var unit = GetString(arrV[r, 3]); if (unit == null || unit == @"/") { unit = null; } var item = new Item(GetString(arrV[r, 0]), GetString(arrV[r, 1]), GetString(arrV[r, 2]), unit); // 比较与匹配 var m = ItemSources.FirstOrDefault(rr => rr.子目号 == item.子目号); if (m != null) { var m2 = ItemSources.FirstOrDefault(rr => rr.子目名称 == item.子目名称); if (m2 != null) { m.备注 = Matched.匹配; } else { m.备注 = Matched.未匹配; } items.Add(m); } else { item.备注 = Matched.未包含; items.Add(new Item("", "", "", "") { 备注 = Matched.未包含 }); } } // 写入 // var arr = new List <object[]>(); foreach (var i in items) { arr.Add(i.ToArray()); } // var arr2 = ArrayConstructor.FromList2D(arr); RangeValueConverter.FillRange(shtDes, dest.Row, dest.Column, arr2); wkbkDes.Activate(); shtDes.Activate(); } catch (Exception ex) { MessageBox.Show("出错行:" + rowNum.ToString() + ex.Message + ex.StackTrace); } finally { excelApp.ScreenUpdating = true; } return(ExternalCommandResult.Succeeded); }
/// <summary> /// 将桩号数值转换为对应的字符 /// </summary> /// <param name="app"></param> /// <param name="selectedRange"></param> /// <param name="maxDigits">转换为字符的最大的小数位数</param> public static void ConvertStationToString(Application app, Range selectedRange, int maxDigits) { var firstFillCell = selectedRange.Ex_CornerCell(CornerIndex.UpRight).Offset[0, 1]; var colCount = selectedRange.Columns.Count; if (colCount == 0 || colCount > 2) { return; } // 必须只有一列或者两列 var stationStrings = new List <string>(); var v = RangeValueConverter.GetRangeValue <object>(selectedRange.Value) as object[, ]; if (v != null) { var rowsCount = v.GetLength(0); var colsCount = v.GetLength(1); if (colsCount == 0 || colsCount > 2) { return; } string s1 = null; string s2 = null; if (colsCount == 1) { for (int r = 0; r < rowsCount; r++) { if (v[r, 0] is double) { s1 = GetStationString((double)v[r, 0], maxDigits); stationStrings.Add(s1); } else { stationStrings.Add(null); } } } else { // 共有两列 for (int r = 0; r < rowsCount; r++) { if (v[r, 0] is double) { s1 = GetStationString((double)v[r, 0], maxDigits); } else { s1 = null; } if (v[r, 1] is double) { s2 = "~" + GetStationString((double)v[r, 1], maxDigits); } else { s2 = null; } stationStrings.Add(s1 + s2); } } } // 写入到 工作表中 RangeValueConverter.FillRange(app.ActiveSheet, firstFillCell.Row, firstFillCell.Column, stationStrings.ToArray()); }