/// <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);
}
private double[] getFirstColumnData(Range rg)
{
Range c = rg.Columns[1];
var d = RangeValueConverter.GetRangeValue <double>(rg.Value, false, 0);
var cv = ArrayConstructor.GetColumn(d, 0);
return(cv);
}
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;
}
}
}
/// <summary>
/// 从 Excel 表格中获取横断面数据
/// </summary>
/// <returns></returns>
public List <KeyValuePair <float, double> > GetSlopes(bool containsHeader, ref Range topLeftCell)
{
var slopes = new List <KeyValuePair <float, double> >();
var rg = _excelApp.Selection as Range;
if (rg != null)
{
// 将选择的范围进行适当的收缩，以匹配有效数据的区域
Range col = rg.Columns[1];
Range cell = col.Ex_ShrinkeVectorAndCheckNull().Ex_CornerCell(CornerIndex.BottomLeft);
// cell 表示选择区域的有效数据区域的最左下角的单元格
rg = containsHeader
? rg.Rows[$"{2}:{cell.Row - rg.Row + 1}"] // 将表头剃除
: rg.Rows[$"{1}:{cell.Row - rg.Row + 1}"];
topLeftCell = rg.Cells[1, 1];
object[,] arr = RangeValueConverter.GetRangeValue <object>(rg.Value);
int errorRow = rg.Row - 1;
bool keepPromt = true;
for (int r = 0; r < arr.GetLength(0); r++)
{
errorRow += 1;
//
float mile = -1;
if (!float.TryParse(arr[r, 0].ToString(), out mile))
{
if (keepPromt)
{
keepPromt = PrompError($"第{errorRow}行数据有误，无法解析桩号数据。");
}
continue;
}
//
double slopeLength = 0;
if (!double.TryParse(arr[r, 2].ToString(), out slopeLength))
{
if (keepPromt)
{
keepPromt = PrompError($"第{errorRow}行数据有误，无法解析边坡长度。");
}
continue;
}
//
var ml = new KeyValuePair <float, double>(mile, slopeLength);
slopes.Add(ml);
}
}
return(slopes);
}
public List <Item> GetStandardSource(Range rg)
{
var items = new List <Item>();
var src = RangeValueConverter.GetRangeValue <object>(rg.Value) as object[, ];
for (int r = 1; r < src.GetLength(0); r++)
{
var unit = GetString(src[r, 3]);
if (unit == null || unit == @"/")
{
unit = null;
}
var item = new Item(GetString(src[r, 0]), GetString(src[r, 1]), GetString(src[r, 2]), unit);
items.Add(item);
}
return(items);
}
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> 从Excel表格中提取信息 </summary>
public static List <Item> GetItemsFromExcel()
{
var xlsFile = Utils.ChooseOpenFile("的Excel文件",
"Excel工作簿(*.xlsx)|*.xlsx| Excel二进制工作簿(*.xlsb) |*.xlsb| Excel 97-2003 工作簿(*.xls)|*.xls", false);
if (xlsFile == null)
{
return(null);
}
var xls = xlsFile[0];
if (!File.Exists(xls))
{
return(null);
}
// 打开Excel文件 并 提取表格中的数据
var app = new Application {
Visible = false
};
var wkbk = app.Workbooks.Open(xls, ReadOnly: true);
var sht = wkbk.Worksheets[1] as Worksheet;
var xlsV = sht.UsedRange.Value;
wkbk.Close(false);
app.Quit();
var table = RangeValueConverter.GetRangeValue <object>(xlsV) as object[, ];
// 解析表格数据
var rowCount = table.GetLength(0);
var colCount = table.GetLength(1);
//var sb = eZstd.Miscellaneous.DebugUtils.PrintArray(table);
//MessageBox.Show(sb.ToString());
// 有效数据有6列，第一行为表头，
var lastPaperId = table[1, 0].ToString();
var items = new List <Item>();
for (var i = 1; i < rowCount; i++)
{
double start = 0;
// 退出准则：没有分类数据。（可能在Excel表中出现了无效行）
if (table[i, 2] == null || !double.TryParse(table[i, 2].ToString(), out start))
{
break;
}
// MessageBox.Show($"{i},{table[i, 0]},{table[i, 1]},{table[i, 2]},{table[i, 3]},{table[i, 4]},{table[i, 5]}");
var paperId = table[i, 0] == null || string.IsNullOrEmpty(table[i, 0].ToString())
? lastPaperId
: table[i, 0].ToString();
lastPaperId = paperId;
//
var it = new Item();
it.PaperId = paperId;
it.Subject = table[i, 1].ToString() == "设计通知单" ? Subject.Inform : Subject.Request;
it.Start = (double)table[i, 2];
it.End = (double)table[i, 3];
it.LeftSide = table[i, 4] != null && table[i, 4].ToString() == "左";
it.Category = table[i, 5].ToString();
items.Add(it);
}
return(items);
}
/// <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());
}
