public static MSExcel.Range GetRange(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position1, ExcelPosition position2) { if (_excelDoc != null) { if (position1.IsValid && position2.IsValid) { MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; MSExcel.Range _excelRge = (MSExcel.Range)_excelSht.Cells.get_Range(position1, position2); return _excelRge; } else { Log.LogHelper.AddLog(@"异常26", @"读取数据时传入了错误的位置坐标:" + position1, true); return null; } } else { Log.LogHelper.AddLog(@"异常27", @"文件没有正常打开,无法读取数据", true); return null; } }
/// <summary> /// 获取指定坐标位置单元格的range变量 /// </summary> /// <param name="sheetIndex"></param> /// <param name="row"></param> /// <param name="col"></param> /// <param name="success"></param> /// <returns></returns> public MSExcel.Range GetRange(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position1, ExcelPosition position2) { if (_excelDoc != null) { MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; MSExcel.Range _excelRge = (MSExcel.Range)_excelSht.Cells.get_Range(position1.PositionString, position2.PositionString); return _excelRge; } else { Log.LogHelper.AddLog(@"异常28", @"文件没有正常打开,无法读取数据", true); return null; } }
public static string GetMergedContent(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position1, ExcelPosition position2, string[] titles) { string temp_text1 = GetText(_excelDoc, sheetIndex, position1).Replace(@":", ":").Replace(@" ", ""); string temp_text2 = GetText(_excelDoc, sheetIndex, position2).Replace(@":", ":").Replace(@" ", ""); if (!temp_text1.Equals("")) { foreach (string item in titles) { if (temp_text1.Equals(item)) { if (temp_text2 != "") { return temp_text2; } else { return "/"; } } else { return temp_text1.Replace(item, "").Trim(); } } } string text = temp_text1 + temp_text2; foreach (string item in titles) { if (text.StartsWith(item)) { text = text.Replace(item, "").Trim(); if (text != "") { return text; } else { return "/"; } } } return ""; }
/// <summary> /// 将图片插入到指定的单元格位置,并设置图片的宽度和高度。 /// 注意:图片必须是绝对物理路径 /// </summary> /// <param name="RangeName">单元格名称,例如:B4</param> /// <param name="PicturePath">要插入图片的绝对路径。</param> /// <param name="PictuteWidth">插入后,图片在Excel中显示的宽度。</param> /// <param name="PictureHeight">插入后,图片在Excel中显示的高度。</param> public void WriteImage(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string fileName, float PictuteWidth, float PictureHeight) { if (_excelDoc != null) { try { float PicLeft, PicTop; MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; MSExcel.Range _excelRge = _excelSht.get_Range(position.PositionString); _excelRge.Select(); PicLeft = Convert.ToSingle(_excelRge.Left); PicTop = Convert.ToSingle(_excelRge.Top); _excelSht.Shapes.AddPicture(fileName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight); } catch (Exception ex) { LogHelper.AddLog(@"异常30", ex.Message, true); LogHelper.AddLog(@"异常31", " " + ex.TargetSite.ToString(), true); } } else { LogHelper.AddLog(@"异常32", @"文件没有正常打开,无法读取数据", true); } }
/// <summary> /// 智能提取混合内容 /// </summary> /// <param name="sheetIndex"></param> /// <param name="row"></param> /// <param name="col"></param> /// <param name="newRow"></param> /// <param name="newCol"></param> /// <param name="titles"></param> /// <param name="success"></param> /// <returns></returns> public string GetMergeContent(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition startPosition, ExcelPosition endPosition, string[] titles, out bool success) { string temp_text1 = GetText(_excelDoc, sheetIndex, startPosition).Replace(@":", ":").Replace(@" ", ""); string temp_text2 = GetText(_excelDoc, sheetIndex, endPosition); string title = ""; if (!temp_text1.Equals("")) { foreach (string item in titles) { if (temp_text1.StartsWith(item)) { if (temp_text1.Equals(item)) { title = item; if (temp_text2 != "") { success = true; return temp_text2; } else { success = false; return "/"; } } else { success = true; return temp_text1.Replace(item, "").Trim(); } } } } success = false; return ""; }
/// <summary> /// 从某个位置复制数据,并向其他某个坐标位置以特定格式粘贴 /// </summary> /// <param name="sourceSheetIndex"></param> /// <param name="sourcePosition"></param> /// <param name="destinationSheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="colomnIndex"></param> /// <param name="numberFormat"></param> /// <param name="success"></param> public void CopyData(MSExcel._Workbook sourceExcelDoc, int sourceSheetIndex, ExcelPosition sourcePosition, MSExcel._Workbook destinationExcelDoc, int destinationSheetIndex, ExcelPosition destinationPosition, string numberFormat, out bool success) { try { string temp; MSExcel.Range _excelRge = GetRange(sourceExcelDoc, sourceSheetIndex, sourcePosition); if (_excelRge.Text.ToString().StartsWith(@"#DI") || _excelRge.Value2 == null) { temp = "/"; success = false; } else { temp = GetText(sourceExcelDoc, sourceSheetIndex, sourcePosition); success = true; } WriteValue(destinationExcelDoc, destinationSheetIndex, destinationPosition, temp, numberFormat); } catch (System.Exception ex) { LogHelper.AddLog(@"异常41", ex.Message, true); LogHelper.AddLog(@"异常42", " " + ex.TargetSite.ToString(), true); success = false; } }
/// <summary> /// 向某个坐标位置写入特定格式的文本 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="colomnIndex"></param> /// <param name="wValue"></param> /// <param name="numberFormat"></param> /// <param name="success"></param> public void WriteValue(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue, string numberFormat) { if (_excelDoc != null) { try { MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; _excelSht.Cells[position.RowIndex, position.ColumnIndex] = wValue; MSExcel.Range _excelRge = _excelSht.get_Range(position.PositionString); _excelRge.NumberFormatLocal = numberFormat; return; } catch (Exception ex) { LogHelper.AddLog(@"异常230", ex.Message, true); LogHelper.AddLog(@"异常230", "位置:" + position.PositionString, true); return; } } else { LogHelper.AddLog(@"异常35", @"文件没有正常打开,无法读取数据", true); return; } }
public string GetText(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position) { try { MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position); return _excelRge.Text.ToString().Trim(); } catch { return ""; } }
public static string GetValueText(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position) { MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position); if (_excelRge != null) { return _excelRge.Value2.ToString(); } else { return ""; } }
public static MSExcel.Range GetRange(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position) { return GetRange(_excelDoc, sheetIndex, position, position); }
public static void WriteImage(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string personPath, float PictuteWidth, float PictureHeight) { if (_excelDoc != null) { try { MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position); _excelRge.Select(); if (PictuteWidth < 1 || PictureHeight < 1) { MSExcel.Pictures pics = (MSExcel.Pictures)_excelSht.Pictures(Missing.Value); pics.Insert(personPath, Missing.Value); } else { float PicLeft = Convert.ToSingle(_excelRge.Left); float PicTop = Convert.ToSingle(_excelRge.Top); _excelSht.Shapes.AddPicture(personPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, PictuteWidth, PictureHeight); } return; } catch (Exception ex) { Log.LogHelper.AddLog(@"异常130", ex.Message, true); Log.LogHelper.AddLog(@"异常131", " " + ex.TargetSite.ToString(), true); return; } } else { Log.LogHelper.AddLog(@"异常32", @"文件没有正常打开,无法读取数据", true); return; } }
public static void WriteFormula(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue, out bool success) { if (_excelDoc != null) { try { MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position); _excelRge.FormulaLocal = wValue; success = true; return; } catch (Exception ex) { success = false; Log.LogHelper.AddLog(@"异常36", ex.Message, true); Log.LogHelper.AddLog(@"异常37", " " + ex.TargetSite.ToString(), true); return; } } else { success = false; Log.LogHelper.AddLog(@"异常38", @"文件没有正常打开,无法读取数据", true); return; } }
public static void WriteValue(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue, string numberFormat, out bool success) { if (_excelDoc != null) { try { bool checkSta = true; MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; _excelSht.Cells[position.RowIndex, position.ColumnIndex] = wValue; if (!string.IsNullOrWhiteSpace(numberFormat)) { MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position); _excelRge.NumberFormatLocal = numberFormat; } success = checkSta; return; } catch (Exception ex) { success = false; Log.LogHelper.AddLog(@"异常33", ex.Message, true); Log.LogHelper.AddLog(@"异常34", " " + ex.TargetSite.ToString(), true); return; } } else { success = false; Log.LogHelper.AddLog(@"异常35", @"文件没有正常打开,无法读取数据", true); return; } }
/// <summary> /// 计算长期稳定性 /// </summary> /// <param name="sheetIndex"></param> /// <param name="row"></param> /// <param name="col"></param> /// <param name="range"></param> /// <param name="success"></param> public void StablizeOneKV(MSExcel._Workbook _excelDoc, int sheetIndex, KVCriterion crit, int row, int col, out string range, out bool success) { try { ExcelPosition pos1 = new ExcelPosition(row - 1, col - 2); ExcelPosition pos2 = new ExcelPosition(row, col - 2); ExcelPosition pos = new ExcelPosition(row, col); MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, pos); if (HadNumber(_excelDoc, sheetIndex, pos2) && HadNumber(_excelDoc, sheetIndex, pos1)) { if (crit.Index > 2) { _excelRge.Formula = "=(" + pos2.PositionString + "-" + pos1.PositionString + ")/" + pos1.PositionString; } else { _excelRge.Formula = "=" + pos1.PositionString + "-" + pos2.PositionString; } _excelRge.NumberFormatLocal = "0.0%"; _excelDoc.Save(); } if (_excelRge != null && _excelRge.Value2 != null) { range = _excelRge.Value2.ToString(); } else { range = GetText(_excelDoc, sheetIndex, pos); } success = true; } catch (System.Exception ex) { success = false; range = null; Log.LogHelper.AddLog(@"异常45", ex.Message, true); Log.LogHelper.AddLog(@"异常46", " " + ex.TargetSite.ToString(), true); } }
/// <summary> /// 找模板页,加入 /// /// 搬运内容: /// 01、L2 02 12 证书编号 /// 02、B4 -D4 04 02-04 送校单位 /// 03、F4 04 06 联系地址 /// 04、B5 05 02 仪器名称 /// 05、F5 05 06 型号 /// 06、H5 05 08 编号 /// 07、J5 05 10 厂家 /// 08、L5 05 12 电离室号 /// 09、K7 07 11 温度 /// 10、M7 07 13 湿度 /// 11、J8 08 10 气压 /// 12、D12-M12 12 04-13 量程 /// 13、D18 18 04 数据 /// 14、F18 18 06 数据 /// 15、H18 18 08 数据 /// 16、J18 18 10 数据 /// 17、L18 18 13 数据 /// 18、D19 19 04 数据 /// 19、F19 19 06 数据 /// 20、H19 19 08 数据 /// 21、J19 19 10 数据 /// 22、L19 19 12 数据 /// 23、D20 20 04 数据 /// 24、F20 20 06 数据 /// 25、H20 20 08 数据 /// 26、J20 20 10 数据 /// 27、L20 20 12 数据 /// 28、G31 31 07 记录者签名 /// 29、I31 31 09 校对者签名 /// 30、K31 31 11 日期 /// </summary> /// <param name="sourceEx"></param> /// <param name="sourceIndex"></param> /// <param name="destiEx"></param> public static void CopyData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int pattern, string certIdori, bool needFix, bool shouldFix, int startDestiRowIndex, out int newSheetIndex, out bool success) { bool noNeed = false; int templateIndex = -1; int startSourceRowIndex = -1; int destiIndex = -1; MSExcel.Range rr = null; MSExcel.Worksheet ws1 = null; bool checkClear; Dictionary<int, string> exSheets = new Dictionary<int, string>(); string temp; string text = ""; string certId = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition("L2")); if (!IsValidCertificate(certId)) { LogHelper.AddException(@"无法提取到证书编号", true); success = false; newSheetIndex = -1; return; } foreach (MSExcel.Worksheet item in destiEx.ExcelWorkbook.Sheets) { if (item.Name == "统计") { destiIndex = item.Index; } else if (item.Name == @"标准模板") { //找到模板页 templateIndex = item.Index; } else if (item.Name.Contains(@"标准模板")) { LogHelper.AddDataError(@"第" + item.Index + "页发现多余的标准模板", true); } else { temp = destiEx.GetText(destiEx.ExcelWorkbook, item.Index, new ExcelPosition("L2")); if (temp.StartsWith(@"20") && (temp.Length == 9 || temp.Length == 10)) { //找到有证书编号的数据页 if (item.Name == certId) { if (FormOperator.MessageBox_Show_YesNo("在历史数据记录的Excel中已发现了证书编号为" + certId + "的页面,是否覆盖?选择是,进行覆盖。选择否,停止对本Excel的处理", "是否覆盖")) { ws1 = item; } else { LogHelper.AddException(@"要合并入的数据已存在于第" + item.Index + "页", true); newSheetIndex = -1; success = false; return; } } else { exSheets.Add(item.Index, temp); } } } } if (!noNeed) { if (templateIndex == -1) { LogHelper.AddException(@"找不到数据的标准模板", true); success = false; newSheetIndex = -1; return; } else { //有模板页。没找到参考证书编号页 if (ws1 == null) { ws1 = (MSExcel.Worksheet)destiEx.ExcelWorkbook.Sheets[templateIndex]; if (exSheets.Count > 0) { //有有效数据页 if (destiIndex < 1) { //没有统计页,把有效数据页里最前面的序号赋给destiIndex foreach (int item in exSheets.Keys) { if (destiIndex < 1 || destiIndex > item) { destiIndex = item; } } } foreach (int item in exSheets.Keys) { if (DataUtility.DataUtility.LargerThan(certId, exSheets[item]) && destiIndex < item) { //在所有比参考编号小的页面里,挑一个最靠后的序号给destiIndex destiIndex = item; } } //在destiIndex右侧复制模板页 ws1.Copy(Type.Missing, destiEx.ExcelWorkbook.Sheets[destiIndex]); //把新复制的模板页赋给ws1 ws1 = (MSExcel.Worksheet)destiEx.ExcelWorkbook.Sheets[destiIndex + 1]; } else { //没有有效数据页时,在模板页左侧复制模板页 ws1.Copy(destiEx.ExcelWorkbook.Sheets[templateIndex], Type.Missing); //把原来模板页,现在的模板复制页的位置给了ws1 ws1 = (MSExcel.Worksheet)destiEx.ExcelWorkbook.Sheets[templateIndex]; } if (!ws1.Name.Contains(@"标准模板")) { LogHelper.AddException(@"标准模板复制出错", true); success = false; newSheetIndex = -1; return; } if (!exSheets.ContainsValue(certId)) { ws1.Name = certId; } } newSheetIndex = ws1.Index; //确定原始数据的数据行 for (int i = 15; i < 22; i++) { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i, 3)); if (text == "1") { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i + 1, 3)); if (text == "2") { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(i + 2, 3)); if (text == "3") { startSourceRowIndex = i; break; } } } } if (startSourceRowIndex == -1) { LogHelper.AddException(@"找不到原始数据所在的行", true); success = false; newSheetIndex = -1; return; } //拷贝数据 CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(2, 12), "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《证书编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(4, 1), new ExcelPosition(4, 2), new string[] { @"送校单位:", @"单位名称:" }, "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《送校单位》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(4, 5), new ExcelPosition(4, 6), @"联系地址:", "@", out checkClear); //if (!checkClear) AddException(@"《联系地址》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 1), new ExcelPosition(5, 2), @"仪器名称:", "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《仪器名称》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 5), new ExcelPosition(5, 6), @"型号:", "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《型号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 7), new ExcelPosition(5, 8), new string[] { @"主机编号:", @"编号:" }, "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《主机编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 9), new ExcelPosition(5, 10), @"厂家:", "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《厂家》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(5, 11), new ExcelPosition(5, 12), new string[] { @"探测器编号:", "电离室号:", "探测器号:" }, "@", out checkClear); if (!checkClear) LogHelper.AddException(@"《探测器编号》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(31, 7), "", out checkClear); //if (!checkClear) AddException(@"《记录者》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(31, 9), "", out checkClear); //if (!checkClear) AddException(@"《校对者》数据复制错误", true); CopyDate(sourceEx, sourceIndex, destiEx, ws1.Index, out checkClear); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(7, 11), "0.000", out checkClear); if (needFix && !checkClear) LogHelper.AddException(@"《温度》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(7, 13), "0.0%", out checkClear); if (needFix && !checkClear) LogHelper.AddException(@"《湿度》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(8, 10), "", out checkClear); if (needFix && !checkClear) LogHelper.AddException(@"《气压》数据复制错误", true); rr = destiEx.GetRange(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition("M8")); if (needFix) { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "修正"); } else { //电离室->半导体 rr = destiEx.GetRange(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition("L8")); rr.FormulaLocal = ""; rr.Formula = ""; rr.FormulaArray = ""; destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 12), "1.000000", "@"); if (shouldFix) { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "自修正"); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, new ExcelPosition(8, 13), "不修正"); } } List<DataStruct> dataStructList = new List<DataStruct>(); DataStruct dataStruct; int standardRowIndex = -1; string keyword = "标准"; switch (pattern) { case 2: keyword = "滤片"; break; } for (int p = 13; p < 18; p++) { text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(p, 1)) + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(p, 2)) + sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(p, 3)); if (text.ToLower().Contains(keyword)) { standardRowIndex = p; break; } } ExcelPosition dataRangePosition = new ExcelPosition(12, 13); ExcelPosition dataDistancePosition = new ExcelPosition(6, 13); switch (pattern) { case 0: //Dose CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(27, 13), "@", out checkClear); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(12, 4), "", out checkClear); if (!checkClear) LogHelper.AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeDoseData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 12)); if (text.StartsWith("单位")) { CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, dataRangePosition, "", out checkClear); if (!checkClear) LogHelper.AddException(@"《单位》数据复制错误", true); } else { dataStruct = DataStruct.CalDataRange(dataStructList, sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)), pattern, out success); switch (dataStruct.DataRanges) { case DataRange.cGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "cGy"); break; case DataRange.mGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mGy"); break; case DataRange.mR: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mR"); break; case DataRange.R: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "R"); break; case DataRange.uGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "μGy"); break; case DataRange.Unknown: LogHelper.AddException("无法判断数据单位", true); break; } switch (dataStruct.Distance) { case Distance.d1: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.0m"); break; case Distance.d1_5: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.5m"); break; case Distance.Unknown: LogHelper.AddException("无法获取标准值,判断测试距离", true); break; } } break; case 1: //CT CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(27, 13), "@", out checkClear); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(12, 4), "", out checkClear); if (!checkClear) LogHelper.AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); dataStructList.Add(CopyThreeCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear)); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 12)); if (text.StartsWith("单位")) { CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, dataRangePosition, "", out checkClear); if (!checkClear) LogHelper.AddException(@"《单位》数据复制错误", true); } else { dataStruct = DataStruct.CalDataRange(dataStructList, sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)), pattern, out success); switch (dataStruct.DataRanges) { case DataRange.mGycm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mGycm"); break; case DataRange.mGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mGy"); break; case DataRange.cGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "cGy"); break; case DataRange.mR: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "mR"); break; case DataRange.R: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "R"); break; case DataRange.uGy: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "μGy"); break; case DataRange.cGycm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "cGycm"); break; case DataRange.Rcm: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataRangePosition, "Rcm"); break; case DataRange.Unknown: LogHelper.AddException("无法判断数据单位", true); break; } switch (dataStruct.Distance) { case Distance.d1: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.0m"); break; case Distance.d1_5: destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, dataDistancePosition, "1.5m"); break; case Distance.Unknown: LogHelper.AddException("无法获取标准值,判断测试距离", true); break; } } //CopyCTData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, startDestiRowIndex, "", out checkClear); break; case 2: //KV CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(28, 13), "@", out checkClear); //if (!checkClear) AddException(@"《备注》数据复制错误", true); CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, new ExcelPosition(12, 4), @"/", ""); if (!checkClear) LogHelper.AddException(@"《量程》数据复制错误", true); text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(12, 4)); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 4, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 6, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 8, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 10, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); CopyThreeKVData(sourceEx, sourceIndex, destiEx, ws1.Index, startSourceRowIndex, 12, startDestiRowIndex, "", standardRowIndex, text, pattern, out checkClear); //text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 12, out checkClear).Trim(); //if (text.StartsWith("单位")) //{ // //CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 13, "", out checkClear); // CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 4, "", out checkClear); // if (!checkClear) AddException(@"《量程》数据复制错误", true); // if (!checkClear) AddException(@"《单位》数据复制错误", true); //} //else //{ // destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 13, "KV", out checkClear); //} //text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 12, 10, out checkClear).Trim(); //if (text.StartsWith("测试类型")) //{ // CopyOneData(sourceEx, sourceIndex, destiEx, ws1.Index, 12, 11, "", out checkClear); // if (!checkClear) AddException(@"《测试类型》数据复制错误", true); //} //else //{ // text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, 5, 6, out checkClear).Trim().ToLower(); // switch (text) // { // case "iba": // keyword = "ppV"; // break; // case "radcal": // keyword = "kVp"; // break; // default: // keyword = @""; // break; // } // destiEx.WriteValue(destiEx.ExcelWorkbook, ws1.Index, 12, 11, keyword, out checkClear); //} break; default: break; } } } else { newSheetIndex = 1; } success = true; }
public string ErrorCalculator(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position) { MSExcel.Range _excelRge = GetRange(_excelDoc, sheetIndex, position); if (_excelRge != null && _excelRge.Value2 != null) { return _excelRge.Value2.ToString(); } else { return GetText(_excelDoc, sheetIndex, position); } }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition position, string style, out bool sc) { string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); if (text == "") { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } sc = true; }
/// <summary> /// 向某个坐标位置写入文本 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="colomnIndex"></param> /// <param name="wValue"></param> /// <param name="success"></param> public void WriteValue(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue) { WriteValue(_excelDoc, sheetIndex, position, wValue, "@"); }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition position, string style, bool checkDouble, out bool sc) { double temp_double = 0.0; string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); bool isDouble = double.TryParse(text, out temp_double); if (text == "" || !isDouble) { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } sc = true; }
/// <summary> /// 向某个坐标位置写入公式 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="colomnIndex"></param> /// <param name="wValue"></param> /// <param name="success"></param> public void WriteFormula(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string wValue) { if (_excelDoc != null) { try { MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; MSExcel.Range _excelRge = _excelSht.get_Range(position.PositionString); _excelRge.FormulaLocal = wValue; } catch (Exception ex) { LogHelper.AddLog(@"异常36", ex.Message, true); LogHelper.AddLog(@"异常37", " " + ex.TargetSite.ToString(), true); } } else { LogHelper.AddLog(@"异常38", @"文件没有正常打开,无法读取数据", true); } }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition position, string defaultValue, string style) { string text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); if (text == "") { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, position, defaultValue); } else if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } }
/// <summary> /// 智能提取混合内容 /// </summary> /// <param name="sheetIndex"></param> /// <param name="row"></param> /// <param name="col"></param> /// <param name="newRow"></param> /// <param name="newCol"></param> /// <param name="title"></param> /// <param name="success"></param> /// <returns></returns> public string GetMergeContent(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition startPosition, ExcelPosition endPosition, string title, out bool success) { string temp_text = GetText(_excelDoc, sheetIndex, startPosition).Replace(@":", ":").Replace(@" ", ""); if (temp_text.StartsWith(title)) { if (temp_text.EndsWith(title)) { temp_text = GetText(_excelDoc, sheetIndex, endPosition); if (temp_text != "") { success = true; return temp_text; } else { success = false; return "/"; } } else { temp_text = temp_text.Replace(title, "").Trim(); success = true; return temp_text; } } else { success = false; return ""; } }
public static void CopyOneData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, ExcelPosition startPosition, ExcelPosition endPosition, string[] pre, string style, out bool sc) { bool checkClear; string text = sourceEx.GetMergeContent(sourceEx.ExcelWorkbook, sourceIndex, startPosition, endPosition, pre, out checkClear); if (text == "") { sc = false; return; } if (style == "") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, endPosition, text); } else { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, endPosition, text, style); } sc = true; }
/// <summary> /// 将图片插入到指定的单元格位置。 /// 注意:图片必须是绝对物理路径 /// </summary> /// <param name="RangeName">单元格名称,例如:B4</param> /// <param name="PicturePath">要插入图片的绝对路径。</param> public void WriteImage(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position, string fileName) { if (_excelDoc != null) { try { MSExcel.Worksheet _excelSht = (MSExcel.Worksheet)_excelDoc.Worksheets[sheetIndex]; MSExcel.Range _excelRge = _excelSht.get_Range(position.PositionString); _excelRge.Select(); MSExcel.Pictures pics = (MSExcel.Pictures)_excelSht.Pictures(Missing.Value); pics.Insert(fileName, Missing.Value); //IDataObject data = null; //data.SetData(DataFormats.Bitmap, image); //OleSetClipboard(data); //Clipboard.SetData(DataFormats.Bitmap, image); //_excelSht.Paste(); } catch (Exception ex) { LogHelper.AddLog(@"异常30", ex.Message, true); LogHelper.AddLog(@"异常31", " " + ex.TargetSite.ToString(), true); } } else { LogHelper.AddLog(@"异常32", @"文件没有正常打开,无法读取数据", true); } }
public static void CopyThreeKVData(ExcelUtility sourceEx, int sourceIndex, ExcelUtility destiEx, int destiIndex, int startSourceRowIndex, int columnIndex, int startDestiRowIndex, string style, int standardRowIndex, string range, int pattern, out bool sc) { double temp_double = 0.0; string text; MSExcel.Range rr = null; KVCriterion dataCri = KVCriterion.Null; sc = true; //规范 rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(13, columnIndex)); text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(13, columnIndex)); rr.Value2 = text; //标准值 sourceEx.GetCriterion(sourceEx.ExcelWorkbook, sourceIndex, columnIndex, true, out text, out dataCri); //仪器滤片 if (standardRowIndex > 12) { rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, new ExcelPosition(standardRowIndex, columnIndex)); if (rr != null) { text = rr.Text.ToString().Trim(); rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(15, columnIndex)); rr.Value2 = text; } else { rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(15, columnIndex)); rr.Value2 = @"/"; } } else { rr = destiEx.GetRange(destiEx.ExcelWorkbook, destiIndex, new ExcelPosition(15, columnIndex)); rr.Value2 = @"/"; } ExcelPosition position; //复制数据 if (style == "") { for (int i = 0; i < 3; i++) { position = new ExcelPosition(startSourceRowIndex + i, columnIndex); rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, position); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, position, @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text); } else { LogHelper.AddException(position.PositionString + "不包含有效数据", true); sc = false; } } } else { for (int i = 0; i < 3; i++) { position = new ExcelPosition(startSourceRowIndex + i, columnIndex); rr = sourceEx.GetRange(sourceEx.ExcelWorkbook, sourceIndex, position); if (rr.Value2 == null || string.IsNullOrWhiteSpace(rr.Value2.ToString())) { sourceEx.WriteValue(sourceEx.ExcelWorkbook, sourceIndex, position, @"/"); sourceEx.ExcelWorkbook.Save(); sourceEx.ExcelWorkbook.Saved = true; text = sourceEx.GetText(sourceEx.ExcelWorkbook, sourceIndex, position); } else { text = rr.Value2.ToString().Trim(); } if (double.TryParse(text, out temp_double) || text == @"/") { destiEx.WriteValue(destiEx.ExcelWorkbook, destiIndex, position, text, style); } else { LogHelper.AddException(position.PositionString + "不包含有效数据", true); sc = false; } } } }
public bool HadNumber(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position) { double tempDig; string text = GetText(_excelDoc, sheetIndex, position); if (text != "-2146826281" && double.TryParse(text, out tempDig)) { return true; } else { return false; } }
public static string GetMergedContent(MSExcel._Workbook _excelDoc, int sheetIndex, ExcelPosition position1, ExcelPosition position2, string title) { return GetMergedContent(_excelDoc, sheetIndex, position1, position2, new string[] { title }); }