/// <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); }