JTAPI.git - Gitblit

			@@ -272,6 +272,171 @@
			}

			/// <summary>
			/// Excel 3个Sheet导入成Datable
			/// </summary>
			/// <param name="file">导入路径(包含文件名与扩展名)</param>
			/// <returns></returns>
			public static List<DataTable> ExcelToThreeTableList(string file)
			{
			List<DataTable> list = new List<DataTable>();
			IWorkbook Workbook;
			try
			{
			using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
			{
			//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
			string fileExt = Path.GetExtension(file).ToLower();
			if (fileExt == ".xls")
			{
			Workbook = new HSSFWorkbook(fileStream);
			}
			else if (fileExt == ".xlsx")
			{
			Workbook = new XSSFWorkbook(fileStream);
			}
			else
			{
			Workbook = null;
			}
			}
			}
			catch (Exception ex)
			{
			throw ex;
			}
			int count = Workbook.NumberOfSheets; //获取所有SheetName
			if (count < 3)
			{
			DataTable table = new DataTable();
			//定位在第一个sheet
			ISheet sheet = Workbook.GetSheetAt(0);
			//第一行为标题行
			IRow headerRow = sheet.GetRow(0);
			if (headerRow == null)
			{
			return list;
			}
			int cellCount = headerRow.LastCellNum;
			int rowCount = sheet.LastRowNum;

			//循环添加标题列
			for (int i = headerRow.FirstCellNum; i < cellCount; i++)
			{
			DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
			table.Columns.Add(column);
			}

			//数据
			for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
			{
			IRow row = sheet.GetRow(i);
			DataRow dataRow = table.NewRow();
			if (row != null && row.Cells.Count > 0)
			{
			for (int j = row.FirstCellNum; j < cellCount; j++)
			{
			if (row.GetCell(j) != null)
			{
			dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
			}
			}
			}
			table.Rows.Add(dataRow);
			}
			//清除最后的空行
			for (int i = 0; i < table.Rows.Count; i++)
			{
			bool isnull = true;
			for (int j = 0; j < table.Columns.Count; j++)
			{
			if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
			{
			if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
			{
			isnull = false;
			break;
			}
			}
			}
			if (isnull)
			{
			table.Rows[i].Delete();
			i = 0;
			}
			}
			list.Add(table);
			}
			else
			{
			for (int m = 0; m < count; m++)
			{
			DataTable table = new DataTable();
			//定位在第一个sheet
			ISheet sheet = Workbook.GetSheetAt(m);
			//第一行为标题行
			IRow headerRow = sheet.GetRow(0);
			if (headerRow == null)
			{
			return list;
			}
			int cellCount = headerRow.LastCellNum;
			int rowCount = sheet.LastRowNum;

			//循环添加标题列
			for (int i = headerRow.FirstCellNum; i < cellCount; i++)
			{
			DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
			table.Columns.Add(column);
			}

			//数据
			for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
			{
			IRow row = sheet.GetRow(i);
			DataRow dataRow = table.NewRow();
			if (row != null && row.Cells.Count > 0)
			{
			for (int j = row.FirstCellNum; j < cellCount; j++)
			{
			if (row.GetCell(j) != null)
			{
			dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
			}
			}
			}
			table.Rows.Add(dataRow);
			}
			//清除最后的空行
			for (int i = 0; i < table.Rows.Count; i++)
			{
			bool isnull = true;
			for (int j = 0; j < table.Columns.Count; j++)
			{
			if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
			{
			if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
			{
			isnull = false;
			break;
			}
			}
			}
			if (isnull)
			{
			table.Rows[i].Delete();
			i = 0;
			}
			}
			list.Add(table);
			}
			}
			return list;
			}




			/// <summary>
			/// 获取单元格类型
			/// </summary>
			/// <param name="cell"></param>
			@@ -700,6 +865,196 @@
			return list;
			}

			/// <summary>
			/// Excel 3个Sheet 数据验证
			/// </summary>
			/// <param name="file">导入路径(包含文件名与扩展名)</param>
			/// <returns></returns>
			public static List<ExcelErro> ExcelToThreeTableListErro(string file)
			{
			List<ExcelErro> list = new List<ExcelErro>();
			IWorkbook Workbook;
			bool iscell = false;
			int iscellCount = 0;
			try
			{
			using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
			{
			//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
			string fileExt = Path.GetExtension(file).ToLower();
			if (fileExt == ".xls")
			{
			Workbook = new HSSFWorkbook(fileStream);
			}
			else if (fileExt == ".xlsx")
			{
			Workbook = new XSSFWorkbook(fileStream);
			}
			else
			{
			Workbook = null;
			}
			}
			}
			catch (Exception ex)
			{
			throw ex;
			}
			for (int mm = 0; mm < 3; mm++)
			{
			DataTable table = new DataTable();
			//定位在第一个sheet
			ISheet sheet = Workbook.GetSheetAt(mm);
			//sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);
			//第一行为标题行
			IRow headerRow = sheet.GetRow(0);
			int cellCount = headerRow.LastCellNum;
			int rowCount0 = sheet.FirstRowNum;
			//int rowCount = sheet.LastRowNum;
			int rowCount = Rownum(headerRow, cellCount, sheet, sheet.LastRowNum); //获取删除空行之后的数据行 (只针对于最后数据项有空行的)
			if (rowCount - rowCount0 >= 1)
			{
			//-------------判断必填项是否为空----------------//
			//循环数据行
			for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
			{
			iscell = false;
			iscellCount = 0;
			IRow row = sheet.GetRow(i);
			//判断是否有空行
			if (row == null)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = (i + 1).ToString();
			erro.ErrorField = "空行";
			erro.ErrorCont = "空行";
			list.Add(erro);
			continue;
			}
			//判断是否存在空数据行
			for (int c = 0; c < cellCount; c++)
			{
			if (GetCellValue(row.GetCell(c)).Trim() != null && GetCellValue(row.GetCell(c)).Trim() != "")
			{
			iscell = false;
			}
			else
			{
			iscell = true;
			iscellCount++;
			}
			}
			if (iscell == true && iscellCount == cellCount) //空行
			{
			continue;
			}
			else
			{
			//循环标题列
			for (int j = headerRow.FirstCellNum; j < cellCount; j++)
			{
			//获取单元格的填充色
			string rgb = headerRow.Cells[j].CellStyle.FillForegroundColor.ToString();
			if (rgb == "51") //必填项字段列头填充色
			{
			if (sheet.GetRow(i) == null \|\| sheet.GetRow(i).ToString() == "") //存在空行
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = i.ToString();
			erro.ErrorField = "{第" + i.ToString() + "行}";
			erro.ErrorCont = "{第" + i.ToString() + "}行为空";
			list.Add(erro);
			break;
			}
			else
			{
			string cellValue = GetCellValue(sheet.GetRow(i).GetCell(j)).Trim(); //GetCell:返回包括空列值 Cells:只返回有值的列
			if (cellValue == "" \|\| cellValue == null)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = (sheet.GetRow(i).RowNum + 1).ToString();
			erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
			erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空";
			list.Add(erro);
			}
			}
			}

			}
			}
			}
			//-------------判断唯一项是否重复----------------//
			//循环添加标题列
			for (int i = headerRow.FirstCellNum; i < cellCount; i++)
			{
			DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
			table.Columns.Add(column);
			}

			//数据
			for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
			{
			IRow row = sheet.GetRow(i);
			DataRow dataRow = table.NewRow();
			if (row != null)
			{
			for (int j = row.FirstCellNum; j < cellCount; j++)
			{
			if (row.GetCell(j) != null)
			{
			dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
			}
			}
			}
			table.Rows.Add(dataRow);
			}
			//清除最后的空行
			for (int i = 0; i < table.Rows.Count; i++)
			{
			bool isnull = true;
			for (int j = 0; j < table.Columns.Count; j++)
			{
			if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
			{
			if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
			{
			isnull = false;
			break;
			}
			}
			}
			if (isnull)
			{
			table.Rows[i].Delete();
			i = 0;
			}
			}
			DataView view = new DataView(table);
			//获取所有唯一列
			List<string> list0 = new List<string>();
			//循环标题列
			for (int j = headerRow.FirstCellNum; j < cellCount; j++)
			{
			bool isContain = headerRow.Cells[j].StringCellValue.Contains("唯一"); //标题列中的必填列
			if (isContain == true)
			{
			//
			if (view.ToTable(true, new string[] { "" + headerRow.Cells[j].StringCellValue + "" }).Rows.Count < table.Rows.Count)
			{
			ExcelErro erro = new ExcelErro();
			erro.RoeNumber = "/";
			erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
			erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据";
			list.Add(erro);
			}
			}
			}
			}
			}
			List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
			return list;
			}