public void TestSetNameName()
{
// Test that renaming named ranges doesn't break our new named range map
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("First Sheet");
// Two named ranges called "name1", one scoped to sheet1 and one globally
XSSFName nameSheet1 = wb.CreateName() as XSSFName;
nameSheet1.NameName = "name1";
nameSheet1.RefersToFormula = "'First Sheet'!$A$1";
nameSheet1.SheetIndex = 0;
XSSFName nameGlobal = wb.CreateName() as XSSFName;
nameGlobal.NameName = "name1";
nameGlobal.RefersToFormula = "'First Sheet'!$B$1";
// Rename sheet-scoped name to "name2", check everything is updated properly
// and that the other name is unaffected
nameSheet1.NameName = "name2";
Assert.AreEqual(1, wb.GetNames("name1").Count);
Assert.AreEqual(1, wb.GetNames("name2").Count);
Assert.AreEqual(nameGlobal, wb.GetName("name1"));
Assert.AreEqual(nameSheet1, wb.GetName("name2"));
// Rename the other name to "name" and check everything again
nameGlobal.NameName = "name2";
Assert.AreEqual(0, wb.GetNames("name1").Count);
Assert.AreEqual(2, wb.GetNames("name2").Count);
Assert.IsTrue(wb.GetNames("name2").Contains(nameGlobal));
Assert.IsTrue(wb.GetNames("name2").Contains(nameSheet1));
wb.Close();
}
public void TestRemoveSheet()
{
// Test removing a sheet maintains the named ranges correctly
XSSFWorkbook wb = new XSSFWorkbook();
wb.CreateSheet("Sheet1");
wb.CreateSheet("Sheet2");
XSSFName sheet1Name = wb.CreateName() as XSSFName;
sheet1Name.NameName = "name1";
sheet1Name.SheetIndex = 0;
sheet1Name.RefersToFormula = "Sheet1!$A$1";
XSSFName sheet2Name = wb.CreateName() as XSSFName;
sheet2Name.NameName = "name1";
sheet2Name.SheetIndex = 1;
sheet2Name.RefersToFormula = "Sheet2!$A$1";
Assert.IsTrue(wb.GetAllNames().Contains(sheet1Name));
Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name));
Assert.AreEqual(2, wb.GetNames("name1").Count);
Assert.AreEqual(sheet1Name, wb.GetNames("name1")[0]);
Assert.AreEqual(sheet2Name, wb.GetNames("name1")[1]);
// Remove sheet1, we should only have sheet2Name now
wb.RemoveSheetAt(0);
Assert.IsFalse(wb.GetAllNames().Contains(sheet1Name));
Assert.IsTrue(wb.GetAllNames().Contains(sheet2Name));
Assert.AreEqual(1, wb.GetNames("name1").Count);
Assert.AreEqual(sheet2Name, wb.GetNames("name1")[0]);
// Check by index as well for sanity
Assert.AreEqual(1, wb.NumberOfNames);
Assert.AreEqual(0, wb.GetNameIndex("name1"));
Assert.AreEqual(sheet2Name, wb.GetNameAt(0));
wb.Close();
}
public static void Test()
{
var filepath = "E:/test.xlsx";
if (File.Exists(filepath))
{
File.Delete(filepath);
}
using (var ms = new FileStream(filepath, FileMode.OpenOrCreate))
{
IWorkbook workbook = new XSSFWorkbook();
try
{
ISheet sheetRef = workbook.CreateSheet("ref");//名为ref的工作表
var items = new dynamic[] {
new { code = "1", name = "项目" },
new { code = "2", name = "标段" },
new { code = "3", name = "桥梁" },
new { code = "4", name = "隧道" }
};
for (int i = 0; i < items.Length; i++)//A1到A4格子里存放0001到0004，这是下拉框可以选择的4个选项
{
var r = sheetRef.CreateRow(i);
r.CreateCell(0).SetCellValue(items[i].code);
r.CreateCell(1).SetCellValue(items[i].name);
//sheetRef.GetRow(i);
}
IName range = workbook.CreateName(); //创建一个命名公式
range.RefersToFormula = "ref!$A$1:$A$" + items.Length; //公式内容，就是上面的区域
range.NameName = "sectionName"; //公式名称，可以在"公式"-->"名称管理器"中看到
ISheet sheet1 = workbook.CreateSheet("data"); //获得第一个工作表
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("项目名称");
row.CreateCell(1).SetCellValue("地图名称");
row.CreateCell(2).SetCellValue("地图类型-代码");
row.CreateCell(3).SetCellValue("地图类型-名称");
row.CreateCell(4).SetCellValue("经纬度");
//设定公式
row.GetCell(3).SetCellFormula("VLOOKUP(C2,ref!A:B,2,FALSE)");
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 3); //约束范围：B1到B65535
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateFormulaListConstraint("sectionName"), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
sheet1.AddValidationData(validation); //添加进去
sheet1.ForceFormulaRecalculation = true;
workbook.Write(ms);
}
finally
{
workbook.Close();
}
}
}
CallingConstructorWithExistingWorkbookShouldInstanstiateNewXssfWorkbookDefaultRowAccessWindowSizeCompressTempFilesAsFalseAndUseSharedStringsTableFalse
()
{
var wb = new XSSFWorkbook();
var name = wb.CreateName();
name.NameName = "test";
var sheet = wb.CreateSheet("test1");
_objectToTest = new SXSSFWorkbook(wb);
Assert.AreEqual(100, _objectToTest.RandomAccessWindowSize);
Assert.AreEqual("test", _objectToTest.XssfWorkbook.GetName("test").NameName);
Assert.AreEqual(1, _objectToTest.NumberOfSheets);
}
public IName CreateName()
{
return(XssfWorkbook.CreateName());
}
//public NpoiMemoryStream GetFileTemplate(string fileName,string[] heads,params CellValidation[] cellValidations)
//{
// var memory = new NpoiMemoryStream();
// var workbook = new XSSFWorkbook();
// var sheet = workbook.CreateSheet(fileName);
// var row = sheet.CreateRow(0);
// for (var i = 0; i < heads.Length; i++)
// {
// row.CreateCell(i).SetCellValue(heads[i]);
// }
// var sheet1 = workbook.GetSheetAt(0);
// cellValidations.ForEach(item =>
// {
// var regions = new CellRangeAddressList(item.FirstRow,item.LastRow,item.FirstCol, item.LastCol);//约束范围：c2到c65535
// var helper = new XSSFDataValidationHelper((XSSFSheet)sheet1);//获得一个数据验证Helper
// var validation =
// helper.CreateValidation(helper.CreateExplicitListConstraint(item.ListOfValues),
// regions);//创建约束
// validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
// validation.ShowErrorBox = true;//显示上面提示=Ture
// sheet1.AddValidationData(validation);//添加进去
// });
// sheet1.ForceFormulaRecalculation = true;
// memory.AllowClose = false;
// workbook.Write(memory);
// memory.Flush();
// memory.Position = 0; // 指定内存流起始值
// return memory;
//}
public 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 set heads
#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 Range
});
#endregion set datasource
cellValidations?.ToList().ForEach(item =>
{
if (item.FirstList != null && item.FirstList.Any())
{
var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol, item.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())
{
var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol, item.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())
{
var regions = new CellRangeAddressList(item.FirstRow, item.LastRow, item.FirstCol + 1, item.LastCol + 1); //约束范围：c2到c65535
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet); //获得一个数据验证Helper
var validation =
helper.CreateValidation(
helper.CreateFormulaListConstraint($"INDIRECT(${Index2ColName(item.FirstCol)}2)"),
regions); //创建约束
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true; //显示上面提示=Ture
sheet.AddValidationData(validation); //添加进去
}
});
sheet.ForceFormulaRecalculation = true;
var memory = new NpoiMemoryStream();
memory.AllowClose = false;
workbook.Write(memory);
memory.Flush();
memory.Position = 0; // 指定内存流起始值
return(memory);
}
/// <summary>
/// 应用规则
/// </summary>
private void ApplyRule()
{
//单独存放一个工作簿存放所有的数据
var sheetName = $"ref_rule";
var sheetRef = _workbook.CreateSheet(sheetName);//名为ref的工作表
//循环 i 作为数据存储的列
// 第一组数据 存储在第一列
// 第二组数据 存储在第二列
// 以此类推
for (int i = 0; i < _rules.Count; i++)
{
var rule = _rules[i];
var options = rule.Source;
if (options.Count == 0)
{
continue;
}
//在当前列 保存所有数据, 所有数据是存储在行上
for (var j = 0; j < options.Count; j++)
{
var row = sheetRef.GetRow(j) ?? sheetRef.CreateRow(j);
row.CreateCell(i).SetCellValue(options[j]);
}
//在Excel工作簿中增加此数据范围
IName range = _workbook.CreateName();//创建一个命名公式
//获取当前列的字符串列信息，如 1=>A ， 2=>B
var colChar = PowerExcelConfigCell.ToColumnChar(i + 1);
//公式范围格式应为：A1-A50 B1:B25
range.RefersToFormula = $"{sheetName}!${colChar}$1:${colChar}${options.Count}"; //公式内容，就是上面的区域
range.NameName = $"rule_{i}"; //公式名称，可以在"公式"-->"名称管理器"中看到
//规则也保存该名称，留着后面使用
rule.RuleName = range.NameName;
//if (rule.RowField != null && _dtList.Rows.Count > 0)
//应用到行级字段
if (rule.RowField != null)
{
foreach (var field in rule.RowField)
{
SetField2Select(range.NameName, field);
}
}
//应用到填充字段
if (rule.FillField != null)
{
foreach (var field in rule.FillField)
{
SetField2Select(range.NameName, field);
}
}
}
var index = _workbook.GetSheetIndex(sheetRef);
_workbook.SetSheetHidden(index, _config.Prop.Debug ? false : true);
}
/// <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);
}
