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