public void FindColumnIndexByName_ShouldReturnZeroColumnIndex_WhileColumnNotExists()
{
//Arrange
string fileName = "列索引测试_ShouldReturnZeroColumnIndex.xlsx";
//删除干扰文件
if (File.Exists(fileName))
{
File.Delete(fileName);
}
var file = new FileInfo(fileName);
using (var excelPackage = new ExcelPackage(file))
{
var worksheet = excelPackage.Workbook.Worksheets.Add("桥面系");
//添加表头
worksheet.Cells[1, 1].Value = "序号";
worksheet.Cells[1, 2].Value = "位置";
worksheet.Cells[1, 3].Value = "要素";
worksheet.Cells[1, 4].Value = "缺损类型";
worksheet.Cells[1, 5].Value = "缺损描述";
worksheet.Cells[1, 6].Value = "图片描述";
worksheet.Cells[1, 7].Value = "照片编号";
excelPackage.Save();
}
int expectedColumn = 0;
int searchedColumn = 0;
//Act
using (var excelPackage = new ExcelPackage(file))
{
var worksheet = excelPackage.Workbook.Worksheets["桥面系"];
searchedColumn = SaveExcelService.FindColumnIndexByName(worksheet, "要素1");
}
//删除临时文件
if (File.Exists(fileName))
{
File.Delete(fileName);
}
//Assert
//桥面系
Assert.Equal(expectedColumn, searchedColumn);
}
public void GenerateDamageStatisticsTableShouldSetCorrectStatisticsData()
{
//Arrange
IKernel kernel = new StandardKernel(new NinjectDependencyResolver());
var dataRepository = kernel.Get <IDataRepository>();
string saveFileName = "桥梁检测病害统计汇总表.xlsx";
List <DamageSummary> lst1, lst2, lst3;
lst1 = dataRepository.ReadDamageData(BridgePart.BridgeDeck);
lst2 = dataRepository.ReadDamageData(BridgePart.SuperSpace);
lst3 = dataRepository.ReadDamageData(BridgePart.SubSpace);
DamageSummaryServices.InitListDamageSummary(lst1);
DamageSummaryServices.InitListDamageSummary(lst2, 2_000_000, BridgePart.SuperSpace);
DamageSummaryServices.InitListDamageSummary(lst3, 3_000_000, BridgePart.SubSpace);
ObservableCollection <DamageSummary> oc1 = new ObservableCollection <DamageSummary>();
ObservableCollection <DamageSummary> oc2 = new ObservableCollection <DamageSummary>();
ObservableCollection <DamageSummary> oc3 = new ObservableCollection <DamageSummary>();
lst1.ForEach(x => oc1.Add(x)); lst2.ForEach(x => oc2.Add(x)); lst3.ForEach(x => oc3.Add(x));
int expectedUnit1TotalCounts = 3; int acturalUnit1TotalCounts = 0;
decimal expectedUnit2TotalCounts = 29.8m; decimal acturalUnit2TotalCounts = 0.0m;
//Act
DamageSummaryServices.GenerateDamageStatisticsTable(oc1, oc2, oc3);
var file = new FileInfo(saveFileName);
using (var excelPackage = new ExcelPackage(file))
{
// 检查"桥面系"Worksheets
var worksheet = excelPackage.Workbook.Worksheets["桥面系病害统计汇总表"];
acturalUnit1TotalCounts = Convert.ToInt32(worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "单位1数量")].Value?.ToString() ?? string.Empty, CultureInfo.InvariantCulture);
acturalUnit2TotalCounts = Convert.ToDecimal(worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "单位2数量")].Value?.ToString() ?? string.Empty, CultureInfo.InvariantCulture);
}
//Assert
Assert.Equal(expectedUnit1TotalCounts, acturalUnit1TotalCounts);
Assert.Equal(expectedUnit2TotalCounts, acturalUnit2TotalCounts);
}
/// <summary>
/// 读取病害数据
/// </summary>
/// <param name="workSheetName">工作簿名称</param>
/// <returns></returns>
public List <DamageSummary> ReadDamageData(BridgePart bridgePart)
{
string strFilePath = App.DamageSummaryFileName;
var workSheetName = EnumHelper.GetEnumDesc(bridgePart).ToString();
var lst = new List <DamageSummary>();
if (!File.Exists(strFilePath))
{
return(lst);
}
try
{
FileInfo file = new FileInfo(strFilePath);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[workSheetName];
int rowCount = 2; // worksheet.Dimension.Rows; //worksheet.Dimension.Rows指的是所有列中最大行
//首行：表头不导入
bool rowCur = true; //行游标指示器
//rowCur=false表示到达行尾
//计算行数
while (rowCur)
{
try
{
//跳过表头
if (string.IsNullOrWhiteSpace(worksheet.Cells[rowCount + 1, 1].Value?.ToString()))
{
rowCur = false;
}
}
catch (Exception ex) //读取异常则终止
{
rowCur = false;
}
if (rowCur)
{
rowCount++;
}
}
//bool validationResult = false;
int row = 2; //excel中行指针
//行号不为空，则继续添加
//while (!string.IsNullOrEmpty(worksheet.Cells[row, 1].Value.ToString()))
for (row = 2; row <= rowCount; row++)
{
//
//1、处理excel数据导入;
//2、验证"视图模型";
//3、验证业务模型;
lst.Add(new DamageSummary
{
No = row - 1
, Position = worksheet.Cells[row, 2].Value?.ToString() ?? string.Empty
, Component = worksheet.Cells[row, 3].Value?.ToString() ?? string.Empty
, Damage = worksheet.Cells[row, 4].Value?.ToString() ?? string.Empty
, DamageDescription = worksheet.Cells[row, 5].Value?.ToString() ?? string.Empty
, DamageDescriptionInPicture = worksheet.Cells[row, 6].Value?.ToString() ?? string.Empty
, PictureNo = worksheet.Cells[row, 7].Value?.ToString() ?? string.Empty
, CustomPictureNo = worksheet.Cells[row, SaveExcelService.FindColumnIndexByName(worksheet, "自定义照片编号")].Value?.ToString() ?? string.Empty
,
Comment = worksheet.Cells[row, SaveExcelService.FindColumnIndexByName(worksheet, "备注")].Value?.ToString() ?? string.Empty
, Unit1 = worksheet.Cells[row, SaveExcelService.FindColumnIndexByName(worksheet, "单位1")].Value?.ToString() ?? string.Empty
, Unit1Counts = GetUnit1Counts(worksheet.Cells[row, SaveExcelService.FindColumnIndexByName(worksheet, "单位1数量")].Value?.ToString() ?? string.Empty)
, Unit2 = worksheet.Cells[row, SaveExcelService.FindColumnIndexByName(worksheet, "单位2")].Value?.ToString() ?? string.Empty
, Unit2Counts = GetUnit2Counts(worksheet.Cells[row, SaveExcelService.FindColumnIndexByName(worksheet, "单位2数量")].Value?.ToString() ?? string.Empty)
});
}
}
//显示导入结果
return(lst);
}
catch (Exception ex)
{
throw ex;
}
}
public void SaveExcel_ShouldSaveCorrectDamageInExcel_WhileDamageIsNotOthers()
{
//Arrange
string saveFileName = "外观检查_ShouldSaveCorrectDamageInExcel.xlsx";
var bridgeDeckListDamageSummary = new List <DamageSummary>
{
new DamageSummary {
Position = "第1跨"
, ComponentValue = 2 //"伸缩缝"
, Component = string.Empty
, DamageValue = 1
, Damage = "缝内沉积物阻塞"
, DamageDescription = "左幅0#伸缩缝沉积物阻塞"
}
};
var superSpaceListDamageSummary = new List <DamageSummary>
{
new DamageSummary {
Position = "第1跨"
, Component = "主梁"
, Damage = "无"
, DamageDescription = "无"
}
};
var subSpaceListDamageSummary = new List <DamageSummary>
{
new DamageSummary {
Position = "0#台"
, Component = "台身"
, Damage = "水蚀"
, DamageDescription = "右幅0#台台身水蚀"
}
};
//默认测试桥面系标签
string expectedDamage = "缝内沉积物阻塞"; string acturalDamage = string.Empty;
//Act
SaveExcelService.SaveExcel(bridgeDeckListDamageSummary, superSpaceListDamageSummary, subSpaceListDamageSummary, saveFileName);
var file = new FileInfo(saveFileName);
using (var excelPackage = new ExcelPackage(file))
{
// 检查"桥面系"Worksheets
var worksheet = excelPackage.Workbook.Worksheets["桥面系"];
acturalDamage = worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "缺损类型")].Value?.ToString() ?? string.Empty;
// 检查"上部结构"Worksheets
worksheet = excelPackage.Workbook.Worksheets["上部结构"];
worksheet = excelPackage.Workbook.Worksheets["下部结构"];
}
if (File.Exists(saveFileName))
{
File.Delete(saveFileName);
}
//Assert
//桥面系
Assert.Equal(expectedDamage, acturalDamage);
}
public void SaveExcel_ShouldSaveVarInExcel()
{
//GlobalData.ComponentComboBox[bridgeDeckListDamageSummary[i].ComponentValue].DamageComboBox[bridgeDeckListDamageSummary[i].DamageValue].Title
//Arrange
string saveFileName = "外观检查_ShouldSaveVarInExcel.xlsx";
var bridgeDeckListDamageSummary = new List <DamageSummary>
{
new DamageSummary {
Position = "第1跨"
, ComponentValue = GlobalData.ComponentComboBox.Where(x => x.Title == "伸缩缝").FirstOrDefault().Idx
, Component = string.Empty
, DamageValue = GlobalData.ComponentComboBox.Where(x => x.Title == "伸缩缝").FirstOrDefault().DamageComboBox.Where(p => p.Title == "缝内沉积物阻塞").FirstOrDefault().Idx //若节约时间不写复杂表达式，直接填1（仅对本次测试有效）
, Damage = "缝内沉积物阻塞"
, DamageDescription = "左幅0#伸缩缝沉积物阻塞"
, Comment = "新增"
}
};
var superSpaceListDamageSummary = new List <DamageSummary>
{
new DamageSummary {
Position = "第1跨"
, ComponentValue = GlobalData.SuperSpaceComponentComboBox.Where(x => x.Title == "主梁").FirstOrDefault().Idx
, Component = string.Empty
, DamageValue = GlobalData.SuperSpaceComponentComboBox.Where(x => x.Title == "主梁").FirstOrDefault().DamageComboBox.Where(p => p.Title == "其它").FirstOrDefault().Idx
, Damage = "无"
, DamageDescription = "无"
}
};
var subSpaceListDamageSummary = new List <DamageSummary>
{
new DamageSummary {
Position = "0#台"
, ComponentValue = GlobalData.SubSpaceComponentComboBox.Where(x => x.Title == "台身").FirstOrDefault().Idx
, Component = "台身"
, DamageValue = GlobalData.SubSpaceComponentComboBox.Where(x => x.Title == "台身").FirstOrDefault().DamageComboBox.Where(p => p.Title == "其它").FirstOrDefault().Idx
, Damage = "水蚀"
, DamageDescription = "右幅0#台台身水蚀"
}
};
//默认测试桥面系标签
string expectedDamage = bridgeDeckListDamageSummary[0].Damage; string acturalDamage = string.Empty;
string expectedDamageDescription = bridgeDeckListDamageSummary[0].DamageDescription; string acturalDamageDescription = string.Empty;
string expectedComment = bridgeDeckListDamageSummary[0].Comment; string acturalComment = string.Empty;
string expectedDamageInSuperStructure = "无"; string acturalDamageInSuperStructure = string.Empty;
string expectedDamageInSubStructure = "水蚀"; string acturalDamageInSubStructure = string.Empty;
//Act
SaveExcelService.SaveExcel(bridgeDeckListDamageSummary, superSpaceListDamageSummary, subSpaceListDamageSummary, saveFileName);
var file = new FileInfo(saveFileName);
using (var excelPackage = new ExcelPackage(file))
{
// 检查"桥面系"Worksheets
var worksheet = excelPackage.Workbook.Worksheets["桥面系"];
acturalDamage = worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "缺损类型")].Value?.ToString() ?? string.Empty;
acturalDamageDescription = worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "缺损描述")].Value?.ToString() ?? string.Empty;
acturalComment = worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "备注")].Value?.ToString() ?? string.Empty;
// 检查"上部结构"Worksheets
worksheet = excelPackage.Workbook.Worksheets["上部结构"];
acturalDamageInSuperStructure = worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "缺损类型")].Value?.ToString() ?? string.Empty;
worksheet = excelPackage.Workbook.Worksheets["下部结构"];
acturalDamageInSubStructure = worksheet.Cells[2, SaveExcelService.FindColumnIndexByName(worksheet, "缺损类型")].Value?.ToString() ?? string.Empty;
}
//Assert
//桥面系
Assert.Equal(expectedDamage, acturalDamage);
Assert.Equal(expectedDamageDescription, acturalDamageDescription);
Assert.Equal(expectedComment, acturalComment);
//上部结构
Assert.Equal(expectedDamageInSuperStructure, acturalDamageInSuperStructure);
//下部结构
Assert.Equal(expectedDamageInSubStructure, acturalDamageInSubStructure);
//Assert.Equal(0, 1); //TODO：初始化变量重构
}
private static ObservableCollection <BridgeDamage> LoadDataFromExcel(BridgePart bridgePart = BridgePart.BridgeDeck)
{
string workSheetName = "桥面系";
if (bridgePart == BridgePart.BridgeDeck)
{
workSheetName = "桥面系";
}
else if (bridgePart == BridgePart.SuperSpace)
{
workSheetName = "上部结构";
}
else
{
workSheetName = "下部结构";
}
string strFilePath = $"{App.ConfigurationFolder}\\{App.DamageSummaryLibFileName}";
var lst = new List <BridgeDamage>();
if (!File.Exists(strFilePath))
{
return(new ObservableCollection <BridgeDamage>(lst));
}
int currRow = 2;
string previousContent = string.Empty; string currContent = string.Empty;
var file = new FileInfo(strFilePath);
try
{
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[workSheetName];
currContent = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素名称")].Value?.ToString() ?? string.Empty).Trim();
previousContent = currContent;
if (!string.IsNullOrWhiteSpace(worksheet.Cells[2, 2].Value?.ToString() ?? string.Empty))
{
lst.Add(new BridgeDamage
{
Title = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素名称")].Value?.ToString() ?? string.Empty).Trim()
,
CategoryTitle = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素分类")].Value?.ToString() ?? string.Empty).Trim()
,
Idx = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素索引")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
,
Id = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素值")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
,
DamageComboBox = new ObservableCollection <BridgeDamage> {
new BridgeDamage
{
Title = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害名称")].Value?.ToString() ?? string.Empty).Trim()
,
CategoryTitle = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害分类")].Value?.ToString() ?? string.Empty).Trim()
,
Idx = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害索引")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
,
Id = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害值")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture),
}
}
});
}
else
{
return(new ObservableCollection <BridgeDamage>(lst));
}
previousContent = currContent;
currRow++;
currContent = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素名称")].Value?.ToString() ?? string.Empty).Trim();
while (!string.IsNullOrWhiteSpace(currContent))
{
if (previousContent != currContent)
{
lst.Add(new BridgeDamage
{
Title = currContent
,
CategoryTitle = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素分类")].Value?.ToString() ?? string.Empty).Trim()
,
Idx = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素索引")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
,
Id = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素值")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
,
DamageComboBox = new ObservableCollection <BridgeDamage> {
new BridgeDamage
{
Title = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害名称")].Value?.ToString() ?? string.Empty).Trim()
,
CategoryTitle = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害分类")].Value?.ToString() ?? string.Empty).Trim()
,
Idx = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害索引")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
,
Id = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害值")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture),
}
}
});
}
else
{
lst.Where(x => x.Title == currContent).FirstOrDefault().DamageComboBox.Add(new BridgeDamage
{
Title = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害名称")].Value?.ToString() ?? string.Empty).Trim()
,
CategoryTitle = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害分类")].Value?.ToString() ?? string.Empty).Trim()
,
Idx = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害索引")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
,
Id = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "病害值")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture),
});
}
previousContent = currContent;
currRow++;
currContent = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "要素名称")].Value?.ToString() ?? string.Empty).Trim();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(new ObservableCollection <BridgeDamage>(lst));
}
private static ObservableCollection <StatisticsUnit> LoadUnitDataFromExcel(string workSheetName)
{
string strFilePath = $"{App.ConfigurationFolder}\\{App.StatisticsUnitFileName}";
var lst = new List <StatisticsUnit>();
if (!File.Exists(strFilePath))
{
return(new ObservableCollection <StatisticsUnit>(lst));
}
int currRow = 2;
string currContent = string.Empty;
var file = new FileInfo(strFilePath);
try
{
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[workSheetName];
currContent = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "名称")].Value?.ToString() ?? string.Empty).Trim();
if (!string.IsNullOrWhiteSpace(worksheet.Cells[2, 2].Value?.ToString() ?? string.Empty))
{
lst.Add(new StatisticsUnit
{
Title = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "名称")].Value?.ToString() ?? string.Empty).Trim()
, DisplayTitle = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "显示名称")].Value?.ToString() ?? string.Empty).Trim()
, Idx = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "索引")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
, PhysicalItem = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "物理量")].Value?.ToString() ?? string.Empty).Trim()
});
}
else
{
return(new ObservableCollection <StatisticsUnit>(lst));
}
currRow++;
while (!string.IsNullOrWhiteSpace(currContent))
{
lst.Add(new StatisticsUnit
{
Title = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "名称")].Value?.ToString() ?? string.Empty).Trim()
, DisplayTitle = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "显示名称")].Value?.ToString() ?? string.Empty).Trim()
, Idx = Convert.ToInt32((worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "索引")].Value?.ToString() ?? string.Empty).Trim(), CultureInfo.InvariantCulture)
, PhysicalItem = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "物理量")].Value?.ToString() ?? string.Empty).Trim()
});
currRow++;
currContent = (worksheet.Cells[currRow, SaveExcelService.FindColumnIndexByName(worksheet, "名称")].Value?.ToString() ?? string.Empty).Trim();
}
}
}
catch (Exception ex)
{
throw ex;
}
return(new ObservableCollection <StatisticsUnit>(lst));
}