/// <summary>
/// 得到一个组合项目
/// </summary>
/// <param name="yybh"></param>
/// <param name="zhxmbh"></param>
/// <returns></returns>
public XtZhxmbModel GetZhxm(string yybh, string zhxmbh)
{
using (IDbConnection conn = new DapperConnection().DbConnection)
{
string sql = "select * from xt_zhxmb a where yybh=@yybh and zhxmbh=@zhxmbh";
XtZhxmbModel model = conn.Query <XtZhxmbModel>(sql, new { yybh = yybh, zhxmbh = zhxmbh }).FirstOrDefault();
return(model);
}
}
/// <summary>
/// 将excel中的数据导入到list中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public List <XtZhxmbModel> ExcelToList(string fileName, string sheetName, out string msg)
{
ISheet sheet = null;
IWorkbook workbook = null;
int startRow = 1;
msg = string.Empty;
List <XtZhxmbModel> lstModels = new List <XtZhxmbModel>();
try
{
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
int col = cellCount; //总列数
if (!ValidateTemplate(firstRow, col))
{
msg = "模板错误，请下载并使用正确的模板";
return(null);
}
//最后一行的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
XtZhxmbModel model = new XtZhxmbModel();
model.yybh = GLYadmin.YYID;
model.zhxmbh = GetCellValue(row, 0).ToString().Trim();
if (string.IsNullOrWhiteSpace(model.zhxmbh))
{
msg = $"第{i}行组合项目编号不能为空";
return(null);
}
model.zhxmmc = GetCellValue(row, 1).ToString().Trim();
if (string.IsNullOrWhiteSpace(model.zhxmmc))
{
msg = $"第{i}行组合项目名称不能为空";
return(null);
}
model.zhxmms = GetCellValue(row, 2).ToString().Trim();
model.zhxmjg = Convert.ToDecimal(GetCellValue(row, 3));
string sex = GetCellValue(row, 4).ToString();
switch (sex)
{
case "男":
model.xb = 1;
break;
case "女":
model.xb = 0;
break;
case "通用":
model.xb = 2;
break;
default:
model.xb = -1;
break;
}
if (model.xb.Equals(-1))
{
msg = $"第{i}行性别不正确(仅支持:男,女,通用)";
return(null);
}
string sffk = GetCellValue(row, 5).ToString();
switch (sffk)
{
case "是":
model.sffk = 1;
break;
case "否":
model.sffk = 0;
break;
default:
model.sffk = -1;
break;
}
if (model.sffk.Equals(-1))
{
msg = $"第{i}行是否妇科不正确(仅支持:是,否)";
return(null);
}
string sfqy = GetCellValue(row, 6).ToString();
switch (sfqy)
{
case "是":
model.sfqy = 1;
break;
case "否":
model.sfqy = 0;
break;
default:
model.sfqy = -1;
break;
}
if (model.sfqy.Equals(-1))
{
msg = $"第{i}行是否启用不正确(仅支持:是,否)";
return(null);
}
model.zhxmksbh = GetCellValue(row, 7).ToString().Trim();
if (string.IsNullOrWhiteSpace(model.zhxmksbh))
{
msg = $"第{i}行组合项目科室编号不能为空";
return(null);
}
model.zhxmksmc = GetCellValue(row, 8).ToString().Trim();
if (string.IsNullOrWhiteSpace(model.zhxmksmc))
{
msg = $"第{i}行组合项目科室名称不能为空";
return(null);
}
model.sxrs = Convert.ToInt32(GetCellValue(row, 9));
lstModels.Add(model);
}
}
if (lstModels.Count.Equals(0))
{
msg = "模板不能为空";
return(null);
}
return(lstModels);
}
catch (Exception ex)
{
msg = "读取模板失败";
Log.WriteLog(ex.Message);
return(null);
}
}
