using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
|
namespace VueWebCoreApi.Tools
|
{
|
public class ImportExcel
|
{
|
public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
|
|
/// <summary>
|
/// Excel导入成Datable
|
/// </summary>
|
/// <param name="file">导入路径(包含文件名与扩展名)</param>
|
/// <returns></returns>
|
public static DataTable ExcelToTable(string file)
|
{
|
IWorkbook Workbook;
|
DataTable table = new DataTable();
|
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;
|
}
|
|
//定位在第一个sheet
|
ISheet sheet = Workbook.GetSheetAt(0);
|
//第一行为标题行
|
IRow headerRow = sheet.GetRow(0);
|
if (headerRow == null)
|
{
|
return table;
|
}
|
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;
|
}
|
}
|
return table;
|
}
|
|
/// <summary>
|
/// Excel 2个Sheet导入成Datable
|
/// </summary>
|
/// <param name="file">导入路径(包含文件名与扩展名)</param>
|
/// <returns></returns>
|
public static List<DataTable> ExcelToTableList(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 < 2)
|
{
|
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>
|
/// 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>
|
/// <returns></returns>
|
private static string GetCellValue(ICell cell)
|
{
|
if (cell == null)
|
{
|
return string.Empty;
|
}
|
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
return string.Empty;
|
case CellType.Boolean:
|
return cell.BooleanCellValue.ToString();
|
case CellType.Error:
|
return cell.ErrorCellValue.ToString();
|
case CellType.Numeric:
|
//Cell为非NUMERIC时，调用IsCellDateFormatted方法会报错，所以先要进行类型判断
|
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
|
return Convert.ToDateTime(cell.DateCellValue).ToString("yyyy-MM-dd");
|
else
|
{
|
return cell.NumericCellValue.ToString();
|
}
|
//if (format != 0) { return Convert.ToDateTime(cell.DateCellValue).ToString("yyyy-MM-dd"); } else { return cell.NumericCellValue.ToString(); }
|
case CellType.Unknown:
|
default:
|
return cell.ToString();
|
case CellType.String:
|
return cell.StringCellValue;
|
case CellType.Formula:
|
try
|
{
|
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
|
e.EvaluateInCell(cell);
|
return cell.ToString();
|
}
|
catch
|
{
|
return cell.NumericCellValue.ToString();
|
}
|
}
|
}
|
|
|
/// <summary>
|
/// 单个Excel 不为空验证、重复数据验证
|
/// </summary>
|
/// <param name="file">导入路径(包含文件名与扩展名)</param>
|
/// <returns></returns>
|
public static List<ExcelErro> ExcelToTableErro(string file)
|
{
|
List<ExcelErro> list = new List<ExcelErro>();
|
IWorkbook Workbook;
|
DataTable table = new DataTable();
|
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;
|
}
|
|
//定位在第一个sheet
|
ISheet sheet = Workbook.GetSheetAt(0);
|
//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 = sheet.PhysicalNumberOfRows;
|
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 = "{" + 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.Trim() + "" }).Rows.Count < table.Rows.Count)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = "/";
|
erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
|
erro.ErrorCont = "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据";
|
list.Add(erro);
|
}
|
}
|
}
|
}
|
List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
|
return list;
|
}
|
/// <summary>
|
/// Excel 2个Sheet 数据验证
|
/// </summary>
|
/// <param name="file">导入路径(包含文件名与扩展名)</param>
|
/// <returns></returns>
|
public static List<ExcelErro> ExcelToTableListErro(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 < 2; 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;
|
}
|
|
/// <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;
|
}
|
|
|
|
|
|
|
|
#region【数据量验证】
|
public static ToMessage ExcelCheckCountSum(string file)
|
{
|
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 < 2)
|
{
|
DataTable table = new DataTable();
|
//定位在第一个sheet
|
ISheet sheet = Workbook.GetSheetAt(0);
|
//第一行为标题行
|
IRow headerRow = sheet.GetRow(0);
|
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)
|
{
|
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] != null)
|
{
|
if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
|
{
|
isnull = false;
|
break;
|
}
|
}
|
}
|
if (isnull)
|
{
|
table.Rows[i].Delete();
|
}
|
}
|
if (table.Rows.Count <= 0)
|
{
|
|
mes.code = "300";
|
mes.message = "文件数据不能为空！";
|
|
}
|
}
|
else
|
{
|
for (int m = 0; m < 2; m++)
|
{
|
DataTable table = new DataTable();
|
//定位在第一个sheet
|
ISheet sheet = Workbook.GetSheetAt(m);
|
//第一行为标题行
|
IRow headerRow = sheet.GetRow(0);
|
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)
|
{
|
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] != null)
|
{
|
if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
|
{
|
isnull = false;
|
break;
|
}
|
}
|
}
|
if (isnull)
|
{
|
table.Rows[i].Delete();
|
}
|
}
|
if (table.Rows.Count <= 0)
|
{
|
mes.code = "300";
|
mes.message = "主子表文件中数据不能为空！";
|
}
|
}
|
}
|
return mes;
|
}
|
|
#endregion
|
|
|
#region【去除空行后获取行数】
|
public static int Rownum(IRow headerRow, int cellCount, ISheet sheet, int rowCount)
|
{
|
DataTable table = new DataTable();
|
//循环添加标题列
|
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;
|
}
|
}
|
return table.Rows.Count;
|
}
|
#endregion
|
|
|
#region
|
public static List<ExcelErro> InportExcelToTableListErro(List<DataTable> excelTable)
|
{
|
List<ExcelErro> list = new List<ExcelErro>();
|
for (int i = 0; i < excelTable.Count; i++)
|
{
|
var emptyColumn = excelTable[i].Columns.Cast<DataColumn>().FirstOrDefault(
|
column => column.ColumnName.Contains("*") && excelTable[i].AsEnumerable().Any(row => row.IsNull(column))
|
);
|
|
if (emptyColumn != null)
|
{
|
int columnIndex = excelTable[i].Columns.IndexOf(emptyColumn);
|
int rowIndex = excelTable[i].AsEnumerable().ToList().FindIndex(row => row.IsNull(emptyColumn));
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = (rowIndex + 1).ToString();
|
erro.ErrorField = emptyColumn.ColumnName;
|
erro.ErrorCont = "模板表头带*的列中存在空值,第" + (rowIndex + 1).ToString() + "行,必填字段："+ emptyColumn.ColumnName +"为空";
|
list.Add(erro);
|
}
|
var duplicateColumn = excelTable[i].Columns.Cast<DataColumn>().FirstOrDefault(
|
column => column.ColumnName.Contains("唯一") && excelTable[i].AsEnumerable().GroupBy(row => row[column]).Any(group => group.Count() > 1));
|
if (duplicateColumn != null)
|
{
|
int columnIndex = excelTable[i].Columns.IndexOf(duplicateColumn);
|
var duplicateRows = excelTable[i].AsEnumerable()
|
.Where(row => row[duplicateColumn] != DBNull.Value)
|
.GroupBy(row => row[duplicateColumn])
|
.Where(group => group.Count() > 1)
|
.SelectMany(group => group.ToList())
|
.ToList();
|
foreach (var row in duplicateRows)
|
{
|
ExcelErro erro = new ExcelErro();
|
erro.RoeNumber = excelTable[i].Rows.IndexOf(row).ToString();
|
erro.ErrorField = duplicateColumn.ColumnName;
|
erro.ErrorCont = "模板表头带(唯一)的列中存在重复值,第" + excelTable[i].Rows.IndexOf(row).ToString() + "行,必填字段：" + duplicateColumn.ColumnName + "重复";
|
list.Add(erro);
|
|
}
|
}
|
}
|
return list;
|
}
|
#endregion
|
}
|
}
|
