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; //合并后起始列更新 } } } }