private static void SetCodeColumn(XSSFSheet sheet, string columnLetter, int colindex, int elements, string sheetName, bool createCodeColumns)
{
if (createCodeColumns)
{
var row0 = sheet.GetRow(0);
var lastColIndex = row0.LastCellNum;
var headerCell = row0.CreateCell(lastColIndex, CellType.String);
headerCell.SetCellValue(sheetName + " Code");
row0.Cells.Add(headerCell);
headerCell.CellStyle = sheet.Workbook?.GetSheetAt(0)?.GetRow(0)?.GetCell(0)?.CellStyle;
for (var i = 1; i <= elements; i++)
{
var row = sheet.GetRow(i) ?? sheet.CreateRow(i);
var cell = row.CreateCell(lastColIndex, CellType.Formula);
var formula = $"INDEX('{sheetName}'!A1:D{elements},MATCH(${columnLetter}{i + 1},'{sheetName}'!D1:D{elements},0),1)";
cell.SetCellFormula(formula);
}
sheet.SetColumnHidden(row0.LastCellNum - 1, true);
}
var validationHelper = new XSSFDataValidationHelper(sheet);
var addressList = new CellRangeAddressList(0, elements - 1, colindex - 1, colindex - 1);
var constraint = validationHelper.CreateFormulaListConstraint($"'{sheetName}'!$D$2:$D$" + elements);
var dataValidation = validationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(dataValidation);
}
public static void AddConstraint(this ISheet sheet, IWorkbook workbook, string name, string mula, int columnIndex, bool isCustom = false)
{
IName namedRange = workbook.CreateName();
namedRange.NameName = name;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
XSSFDataValidationConstraint dvConstraint;
// XSSFDataValidation validation;
if (!isCustom)
{
namedRange.RefersToFormula = mula;//公式
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(namedRange.NameName);
}
else
{
//自定义
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(name.Split(','));
}
CellRangeAddressList addressList = new CellRangeAddressList(1, 10000, columnIndex, columnIndex);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
public override Action <ISheet> SetExcelWorksheet()
{
return((s) =>
{
base.SetExcelWorksheet()(s);
var address = typeof(Student1).GetCellAddress(nameof(Student1.Email));
address = $"{address}2:{address}1000";
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)s);
//创建验证规则
IDataValidationConstraint constraint = helper.CreateCustomConstraint($"=COUNTIF({address},\"?*@*.*\")");
var validation = helper.CreateValidation(constraint, new CellRangeAddressList(1, 1000, 0, 0));
//设置约束提示信息
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
validation.ShowErrorBox = true;
validation.ShowPromptBox = true;
validation.CreateErrorBox("请输入邮箱ErrorTitle", "请输入邮箱Error");
validation.CreatePromptBox("自定义错误信息PromptTitle", "自定义错误Prompt");
validation.ErrorStyle = 1;
s.AddValidationData(validation);
});
}
/// <summary>
/// EXCEL2007下拉值
/// </summary>
/// <param name="sheet"></param>
/// <param name="data"></param>
/// <param name="StarCol"></param>
/// <param name="EndCol"></param>
private static void CreateDropDwonListForXLSX(XSSFSheet sheet, List <string> data, int StarCol, int EndCol)
{
XSSFDataValidationHelper Validation = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint Constraint = (XSSFDataValidationConstraint)Validation.CreateExplicitListConstraint(data.ToArray());
CellRangeAddressList AddressList = new CellRangeAddressList(1, 65535, StarCol, EndCol);
var XSSF = Validation.CreateValidation(Constraint, AddressList);
sheet.AddValidationData(XSSF);
}
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();
}
}
}
public IActionResult ExportToExcel()
{
//创建EXCEL工作薄
IWorkbook workBook = new XSSFWorkbook();
//创建sheet文件表
ISheet sheet = workBook.CreateSheet("上下料点信息");
var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory,
DateTime.Now.ToString("yyyyMM"));
if (!Directory.Exists(expDir))
{
Directory.CreateDirectory(expDir);
}
string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss"));
#region 创建Excel表头
//创建表头
IRow header = sheet.CreateRow(0);
ICell cell = header.CreateCell(0);
cell.SetCellValue("料点编号");
cell = header.CreateCell(1);
cell.SetCellValue("料点名称");
cell = header.CreateCell(2);
cell.SetCellValue("仓库编号");
cell = header.CreateCell(3);
cell.SetCellValue("巷道编号");
cell = header.CreateCell(4);
cell.SetCellValue("料点类型");
ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 4, 4); //设定位置 行起，行止,列起,列终
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "In", "Out", "InOut", "OutBack" }), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
sheet1.AddValidationData(validation); //添加进去
sheet1.ForceFormulaRecalculation = true;
#endregion
//工作流写入，通过流的方式进行创建生成文件
using (MemoryStream stream = new MemoryStream())
{
workBook.Write(stream);
byte[] buffer = stream.ToArray();
return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("上下料点信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
}
/// <summary>
/// 建立级联关系
/// </summary>
/// <param name="sheet">表</param>
/// <param name="source">数据源(EXCEL表)</param>
/// <param name="minRow">起始行</param>
/// <param name="maxRow">终止行</param>
/// <param name="minCell">起始列</param>
/// <param name="maxCell">终止列</param>
public void ExcelLevelRelation(ISheet sheet, string source, int minRow, int maxRow, int minCell, int maxCell)
{
// 第一层绑定下拉的时候，可以一次性选择多个单元格进行绑定
// 要是从第二层开始，就只能一对一的绑定，如果目标单元格要与哪一个一级单元格进行关联
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet as XSSFSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(source);
CellRangeAddressList cellRegions = new CellRangeAddressList(minRow, maxRow, minCell, maxCell);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, cellRegions);
validation.SuppressDropDownArrow = true;
validation.CreateErrorBox("输入不合法", "请选择下拉列表中的值。");
validation.ShowErrorBox = true;
sheet.AddValidationData(validation);
}
/// <summary>
/// 设置字段为下拉框
/// </summary>
/// <param name="ruleName">规则名称</param>
/// <param name="fieldName">字段名称</param>
private void SetField2Select(string ruleName, string fieldName)
{
//查找字符索引
var field = _config.Row.Where(t => t.Field == fieldName).FirstOrDefault();
if (field == null)
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(_config.Prop.StartRow - 1, 65535, field.ColumnIndex, field.ColumnIndex); //约束范围：B1到B65535
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)_sheet); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateFormulaListConstraint(ruleName), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.EmptyCellAllowed = true;
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
_sheet.AddValidationData(validation); //添加进去
}
/// <summary>
/// Sheet中引用校验引用区域
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="item"></param>
private void SheetAddDataValidation(IWorkbook workbook, ISheet sheet, ColumnProperty item)
{
if (item == null || string.IsNullOrWhiteSpace(item.ConstraintReference)) //如果没有引用区域， 则退出
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, item.ColumnIndex, item.ColumnIndex);
IDataValidation dataValidate = null;
if (excelVersion == ExcelVersion.XLSX)
{
XSSFSheet xssfSheet = sheet as XSSFSheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint(item.ConstraintReference);
dataValidate = dvHelper.CreateValidation(dvConstraint, regions);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
else
{
IName range = workbook.CreateName();
range.RefersToFormula = item.ConstraintReference;
range.NameName = item.ConstraintName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(item.ConstraintName);
dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.EmptyCellAllowed = true;
dataValidate.ShowErrorBox = true;
dataValidate.CreateErrorBox("系统提示", "请选择指定的" + item.ColumnHeader + "选项");
dataValidate.ShowPromptBox = true;
dataValidate.CreatePromptBox("", item.ColumnHeader);
}
sheet.AddValidationData(dataValidate);
}
private void CreateEmptyExcel(string excelName)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = 42;
style.BorderLeft =
style.BorderTop =
style.BorderRight =
style.BorderBottom = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
sheet.CreateRow(0).RowStyle = style;
sheet.CreateRow(1).RowStyle = style;
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint)helper.CreateExplicitListConstraint(Enum.GetNames(typeof(DataConverter.DataType)));
CellRangeAddressList range = new CellRangeAddressList(1, 1, 0, 255);
sheet.AddValidationData((XSSFDataValidation)helper.CreateValidation(constraint, range));
style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = 42;
style.BorderLeft =
style.BorderTop =
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Medium;
style.Alignment = HorizontalAlignment.Center;
sheet.CreateRow(2).RowStyle = style;
using (FileStream fs = new FileStream(ExcelDirectory + excelName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
workbook.Close();
}
private static void buildDataValidationForEnum <T>(ISheet sheet1, int headerEndIndex, IEnumerable <ExcelOnWritingColumnConfiguration> columnMaps)
{
var cellIndex = 0;
for (int i = 0; i < columnMaps.Count(); i++)
{
var columnMap = columnMaps.ElementAt(i);
var property = columnMap.Property;
var isRefType = property.PropertyType.IsClass && property.PropertyType.Assembly != typeof(object).Assembly;
if (isRefType)
{
cellIndex = (cellIndex - 1) + (property.PropertyType.GetProperties().Length);
}
if (property.PropertyType.IsEnum)
{
var names = EnumHelper.GetNames(property.PropertyType);
if (names == null || names.Count == 0)
{
continue;
}
var dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet1);
var constraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(names.ToArray());
var addressList = new CellRangeAddressList(headerEndIndex, sheet1.LastRowNum, cellIndex, cellIndex);
var validation = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList);
validation.EmptyCellAllowed = true;
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
validation.ErrorStyle = 0;//Stop
validation.CreateErrorBox("Invalid selection", "Select a valid data from the list.");
sheet1.AddValidationData(validation);
}
cellIndex++;
}
}
/// <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 IActionResult ExportToExcel()
{
//创建EXCEL工作薄
IWorkbook workBook = new XSSFWorkbook();
//创建sheet文件表
ISheet sheet = workBook.CreateSheet("客户信息");
var expDir = string.Format("{0}Export\\{1}", System.AppDomain.CurrentDomain.BaseDirectory,
DateTime.Now.ToString("yyyyMM"));
if (!Directory.Exists(expDir))
{
Directory.CreateDirectory(expDir);
}
string filePath = string.Format("{0}\\CD{1}.xlsx", expDir, DateTime.Now.ToString("yyyyMMddHHmmss"));
#region 创建Excel表头
//创建表头
IRow header = sheet.CreateRow(0);
ICell cell = header.CreateCell(0);
cell.SetCellValue("客户编号");
cell = header.CreateCell(1);
cell.SetCellValue("客户名称");
cell = header.CreateCell(2);
cell.SetCellValue("客户类型");
cell = header.CreateCell(3);
cell.SetCellValue("联系电话/Phone");
cell = header.CreateCell(4);
cell.SetCellValue("传真/Fax");
cell = header.CreateCell(5);
cell.SetCellValue("邮箱/Email");
//IName range = workBook.CreateName();//创建一个命名公式
//range.RefersToFormula = "客户信息!$A$1:$A$4";//公式内容，就是上面的区域
//range.NameName = "sectionName";//公式名称，可以在"公式"-->"名称管理器"中看到
ISheet sheet1 = workBook.GetSheetAt(0); //获得第一个工作表
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 2, 2); //设定位置 行起，行止,列起,列终
XSSFDataValidationHelper helper = new XSSFDataValidationHelper((XSSFSheet)sheet1); //获得一个数据验证Helper
IDataValidation validation = helper.CreateValidation(helper.CreateExplicitListConstraint(new string[] { "Company", "Personal", "Virtual", "Internal" }), regions); //创建一个特定约束范围内的公式列表约束（即第一节里说的"自定义"方式）
validation.CreateErrorBox("错误", "请按右侧下拉箭头选择!"); //不符合约束时的提示
validation.ShowErrorBox = true; //显示上面提示 = True
sheet1.AddValidationData(validation); //添加进去
sheet1.ForceFormulaRecalculation = true;
#endregion
//工作流写入，通过流的方式进行创建生成文件
using (MemoryStream stream = new MemoryStream())
{
workBook.Write(stream);
byte[] buffer = stream.ToArray();
return(File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", string.Format("客户信息表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
}
public static byte[] GenerateTemplate(List <Business.Entities.company> listCompany, List <Business.Entities.contractor> listContractor, List <Business.Entities.project> listProject)
{
//culture
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt
int parseRecordNumber = 100; // number of rows that has style or validation
int startRowIndex = 3;
XSSFCellStyle styleCurrency;
XSSFCellStyle styleDate;
XSSFCellStyle styleNumeric;
XSSFCellStyle styleDecimal;
//kamus
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet; XSSFRow row; XSSFCell cell;
XSSFCellStyle style; XSSFFont font;
CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint dvConstraint; XSSFDataValidation validation;
List <string> listCompanyString = new List <string>();
foreach (var data in listCompany)
{
listCompanyString.Add(data.name);
}
List <string> listContractorString = new List <string>();
foreach (var data in listContractor)
{
listContractorString.Add(data.name);
}
List <string> listProjectString = new List <string>();
foreach (var data in listProject)
{
listProjectString.Add(data.name);
}
styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle();
styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");
styleNumeric = (XSSFCellStyle)workbook.CreateCellStyle();
styleNumeric.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("mm/dd/yyyy");
styleDecimal = (XSSFCellStyle)workbook.CreateCellStyle();
styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
List <string> columnList = new List <string>();
columnList.Add("Name");
int ContractorStringLocation = 1;
columnList.Add("Contractor");
columnList.Add("Photo");
columnList.Add("Description");
columnList.Add("Start Date");
columnList.Add("Finish Date");
columnList.Add("Highlight");
columnList.Add("Project Stage");
columnList.Add("Status");
columnList.Add("Budget");
columnList.Add("Currency");
columnList.Add("Num");
int PmcStringLocation = 12;
columnList.Add("Pmc");
columnList.Add("Summary");
int CompanyStringLocation = 14;
columnList.Add("Company");
columnList.Add("Status Non Technical");
columnList.Add("Is Completed");
columnList.Add("Completed Date");
int ProjectStringLocation = 18;
columnList.Add("Project");
columnList.Add("Submit For Approval Time");
columnList.Add("Approval Status");
columnList.Add("Approval Time");
columnList.Add("Deleted");
columnList.Add("Approval Message");
columnList.Add("Status Technical");
columnList.Add("Scurve Data");
sheet = (XSSFSheet)workbook.CreateSheet("Data");
int col = 0;
int rowNumber = 0;
//create row (header)
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
dvHelper = new XSSFDataValidationHelper(sheet);
//header data
style = (XSSFCellStyle)workbook.CreateCellStyle();
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue("M Project");
font = (XSSFFont)workbook.CreateFont();
font.FontHeight = 24;
style.SetFont(font);
cell.CellStyle = style;
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
style = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//header data
foreach (string data in columnList)
{
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue(data);
cell.CellStyle = style;
//cell.CellStyle.IsLocked = true;
//column width
sheet.SetColumnWidth(col, (30 * 256));
++col;
}
//sheet.CreateFreezePane(0, 4);
//dropdownlist Company
if (listCompanyString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterCompany");
int i = 0;
foreach (string a in listCompanyString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper = new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, CompanyStringLocation, CompanyStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterCompany!$A$1:$A$" + listCompanyString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(1, true);
sheet.AddValidationData(validation);
}
//dropdownlist Contractor
if (listContractorString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterContractor");
int i = 0;
foreach (string a in listContractorString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper = new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, ContractorStringLocation, ContractorStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterContractor!$A$1:$A$" + listContractorString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(2, true);
sheet.AddValidationData(validation);
}
//dropdownlist Project
if (listProjectString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterProject");
int i = 0;
foreach (string a in listProjectString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper = new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, ProjectStringLocation, ProjectStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterProject!$A$1:$A$" + listProjectString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(3, true);
sheet.AddValidationData(validation);
}
/*Cell formatting*/
for (int i = startRowIndex; i <= parseRecordNumber; i++)
{
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//start_date
col = 4;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 4, 4);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//finish_date
col = 5;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 5, 5);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//budget
col = 9;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDecimal;
if (i == startRowIndex)
{
}
//num
col = 11;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleNumeric;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 11, 11);
dvHelper = new XSSFDataValidationHelper(sheet);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "1000000000000000000");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a number, maximum 1.000.000.000.000.000.000");
sheet.AddValidationData(validation);
}
//completed_date
col = 17;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 17, 17);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//submit_for_approval_time
col = 19;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 19, 19);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//approval_time
col = 21;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if (i == startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 21, 21);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
}
//write to byte[]
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
/// <summary>
/// Método que crea la plantilla vacía que el administrador podrá descargar para llenar y registrar varios productos
/// </summary>
public string GenerarPlantillaVacia(bool _lParaDescargar = true, string _cRutaAlternativa = "")
{
string cHome = AppDomain.CurrentDomain.BaseDirectory;
string cRutaPlantilla = "";
if (_lParaDescargar)
{
cRutaPlantilla = cHome + "Plantillas\\PlantillaVacia\\Plantilla.xlsx";
}
else
{
cRutaPlantilla = cHome + _cRutaAlternativa;
}
cRutaPlantilla = cRutaPlantilla.Normalize();
List <string> lstEncabezados = new List <string> {
"Nombre",
"Descripción",
"Precio",
"Categoría",
"Estatus",
"Existencia"
};
List <string> lstCategorias = (from cat in db.tblCat_Categoria
select cat.cNombre).ToList();
using (FileStream _oFileStream = new FileStream(cRutaPlantilla, FileMode.Create, FileAccess.Write))
{
IWorkbook oLibro = new XSSFWorkbook();
ISheet oHoja = oLibro.CreateSheet("Plantilla");
ISheet oHojaCategorias = oLibro.CreateSheet("Categorias");
ICreationHelper oAyudanteCreacion = oLibro.GetCreationHelper();
IRow oFilaEncabezados = oHoja.CreateRow(0);
ICellStyle oEstiloDeCelda = oLibro.CreateCellStyle();
oEstiloDeCelda.Alignment = HorizontalAlignment.Center;
XSSFFont fuenteDeCelda = new XSSFFont();
fuenteDeCelda.Boldweight = 700;
oEstiloDeCelda.SetFont(fuenteDeCelda);
for (int i = 0; i < lstEncabezados.Count; i++)
{
ICell oCelda = oFilaEncabezados.CreateCell(i);
oCelda.SetCellValue(lstEncabezados[i]);
oCelda.CellStyle = oEstiloDeCelda;
oHoja.AutoSizeColumn(i);
GC.Collect();
}
IDataValidationHelper oAyudanteDeValidacionExcel = new XSSFDataValidationHelper((XSSFSheet)oHoja);
CellRangeAddressList oRangoDeCeldasCategoria = new CellRangeAddressList(1, 300, 3, 3);
CellRangeAddressList oRangoDeCeldasEstatus = new CellRangeAddressList(1, 300, 4, 4);
XSSFDataValidationConstraint oReglaValidacionListaCategorias = (XSSFDataValidationConstraint)oAyudanteDeValidacionExcel.CreateExplicitListConstraint(lstCategorias.ToArray());
XSSFDataValidationConstraint oReglaDeValidacionEstatus = (XSSFDataValidationConstraint)oAyudanteDeValidacionExcel.CreateExplicitListConstraint(new string[] { "Activo", "Inactivo" });
IDataValidation oValidadorCategorias = oAyudanteDeValidacionExcel.CreateValidation(oReglaValidacionListaCategorias, oRangoDeCeldasCategoria);
IDataValidation oValidadorEstatus = oAyudanteDeValidacionExcel.CreateValidation(oReglaDeValidacionEstatus, oRangoDeCeldasEstatus);
oHoja.AddValidationData(oValidadorCategorias);
oHoja.AddValidationData(oValidadorEstatus);
oLibro.Write(_oFileStream);
oLibro.Close();
_oFileStream.Close();
}
return(cRutaPlantilla);
}
/// <summary>
/// 设置Excel单元格样式（标题），数据格式
/// </summary>
/// <param name="dateType">数据类型</param>
/// <param name="porpetyIndex">单元格索引</param>
/// <param name="sheet">Sheet页</param>
/// <param name="dataSheet">数据Sheet页</param>
/// <param name="dataStyle">样式</param>
/// <param name="dataFormat">格式</param>
public void SetColumnFormat(ColumnDataType dateType, int porpetyIndex, ISheet sheet, ISheet dataSheet, ICellStyle dataStyle, IDataFormat dataFormat)
{
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
CellRangeAddressList CellRangeList = new CellRangeAddressList(1, 1048576 - 1, porpetyIndex, porpetyIndex); //超过1048576最大行数，打开Excel会报错
XSSFDataValidationConstraint dvConstraint = null;
XSSFDataValidation dataValidation = null;
switch (dateType)
{
case ColumnDataType.Date:
case ColumnDataType.DateTime:
//因为DateTime类型，添加Validation报错，所以去掉
dataStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd HH:mm:ss");
break;
case ColumnDataType.Number:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? long.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? long.MaxValue.ToString() : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputNumber"]);
dataStyle.DataFormat = dataFormat.GetFormat("0");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputNumberFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.Float:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? decimal.MinValue.ToString() : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? decimal.MaxValue.ToString() : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputDecimal"]);
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputDecimalFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.Bool:
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("Sheet1!$A$1:$B$1");
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
break;
case ColumnDataType.Text:
this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? "0" : this.MinValueOrLength;
this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? "2000" : this.MaxValuseOrLength;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputText"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]);
break;
case ColumnDataType.ComboBox:
case ColumnDataType.Enum:
int count = this.ListItems.Count() == 0 ? 1 : this.ListItems.Count();
string cloIndex = "";
if (porpetyIndex > 25)
{
cloIndex += Convert.ToChar((int)(Math.Floor(porpetyIndex / 26d)) - 1 + 65);
}
cloIndex += Convert.ToChar(65 + porpetyIndex % 26).ToString();
IName range = sheet.Workbook.CreateName();
range.RefersToFormula = "Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count;
range.NameName = "dicRange" + porpetyIndex;
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("dicRange" + porpetyIndex);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
var listItemsTemp = this.ListItems.ToList();
for (int rowIndex = 0; rowIndex < this.ListItems.Count(); rowIndex++)
{
IRow dataSheetRow = dataSheet.GetRow(rowIndex);
if (dataSheetRow == null)
{
dataSheetRow = dataSheet.CreateRow(rowIndex);
}
dataSheetRow.CreateCell(porpetyIndex).SetCellValue(listItemsTemp[rowIndex].Text);
dataStyle.DataFormat = dataFormat.GetFormat("@");
dataSheetRow.Cells.Where(x => x.ColumnIndex == porpetyIndex).FirstOrDefault().CellStyle = dataStyle;
}
dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]);
break;
default:
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength);
dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList);
dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]);
dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
break;
}
if (dataValidation == null)
{
return;
}
if (!this.IsNullAble)
{
dataValidation.EmptyCellAllowed = false;
}
sheet.SetDefaultColumnStyle(porpetyIndex, dataStyle);
dataValidation.ShowErrorBox = true;
sheet.AddValidationData(dataValidation);
}
public static MemoryStream CreateExcelHeader(MDataTable header, Dictionary <string, string[]> validateData)
{
MemoryStream ms = new MemoryStream();
if (header != null && header.Rows.Count > 0)
{
MDataTable importHeader = header.FindAll("Import=1");
try
{
XSSFWorkbook export = new XSSFWorkbook();
ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index);
ISheet sheet = export.CreateSheet("Sheet1");//创建内存Excel
#region 创建引用
int rowStartIndex = 1;
CreateValidationSheet(export, validateData, rowStartIndex);
#endregion
importHeader.Rows.Sort("ORDER BY MergeIndexed DESC");//Hidden=0 AND (Export=1 OR Field LIKE 'mg_%')
MDataTable headTable = importHeader.Clone();
int ColTitleRowCount = 0;
Dictionary <string, int> formatdic = new Dictionary <string, int>();
for (int i = importHeader.Rows.Count - 1; i >= 0; i--)//MDataTable 不支持 NOT LIKE
{
if (importHeader.Rows[i]["Field"].Value.ToString().IndexOf("mg") > -1)
{
importHeader.Rows.RemoveAt(i);//非字段列移除
}
}
int colSum = importHeader.Rows.Count;//实际列数
importHeader.Rows.Sort("ORDER BY OrderNum ASC");
if (!ExportMulHeader(header, true))
{
IRow row = sheet.CreateRow(0);
ICell cell;
for (int i = 0; i < colSum; i++)
{
string title = importHeader.Rows[i]["Title"].Value.ToString();
cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
else
{
CreateMulHeadExcel(export, headTable, out ColTitleRowCount, colSum);
ColTitleRowCount -= 1;
}
for (int i = 0; i < importHeader.Rows.Count; i++)
{
string formater = importHeader.Rows[i].Get <string>("Formatter");
if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
{
formatdic.Add(formater, i);//存储列索引
}
}
int maxRow = 50000;//限制最大行数(07之前版本的excel最大行数为65536，但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
XSSFSheet xssfSheet = (XSSFSheet)sheet;
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(xssfSheet);
for (int i = 0; i < importHeader.Rows.Count; i++)
{
MDataRow dtRow = importHeader.Rows[i];
string formatter = dtRow.Get <string>("Formatter");
if (formatter == "boolFormatter")
{
formatter = "#是否"; //对bool型特殊处理。
}
if (!string.IsNullOrEmpty(formatter) && formatter.StartsWith("#") && validateData != null && formatter.Length > 1) //&& validateData.ContainsKey(formatter)
{
//处理数据的有效性
CellRangeAddressList regions = null;
IDataValidationConstraint constraint = null;
IDataValidation dataValidate = null;
//int maxRow = 65535;
if (validateData.ContainsKey(formatter))
{
regions = new CellRangeAddressList(ColTitleRowCount + 1, maxRow, i, i);
string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
/*03版本api
* constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
* dataValidate = new HSSFDataValidation(regions, constraint);
*/
constraint = dvHelper.CreateFormulaListConstraint(key);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
//regions = new CellRangeAddressList(ColTitleRowCount, maxRow, i, i);
//string key = formatter.Split('=')[0].Replace("#", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
//constraint = DVConstraint.CreateFormulaListConstraint(key);//);//validateData[formatter]
//dataValidate = new HSSFDataValidation(regions, constraint);
//sheet.AddValidationData(dataValidate);
}
//
if (formatter.StartsWith("#C"))//级联要接着父级后加数据有效性才行
{
string Parentformatter = formatter;
while (formatdic.ContainsKey(Parentformatter))
{
int point = 0;
int parentindex = formatdic[Parentformatter];
formatdic.Remove(Parentformatter);
foreach (var item in formatdic)
{
if (item.Key.IndexOf('=') > -1)
{
string parent = item.Key.Split('=')[1];
parent = parent.Replace(">", "#");
if (parent.Equals(Parentformatter.Split('=')[0]))
{
int selfindex = item.Value;
//int parentindex = formatdic[hereformatter];
string t = IntToMoreChar(parentindex);
for (int im = ColTitleRowCount; im < maxRow; im++)
{
string func = string.Format("@INDIRECT({0}{1})", t, im + 1);
regions = new CellRangeAddressList(im, im, selfindex, selfindex);
constraint = dvHelper.CreateFormulaListConstraint(func);
dataValidate = dvHelper.CreateValidation(constraint, regions);
sheet.AddValidationData(dataValidate);
}
//for (int im = ColTitleRowCount; im < maxRow; im++)//1000应为maxRow
//{
// string func = "INDIRECT(" + t + (im + 1) + ")";//excel2013不能级联，03可以，其他没测过
// regions = new CellRangeAddressList(ColTitleRowCount, im, selfindex, selfindex);
// constraint = DVConstraint.CreateFormulaListConstraint(func);
// dataValidate = new HSSFDataValidation(regions, constraint);
// sheet.AddValidationData(dataValidate);
//}
Parentformatter = item.Key;
break;
}
}
point += 1;
}
if (point.Equals(formatdic.Count))
{
Parentformatter = string.Empty;
}
}
}
}
}
export.Write(ms);
ms.Flush();
ms.Close();
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
return(ms);
}
public void CreateSheet <T>(IEnumerable <T> data, XSSFSheet sheet)
{
//CreateHeader
var header = sheet.CreateRow(0);
header.CreateCell(0);
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
int i = 0, j = 0;
foreach (PropertyDescriptor prop in props)
{
var cell = header.CreateCell(i);
cell.SetCellValue(prop.Name);
i++;
}
//CreateRows
i = 1;
foreach (T item in data)
{
var row = sheet.CreateRow(i);
j = 0;
foreach (PropertyDescriptor prop in props)
{
if (prop.Converter.ConvertToString(prop.GetValue(item)).Contains("$"))
{
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(DropDownList);
CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// row.CreateCell(j).SetCellValue(prop.Converter.ConvertToString(prop.GetValue(item)));
}
else
{
row.CreateCell(j).SetCellValue(prop.Converter.ConvertToString(prop.GetValue(item)));
}
j++;
}
i++;
}
}
//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>
/// <param name="dtSource"></param>
/// <param name="exportTemplateFilePath"></param>
/// <param name="fillRow"></param>
/// <param name="replaceCells"></param>
/// <returns></returns>
private static MemoryStream ExportOpeningTemplate(DataTable dtSource, string[] dropDowndtSource, string exportTemplateFilePath, int fillRow, int dropDownFillStartCell, int dropDownFillEndCell)
{
try
{
//打开Excle模板文件
IWorkbook workbook = null;
using (FileStream fileOne = new FileStream(exportTemplateFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fileOne); //获取第一个工作表
}
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); //获取第一个sheet
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper
.CreateExplicitListConstraint(dropDowndtSource);
CellRangeAddressList addressList = new CellRangeAddressList(1, dtSource.Rows.Count, dropDownFillStartCell, dropDownFillEndCell);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
//格式日期
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//格式数字
XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
//单元格样式
ICellStyle style = workbook.CreateCellStyle();
//style.BorderBottom = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderTop = BorderStyle.Thin;
int rowIndex = fillRow;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
//sheet.ShiftRows(rowIndex, sheet.LastRowNum, 1, true, false);
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
}
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
Double doubV = 0;
Double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
newCell.CellStyle = decimelStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
newCell.CellStyle = style;
}
#endregion 填充内容
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
catch (Exception ex)
{
throw ex;
}
}
static void ToExcel(string[] args)
{
Console.WriteLine("Convert To Excel OK? (y/n)");
if (Console.ReadKey().Key != ConsoleKey.Y)
{
return;
}
//Console.ReadKey();
if (args.Length == 0)
{
Console.WriteLine("Please Drag and Drop");
Console.ReadKey();
return;
}
string path = args[0];
var jsonPath = path + @"\ExportedMasterDefine";
var masterPath = jsonPath + @"\master.json";
var masterconfPath = jsonPath + @"\master_conf.json";
var masterdefPath = jsonPath + @"\master_def.json";
var masterDataPath = path + @"\Assets\Resources";
if (
!Directory.Exists(jsonPath) ||
!Directory.Exists(masterDataPath) ||
!File.Exists(masterPath) ||
!File.Exists(masterdefPath)
)
{
Console.WriteLine("Master: " + masterDataPath);
Console.WriteLine("Json : " + jsonPath);
Console.WriteLine("master: " + masterPath);
Console.WriteLine("def : " + masterdefPath);
Console.WriteLine("Invalid Project");
Console.ReadKey();
return;
}
Console.WriteLine("Dir : " + path);
Console.WriteLine("Json : " + jsonPath);
var masterjsontext = File.ReadAllText(masterPath);
var masterdefjsontext = File.ReadAllText(masterdefPath);
var masterconfjsontext = File.ReadAllText(masterconfPath);
//Console.ReadKey();
var masterjson = MiniJSON.Json.Deserialize(masterjsontext);
var masterdefjson = MiniJSON.Json.Deserialize(masterdefjsontext);
var masterconfjson = MiniJSON.Json.Deserialize(masterconfjsontext);
//Console.ReadKey();
var bookRootPath = path + @"\ExportedMasterExcel";
var bookPath = bookRootPath + @"\master_01.xlsx";
var book = CreateNewBook(bookPath);
var dataDef = masterdefjson as Dictionary <string, object>;
var dataConf = masterconfjson as Dictionary <string, object>;
var data = masterjson as Dictionary <string, object>;
ICellStyle stylethin = book.CreateCellStyle();
stylethin.BorderTop = BorderStyle.Thin;
stylethin.BorderRight = BorderStyle.Thin;
stylethin.BorderBottom = BorderStyle.Thin;
stylethin.BorderTop = BorderStyle.Thin;
foreach (var keyValuePair in data)
{
if (!dataConf.ContainsKey(keyValuePair.Key))
{
continue;
}
var conf = dataConf[keyValuePair.Key] as Dictionary <string, object>;
var sheetName = Path.GetFileName(conf["path"].ToString());
var col = 0;
var sheet = book.CreateSheet(sheetName);
Console.WriteLine(keyValuePair.Key);
var value = keyValuePair.Value as Dictionary <string, object>;
foreach (var valuePair in value)
{
WriteCell(sheet, col, 0, valuePair.Key, stylethin);
WriteCell(sheet, col, 1, valuePair.Value.ToString(), stylethin);
var key = valuePair.Value.ToString();
if (dataDef.ContainsKey(key))
{
//enum
CellRangeAddressList addressList = new CellRangeAddressList(
2,
100,
col,
col
);
var dataList = dataDef[key] as Dictionary <string, object>;
string[] converted = dataList.Values.ToList().ConvertAll(_ => _ as string).ToArray();
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet as XSSFSheet);
XSSFDataValidationConstraint dvConstraint
= (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(converted);
XSSFDataValidation dataValidation =
(XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
dataValidation.ShowErrorBox = true;
sheet.AddValidationData(dataValidation);
}
Console.WriteLine(" - " + valuePair.Key + " : " + valuePair.Value.ToString());
col++;
}
var p = masterDataPath + conf["path"].ToString();
if (File.Exists(p))
{
var masterdatajsontext = File.ReadAllText(p);
var masterdatajson = MiniJSON.Json.Deserialize(masterdatajsontext);
var masterList = masterdatajson as List <object>;
var col2 = 0;
var row2 = 2;
var defs = value.Values.ToList();
var intStyle = book.CreateDataFormat().GetFormat("#,##0");
var singleStyle = book.CreateDataFormat().GetFormat("#,##0.0");
foreach (object o in masterList)
{
col2 = 0;
var list = o as Dictionary <string, object>;
foreach (var valuePair in value)
{
if (list.ContainsKey(valuePair.Key))
{
var v = list[valuePair.Key].ToString();
var def = defs[col2].ToString();
if (dataDef.ContainsKey(def))
{
//enum
var dataList = dataDef[def] as Dictionary <string, object>;
if (dataList.ContainsKey(v))
{
v = dataList[v].ToString();
}
}
if (float.TryParse(v, out float arg))
{
WriteCell(sheet, col2, row2, arg, stylethin);
}
else
{
WriteCell(sheet, col2, row2, v, stylethin);
}
}
col2++;
}
row2++;
}
}
}
var defSheet = book.CreateSheet("_def");
var dcol = 0;
foreach (var keyValuePair in dataDef)
{
WriteCell(defSheet, dcol, 0, keyValuePair.Key);
WriteCell(defSheet, dcol + 1, 0, keyValuePair.Key);
WriteCell(defSheet, dcol, 1, "num");
WriteCell(defSheet, dcol + 1, 1, "name");
var row = 2;
var list = keyValuePair.Value as Dictionary <string, object>;
foreach (var valuePair in list)
{
WriteCell(defSheet, dcol, row, valuePair.Key);
WriteCell(defSheet, dcol + 1, row, valuePair.Value.ToString());
row++;
}
dcol += 2;
}
SafeCreateDirectory(bookRootPath);
using (var fs = File.Create(bookPath))
{
book.Write(fs);
}
Console.ReadKey();
}