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