/// <summary> /// 复制Excel属性 /// </summary> /// <param name="excelPropety">单元格属性</param> /// <returns>复制后的单元格</returns> private ExcelPropety CopyExcelPropety(ExcelPropety excelPropety) { ExcelPropety ep = new ExcelPropety { BackgroudColor = excelPropety.BackgroudColor, ColumnName = excelPropety.ColumnName, DataType = excelPropety.DataType, ResourceType = excelPropety.ResourceType, IsNullAble = excelPropety.IsNullAble, ListItems = excelPropety.ListItems, MaxValuseOrLength = excelPropety.MaxValuseOrLength, MinValueOrLength = excelPropety.MinValueOrLength, Value = excelPropety.Value, SubTableType = excelPropety.SubTableType, CharCount = excelPropety.CharCount, ReadOnly = excelPropety.ReadOnly, FormatData = excelPropety.FormatData, FormatSingleData = excelPropety.FormatSingleData, FieldName = excelPropety.FieldName }; List <ExcelPropety> li = new List <ExcelPropety>(); foreach (var item in excelPropety.DynamicColumns) { li.Add(CopyExcelPropety(item)); } ep.DynamicColumns = li; return(ep); }
protected void SetEntityFieldValue(object entity, ExcelPropety ep, int rowIndex, string fieldName, T templateVM) { if (ep.FormatData != null) { ProcessResult processResult = ep.FormatData(ep.Value, templateVM); if (processResult != null) { //未添加任何处理结果 if (processResult.EntityValues.Count == 0) { PropertyHelper.SetPropertyValue(entity, fieldName, ep.Value, stringBasedValue: true); } //字段为一对一 if (processResult.EntityValues.Count == 1) { ep.Value = processResult.EntityValues[0].FieldValue; if (!string.IsNullOrEmpty(processResult.EntityValues[0].ErrorMsg)) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = processResult.EntityValues[0].ErrorMsg, ExcelIndex = rowIndex }); } PropertyHelper.SetPropertyValue(entity, fieldName, ep.Value, stringBasedValue: true); } //字段为一对多 if (processResult.EntityValues.Count > 1) { foreach (var entityValue in processResult.EntityValues) { if (!string.IsNullOrEmpty(entityValue.ErrorMsg)) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = entityValue.ErrorMsg, ExcelIndex = rowIndex }); } PropertyHelper.SetPropertyValue(entity, entityValue.FieldName, entityValue.FieldValue, stringBasedValue: true); } } } } else if (ep.FormatSingleData != null) { ep.FormatSingleData(ep.Value, templateVM, out string singleEntityValue, out string errorMsg); if (!string.IsNullOrEmpty(errorMsg)) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = errorMsg, ExcelIndex = rowIndex }); } PropertyHelper.SetPropertyValue(entity, fieldName, singleEntityValue, stringBasedValue: true); } else { PropertyHelper.SetPropertyValue(entity, fieldName, ep.Value, stringBasedValue: true); } }
private void CreateDataTable() { TemplateDataTable = new DataTable(); var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); foreach (var p in propetys) { ExcelPropety excelPropety = (ExcelPropety)p.GetValue(this); ColumnDataType dateType = excelPropety.DataType; switch (dateType) { case ColumnDataType.Bool: TemplateDataTable.Columns.Add(p.Name, typeof(bool)); break; case ColumnDataType.Date: TemplateDataTable.Columns.Add(p.Name, typeof(string)); break; case ColumnDataType.Number: TemplateDataTable.Columns.Add(p.Name, typeof(int)); break; case ColumnDataType.Text: TemplateDataTable.Columns.Add(p.Name, typeof(string)); break; case ColumnDataType.Float: TemplateDataTable.Columns.Add(p.Name, typeof(decimal)); break; default: TemplateDataTable.Columns.Add(p.Name, typeof(string)); break; } } }
/// <summary> /// 读取模版中的数据 /// </summary> private void DoMapList() { try { Template.InitExcelData(); Template.InitCustomFormat(); TemplateData = new List <T>(); hssfworkbook = new HSSFWorkbook(); if (UploadFileId == null) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = Program._localizer["PleaseUploadTemplate"] }); return; } var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault(); hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo); if (ValidityTemplateType && hssfworkbook.GetSheetAt(1).GetRow(0).Cells[2].ToString() != typeof(T).Name) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = Program._localizer["WrongTemplate"] }); return; } ISheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); //所有ExcelPropety属性 List <ExcelPropety> excelPropetys = new List <ExcelPropety>(); for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template); excelPropetys.Add(ep); } #region 验证模版正确性 add by dufei //取得列数 int columnCount = excelPropetys.Count; //int excelPropetyCount = excelPropetys.Count; var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault(); if (dynamicColumn != null) { columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1; //excelPropetyCount = excelPropetyCount + dynamicColumn.DynamicColumns.Count - 1; } int pIndex = 0; var cells = sheet.GetRow(0).Cells; if (columnCount != cells.Count) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = Program._localizer["WrongTemplate"] }); return; } else { bool hassubtable = false; for (int i = 0; i < cells.Count; i++) { if (excelPropetys[pIndex].SubTableType != null) { hassubtable = true; } if (excelPropetys[pIndex].DataType != ColumnDataType.Dynamic) { if (cells[i].ToString().Trim('*') != excelPropetys[pIndex].ColumnName) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = Program._localizer["WrongTemplate"] }); return; } pIndex++; } else { var listDynamicColumns = excelPropetys[i].DynamicColumns; int dcCount = listDynamicColumns.Count; for (int dclIndex = 0; dclIndex < dcCount; dclIndex++) { if (cells[i].ToString().Trim('*') != listDynamicColumns[dclIndex].ColumnName) { ErrorListVM.EntityList.Add(new ErrorMessage { Message = Program._localizer["WrongTemplate"] }); break; } i = i + 1; } i = i - 1; pIndex++; } } //如果有子表,则设置主表字段非必填 if (hassubtable == true) { for (int i = 0; i < cells.Count; i++) { if (excelPropetys[i].SubTableType == null) { excelPropetys[i].IsNullAble = true; } } } } #endregion int rowIndex = 2; rows.MoveNext(); while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; if (IsEmptyRow(row, columnCount)) { return; } T result = new T(); int propetyIndex = 0; for (int i = 0; i < columnCount; i++) { ExcelPropety excelPropety = CopyExcelPropety(excelPropetys[propetyIndex]); //excelPropetys[propetyIndex]; var pts = propetys[propetyIndex]; string value = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(); if (excelPropety.DataType == ColumnDataType.Dynamic) { int dynamicColCount = excelPropety.DynamicColumns.Count(); for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++) { //验证数据类型并添加错误信息 excelPropety.DynamicColumns[dynamicColIndex].ValueValidity(row.GetCell(i + dynamicColIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString(), ErrorListVM.EntityList, rowIndex); } i = i + dynamicColCount - 1; } else { excelPropety.ValueValidity(value, ErrorListVM.EntityList, rowIndex); } if (ErrorListVM.EntityList.Count == 0) { pts.SetValue(result, excelPropety); } propetyIndex++; } result.ExcelIndex = rowIndex; TemplateData.Add(result); rowIndex++; } return; } catch { ErrorListVM.EntityList.Add(new ErrorMessage { Message = Program._localizer["WrongTemplate"] }); //ErrorListVM.ErrorList.Add(new ErrorMessage { Message = ex.Message }); } return; }
/// <summary> /// 获取上传的结果值 /// </summary> public virtual void SetEntityList() { EntityList = new List <P>(); //获取Model类的所有属性 var pros = typeof(P).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance); var excelPros = typeof(T).GetFields(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance).Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); if (TemplateData == null) { DoMapList(); } //循环Excel中的数据 foreach (var item in TemplateData) { int rowIndex = 2; bool isMainData = false; //主表信息 Dictionary <string, ExcelPropety> mainInfo = new Dictionary <string, ExcelPropety>(); string mainValString = string.Empty; //子表信息 Dictionary <Type, List <FieldInfo> > subpros = new Dictionary <Type, List <FieldInfo> >(); Dictionary <Type, string> subValString = new Dictionary <Type, string>(); //循环TemplateVM中定义的所有的列,区分出主子表 foreach (var epro in excelPros) { //获取本列的ExcelProperty的值 if (typeof(T).GetField(epro.Name).GetValue(item) is ExcelPropety ep) { //如果是子表的字段 if (ep.SubTableType != null) { //保存子表字段信息稍后处理 if (!subpros.ContainsKey(ep.SubTableType)) { subpros[ep.SubTableType] = new List <FieldInfo>(); } subpros[ep.SubTableType].Add(epro); } else { //PropertyHelper.SetPropertyValue(entity, epro.Name, ep.Value, stringBasedValue: true); //保存子表字段信息稍后处理 mainInfo.Add(ep.FieldName, ep); mainValString += ep.Value; } } } //子表信息是否为空 foreach (var sub in subpros) { string subVal = string.Empty; foreach (var field in sub.Value) { ExcelPropety ep = typeof(T).GetField(field.Name).GetValue(item) as ExcelPropety; subVal += ep.Value; } subValString.Add(sub.Key, subVal); } P entity = null; //说明主表信息为空 if (string.IsNullOrEmpty(mainValString)) { entity = EntityList.LastOrDefault(); } else { //初始化一个新的Entity entity = new P(); isMainData = true; //给主表赋值 foreach (var mep in mainInfo) { SetEntityFieldValue(entity, mep.Value, rowIndex, mep.Key, item); } } //给子表赋值 foreach (var sub in subpros) { //循环Entity的所有属性,找到List<SubTableType>类型的字段 foreach (var pro in pros) { if (pro.PropertyType.IsGenericType) { var gtype = pro.PropertyType.GetGenericArguments()[0]; if (gtype == sub.Key) { //子表 var subList = entity.GetType().GetProperty(pro.Name).GetValue(entity); string fk = DC.GetFKName <P>(pro.Name); //如果子表不为空 if (!string.IsNullOrEmpty(subValString.Where(x => x.Key == sub.Key).FirstOrDefault().Value)) { IList list = null; if (subList == null) { //初始化List<SubTableType> list = typeof(List <>).MakeGenericType(gtype).GetConstructor(Type.EmptyTypes).Invoke(null) as IList; } else { list = subList as IList; } //初始化一个SubTableType var obj = gtype.GetConstructor(System.Type.EmptyTypes).Invoke(null); //给SubTableType中和本ExcelProperty同名的字段赋值 foreach (var field in sub.Value) { ExcelPropety ep = typeof(T).GetField(field.Name).GetValue(item) as ExcelPropety; //PropertyHelper.SetPropertyValue(obj, field.Name, ep.Value, stringBasedValue: true); SetEntityFieldValue(obj, ep, rowIndex, ep.FieldName, item); } if (string.IsNullOrEmpty(fk) == false) { PropertyHelper.SetPropertyValue(obj, fk, entity.GetID()); } //将付好值得SubTableType实例添加到List中 list.Add(obj); PropertyHelper.SetPropertyValue(entity, pro.Name, list); } break; } } } } entity.ExcelIndex = item.ExcelIndex; var cinfo = this.SetDuplicatedCheck(); //if (IsUpdateRecordDuplicated(cinfo, entity) == false) //{ if (isMainData) { EntityList.Add(entity); } //} } isEntityListSet = true; }
public ErrorObj GetErrorJson() { var mse = new ErrorObj(); mse.Form = new Dictionary <string, string>(); var err = ErrorListVM?.EntityList?.Where(x => x.Index == 0).FirstOrDefault()?.Message; if (string.IsNullOrEmpty(err)) { var fa = DC.Set <FileAttachment>().Where(x => x.ID == UploadFileId).SingleOrDefault(); hssfworkbook = FileHelper.GetHSSWorkbook(hssfworkbook, (FileAttachment)fa, ConfigInfo); var propetys = Template.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); List <ExcelPropety> excelPropetys = new List <ExcelPropety>(); for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { ExcelPropety ep = (ExcelPropety)propetys[porpetyIndex].GetValue(Template); excelPropetys.Add(ep); } int columnCount = excelPropetys.Count; //int excelPropetyCount = excelPropetys.Count; var dynamicColumn = excelPropetys.Where(x => x.DataType == ColumnDataType.Dynamic).FirstOrDefault(); if (dynamicColumn != null) { columnCount = columnCount + dynamicColumn.DynamicColumns.Count - 1; } ISheet sheet = hssfworkbook.GetSheetAt(0); var errorStyle = hssfworkbook.CreateCellStyle(); IFont f = hssfworkbook.CreateFont(); f.Color = HSSFColor.Red.Index; errorStyle.SetFont(f); errorStyle.IsLocked = true; foreach (var e in ErrorListVM?.EntityList) { if (e.Index > 0) { var c = sheet.GetRow((int)(e.Index - 1)).CreateCell(columnCount); c.CellStyle = errorStyle; c.SetCellValue(e.Message); } } MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); ms.Position = 0; FileAttachmentVM vm = new FileAttachmentVM(); vm.CopyContext(this); vm.Entity.FileName = "Error-" + fa.FileName; vm.Entity.Length = ms.Length; vm.Entity.UploadTime = DateTime.Now; vm.Entity.SaveFileMode = ConfigInfo.FileUploadOptions.SaveFileMode; vm = FileHelper.GetFileByteForUpload(vm, ms, ConfigInfo, vm.Entity.FileName, null, null); vm.Entity.IsTemprory = true; if ((!string.IsNullOrEmpty(vm.Entity.Path) && (vm.Entity.SaveFileMode == SaveFileModeEnum.Local || vm.Entity.SaveFileMode == SaveFileModeEnum.DFS)) || (vm.Entity.FileData != null && vm.Entity.SaveFileMode == SaveFileModeEnum.Database)) { vm.DoAdd(); } ms.Close(); ms.Dispose(); err = "导入时发生错误"; mse.Form.Add("Entity.Import", err); mse.Form.Add("Entity.ErrorFileId", vm.Entity.ID.ToString()); } else { mse.Form.Add("Entity.Import", err); } return(mse); }
public static ExcelPropety CreateProperty <T>(Expression <Func <T, object> > field, bool isDateTime = false) { ExcelPropety cp = new ExcelPropety(); cp.ColumnName = field.GetPropertyDisplayName(); var fname = field.GetPropertyName(); Type t = field.GetPropertyInfo().PropertyType; if (fname.Contains('.')) { int index = fname.LastIndexOf('.'); cp.FieldName = fname.Substring(index + 1); cp.SubTableType = field.GetPropertyInfo().DeclaringType; } else { cp.FieldName = fname; } if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable <>)) { var req = field.GetPropertyInfo().GetCustomAttributes(typeof(RequiredAttribute), false).Cast <RequiredAttribute>().FirstOrDefault(); if (req == null) { cp.IsNullAble = true; } t = t.GenericTypeArguments[0]; } if (t == typeof(int) || t == typeof(long) || t == typeof(short)) { var sl = t.GetCustomAttributes(typeof(RangeAttribute), false).Cast <RangeAttribute>().FirstOrDefault(); cp.DataType = ColumnDataType.Number; if (sl != null) { if (sl.Maximum != null) { cp.MaxValuseOrLength = sl.Maximum.ToString(); } if (sl.Minimum != null) { cp.MinValueOrLength = sl.Minimum.ToString(); } } } else if (t == typeof(float) || t == typeof(double) || t == typeof(decimal)) { cp.DataType = ColumnDataType.Float; } else if (t == typeof(bool)) { cp.DataType = ColumnDataType.Bool; } else if (t.IsEnum) { cp.DataType = ColumnDataType.Enum; cp.EnumType = t; } else if (t == typeof(DateTime)) { cp.DataType = ColumnDataType.Date; if (isDateTime) { cp.DataType = ColumnDataType.DateTime; } } else { var sl = field.GetPropertyInfo().GetCustomAttributes(typeof(StringLengthAttribute), false).Cast <StringLengthAttribute>().FirstOrDefault(); var req = field.GetPropertyInfo().GetCustomAttributes(typeof(RequiredAttribute), false).Cast <RequiredAttribute>().FirstOrDefault(); cp.DataType = ColumnDataType.Text; if (req == null) { cp.IsNullAble = true; } if (sl != null) { if (sl.MaximumLength != 0) { cp.MaxValuseOrLength = sl.MaximumLength + ""; } if (sl.MinimumLength != 0) { cp.MinValueOrLength = sl.MinimumLength + ""; } } } cp.CharCount = 20; return(cp); }
/// <summary> /// 生成模板 /// </summary> /// <param name="displayName">文件名</param> /// <returns>生成的模版文件</returns> public byte[] GenerateTemplate(out string displayName) { HSSFWorkbook workbook = new HSSFWorkbook(); InitExcelData(); CreateDataTable(); //add by dufei SetTemplateDataValus(); //add by dufei if (!string.IsNullOrEmpty(FileDisplayName)) { displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls"; } else { displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls"; } //模板sheet页 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName); HSSFRow row = (HSSFRow)sheet.CreateRow(0); row.HeightInPoints = 20; HSSFSheet enumSheet = (HSSFSheet)workbook.CreateSheet(); HSSFRow enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0); enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]); enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]); enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号 HSSFSheet dataSheet = (HSSFSheet)workbook.CreateSheet(); #region 设置excel模板列头 //默认灰色 var headerStyle = GetCellStyle(workbook); headerStyle.IsLocked = true; //黄色 var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow); yellowStyle.IsLocked = true; //红色 var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red); redStyle.IsLocked = true; //取得所有ExcelPropety var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); int _currentColunmIndex = 0; bool IsProtect = false; for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this); ColumnDataType dateType = excelPropety.DataType; if (excelPropety.ReadOnly) { IsProtect = true; } //给必填项加星号 string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*"; row.CreateCell(_currentColunmIndex).SetCellValue(colName); //修改列头样式 switch (excelPropety.BackgroudColor) { case BackgroudColorEnum.Yellow: row.Cells[_currentColunmIndex].CellStyle = yellowStyle; break; case BackgroudColorEnum.Red: row.Cells[_currentColunmIndex].CellStyle = redStyle; break; default: row.Cells[_currentColunmIndex].CellStyle = headerStyle; break; } var dataStyle = workbook.CreateCellStyle(); var dataFormat = workbook.CreateDataFormat(); if (dateType == ColumnDataType.Dynamic) { int dynamicColCount = excelPropety.DynamicColumns.Count(); for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++) { var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex]; string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*"; row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName); row.Cells[_currentColunmIndex].CellStyle = headerStyle; if (dynamicCol.ReadOnly) { IsProtect = true; } //设定列宽 if (excelPropety.CharCount > 0) { sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256); dataStyle.WrapText = true; } else { sheet.AutoSizeColumn(_currentColunmIndex); } //设置单元格样式及数据类型 dataStyle.IsLocked = excelPropety.ReadOnly; dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat); _currentColunmIndex++; } } else { //设定列宽 if (excelPropety.CharCount > 0) { sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256); dataStyle.WrapText = true; } else { sheet.AutoSizeColumn(_currentColunmIndex); } //设置是否锁定 dataStyle.IsLocked = excelPropety.ReadOnly; //设置单元格样式及数据类型 excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat); _currentColunmIndex++; } } #endregion #region 添加模版数据 add by dufei if (TemplateDataTable.Rows.Count > 0) { for (int i = 0; i < TemplateDataTable.Rows.Count; i++) { DataRow tableRow = TemplateDataTable.Rows[i]; HSSFRow dataRow = (HSSFRow)sheet.CreateRow(1 + i); for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { string colName = propetys[porpetyIndex].Name; tableRow[colName].ToString(); dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString()); } } } #endregion //冻结行 sheet.CreateFreezePane(0, 1, 0, 1); //锁定excel if (IsProtect) { sheet.ProtectSheet("password"); } workbook.SetSheetHidden(1, true); workbook.SetSheetHidden(2, true); MemoryStream ms = new MemoryStream(); workbook.Write(ms); return(ms.ToArray()); }
/// <summary> /// 生成模板 /// </summary> /// <param name="displayName">文件名</param> /// <returns>生成的模版文件</returns> public byte[] GenerateTemplate(out string displayName) { //设置导出的文件名称 string SheetName = !string.IsNullOrEmpty(FileDisplayName) ? FileDisplayName : this.GetType().Name; displayName = SheetName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xlsx"; //1.声明Excel文档 IWorkbook workbook = new XSSFWorkbook(); //加载初始化数据和下拉菜单数据,可重载 InitExcelData(); //设置TemplateDataTable的各列的类型 CreateDataTable(); //设置初始化数据到DataTable中 SetTemplateDataValus(); //2.设置workbook的sheet页 ISheet sheet = workbook.CreateSheet(); workbook.SetSheetName(0, SheetName); //3.设置Sheet页的Row IRow row = sheet.CreateRow(0); row.HeightInPoints = 20; ISheet enumSheet = workbook.CreateSheet(); IRow enumSheetRow1 = enumSheet.CreateRow(0); enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]); enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]); enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号 ISheet dataSheet = workbook.CreateSheet(); #region 设置excel模板列头 //默认灰色 var headerStyle = GetCellStyle(workbook); headerStyle.IsLocked = true; //黄色 var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow); yellowStyle.IsLocked = true; //红色 var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red); redStyle.IsLocked = true; //取得所有ExcelPropety var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); //设置列的索引 int _currentColunmIndex = 0; //设置Excel是否需要保护,默认不保护 bool IsProtect = false; //循环类的属性,赋值给列 for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { //依次获取属性字段 ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this); ColumnDataType dateType = (excelPropety.DataType == ColumnDataType.DateTime || excelPropety.DataType == ColumnDataType.Date) ? ColumnDataType.Text : excelPropety.DataType; //日期类型默认设置成Text类型,在赋值时会进行日期验证 //设置是否保护Excel if (excelPropety.ReadOnly) { IsProtect = true; } //给必填项加星号 string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*"; row.CreateCell(_currentColunmIndex).SetCellValue(colName); //修改列头样式 switch (excelPropety.BackgroudColor) { case BackgroudColorEnum.Yellow: row.Cells[_currentColunmIndex].CellStyle = yellowStyle; break; case BackgroudColorEnum.Red: row.Cells[_currentColunmIndex].CellStyle = redStyle; break; default: row.Cells[_currentColunmIndex].CellStyle = headerStyle; break; } var dataStyle = workbook.CreateCellStyle(); var dataFormat = workbook.CreateDataFormat(); if (dateType == ColumnDataType.Dynamic) { int dynamicColCount = excelPropety.DynamicColumns.Count(); for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++) { var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex]; string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*"; row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName); row.Cells[_currentColunmIndex].CellStyle = headerStyle; if (dynamicCol.ReadOnly) { IsProtect = true; } //设定列宽 if (excelPropety.CharCount > 0) { sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256); dataStyle.WrapText = true; } else { sheet.AutoSizeColumn(_currentColunmIndex); } //设置单元格样式及数据类型 dataStyle.IsLocked = excelPropety.ReadOnly; dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat); _currentColunmIndex++; } } else { //设定列宽 if (excelPropety.CharCount > 0) { sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256); dataStyle.WrapText = true; } else { sheet.AutoSizeColumn(_currentColunmIndex); } //设置是否锁定 dataStyle.IsLocked = excelPropety.ReadOnly; //设置单元格样式及数据类型 excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat); _currentColunmIndex++; } } #endregion #region 添加模版数据 if (TemplateDataTable.Rows.Count > 0) { for (int i = 0; i < TemplateDataTable.Rows.Count; i++) { DataRow tableRow = TemplateDataTable.Rows[i]; IRow dataRow = sheet.CreateRow(1 + i); for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { string colName = propetys[porpetyIndex].Name; tableRow[colName].ToString(); dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString()); } } } #endregion //冻结行 sheet.CreateFreezePane(0, 1, 0, 1); //锁定excel if (IsProtect) { sheet.ProtectSheet("password"); } //隐藏前2个Sheet workbook.SetSheetHidden(1, SheetState.Hidden); workbook.SetSheetHidden(2, SheetState.Hidden); //返回byte数组 MemoryStream ms = new MemoryStream(); workbook.Write(ms); return(ms.ToArray()); }