/// <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);
}
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);
}
/// <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>
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());
}