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; }