public Workbook SaveToExcel(string path)
{
var scheme = new PrintPageScheme
{
TopMargin = 0.4,
RightMargin = 0.3,
BottomMargin = 0.4,
LeftMargin = 0.3,
DataRowHeight = 13.5,
DataFontSize = 9,
HeadRowHeight = 13.5,
HeadForegroundColor = System.Drawing.Color.LightBlue, //默认是rgb(0,0,0),但显示为无色，原因未明
HeadFontSize = 9,
TitleRowHeight = 25.5,//20,
TitleFontSize = 16,
TitleFontIsBold = true,
TitleHorizontalAlignment = PFTextAlignmentType.Center,
FootRowHeight = 36.75,
FootFontSize = 11
};
var leftHeadScheme = new PrintPageScheme
{
TopMargin = 0.4,
RightMargin = 0.3,
BottomMargin = 0.4,
LeftMargin = 0.3,
DataRowHeight = 13.5,
DataFontSize = 9,
HeadRowHeight = 13.5,
HeadForegroundColor = System.Drawing.Color.LightGreen, //默认是rgb(0,0,0),但显示为无色，原因未明
HeadFontSize = 9,
TitleRowHeight = 25.5,//20,
TitleFontSize = 16,
TitleFontIsBold = true,
TitleHorizontalAlignment = PFTextAlignmentType.Center,
FootRowHeight = 36.75,
FootFontSize = 11
};
var fileName = Path.GetFileName(path);
var _sheetTitle = Path.GetFileNameWithoutExtension(path);
IExport export = new XlsxExport();
export.Init(null, scheme);// Exporter.FinancialScheme);
Dictionary <int, int> currentHeadRow = new Dictionary <int, int>();
Dictionary <int, int> currentHeadCell = new Dictionary <int, int>();
Dictionary <string, List <int> > fieldIndex = new Dictionary <string, List <int> >();
Dictionary <string, List <int> > fieldIndexLeft = new Dictionary <string, List <int> >();
int titleRowCount = 0;
int titleCellCount = 0;
if (!string.IsNullOrWhiteSpace(_sheetTitle))
{
titleRowCount++;
}
Func <int, int> GetCurrentHeadRow = cell => currentHeadRow.ContainsKey(cell) ? currentHeadRow[cell] : titleRowCount;
Func <int, int> GetCurrentHeadCell = row => currentHeadCell.ContainsKey(row) ? currentHeadCell[row] : titleCellCount;
var currentRow = 0;
var currentCell = 0;
//提前计算leftTitle是为了可以知道top要向右移多少格
var columns = GetTreeTop();
var leftTitle = new List <List <StoreColumn> >();
var leftColumns = GetTreeLeft();
leftColumns.ForEach(a =>
{
if (a.Children.Any() && a.GetAllLeafCount(b => b.visible) < 1)//如果不是叶节点且所有子叶节点都隐藏,那么本父节点也隐藏
{
a.visible = false;
}
});
var maxDepth = new StoreColumn {
Children = leftColumns
}.GetDepth() - 1;
var topBeginCell = maxDepth;
StoreColumnCollection.StoreColumnTo2DArray(ref leftTitle, leftColumns, ref maxDepth);
//标题--wxj20181011
StoreColumn temp = new StoreColumn {
Children = columns
};
int columnCount = temp.GetAllLeafCount(a => a.visible);
var firstData = temp.FirstLeaf(a => true).data;
if (!string.IsNullOrWhiteSpace(_sheetTitle))
{
export.FillData(0, 0, firstData, _sheetTitle);
//export.SetTitleStyle(0, 0, columnCount - 1+ leftTitle.Count, 0);
export.SetTitleStyle(0, 0, columnCount - 1 + (HasLeft() ? leftTitle.Count : 0), 0);
currentRow++;
}
#region 生成top 3秒(后来发现只有调试时特别慢,原因未明)
////生成多行题头
var title = new List <List <StoreColumn> >();
columns.ForEach(a =>
{
if (a.Children.Any() && a.GetAllLeafCount(b => b.visible) < 1)//如果不是叶节点且所有子叶节点都隐藏,那么本父节点也隐藏
{
a.visible = false;
}
});
maxDepth = new StoreColumn {
Children = columns
}.GetDepth() - 1;
StoreColumnCollection.StoreColumnTo2DArray(ref title, columns, ref maxDepth);
var _title = title;
//var topBeginCell = leftTitle.Count;
for (var i = 0; i < _title.Count; i++)
{
currentCell = topBeginCell;
for (var j = 0; j < _title[i].Count; j++)
{
var item = _title[i][j];
if (item.visible == false)
{
continue;
} //隐藏列不导出--wxj20181009
while (currentRow < GetCurrentHeadRow(currentCell))
{
currentCell++;
}
export.FillData(currentCell, currentRow, "title_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的title是null
if (item.rowspan + item.colspan > 2)
{
export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1);
}
if (!string.IsNullOrEmpty(item.data))
{
if (!fieldIndex.ContainsKey(item.data))
{
fieldIndex[item.data] = new List <int>();
}
fieldIndex[item.data].Add(currentCell);
}
for (var k = 0; k < item.colspan; k++)
{
currentHeadRow[currentCell] = GetCurrentHeadRow(currentCell++) + item.rowspan;
}
//resultCount++;
}
currentRow++;
}
#endregion
//设置题头样式
//export.SetHeadStyle(0, 0, currentCell - 1, currentRow - 1);
//export.SetHeadStyle(0, titleRowCount, currentHeadRow.Count - 1, currentRow - 1);//上面那样,当后面的列不是多表头时,背景色只填到最后一个多表头为止
export.SetHeadStyle(topBeginCell, titleRowCount, currentHeadRow.Count + topBeginCell - 1, currentRow - 1);
#region 生成left
////生成left
var _leftTitle = leftTitle;
var leftBeginRow = currentRow;
var dataBeginRow = currentRow;
currentCell = 0;
try
{
for (var i = 0; i < _leftTitle.Count; i++)
{
currentRow = leftBeginRow;
if (currentRow == 7)
{
var aa = "aa";
}
for (var j = 0; j < _leftTitle[i].Count; j++)
{
var item = _leftTitle[i][j];
if (item.visible == false)
{
continue;
} //隐藏列不导出--wxj20181009
while (currentCell < GetCurrentHeadCell(currentRow))
{
currentRow++;
}
export.FillData(currentCell, currentRow, "leftTitle_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的leftTitle是null
if (item.rowspan + item.colspan > 2)
{
//export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1);
export.MergeCell(currentCell, currentRow, currentCell + item.rowspan - 1, currentRow + item.colspan - 1);//这里应该要改(和top比是应该反过来)--benjamin todo
}
if (!string.IsNullOrEmpty(item.data))
{
if (!fieldIndexLeft.ContainsKey(item.data))
{
fieldIndexLeft[item.data] = new List <int>();
}
fieldIndexLeft[item.data].Add(currentRow);
}
for (var k = 0; k < item.colspan; k++)
{
currentHeadCell[currentRow] = GetCurrentHeadCell(currentRow++) + item.rowspan;
}
//resultCount++;
}
currentCell++;
}
}
catch (Exception e)
{
throw e;
//var aa = "a";
}
#endregion
//设置left题头样式
//export.SetHeadStyle(0, 0, currentCell - 1, currentRow - 1);
//export.SetHeadStyle(0, titleRowCount, currentHeadRow.Count - 1, currentRow - 1);//上面那样,当后面的列不是多表头时,背景色只填到最后一个多表头为止
//export.SetHeadStyle(topBeginCell, titleRowCount, currentHeadRow.Count + topBeginCell - 1, currentRow - 1);
(export as XlsxExport).SetHeadStyle(0, leftBeginRow, currentCell - 1, currentHeadCell.Count + leftBeginRow - 1, leftHeadScheme);
#region 填充数据
var sColumn = new StoreColumn {
Children = columns
};
var sLeftColumn = new StoreColumn {
Children = leftColumns
};
var x = topBeginCell;
//var dataXCount = _pivotTop.Count;
var dataYCount = HasLeft() ? _leftTitle[_leftTitle.Count - 1].Count : 1;//就算没有左,也要有一行
_title[_title.Count - 1].ForEach(c =>
{
for (int y = 0; y < dataYCount; y++)
{
var r = HasLeft() ? _leftTitle[_leftTitle.Count - 1][y] : new StoreColumn();
//var xGroupValues = new List<string>();
//var yGroupValues = new List<string>();
//try
//{
// for (int m = 0; m < _pivotTop.Count; m++)
// {
// xGroupValues.Add(_title[m][x - topBeginCell].title.ToString());//这样有问题的,_title并不是每行的第二维长度都一样--benjamin todo
// }
// for (int m = 0; m < _pivotLeft.Count; m++)
// {
// yGroupValues.Add(_leftTitle[m][y].title.ToString());
// }
// //xGroupValues.Add(_title[0][x - topBeginCell].title.ToString());//data??title--benjamin
// //yGroupValues.Add(_leftTitle[0][y- leftBeginRow].title.ToString());
//}
//catch (Exception e)
//{
// throw e;
// //var a = "a";
//}
var xGroupValues = c.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList();
var yGroupValues = HasLeft() ? r.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList() : null;
var valueField = xGroupValues[xGroupValues.Count - 1];
xGroupValues.Remove(valueField);
var whereList = new List <string>();
for (int m = 0; m < _pivotTop.Count; m++)
{
whereList.Add(string.Format("{0}='{1}'", _pivotTop[m], xGroupValues[m]));
}
if (yGroupValues != null)
{
for (int m = 0; m < _pivotLeft.Count; m++)
{
try
{
whereList.Add(string.Format("{0}='{1}'", _pivotLeft[m], yGroupValues[m]));
}
catch (Exception e)
{
throw e;
//var aa = e;
}
}
}
var valueRows = _dt.Select(string.Join(" and ", whereList));
if (valueRows != null && valueRows.Length > 0)
{
//export.FillData(x, y+ leftBeginRow, r.data + "_" + c.data, valueRows[0][c.data]);
export.FillData(x, y + leftBeginRow, r.data + "_" + c.data, valueRows[0][valueField]);
}
}
//var y = leftBeginRow;
//_leftTitle[_leftTitle.Count - 1].ForEach(r =>
//{
// var xGroupValues = new List<string>();
// var yGroupValues = new List<string>();
// try
// {
// for (int m = 0; m < _pivotTop.Count; m++)
// {
// xGroupValues.Add(_title[m][x - topBeginCell].title.ToString());
// }
// for (int m = 0; m < _pivotLeft.Count; m++)
// {
// yGroupValues.Add(_leftTitle[m][y - leftBeginRow].title.ToString());
// }
// //xGroupValues.Add(_title[0][x - topBeginCell].title.ToString());//data??title--benjamin
// //yGroupValues.Add(_leftTitle[0][y- leftBeginRow].title.ToString());
// }catch(Exception e)
// {
// throw e;
// //var a = "a";
// }
// //var xGroupFields = c.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList();
// ////var yGroupFields = r.data.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries).ToList();
// //var valueField = xGroupFields[xGroupFields.Count - 1];
// //xGroupFields.Remove(valueField);
// var whereList =new List<string>();
// for(int m = 0; m < _pivotTop.Count; m++)
// {
// whereList.Add(string.Format("{0}='{1}'", _pivotTop[m], xGroupValues[m]));
// }
// for (int m = 0; m < _pivotLeft.Count; m++)
// {
// whereList.Add(string.Format("{0}='{1}'", _pivotLeft[m], yGroupValues[m]));
// }
// var valueRows = _dt.Select(string.Join(" and ", whereList));
// if (valueRows != null && valueRows.Length > 0)
// {
// export.FillData(x, y, r.data + "_" + c.data, valueRows[0][c.data]);
// }
// //var value = _dt.Select(string.Join(" and ", whereList))[0][c.data];
// ////var value =_dt.Select(string.Format("{0}='{1}'",_pivotTop[0], xGroupValues[0]))[0][c.data];
// ////var value = PFDataHelper.DataTableGroupBy(
// //// _dt,
// //// PFDataHelper.MergeList(xGroupFields, yGroupFields).ToArray(),
// //// new PFKeyValueCollection<SummaryType> { { valueField, SummaryType.Sum } }
// //// );
// //export.FillData(x, y, r.data + "_" + c.data, value);
// y++;
//});
x++;
});
#endregion
export.SetRowsStyle(topBeginCell, leftBeginRow, currentHeadRow.Count + topBeginCell - 1, HasLeft() ? (currentHeadCell.Count + leftBeginRow - 1) : leftBeginRow);
var tmpEx = export as XlsxExport;
if (tmpEx != null)
{
return(tmpEx.workbook);
//tmpEx.workbook.Save(path);
}
return(null);
}
public Exporter Export(IExport export)
{
int i = 0;
if (_title == null)
{
_title = new List <List <StoreColumn> >();
_title.Add(new List <StoreColumn>());
//PFDataHelper.EachListHeader(_data, (i, field, type) => _title[0].Add(new StoreColumn() { title = field, field = field, rowspan = 1, colspan = 1 }));
PFDataHelper.EachListHeader(_data, (a, field, type) => _title[0].Add(new StoreColumn()
{
title = field,
data = field
//, rowspan = 1, colspan = 1
}));
}
Dictionary <int, int> currentHeadRow = new Dictionary <int, int>();
Dictionary <string, List <int> > fieldIndex = new Dictionary <string, List <int> >();
int titleRowCount = 0;
if (!string.IsNullOrWhiteSpace(_sheetTitle))
{
titleRowCount++;
}
Func <int, int> GetCurrentHeadRow = cell => currentHeadRow.ContainsKey(cell) ? currentHeadRow[cell] : titleRowCount;
var currentRow = 0;
var currentCell = 0;
export.Init(_data, _printPageScheme);
//标题--wxj20181011
var temp = new StoreColumn {
Children = _columns
};
int columnCount = temp.GetAllLeafCount(a => a.visible);
var firstData = temp.FirstLeaf(a => true).data;
if (!string.IsNullOrWhiteSpace(_sheetTitle))
{
export.FillData(0, 0, firstData, _sheetTitle);
export.SetTitleStyle(0, 0, columnCount - 1, 0);
currentRow++;
}
//Stopwatch sw = new Stopwatch();
//sw.Start();
//int resultCount = 0;
#region 3秒(后来发现只有调试时特别慢,原因未明)
////生成多行题头
for (i = 0; i < _title.Count; i++)
{
currentCell = 0;
for (var j = 0; j < _title[i].Count; j++)
{
var item = _title[i][j];
if (item.visible == false)
{
continue;
} //隐藏列不导出--wxj20181009
while (currentRow < GetCurrentHeadRow(currentCell))
{
currentCell++;
}
//export.FillData(currentCell, currentRow, "title_" + item.data, item.title);
export.FillData(currentCell, currentRow, "title_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的title是null
if (item.rowspan + item.colspan > 2)
{
export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1);
}
if (!string.IsNullOrEmpty(item.data))
{
if (!fieldIndex.ContainsKey(item.data))
{
fieldIndex[item.data] = new List <int>();
}
fieldIndex[item.data].Add(currentCell);
}
for (var k = 0; k < item.colspan; k++)
{
currentHeadRow[currentCell] = GetCurrentHeadRow(currentCell++) + item.rowspan;
}
//resultCount++;
}
currentRow++;
}
#endregion
#region 一样是3秒
////生成多行题头
//foreach (var ii in _title)
//{
// currentCell = 0;
// foreach (var j in ii)
// {
// //var item = _title[i][j];
// var item = j;
// if (item.visible == false) { continue; }//隐藏列不导出--wxj20181009
// //if (item.hidden) continue;
// while (currentRow < GetCurrentHeadRow(currentCell))
// currentCell++;
// //export.FillData(currentCell, currentRow, "title_" + item.data, item.title);
// export.FillData(currentCell, currentRow, "title_" + item.data, item.title ?? item.data);//e:\svn\businesssys2018\yjquery.web\areas\bonus\views\reportquery05\treegrid.cshtml里的title是null
// if (item.rowspan + item.colspan > 2)
// export.MergeCell(currentCell, currentRow, currentCell + item.colspan - 1, currentRow + item.rowspan - 1);
// if (!string.IsNullOrEmpty(item.data))
// {
// if (!fieldIndex.ContainsKey(item.data))
// fieldIndex[item.data] = new List<int>();
// fieldIndex[item.data].Add(currentCell);
// }
// for (var k = 0; k < item.colspan; k++)
// currentHeadRow[currentCell] = GetCurrentHeadRow(currentCell++) + item.rowspan;
// }
// currentRow++;
//}
#endregion
//sw.Stop();
//var aa = string.Format("插入{0}条记录共花费{1}毫秒，{2}分钟", resultCount, sw.ElapsedMilliseconds, sw.ElapsedMilliseconds / 1000 / 60);
//设置题头样式
//export.SetHeadStyle(0, 0, currentCell - 1, currentRow - 1);
export.SetHeadStyle(0, titleRowCount, currentHeadRow.Count - 1, currentRow - 1);//上面那样,当后面的列不是多表头时,背景色只填到最后一个多表头为止
////设置数据样式
var dataCount = 0;
if (_data is PagingResult)
{
var data = _data as PagingResult;
var list = data.data as ArrayList;
if (list != null)
{
for (var rowIndex = 0; rowIndex < list.Count; rowIndex++)
{
dataCount++;
}
}
else
{
var list1 = data.data as List <TreeListItem>;
if (list1 != null)
{
(new TreeListItem {
Children = list1
}).EachChild(a => dataCount++);
}
}
}
else
{
PFDataHelper.EachListRow(_data, (a, r) => dataCount++);//原版
}
////export.SetRowsStyle(0, currentRow, currentCell - 1, currentRow + dataCount - 1);
//此句内报错，要优化--benjamin todo
if (!PFDataHelper.IsDebug)
{
export.SetRowsStyle(0, currentRow, currentHeadRow.Count - 1, currentRow + dataCount - 1);//上面那样,当后面的列不是多表头时,边框不见了
}
//填充数据
if (_data is PagingResult)
{
var data = _data as PagingResult;
if (data.data is List <TreeListItem> )
{
export.SetFont(0, currentRow, 0, currentRow + dataCount - 1, "宋体");//默认的Arial字体中,树型的┝等符号对不齐--benjamin20190711
var tree = new TreeListItem();
tree.Children = data.data as List <TreeListItem>;
int rowIndex = 0;
//int colIndex = 0;
var matrix = new TreeMatrix(tree.Children);
tree.EachChild((a, deep) =>
{
//colIndex = 0;
PFDataHelper.EachObjectProperty(a.Data, (b, name, value) =>
{
if (fieldIndex.ContainsKey(name))
{
foreach (int cellIndex in fieldIndex[name])
{
if (_fieldFormatter.ContainsKey(name))
{
value = _fieldFormatter[name].Format(value);
}
//if (colIndex == 0)
if (cellIndex == 0)
{
var line = "";
for (var j = 0; j < deep - 2; j++)
{
//line += string.Format("<div class='{0} {1}'></div>", "tree-tr-linearea ", GetClassByTreeMatrixNetLine(matrix.GetNetLine(j, rowIdx)));
line += matrix.GetNetLineString(j, rowIndex);
}
value = line + PFDataHelper.ObjectToString(value);
//var line = GetClassByTreeMatrixNetLine(matrix.GetNetLine(cellIndex, currentRow))
}
export.FillData(cellIndex, currentRow, name, value);
}
//colIndex++;
}
});
rowIndex++;
currentRow++;
});
}
else
{
var list = data.data as ArrayList;
for (var rowIndex = 0; rowIndex < list.Count; rowIndex++)
{
var rowData = list[rowIndex] as Dictionary <string, object>;
for (i = 0; i < rowData.Count; i++)
{
var name = rowData.ElementAt(i).Key;
var value = rowData.ElementAt(i).Value;
if (fieldIndex.ContainsKey(name))
{
foreach (int cellIndex in fieldIndex[name])
{
if (_fieldFormatter.ContainsKey(name))
{
value = _fieldFormatter[name].Format(value);
}
export.FillData(cellIndex, currentRow, name, value);
}
}
}
currentRow++;
}
}
}
else
{
//原版
PFDataHelper.EachListRow(_data, (rowIndex, rowData) =>
{
PFDataHelper.EachObjectProperty(rowData, (a, name, value) =>
{
if (fieldIndex.ContainsKey(name))
{
foreach (int cellIndex in fieldIndex[name])
{
if (_fieldFormatter.ContainsKey(name))
{
value = _fieldFormatter[name].Format(value);
}
export.FillData(cellIndex, currentRow, name, value);
}
}
});
currentRow++;
});
}
//汇总行
bool hasSummary = false;
i = 0;
int firstSummary = 0;//第一个有汇总的格的位置
string firstSummaryField = "";
new StoreColumn {
Children = _columns
}.EachLeaf(a =>
{
//设置列宽--wxjtodo20190417
if (!PFDataHelper.StringIsNullOrWhiteSpace(a.width))
{
//export.SetColumnWidth(i, PFDataHelper.WebWidthToExcel(a.width).Value);
export.SetColumnWidth(i, a.width);
}
//if (a.excelWidth.HasValue)
//{
// export.SetColumnWidth(i, double.Parse(a.width.Replace("px", "")));
//}
//var column = _title[_title.Count - 1][i];
if (a.visible)
{
var column = a;
if (!hasSummary)
{
firstSummary = i; firstSummaryField = column.data;
}
if (column.summary != null)
{
hasSummary = true;
export.FillData(i, currentRow, column.data, column.summary);
}
i++;
}
});
if (hasSummary)
{
export.FillData(firstSummary - 1, currentRow, firstSummaryField, "合计：");
export.SetRowsStyle(0, currentRow, columnCount - 1, currentRow);//上面那样,当后面的列不是多表头时,边框不见了
currentRow++;
}
//Foot--wxj20181011
if (!string.IsNullOrWhiteSpace(_sheetFoot))
{
export.FillData(0, currentRow, firstData, _sheetFoot);
export.SetFootStyle(0, currentRow, columnCount - 1, currentRow);
//titleRowCount++;
currentRow++;
}
_exporter = export;
//_fileStream = export.SaveAsStream();
_suffix = export.suffix;
if (string.IsNullOrEmpty(_fileName))
{
_fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
}
return(this);
}