/// <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 void RangeReference()
{
// (unnamed range) reference list
String reference = "A1:A5";
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(listType, ignoredType, reference, null);
Assert.IsNull(constraint.ExplicitListValues);
Assert.AreEqual("A1:A5", constraint.Formula1);
}
public void NamedRangeReference()
{
// named range list
String namedRange = "MyNamedRange";
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(listType, ignoredType, namedRange, null);
Assert.IsNull(constraint.ExplicitListValues);
Assert.AreEqual("MyNamedRange", constraint.Formula1);
}
public void ListLiteralsQuotesAreStripped_arrayConstructor()
{
// literal list, using array constructor
String literal = "\"one, two, three\"";
String[] expected = new String[] { "one", "two", "three" };
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(expected);
CollectionAssert.AreEqual(expected, constraint.ExplicitListValues);
// Excel and DataValidationConstraint Parser ignore (strip) whitespace; quotes should still be intact
Assert.AreEqual(literal.Replace(" ", ""), constraint.Formula1);
}
public void ListLiteralsQuotesAreStripped_formulaConstructor()
{
// literal list, using formula constructor
String literal = "\"one, two, three\"";
String[] expected = new String[] { "one", "two", "three" };
IDataValidationConstraint constraint = new XSSFDataValidationConstraint(listType, ignoredType, literal, null);
CollectionAssert.AreEqual(expected, constraint.ExplicitListValues);
// Excel and DataValidationConstraint Parser ignore (strip) whitespace; quotes should still be intact
// FIXME: whitespace wasn't stripped
Assert.AreEqual(literal, constraint.Formula1);
}
/// <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);
}
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++;
}
}
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();
}
/// <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);
}
/// <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);
}
/// <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>
/// 导出Excel DataTable
/// </summary>
/// <param name="records">records必须都为DataTable</param>
/// <param name="formatter">Dictionary key:DataTable中的列明此处必须小写 value:EnumColumnTrans</param>
/// <returns></returns>
public byte[] Write(DataTable records, Dictionary <string, ExcelFormatter> formatter = null, string imgBasepath = "")
{
Stopwatch sw = new Stopwatch();
sw.Start();
var workBook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
var sheet = workBook.CreateSheet();
var headerRow = sheet.CreateRow(0);
var cellIndex = 0;
Color lightGrey = Color.FromArgb(221, 221, 221);
ICellStyle cstyle = workBook.CreateCellStyle();
cstyle.Alignment = HorizontalAlignment.Center;
cstyle.IsLocked = true;
// cstyle.FillForegroundColor = new XSSFColor(lightGrey).Indexed;
cstyle.FillForegroundColor = IndexedColors.Grey25Percent.Index;
foreach (var map in Maps)
{
var hcell = headerRow.CreateCell(cellIndex, CellType.String);
hcell.CellStyle = cstyle;
hcell.SetCellValue(map.Name);
cellIndex++;
}
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
var rowIndex = 1;
IDrawing patriarch = sheet.CreateDrawingPatriarch();
bool isimg = false;
foreach (DataRow record in records.Rows)
{
var dr = sheet.CreateRow(rowIndex);
for (int i = 0; i < Maps.Count; i++)
{
string drValue = record[Maps[i].Info.ToString()].ToString();
ICell cell = dr.CreateCell(i);
if (formatter.Any() && formatter.ContainsKey(Maps[i].Info.ToLower()) && formatter[Maps[i].Info.ToLower()] != null)
{
ExcelFormatter excelFormatter = formatter[Maps[i].Info.ToLower()];
if (!string.IsNullOrEmpty(drValue))
{
if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertDownList)
{
cell.SetCellValue(drValue);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateExplicitListConstraint(excelFormatter.DropSource.Split(','));
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, i, i);
XSSFDataValidation dataValidate = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, regions);
sheet.AddValidationData(dataValidate);
}
else if (excelFormatter != null && excelFormatter.ColumnTrans == EnumColumnTrans.ConvertImage)
{
if (File.Exists(@"" + imgBasepath + drValue))
{
if (!isimg)
{
sheet.SetColumnWidth(i, 256 * 20);
isimg = true;
}
dr.HeightInPoints = 90;
byte[] bytes = System.IO.File.ReadAllBytes(@"" + imgBasepath + drValue);
int pictureIdx = workBook.AddPicture(bytes, XSSFWorkbook.PICTURE_TYPE_PNG);
IClientAnchor anchor = new XSSFClientAnchor(100, 50, 0, 0, i, rowIndex, i + 1,
rowIndex + 1);
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(0.3);
}
else
{
cell.SetCellValue("");
}
}
else
{
cell.SetCellValue(FormatterCoulumn(drValue, excelFormatter.ColumnTrans));
}
}
else
{
cell.SetCellValue(drValue);
}
}
else
{
switch (records.Columns[Maps[i].Info].DataType.ToString())
{
case "System.String": //字符串类型
cell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell.SetCellValue(dateV);
//cell.CellStyle =
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
}
rowIndex++;
}
workBook.Write(ms);
byte[] buffer = ms.ToArray();
ms.Close();
sw.Stop();
return(buffer);
}
/// <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();
}
