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);
}