/// <summary>
/// 更新行(合并处理,单元格无计算公式时填充)
/// </summary>
/// <param name="holder">分组数据持有</param>
/// <param name="currentRowIndex">当前行</param>
/// <param name="startCellIndex">起始单元格列索引</param>
/// <param name="table">数据源</param>
/// <param name="valueRowIndex">数据源行</param>
/// <param name="mergeOption">合并方式</param>
/// <param name="updateMergeOnly">只合并(不写数据)</param>
private void UpdateLine(GroupDataHolder holder, int currentRowIndex, int startCellIndex, DataTable table,
int valueRowIndex, MergeOption mergeOption, bool updateMergeOnly)
{
var lastColIndex = CellList[0].LastColIndex;
for (var i = 0; i < CellList.Count; i++)
{
var cell = CellList[i];
var isMerged = false;
//分组合并处理
if ((cell.MergeOption == mergeOption) && (
((i < startCellIndex) || //startCellIndex:第一个需要分组的索引
((i >= startCellIndex) && ((InsertOption & InsertOption.BeforeChange) != InsertOption.Never))) ||
(cell.GroupAlign == GroupAlign.None)))
{
var currentCellRange = RangeHelper.GetCell(TplRange.Worksheet, lastColIndex, currentRowIndex);
isMerged = cell.DoMerge(currentCellRange);
}
//无计算公式时,填充值
if ((!isMerged && !updateMergeOnly) && (cell.Formula == null))
{
var cellRange = RangeHelper.GetCell(TplRange.Worksheet, lastColIndex, currentRowIndex);
cell.WriteCell(SheetTemplate, holder, cellRange, table, valueRowIndex);
}
lastColIndex++;
}
}
public void CheckEachColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, DataTable columnTalbe,
int valueIndex)
{
for (var i = 0; i < GroupColumnCount; i++)
{
var flag = false;
foreach (var line in block.TplLineList)
{
var cell = line.CellList[StartCellIndex + i];
if (cell.GroupAlign == GroupAlign.Horizontal)
{
if (!flag)
{
if (cell.IsNeedNewCell(holder, cell.HgOption, 0, currentRowIndex, columnTalbe, valueIndex))
{
InsertOneColumn(block, i, holder, columnTalbe, valueIndex, false);
flag = true;
}
}
else if ((cell.HgOption & InsertOption.BeforeChange) == InsertOption.Never)
{
cell.LastGroupedValue = cell.GetGroupValue(holder, columnTalbe, valueIndex);
}
}
}
}
}
/// <summary>
/// 是否新增行,返回新增行起始单元格索引(小于0不新增行)
/// </summary>
/// <param name="holder"></param>
/// <param name="currentRowIndex"></param>
/// <param name="table"></param>
/// <param name="valueRowIndex"></param>
/// <returns></returns>
public int IsNeedNewLine(GroupDataHolder holder, int currentRowIndex, DataTable table, int valueRowIndex)
{
if (InsertOption != InsertOption.Never)
{
//数据源第一行且行添加方式为First时新增行
if ((valueRowIndex == 0) && ((InsertOption & InsertOption.OnFirst) != InsertOption.Never))
{
return(0);
}
//数据源最后一行且行添加方式为Last时新增行
if ((valueRowIndex == (table.Rows.Count - 1)) &&
((InsertOption & InsertOption.OnLast) != InsertOption.Never))
{
return(0);
}
if ((((InsertOption & InsertOption.AfterChange) == InsertOption.Never) &&
((InsertOption & InsertOption.BeforeChange) == InsertOption.Never)) &&
((InsertOption & InsertOption.Always) == InsertOption.Never))
{
return(-1);
}
for (var i = 0; i < CellList.Count; i++)
{
var cell = CellList[i];
if ((cell.GroupAlign != GroupAlign.Horizontal) &&
cell.IsNeedNewCell(holder, InsertOption, cell.LastColIndex, currentRowIndex, table,
valueRowIndex))
{
return(i);
}
}
}
return(-1);
}
/// <summary>
/// 获取分组值
/// </summary>
/// <param name="holder"></param>
/// <param name="table"></param>
/// <param name="valueIndex"></param>
/// <returns></returns>
public object GetGroupValue(GroupDataHolder holder, DataTable table, int valueIndex)
{
if (ReusedKey == null)
{
ReusedKey = SearchKey.FindReusedKey(TplGroupColumnName);
}
return(ReusedKey.GetReusedValue(table, valueIndex));
}
public int CheckColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, DataTable dataTable,
int valueIndex)
{
if (IsNeedNewCol(dataTable, valueIndex) < 0)
{
return(0);
}
InsertColumn(block, holder, dataTable, valueIndex, true);
return(1);
}
/// <summary>
/// 单元格有公式时更新行数据.
/// </summary>
/// <param name="holder"></param>
/// <param name="currentRowIndex"></param>
/// <param name="table"></param>
/// <param name="valueRowIndex"></param>
public void UpdateRowData(GroupDataHolder holder, int currentRowIndex, DataTable table, int valueRowIndex)
{
var lastColIndex = CellList[0].LastColIndex;
foreach (var cell in CellList)
{
if (cell.Formula != null)
{
var currentCellRange = RangeHelper.GetCell(TplRange.Worksheet, lastColIndex, currentRowIndex);
cell.WriteCell(SheetTemplate, holder, currentCellRange, table, valueRowIndex);
}
lastColIndex++;
}
}
/// <summary>
/// 获取单元格显示值 优先级:公式->水平分组列添加值->数据源值
/// </summary>
/// <param name="holder"></param>
/// <param name="table"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
private object GetValue(GroupDataHolder holder, DataTable table, int rowIndex)
{
if (Formula != null)
{
return(Formula.GetValue(holder, table, rowIndex));
}
if (HgOption != InsertOption.Never)
{
return(TplTextContent); //插入动态列时更新赋值
}
var tableValue = RangeHelper.GetTableValue(table, rowIndex, TplValueColName);
return(tableValue ?? TplTextContent);
}
public object GetValue(GroupDataHolder holder, DataTable table, int rowIndex)
{
object lastValue = 0;
foreach (var groupKey in KeyList)
{
if (groupKey.SearchKey != null)
{
groupKey.SearchKey.FillKey(table, rowIndex);
}
var groupValue = holder.GetValue(groupKey);
lastValue = Calculate(Formula, lastValue, groupValue);
}
return(lastValue);
}
public TplBlock()
{
GroupKeyList = new List <GroupValueSearchKey>(10000);
CountedMap = new Dictionary <GroupValueSearchKey, bool>(10000);
Holder = new GroupDataHolder();
TplLineList = new List <TplLine>();
Joinat = -1;
LastUsedLineValueIndex = -1;
TplColumCount = 1;
TplRowCount = 1;
DataTableIndex = TplColumnTableIndex = -1;
}
/// <summary>
/// 复制填充行(非分组更新单元格值)
/// </summary>
/// <param name="holder"></param>
/// <param name="currentRowIndex"></param>
/// <param name="table"></param>
/// <param name="valueRowIndex"></param>
/// <returns></returns>
public bool FillLine(GroupDataHolder holder, int currentRowIndex, DataTable table, int valueRowIndex)
{
var startCellIndex = IsNeedNewLine(holder, currentRowIndex, table, valueRowIndex);
if (startCellIndex < 0)
{
return(false);
}
RangeHelper.InsertCopyRange(TplRange.Worksheet, TplRange,
StartColumnIndex, currentRowIndex, CellList.Count, 1,
InsertRangeDirection.Down);
InsertedRowList.Add(currentRowIndex);
UpdateLine(holder, currentRowIndex, startCellIndex, table, valueRowIndex, MergeOption.Left, false);
UpdateLine(holder, currentRowIndex, startCellIndex, table, valueRowIndex, MergeOption.Up, true);
return(true);
}
public bool IsNeedNewCell(GroupDataHolder holder, InsertOption insertOption, int currentColIndex,
int currentRowIndex, DataTable table, int valueRowIndex)
{
if (GroupAlign == GroupAlign.Always)
{
return(true);
}
if (GroupAlign == GroupAlign.None)
{
//无分组时,行添加方式为all时才新增单元格
return((insertOption & InsertOption.Always) != InsertOption.Never);
}
var currentGroupValue = GetGroupValue(holder, table, valueRowIndex);
switch (GroupAlign)
{
case GroupAlign.Vertical:
case GroupAlign.Horizontal:
if ((insertOption & InsertOption.BeforeChange) == InsertOption.Never)
{
//行添加方式不为before时,判断与上一分组值是否相等(不相等则新增)
if ((LastGroupedValue != null) && LastGroupedValue.Equals(currentGroupValue))
{
return(false);
}
LastGroupedValue = currentGroupValue;
return(true);
}
if ((valueRowIndex + 1) < table.Rows.Count)
{
//before时(非数据源最后一行),判断与下一行单元格值是否相等(不相等则新增)
var nextGroupValue = GetGroupValue(holder, table, valueRowIndex + 1);
return(!Equals(currentGroupValue, nextGroupValue));
}
return(true);
}
return(false);
}
/// <summary>
/// 写Sheet表单元格
/// </summary>
/// <param name="tpl"></param>
/// <param name="holder"></param>
/// <param name="currentCellRange"></param>
/// <param name="table"></param>
/// <param name="valueRowIndex"></param>
public void WriteCell(ReportSheetTemplate tpl, GroupDataHolder holder, CellRange currentCellRange,
DataTable table, int valueRowIndex)
{
if (UseR1C1Formula)
{
currentCellRange.FormulaR1C1 = "=" + TplTextContent;
}
else
{
var cellValue = GetValue(holder, table, valueRowIndex);
if ((cellValue == null || cellValue == string.Empty) ||
(cellValue == DBNull.Value))
{
cellValue = TplDefaultContent;
}
RangeHelper.UpdateCellValue(tpl, currentCellRange, cellValue, TplFormat);
if (GroupAlign == GroupAlign.Vertical)
{
//纵向分组才更新最后分组值,水平分组在判断是否新增单元格时更新(before情况)
LastGroupedValue = GetGroupValue(holder, table, valueRowIndex);
}
}
}
public void InsertOneColumn(TplBlock block, int colIndex, GroupDataHolder holder, DataTable columnTalbe,
int valueIndex, bool hasData)
{
if (hasData)
{
var range = RangeHelper.GetRange(TplRange.Worksheet, StartColIndex + colIndex, block.StartParseRowIndex,
1,
block.RowsCount);
RangeHelper.InsertCopyRange(TplRange.Worksheet, range,
(StartColIndex + GroupColumnCount) + InsertCount, block.StartParseRowIndex,
1, block.RowsCount,
InsertRangeDirection.Right, TplLastColumnIndex);
}
var orign = RangeHelper.GetRange(TplRange.Worksheet, StartColIndex + colIndex, block.TplRange.Row, 1,
block.TplRowCount);
RangeHelper.InsertCopyRange(TplRange.Worksheet, orign,
(StartColIndex + GroupColumnCount) + InsertCount, orign.Row,
1, block.TplRowCount,
InsertRangeDirection.Right, TplLastColumnIndex);
RefreshLineTplRanges(block, 1);
block.TplColumCount++;
block.ColumnsCount++;
for (var i = 0; i < block.TplLineList.Count; i++)
{
var line = block.TplLineList[i];
var num2 = StartCellIndex + colIndex;
var tplCell = line.CellList[num2].Copy();
tplCell.LastColIndex++;
line.CellList.Insert((StartCellIndex + GroupColumnCount) + InsertCount, tplCell);
if (tplCell.Formula != null)
{
foreach (var groupKey in tplCell.Formula.KeyList)
{
if (groupKey.ReusedKey == null)
{
groupKey.ReusedKey = SearchKey.FindReusedKey(groupKey.ValueColName);
}
for (var key3 = groupKey.SearchKey; key3 != null; key3 = key3.NextKey)
{
if (IsGroupedColumn(key3.KeyName))
{
key3.KeyValue = RangeHelper.GetTableValue(columnTalbe, valueIndex, key3.KeyName);
key3.IsFixedValue = true;
}
}
block.GroupKeyList.Add(groupKey.Copy());
if (groupKey.SearchKey != null)
{
groupKey.SearchKey.FillKey(columnTalbe, valueIndex);
}
block.Holder.AddValue(block.CountedMap, groupKey, columnTalbe, valueIndex);
}
}
tplCell.GroupAlign = GroupAlign.None;
Console.WriteLine(string.Concat("Inserted hg [", i.ToString().PadLeft(3), "][",
num2.ToString().PadLeft(3), "] = ", tplCell.Formula));
if (i < block.RowsCount)
{
var currentCellRange = RangeHelper.GetCell(TplRange.Worksheet,
(StartColIndex + GroupColumnCount) + InsertCount,
block.StartParseRowIndex + i);
tplCell.WriteCell(Tpl, holder, currentCellRange, columnTalbe, valueIndex);
}
}
InsertCount++;
}
public void InsertColumn(TplBlock block, GroupDataHolder holder, DataTable dataTable, int valueIndex,
bool hasData)
{
if (InsertCount > 0)
{
if (hasData)
{
var range = RangeHelper.GetRange(TplRange.Worksheet,
(StartColIndex + InsertCount) - GroupColumnCount, block.StartParseRowIndex,
GroupColumnCount, block.RowsCount);
//Console.WriteLine(string.Format(
// "orign1:[StartColIndex-{4},InsertCount-{0},groupColumnCount-{1},StartRowIndex-{2},RowCount{3},tplLastColumnIndex{5}]",
// InsertCount, groupColumnCount, block.StartRowIndex, block.RowCount, StartCellIndex, tplLastColumnIndex));
RangeHelper.InsertCopyRange(TplRange.Worksheet, range,
StartColIndex + InsertCount, block.StartParseRowIndex,
GroupColumnCount, block.RowsCount,
InsertRangeDirection.Right,
TplLastColumnIndex);
}
var orign = RangeHelper.GetRange(TplRange.Worksheet, (StartColIndex + InsertCount) - GroupColumnCount,
block.TplRange.Row, GroupColumnCount, block.TplRowCount);
RangeHelper.InsertCopyRange(TplRange.Worksheet, orign,
StartColIndex + InsertCount, orign.Row,
GroupColumnCount, block.TplRowCount,
InsertRangeDirection.Right, TplLastColumnIndex);
RefreshLineTplRanges(block, GroupColumnCount);
block.TplColumCount += GroupColumnCount;
block.ColumnsCount += GroupColumnCount;
}
for (var i = 0; i < block.TplLineList.Count; i++)
{
var line = block.TplLineList[i];
for (var j = 0; j < GroupColumnCount; j++)
{
var num3 = (StartCellIndex + ((InsertCount > 0) ? (InsertCount - GroupColumnCount) : 0)) + j;
var tplCell = line.CellList[num3];
if (InsertCount > 0)
{
tplCell = tplCell.Copy();
tplCell.LastColIndex += GroupColumnCount;
line.CellList.Insert(num3 + GroupColumnCount, tplCell);
}
if (tplCell.Formula != null)
{
foreach (var groupKey in tplCell.Formula.KeyList)
{
for (var searchKey = groupKey.SearchKey; searchKey != null; searchKey = searchKey.NextKey)
{
if (IsGroupedColumn(searchKey.KeyName))
{
searchKey.KeyValue = RangeHelper.GetTableValue(dataTable, valueIndex,
searchKey.KeyName);
searchKey.IsFixedValue = true;
}
}
block.GroupKeyList.Add(groupKey.Copy());
if (groupKey.SearchKey != null)
{
groupKey.SearchKey.FillKey(dataTable, valueIndex);
}
block.Holder.AddValue(block.CountedMap, groupKey, dataTable, valueIndex);
}
}
else if (tplCell.HgOption != InsertOption.Never)
{
tplCell.TplTextContent = Convert.ToString(
RangeHelper.GetTableValue(dataTable, valueIndex, tplCell.TplValueColName));
}
tplCell.GroupAlign = GroupAlign.None;
Console.WriteLine(string.Concat("Inserted hg [", i.ToString().PadLeft(3), "][",
num3.ToString().PadLeft(3), "] = ", tplCell.Formula));
if (i < block.RowsCount)
{
var currentCellRange = RangeHelper.GetCell(TplRange.Worksheet, (StartColIndex + InsertCount) + j,
block.StartParseRowIndex + i);
tplCell.WriteCell(Tpl, holder, currentCellRange, dataTable, valueIndex);
}
}
}
InsertCount += GroupColumnCount;
}