private static SearchKey GetGroupedSearchKey(TplBlock block, TplLine line)
{
SearchKey searchKey = null;
SearchKey nextKey = null;
//水平分组查找同行
foreach (var cell in line.CellList)
{
if (cell.GroupAlign == GroupAlign.Vertical)
{
var cellVGroupKey = new SearchKey {
KeyName = cell.TplGroupColumnName
};
if (searchKey == null)
{
searchKey = cellVGroupKey;
}
if (nextKey == null)
{
nextKey = cellVGroupKey;
}
else
{
//递进赋值NextKey
nextKey.NextKey = cellVGroupKey;
nextKey = cellVGroupKey;
}
}
}
//垂直分组遍历Block行查找同列
foreach (var vLine in block.TplLineList)
{
if (vLine.CellList.Count > line.CellList.Count)
{
var cell = vLine.CellList[line.CellList.Count];
if (cell.GroupAlign == GroupAlign.Horizontal)
{
var cellHGroupKey = new SearchKey {
KeyName = cell.TplGroupColumnName
};
if (searchKey == null)
{
searchKey = cellHGroupKey;
}
if (nextKey == null)
{
nextKey = cellHGroupKey;
}
else
{
nextKey.NextKey = cellHGroupKey;
nextKey = cellHGroupKey;
}
}
}
}
return(searchKey);
}
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);
}
}
}
}
}
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);
}
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);
}
private void RefreshLineTplRanges(TplBlock block, int colCount)
{
foreach (var line in block.TplLineList)
{
line.TplRange = RangeHelper.GetRange(line.TplRange.Worksheet,
line.TplRange.Column, line.TplRange.Row,
line.TplCellCount + InsertCount, colCount);
line.TplCellCount += colCount;
}
}
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);
}
private static GroupValueSearchKey ParseCellFormulaGroupKey(TplBlock block, TplLine line, string formulaName,
string pairValue)
{
var groupKey = new GroupValueSearchKey
{
Formula = formulaName
};
var valueKeyArray = pairValue.Trim()
.Split(new[] { TemplateFlags.CellValueKeySpliter }, StringSplitOptions.RemoveEmptyEntries);
SearchKey nextKey = null;
for (var i = 0; i < valueKeyArray.Length; i++)
{
//GroupKey筛选固定值时
var valueFixedArray = valueKeyArray[i].Split(new[] { TemplateFlags.CellValueFixedSpliter },
StringSplitOptions.RemoveEmptyEntries);
if (i == 0)
{
//第一项Key作为绑定关联列名
groupKey.ValueColName = valueFixedArray[0].Trim().ToUpper();
}
else
{
var searchKey = new SearchKey
{
KeyName = valueFixedArray[0].Trim().ToUpper()
};
if (valueFixedArray.Length > 1)
{
//包含=号赋固定值
searchKey.IsFixedValue = true;
searchKey.KeyValue = valueFixedArray[1];
}
if (searchKey.KeyName == TemplateFlags.CellGroupKeySearchPattern)
{
//从之前添加的分组cells中查找
searchKey = GetGroupedSearchKey(block, line);
if (searchKey == null)
{
continue;
}
}
if (nextKey == null)
{
groupKey.SearchKey = searchKey;
nextKey = searchKey;
}
else
{
nextKey.NextKey = searchKey;
nextKey = searchKey;
}
//nextKey置尾,后续值追加到searchKey尾部
while (nextKey.NextKey != null)
{
nextKey = nextKey.NextKey;
}
}
}
return(groupKey);
}
private static void ParseCell(TplBlock block, TplLine line, TplCell cell, string text)
{
text = text.Trim();
if (text.StartsWith("R1C1:"))
{
cell.UseR1C1Formula = true;
cell.TplTextContent = text.Substring(5);
}
else if (text[0] != TemplateFlags.CellStart)
{
cell.TplTextContent = text;
}
else
{
var index = text.IndexOf(TemplateFlags.CellEnd);
if (index > 0)
{
if ((index + 1) != text.Length)
{
cell.TplTextContent = text.Substring(index + 1);
}
text = text.Substring(1, index - 1);
}
cell.TplValueColName = text;
var pair = ParseKeyValuePair(text);
cell.TplFormat = GetPairValue(pair, TemplateFlags.CellFormat);
if (!string.IsNullOrEmpty(cell.TplFormat))
{
cell.TplFormat = cell.TplFormat.ToLower();
}
cell.MergeOption = ParseMergeOption(GetPairValue(pair, TemplateFlags.CellMergeOption));
#region CellGroupAlign
if (GetPairValue(pair, TemplateFlags.CellVGroup) != null)
{
cell.GroupAlign = GroupAlign.Vertical;
cell.TplGroupColumnName = GetPairValue(pair, TemplateFlags.CellVGroup).Trim().ToUpper();
Console.WriteLine(
string.Format("Contains Vg GroupColName:{0}", cell.TplGroupColumnName));
}
else if (GetPairValue(pair, TemplateFlags.CellHGroup) != null)
{
cell.GroupAlign = GroupAlign.Horizontal;
cell.TplGroupColumnName = GetPairValue(pair, TemplateFlags.CellHGroup).ToUpper();
line.ContainsHGroup = true;
var insertOption = GetLineInsertOption(GetPairValue(pair, TemplateFlags.CellHGroupOption));
if ((insertOption != InsertOption.AfterChange) &&
(insertOption != InsertOption.BeforeChange))
{
insertOption = InsertOption.AfterChange;
}
cell.HgOption = insertOption;
Console.WriteLine(
string.Format("Contains Hg GroupColName:{0},LineInsertOption:{1}", cell.TplGroupColumnName,
insertOption));
}
#endregion CellGroupAlign
var pairValue = GetPairValue(pair, TemplateFlags.CellValue);
if (string.IsNullOrEmpty(pairValue))
{
if (!string.IsNullOrEmpty(cell.TplGroupColumnName))
{
//无值(v)标记时以分组列字段绑定
cell.TplValueColName = cell.TplGroupColumnName;
}
}
else
{
cell.TplDefaultContent = GetPairValue(pair, TemplateFlags.CellDefaultContent);
if (ParseCellFormula(cell, ref pairValue))
{
var groupKey = ParseCellFormulaGroupKey(block, line, cell.Formula.Formula, pairValue);
Console.WriteLine(groupKey.ToString());
cell.Formula.KeyList.Add(groupKey);
block.GroupKeyList.Add(groupKey.Copy());
}
}
}
}
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;
}
private void JoinTable()
{
if (BlockList.Count >= 2)
{
TplBlock block = BlockList[1]; //要合并的第一个Block
int startRowIndex = block.StartParseRowIndex;
int startColumn = block.StartParseColumnIndex + block.ColumnsCount;
int num3 = 0;
for (int i = 2; i < BlockList.Count; i++) //以第一个block为基准,合并其他指定joinat的block
{
TplBlock block2 = BlockList[i];
if ((block2.Joinat >= 0) && (block2.RowsCount > 0))
{
//获取joinat之后的区域
CellRange range = RangeHelper.GetRange(Sheet,
block2.StartParseColumnIndex + block2.Joinat + 1,
block2.StartParseRowIndex - num3,
block2.ColumnsCount,
block2.RowsCount);
CellRange destRange = RangeHelper.GetRange(Sheet, startColumn + 1, block.StartParseRowIndex,
block2.ColumnsCount, block2.RowsCount);
range.Copy(destRange);//block2区域复制到block1右边
Sheet.DeleteRow(range.Row, range.RowCount);
num3 += block2.RowsCount;
if ((block2.DynamicColumn != null) && (block2.DynamicColumn.StartCellIndex == block2.Joinat))
{
foreach (var line in block2.TplLineList)
{
if (line.ContainsHGroup) //block2中包含横向分组的行hg
{
bool flag = false;
foreach (var rowIndex in line.InsertedRowList)
{
int startRow = (rowIndex - block2.StartParseRowIndex) + startRowIndex;
//动态添加移动的起始行
CellRange range2 = RangeHelper.GetRange(Sheet, startColumn, startRow, 1, 1);
//相同行区域的block1最后单元格?
if (range2.MergeArea != null)
{
range2 = RangeHelper.GetRange(Sheet, range2.MergeArea.Column,
//?range2合并区域的起始单元格
range2.MergeArea.Row, 1, 1);
}
CellRange range3 = RangeHelper.GetRange(Sheet, startColumn + 1, startRow, 1, 1);
//block2的第一个单元格
if (range3.MergeArea != null)
{
range3 = RangeHelper.GetRange(Sheet, range3.MergeArea.Column, //第一个单元格的起始区域
range3.MergeArea.Row, 1, 1);
}
if (range2.Text.Equals(range3.Text))
{
RangeHelper.GetRange(Sheet, range2.Column, range2.Row,
(range3.Column + range3.ColumnCount) - range2.Column,
Math.Min(range3.RowCount, range2.RowCount)).Merge(true);
flag = true;
}
}
if (!flag)
{
break;
}
}
}
}
startColumn += block2.ColumnsCount - block2.Joinat; //合并后起始列更新
}
}
}
}
