private void MergeVGroupCells()
{
foreach (var line in TplLineList)
{
var colIndex = line.StartColumnIndex;
foreach (var cell in line.CellList)
{
if ((cell.MergeOption != MergeOption.Up) ||
((cell.GroupAlign != GroupAlign.None) && (cell.GroupAlign != GroupAlign.Always)))
{
colIndex++;
}
else
{
foreach (var startRow in line.InsertedRowList)
{
var currentCellRange = RangeHelper.GetCell(TplRange.Worksheet, colIndex, startRow);
if (!currentCellRange.HasMerged)
{
RangeHelper.MergeRanges(currentCellRange, MergeOption.Up);
}
}
colIndex++;
}
}
}
}
/// <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++;
}
}
private static TplLine ParseLine(Worksheet sheet, TplBlock block, int startCol, int startRow)
{
var colCount = block.ColumnsCount;
var line = new TplLine
{
TplRange = RangeHelper.GetRange(sheet, startCol, startRow, colCount, 1)
};
for (var i = 0; i < colCount; i++)
{
var range = RangeHelper.GetCell(sheet, startCol + i, startRow);
var cell = new TplCell
{
TplRange = range,
LastColIndex = i + startCol
};
var str = range.Value2 as string;
if (!string.IsNullOrEmpty(str))
{
ParseCell(block, line, cell, str.Trim());
}
line.CellList.Add(cell);
}
return(line);
}
/// <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++;
}
}
private void MergeHGroupCells()
{
for (var i = 0; i < TplLineList.Count; i++)
{
var line = TplLineList[i];
if (line.ContainsHGroup)
{
foreach (var startRow in line.InsertedRowList)
{
object objA = null;
var startColIndex = DynamicColumn.StartColIndex;
for (var k = 0; k < DynamicColumn.InsertCount; k++)
{
var currentCellRange = RangeHelper.GetCell(TplRange.Worksheet, startColIndex, startRow);
var objB = currentCellRange.Value2;
if ((objA != null) && Equals(objA, objB))
{
if ((i == (TplLineList.Count - 1)) || TplLineList[i + 1].ContainsHGroup)
{
RangeHelper.MergeRanges(currentCellRange, MergeOption.Left);
}
else if ((k % DynamicColumn.GroupColumnCount) > 0)
{
RangeHelper.MergeRanges(currentCellRange, MergeOption.Left);
}
}
objA = objB;
startColIndex++;
}
var num6 = DynamicColumn.StartCellIndex + DynamicColumn.InsertCount;
for (var m = num6; m < line.CellList.Count; m++)
{
var cell = line.CellList[m];
if (cell.MergeOption == MergeOption.Left)
{
RangeHelper.MergeRanges(
RangeHelper.GetCell(TplRange.Worksheet, startColIndex, startRow), MergeOption.Left);
}
startColIndex++;
}
}
}
}
}
public static ReportSheetTemplate ParseSheetTemplate(Worksheet sheet)
{
var template = new ReportSheetTemplate
{
Sheet = sheet
};
var sheetIndex = sheet.Index + 1;
for (var i = 1; i < TemplateFlags.IndexTemplateEndRow; i++)
{
var str = (string)RangeHelper.GetRange(sheet, 1, i, 1, 1).Value2;
if (!string.IsNullOrEmpty(str))
{
if (str.Equals(TemplateFlags.EmptyFields, StringComparison.CurrentCultureIgnoreCase))
{
var emptyFieldStr = RangeHelper.GetRange(sheet, 2, i, 1, 1).Value2 as string;
if (!string.IsNullOrEmpty(emptyFieldStr))
{
template.EmptyFieldsDict = ParseKeyValuePair(emptyFieldStr);
}
}
else
{
var dict = ParseKeyValuePair(str);
template.AutoFit = dict.ContainsKey(TemplateFlags.BlockAutoFit) &&
dict[TemplateFlags.BlockAutoFit] == "true";
var namePrefix = string.Concat("S", sheetIndex, ".");
//Todo:JoinAt 动态列合并
int joinat;
var block = new TplBlock
{
StartParseColumnIndex = 2,
StartParseRowIndex = i,
ColumnsCount = int.Parse(dict[TemplateFlags.BlockColumnCount]),
TplColumCount = int.Parse(dict[TemplateFlags.BlockColumnCount]),
TplRowCount = int.Parse(dict[TemplateFlags.BlockRowCount]),
Name = dict.ContainsKey(TemplateFlags.BlockName)
? dict[TemplateFlags.BlockName]
: string.Concat(namePrefix + "block", template.BlockList.Count + 1),
DataTableIndex = dict.ContainsKey(TemplateFlags.BlockTable)
? int.Parse(dict[TemplateFlags.BlockTable])
: -1,
TplColumnTableIndex = dict.ContainsKey(TemplateFlags.BlockColumnTalbe)
? int.Parse(dict[TemplateFlags.BlockColumnTalbe])
: -1,
CopyOnly = dict.ContainsKey(TemplateFlags.BlockCopyOnly) &&
dict[TemplateFlags.BlockCopyOnly] == "true",
UpdateAllRow = dict.ContainsKey(TemplateFlags.BlockUpdateAllRow) &&
dict[TemplateFlags.BlockUpdateAllRow] == "true",
Joinat = dict.ContainsKey(TemplateFlags.BlockJoinAt) &&
int.TryParse(dict[TemplateFlags.BlockJoinAt], out joinat)
? joinat
: -1
};
block.TplRange = RangeHelper.GetRange(sheet, block.StartParseColumnIndex, block.StartParseRowIndex,
block.ColumnsCount, block.TplRowCount);
if (block.CopyOnly)
{
template.BlockList.Add(block);
}
else
{
for (var j = 0; j < block.TplRowCount; j++)
{
var startColumn = TemplateFlags.IndexLineContendStartColumn;
var line = ParseLine(sheet, block, startColumn, j + block.StartParseRowIndex);
line.SheetTemplate = template;
line.StartColumnIndex = startColumn;
line.InsertOption = GetLineInsertOption(
RangeHelper.GetCell(sheet, TemplateFlags.IndexLineInsertOptionColumn,
j + block.StartParseRowIndex).Value2 as string);
line.TplCellCount = block.ColumnsCount;
block.TplLineList.Add(line);
}
block.InitDynamicColumn(template);
template.BlockList.Add(block);
}
}
}
}
return(template);
}
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;
}