/// <summary> /// 下载excel模板 /// </summary> /// <param name="fileName">文件名称</param> /// <param name="heads">表头</param> /// <param name="contentInfo">导出excel数据源</param> /// <param name="cellContents"></param> /// <param name="templteRowCount">模板或者导出excel样式设置行数</param> /// <param name="cellValidations">验证数据源</param> /// <returns></returns> public static NpoiMemoryStream GetFileTemplate(string fileName , List <HeadInfo> heads , List <List <ContentInfo> > contentInfo = null , List <CellContent> cellContents = null , int templteRowCount = 65535 , params CellValidation[] cellValidations) { #region 内容特殊字符过滤 待处理 for (var i = 0; i < cellValidations?.Length; i++) { if (cellValidations[i].FirstList == null || !cellValidations[i].FirstList.Any()) { continue; } cellValidations[i].FirstList[0].Key = ReplaceSpecialCharacter(cellValidations[i].FirstList[0].Key); cellValidations[i].FirstList[0].Value = ReplaceSpecialCharacter(cellValidations[i].FirstList[0].Value); //foreach (var item in cellValidations[i].FirstList) //{ // item.Key =ReplaceSpecialCharacter(item.Key); // item.Value =ReplaceSpecialCharacter(item.Value); //} } #endregion var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet(fileName); #region set heads var rowCount = contentInfo?.Count ?? templteRowCount; var newRow = sheet.CreateRow(0); for (var i = 0; i < heads.Count; i++) { sheet.SetColumnWidth(i, heads[i].ColumnWidth); var style = GetCellStyle(workbook, heads[i].CellStyle); var cell = newRow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(heads[i].Name); for (var j = 1; j <= rowCount; j++) { var row = sheet.GetRow(j) ?? sheet.CreateRow(j); cell = row.CreateCell(i); cell.CellStyle = style; } } #endregion #region 初始化导出excel数据源 for (var i = 0; i < contentInfo?.Count; i++) { var contentRow = sheet.CreateRow(i + 1); for (var j = 0; j < contentInfo[i]?.Count; j++) { var contentCell = contentRow.CreateCell(j); contentCell.SetCellValue(contentInfo[i][j].Name); } } #endregion #region set datasource cellValidations?.ToList().ForEach(item => { if (item.FirstList == null) { return; } var dataSourceSheetName = $"{item.FirstList?[0]?.Value.Replace(" ", "")}"; var dataSourceSheet = workbook.CreateSheet(dataSourceSheetName);//创建sheet /* * FirstList * 1 2 3 * 2 * 3 */ #region init datasource for (var i = 0; i < item.FirstList.Count; i++) { var row = dataSourceSheet.GetRow(i) ?? dataSourceSheet.CreateRow(i);//添加行 row.CreateCell(0).SetCellValue(i == 0 ? item.FirstList[i].Value.Replace(" ", "") : item.FirstList[i].Value);//单元格写值 if (i <= 0) { continue; } if (item.SecondList != null && item.SecondList.Any()) { dataSourceSheet.GetRow(0).CreateCell(i).SetCellValue(item.FirstList[i].Value);//一级列头 var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList(); for (var j = 0; j < cellDatas.Count; j++) { var secondRow = dataSourceSheet.GetRow(j + 1) ?? dataSourceSheet.CreateRow(j + 1); secondRow.CreateCell(i).SetCellValue(cellDatas[j].Value);//单元格写值 } } } #endregion #region Range for (var i = 0; i < item.FirstList.Count; i++) { if (i == 0) { var range = workbook.CreateName(); range.NameName = item.FirstList[i].Value.Replace(" ", ""); range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}", dataSourceSheetName, item.FirstList.Count, 2, Index2ColName(i)); } else { if (item.SecondList != null && item.SecondList.Any()) { var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList(); if (cellDatas.Any()) { var range = workbook.CreateName(); range.NameName = item.FirstList[i].Value.Replace(" ", ""); range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}", dataSourceSheetName, cellDatas.Count + 1, 2, Index2ColName(i)); } } } } #endregion }); #endregion #region set cellContents cellContents?.ForEach(item => { var writeSheet = workbook.GetSheetAt(item.SheetIndex) ?? workbook.CreateSheet(item.SheetIndex.ToString()); var i = 0; item.ListOfValues?.ForEach(info => { var writeRow = writeSheet.GetRow(item.FirstRow + i) ?? writeSheet.CreateRow(item.FirstRow + i); var writeCell = writeRow.GetCell(item.FirstCol) ?? writeRow.CreateCell(item.FirstCol); writeCell.SetCellValue(info); i++; }); }); #endregion #region 设置验证 cellValidations?.ToList().ForEach(item => { if (item.FirstList != null && item.FirstList.Any()) { if (item.FirstCellLocation != null) { var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation( helper.CreateFormulaListConstraint(item.FirstList[0].Value), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示 sheet.AddValidationData(validation); //添加进去 } } else { if (item.ListOfValues != null && item.ListOfValues.Any()) { if (item.FirstCellLocation != null) { var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示 sheet.AddValidationData(validation); //添加进去 } } } }); #endregion #region 设置验证 cellValidations?.ToList().ForEach(item => { if (item.SecondList != null && item.SecondList.Any()) { if (item.SecondCellLocation != null) { var regions = new CellRangeAddressList(item.SecondCellLocation.FirstRow, item.SecondCellLocation.LastRow, item.SecondCellLocation.FirstCol, item.SecondCellLocation.LastCol); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation( helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示=Ture sheet.AddValidationData(validation); //添加进去 } else if (item.FirstCellLocation != null) { var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol + 1, item.FirstCellLocation.LastCol + 1); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation( helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示=Ture sheet.AddValidationData(validation); //添加进去 } } }); #endregion sheet.ForceFormulaRecalculation = true; var memory = new NpoiMemoryStream { AllowClose = false }; workbook.Write(memory); memory.Flush(); memory.Position = 0; // 指定内存流起始值 return(memory); }
public static NpoiMemoryStream GetFileTemplate(string fileName , string[] heads , params CellValidation[] cellValidations) { var workbook = new XSSFWorkbook(); var sheet = workbook.CreateSheet(fileName); var style = workbook.CreateCellStyle(); style.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); #region set heads var newRow = sheet.CreateRow(0); for (var i = 0; i < heads.Length; i++) { var cell = newRow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(heads[i]); for (int j = 1; j <= 65535; j++) { var row = sheet.GetRow(j) ?? sheet.CreateRow(j); cell = row.CreateCell(i); cell.CellStyle = style; } } #endregion #region set datasource var dataNum = 0; cellValidations?.ToList().ForEach(item => { if (item.FirstList == null) { return; } var dataSourceSheetName = $"dataSource{dataNum}"; dataNum++; var dataSourceSheet = workbook.CreateSheet(dataSourceSheetName);//创建sheet for (var i = 0; i < item.FirstList.Count; i++) { var row = dataSourceSheet.GetRow(i) ?? dataSourceSheet.CreateRow(i); //添加行 row.CreateCell(0).SetCellValue(item.FirstList[i].Value); //单元格写值 if (i > 0) { if (item.SecondList != null && item.SecondList.Any()) { dataSourceSheet.GetRow(0).CreateCell(i).SetCellValue(item.FirstList[i].Value);//一级列头 var datas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList(); for (var j = 0; j < datas.Count; j++) { var secondRow = dataSourceSheet.GetRow(j + 1) ?? dataSourceSheet.CreateRow(j + 1); secondRow.CreateCell(i).SetCellValue(datas[j].Value);//单元格写值 } } } } #region Range for (var i = 0; i < item.FirstList.Count; i++) { if (i == 0) { var range = workbook.CreateName(); range.NameName = item.FirstList[i].Value; range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}", dataSourceSheetName, item.FirstList.Count, 2, Index2ColName(i)); } else { if (item.SecondList != null && item.SecondList.Any()) { var cellDatas = item.SecondList.Where(c => c.Key == item.FirstList[i].Key).ToList(); if (cellDatas.Any()) { var range = workbook.CreateName(); range.NameName = item.FirstList[i].Value; range.RefersToFormula = string.Format("{0}!${3}${2}:${3}${1}", dataSourceSheetName, cellDatas.Count + 1, 2, Index2ColName(i)); } } } } #endregion }); #endregion cellValidations?.ToList().ForEach(item => { if (item.FirstList != null && item.FirstList.Any()) { if (item.FirstCellLocation != null) { var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation( helper.CreateFormulaListConstraint(item.FirstList[0].Value), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示 sheet.AddValidationData(validation); //添加进去 } } else { if (item.ListOfValues != null && item.ListOfValues.Any()) { if (item.FirstCellLocation != null) { var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol, item.FirstCellLocation.LastCol); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示 sheet.AddValidationData(validation); //添加进去 } } } }); cellValidations?.ToList().ForEach(item => { if (item.SecondList != null && item.SecondList.Any()) { if (item.SecondCellLocation != null) { var regions = new CellRangeAddressList(item.SecondCellLocation.FirstRow, item.SecondCellLocation.LastRow, item.SecondCellLocation.FirstCol, item.SecondCellLocation.LastCol); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation( helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示=Ture sheet.AddValidationData(validation); //添加进去 } else { if (item.FirstCellLocation != null) { var regions = new CellRangeAddressList(item.FirstCellLocation.FirstRow, item.FirstCellLocation.LastRow, item.FirstCellLocation.FirstCol + 1, item.FirstCellLocation.LastCol + 1); //约束范围:c2到c65535 var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper var validation = helper.CreateValidation( helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCellLocation.FirstCol)}2)"), regions); //创建约束 validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); validation.ShowErrorBox = true; //显示上面提示=Ture sheet.AddValidationData(validation); //添加进去 } } } }); sheet.ForceFormulaRecalculation = true; var memory = new NpoiMemoryStream { AllowClose = false }; workbook.Write(memory); memory.Flush(); memory.Position = 0; // 指定内存流起始值 return(memory); }