public void TestCreateMultipleRegionsValidation()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateExplicitListConstraint(new string[] { "A" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(0, 0, 0, 0);
cellRangeAddressList.AddCellRangeAddress(0, 1, 0, 1);
cellRangeAddressList.AddCellRangeAddress(0, 2, 0, 2);
XSSFDataValidation dataValidation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList) as XSSFDataValidation;
sheet.AddValidationData(dataValidation);
Assert.AreEqual(new CellRangeAddress(0, 0, 0, 0), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[0]);
Assert.AreEqual(new CellRangeAddress(0, 0, 1, 1), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[1]);
Assert.AreEqual(new CellRangeAddress(0, 0, 2, 2), sheet.GetDataValidations()[0].Regions.CellRangeAddresses[2]);
Assert.AreEqual("A1 B1 C1", dataValidation.GetCTDataValidation().sqref);
}
finally
{
wb.Close();
}
}
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);
}
private string d2007()
{
string fileName = "G:/" + DateTime.Now.Ticks.ToString() + ".xlsx";
XSSFWorkbook wk = new XSSFWorkbook();
ISheet sheet = wk.CreateSheet();
CellRangeAddressList regions = new CellRangeAddressList(0, 5, 2, 2);
CT_DataValidation ctDataValidation = new CT_DataValidation();
ctDataValidation.allowBlank = true;
ctDataValidation.type = ST_DataValidationType.list;
ctDataValidation.showInputMessage = true;
ctDataValidation.showErrorMessage = true;
ctDataValidation.sqref = "C1:C88";
ctDataValidation.formula1 = "\"itemA,itemB,itemC\"";
//ctDataValidation.showDropDown = true;
//XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(new string[] { "itemA", "itemB", "itemC" });
XSSFDataValidation dataValidate = new XSSFDataValidation(regions, ctDataValidation);
//dataValidate= new XSSFDataValidation()
sheet.AddValidationData(dataValidate);
using (FileStream fs = File.OpenWrite(fileName))
{
wk.Write(fs);
}
return(fileName);
}
/// <summary>
/// 为页签序列验证
/// </summary>
/// <param name="sheetName">目标Sheet</param>
/// <param name="rangeName">值域名称</param>
/// <param name="cellRange">应用此验证的范围</param>
public void SetSheetValidationForListConstraint(string sheetName, string rangeName, MergeCellRange cellRange)
{
CellRangeAddressList rangeList = new CellRangeAddressList(cellRange.FirstRowIndex, cellRange.LastRowIndex, cellRange.FirstColumnIndex, cellRange.LastColumnIndex);
ISheet sheet = _workbook.GetSheet(sheetName);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateFormulaListConstraint(rangeName);
XSSFDataValidation validation = (XSSFDataValidation)dataValidationHelper.CreateValidation(constraint, rangeList);
sheet.AddValidationData(validation);
}
private XSSFDataValidation CreateValidation(XSSFSheet sheet)
{
//create the cell that will have the validation applied
IRow row = sheet.CreateRow(0);
row.CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("true");
XSSFDataValidation validation = (XSSFDataValidation)dataValidationHelper.CreateValidation(constraint, new CellRangeAddressList(0, 0, 0, 0));
return(validation);
}
/// <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++;
}
}
public void TestDefaultAllowBlank()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDataValidation validation = CreateValidation(sheet);
sheet.AddValidationData(validation);
List <IDataValidation> dataValidations = sheet.GetDataValidations();
Assert.AreEqual(true, (dataValidations[0] as XSSFDataValidation).GetCTDataValidation().allowBlank);
}
finally
{
wb.Close();
}
}
/// <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);
}
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();
}
