/// <summary>
/// 设置Excel列头数据（支持多级表头）。
/// <param name="maxLevel">表头的层级数</param>
/// </summary>
private static ISheet SetHeader(IWorkbook export, MDataTable header, MDataColumn columns, out int maxLevel)
{
ISheet sheet = export.CreateSheet("Sheet1");//创建内存Excel
ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index);
if (header != null && header.FindRow(Config_Grid.Field + " like 'mg_%'") != null)
{
Dictionary <int, List <MDataRow> > headerGroup = GetColumnGroup(header);// header 包含mg_
MDataTable[] items = header.Split(Config_Grid.Field + " like 'mg_%'");
CreateMergeHeader(sheet, style, headerGroup, items[1]);
maxLevel = headerGroup.Count;
}
else
{
IRow row = sheet.CreateRow(0);//index代表第N行
if (columns != null)
{
for (int i = 0; i < columns.Count; i++)
{
string title = string.IsNullOrEmpty(columns[i].Description) ? columns[i].ColumnName : columns[i].Description;
ICell cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
else
{
for (int i = 0; i < header.Rows.Count; i++)
{
string title = header.Rows[i].Get <string>(Config_Grid.Title);
ICell cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
maxLevel = 1;
}
return(sheet);
}
/// <summary>
/// 生成Sheet的级联指定
/// </summary>
private static void CreateCascadeSheet(ISheet sheet, MDataTable header, Dictionary <string, string[]> validateData, int maxLevel)
{
Dictionary <string, int> formatdic = new Dictionary <string, int>();
MDataTable[] dt2 = header.Split(Config_Grid.Field + " like 'mg_%'");
header = dt2[1];//去掉多级表头的数据。
for (int i = 0; i < header.Rows.Count; i++)
{
string formater = header.Rows[i].Get <string>(Config_Grid.Formatter);
if (!string.IsNullOrEmpty(formater) && formater.Length > 1 && !formatdic.ContainsKey(formater))
{
formatdic.Add(formater, i);//存储列索引
}
}
int maxRow = 1000;//限制最大行数(07之前版本的excel最大行数为65536，但NOPI似乎没有支持到最大行数,这里设置为50000行,到60000行数据有效性失效)
IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();
for (int i = 0; i < header.Rows.Count; i++)
{
MDataRow dtRow = header.Rows[i];
string formatter = dtRow.Get <string>(Config_Grid.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;
if (validateData.ContainsKey(formatter))
{
regions = new CellRangeAddressList(maxLevel, maxRow, i, i);
string key = formatter.Split('=')[0].Replace("#", "").Replace(" ", "");// "V" + (char)formatter.Length;// formatter.Replace("#", "V");
constraint = dvHelper.CreateFormulaListConstraint(key);
dataValidate = dvHelper.CreateValidation(constraint, regions);
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;
string t = ConvertIndexToChar(parentindex);
for (int im = maxLevel; 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);
}
parentFormatter = item.Key;
break;
}
}
point += 1;
}
if (point.Equals(formatdic.Count))
{
parentFormatter = string.Empty;
}
}
}
}
}
}