/// <summary>
/// 验证与提示
/// </summary>
/// <param name="validItem"></param>
/// <param name="colIndex"></param>
/// <param name="sheet"></param>
private void ValidAndMessage(ColumnValidItem validItem, int colIndex, ISheet sheet)
{
DVConstraint constraint = null;
HSSFDataValidation dataValidate = null;
switch (validItem.ValidType)//验证类型
{
case EnumValidType.Integer:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.Decimal:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax);
break;
case EnumValidType.DateTime:
constraint = DVConstraint.CreateDateConstraint((int)validItem.ValidQualifier, validItem.ValidMin, validItem.ValidMax, "yyyy-MM-dd");
break;
case EnumValidType.TextLength:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)validItem.ValidQualifier, validItem.ValidMin ?? "1", validItem.ValidMax ?? "200");
break;
case EnumValidType.Customize:
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format(validItem.ValidFormula, Chr(colIndex)));
break;
}
if (constraint != null)
{
CellRangeAddressList regions = null;
if (validItem.ValidType == EnumValidType.Customize && validItem.ValidFormula == "COUNTIF({0}:{0},\"*@*.*\")=1") //邮件格式验证
{
int rowCount = 2000; //只支持前2000行邮件格式验证
for (int i = 0; i < rowCount; i++)
{
constraint = DVConstraint.CreateCustomFormulaConstraint(string.Format("COUNTIF({0}{1},\"*@*.*\")=1", Chr(colIndex), i + 2));
regions = new CellRangeAddressList(i + 1, i + 1, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
else
{
regions = new CellRangeAddressList(1, 65535, colIndex, colIndex);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.CreatePromptBox(validItem.Name, validItem.InputMessage);
dataValidate.CreateErrorBox(validItem.Name, validItem.ErrorMessage);
((HSSFSheet)sheet).AddValidationData(dataValidate);
}
}
}
/// <summary>
/// 创建下拉列表
/// </summary>
/// <param name="validItem"></param>
/// <param name="colIndex"></param>
/// <param name="sheet"></param>
/// <returns></returns>
private int CreateColumnList(ColumnValidItem validItem, int colIndex, ISheet sheet)
{
int length = 0;
if (!string.IsNullOrEmpty(validItem.DropDownValue))
{
var splitArray = validItem.DropDownValue.Split(new string[] { "$_$" }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < splitArray.Length; i++)
{
GetRow(sheet, i).CreateCell(colIndex).SetCellValue(splitArray[i]);
}
length = splitArray.Length;
}
if (length == 0)
{
throw new ApplicationException(validItem.Name + "不能为空");
}
return(length);
}
/// <summary>
/// 创建标题行
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowTitle"></param>
private void CreateTitleRow(ISheet sheet, List <ColumnValidItem> rowTitle)
{
var ShtDictionary = hssfworkbook.GetSheet("ShtDictionary") ?? hssfworkbook.CreateSheet("ShtDictionary");
IRow hsTitleRow = sheet.CreateRow(0);
hsTitleRow.HeightInPoints = 20;
//单元格格式是文本与居中
var formatCellStyle1 = hssfworkbook.CreateCellStyle();
formatCellStyle1.Alignment = HorizontalAlignment.Center;
formatCellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
//单元格格式是常规与居中
var formatCellStyle2 = hssfworkbook.CreateCellStyle();
formatCellStyle2.Alignment = HorizontalAlignment.Center;
//日期
var formatCellStyle3 = hssfworkbook.CreateCellStyle();
formatCellStyle3.Alignment = HorizontalAlignment.Center;
var format = hssfworkbook.CreateDataFormat();
formatCellStyle3.DataFormat = format.GetFormat("yyyy-m-d");
var cellStyle1 = hssfworkbook.CreateCellStyle();
cellStyle1.Alignment = HorizontalAlignment.Center;
var font1 = hssfworkbook.CreateFont();
font1.Color = HSSFColor.Red.Index;
font1.FontHeightInPoints = 12;
font1.FontName = "宋体";
cellStyle1.SetFont(font1);
var cellStyle2 = hssfworkbook.CreateCellStyle();
cellStyle2.Alignment = HorizontalAlignment.Center;
var font2 = hssfworkbook.CreateFont();
font2.FontHeightInPoints = 12;
font2.FontName = "宋体";
cellStyle2.SetFont(font2);
ICell cell = null;
ColumnValidItem validItem = null;
for (int i = 0; i < rowTitle.Count; i++)
{
validItem = rowTitle[i];
if (validItem != null)
{
cell = hsTitleRow.CreateCell(i);
cell.SetCellValue(validItem.Name);
//列宽度
sheet.SetColumnWidth(i, validItem.Width == 0 ? 80 * 50 : validItem.Width * 50);
#region 项类型
switch (validItem.ItemType)//是否以红色标记
{
case EnumItemType.MustHave:
case EnumItemType.MustFill:
cell.CellStyle = cellStyle1;
break;
case EnumItemType.SelectFill:
cell.CellStyle = cellStyle2;
break;
}
#endregion
#region 值类型
switch (validItem.ValueType)//值类型
{
case EnumValueType.String:
sheet.SetDefaultColumnStyle(i, formatCellStyle1);
break;
case EnumValueType.Number:
sheet.SetDefaultColumnStyle(i, formatCellStyle2);
break;
case EnumValueType.DateTime:
sheet.SetDefaultColumnStyle(i, formatCellStyle3);
break;
case EnumValueType.List:
int count = this.CreateColumnList(validItem, i, ShtDictionary);
var regions = new CellRangeAddressList(1, 65535, i, i);
var constraint = DVConstraint.CreateFormulaListConstraint(string.Format("ShtDictionary!${0}${1}:${0}${2}", Chr(i), 1, count));
var dataValidate = new HSSFDataValidation(regions, constraint);
((HSSFSheet)sheet).AddValidationData(dataValidate);
break;
}
#endregion
this.ValidAndMessage(validItem, i, sheet);//验证类型
}
}
}
/// <summary>
/// 获取导入的EXCEL数据
/// </summary>
/// <param name="path">路径</param>
/// <param name="itemList"></param>
/// <returns></returns>
public DataTable GetImportData(string path, List <ColumnValidItem> itemList)
{
this.InitializeWorkbook(path);
var sheet = hssfworkbook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
var table = new DataTable();
DataRow tableRow = null;
ColumnValidItem validItem = null;
while (rows.MoveNext())
{
var row = (HSSFRow)rows.Current;
if (row.RowNum != 0)//数据行
{
tableRow = table.NewRow();
table.Rows.Add(tableRow);
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
continue;
}
var cellValue = cell.ExtToString().Trim();
if (row.RowNum == 0)//标题（列表）
{
if (!table.Columns.Contains(cellValue) && !string.IsNullOrEmpty(cellValue))
{
table.Columns.Add(cellValue);
}
}
else if (cell.ColumnIndex < table.Columns.Count)//数据行
{
validItem = itemList.FirstOrDefault(v => v.Name == table.Columns[cell.ColumnIndex].ColumnName);
if (validItem != null)
{
try
{
switch (validItem.ValueType)
{
case EnumValueType.String:
tableRow[cell.ColumnIndex] = cell.StringCellValue.Trim();
break;
case EnumValueType.DateTime:
DateTime currDate = DateTime.MinValue;
if (cell.ToString().Contains("-") || cell.ToString().Contains("/"))
{
currDate = cell.DateCellValue;
}
tableRow[cell.ColumnIndex] = currDate.ToShortDateString();
break;
default:
tableRow[cell.ColumnIndex] = cellValue;
break;
}
}
catch
{
tableRow[cell.ColumnIndex] = cellValue;
}
}
}
}
}
return(table);
}
