private static TplLine ParseLine(Worksheet sheet, TplBlock block, int startCol, int startRow, int colCount) { TplLine line = new TplLine(); line.tplRange = RangeHelper.GetRange(sheet, startCol, startRow, colCount, 1); for (int colIndex = 0; colIndex < colCount; colIndex++) { Range range = RangeHelper.GetCell(sheet, startCol + colIndex, startRow); TplCell cell = new TplCell(); cell.acrossColumns = 1; cell.tplRange = range; cell.lastColIndex = colIndex + startCol; string text = range.Value2 as string; if (!string.IsNullOrEmpty(text)) { ParseCell(block, line, cell, text.Trim()); } line.cellList.Add(cell); } return(line); }
public void FillTemplate() { DateTime now = DateTime.Now; for (int i = 0; i < sheetList.Count; i++) { ReportSheetTemplate tpl = sheetList [i]; tpl.FillTemplate(); } // setup chart for (int i = 0; i < sheetList.Count; i++) { ReportSheetTemplate tpl = sheetList [i]; for (int j = 0; j < tpl.blockList.Count; j++) { TplBlock block = tpl.blockList [j]; if (!block.isChart) { continue; } block.SetupChart(); } } for (int i = 0; i < sheetList.Count; i++) { ReportSheetTemplate tpl = sheetList[i]; for (int j = 0; j < tpl.blockList.Count; j++) { TplBlock block = tpl.blockList[j]; Console.WriteLine("Block: " + block.name + " gKeyList: " + block.gkeyList.Count + " ValueList: " + block.holder.valueList.Count); } } Console.WriteLine("GetColVlaue Call Times: " + RangeHelper.getColValueCount); Console.WriteLine("GetColVlaueByIndex Call Times: " + RangeHelper.getColValueByIndexCount); Console.WriteLine("getReusedValueCallCount Call Times: " + ReusedKey.getReusedValueCallCount); Console.WriteLine("columnNameFindCount Call Times: " + ReusedKey.columnNameFindCount); Console.WriteLine("valueReusedCount Call Times: " + ReusedKey.valueReusedCount); Console.WriteLine("ReusedKeyPool Size: " + ReusedKey.keyPool.Count); Console.WriteLine("GetRange Call Times: " + RangeHelper.getRangeCallTimes); Console.WriteLine("UpdateCellValue Call Times: " + RangeHelper.getRangeCallTimes); Console.WriteLine("insertCopyRange Call Times: " + RangeHelper.insertCopyRangeCallTimes); Console.WriteLine("Total time: " + (DateTime.Now.Subtract(now)).TotalMilliseconds + "ms."); }
private void RefreshLineTplRanges(TplBlock block, int colCount) { for (int i = 0; i < block.lineList.Count; i++) { TplLine line = block.lineList [i]; line.tplRange = RangeHelper.GetRange(line.tplRange.Worksheet, line.tplRange.Column, line.tplRange.Row, line.tplCellCount + insertCount, colCount ); line.tplCellCount += colCount; } }
private TplBlock FindDataBlock(string blockName) { for (int i = 0; i < sheetList.Count; i++) { ReportSheetTemplate tpl = sheetList [i]; for (int j = 0; j < tpl.blockList.Count; j++) { TplBlock block = tpl.blockList [j]; if (block.name == blockName) { return(block); } } } return(null); }
public int CheckColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueIndex) { // if grouped Value changed. // if value has be inserted. // Insert Cell At EachLine // Update TplLine Info. int startIndex = IsNeedNewCol(currentRowIndex, holder, table, valueIndex); if (startIndex < 0) { return(0); } // Console.WriteLine("---- Start of " + valueIndex); InsertColumn(block, holder, table, valueIndex, true); return(1); }
public void LoadTemplate(DBO db, Workbook book, Dictionary <string, object> paramMap) { for (int i = 0; i < book.Worksheets.Count; i++) { Worksheet sheet = book.Worksheets [i]; ReportSheetTemplate tpl = TplLoader.ParseTemplate(sheet, i + 1); sheetList.Add(tpl); /* tpl.startRowIndex = 300; */ tpl.dset = dataSet; tpl.paramMap = paramMap; tpl.LoadDataSource(db); } for (int i = 0; i < sheetList.Count; i++) { ReportSheetTemplate tpl = sheetList [i]; for (int j = 0; j < tpl.blockList.Count; j++) { TplBlock block = tpl.blockList [j]; if (!block.isChart) { continue; } if (block.chartDataBlock != null) { continue; } // find block in each sheet. block.chartDataBlock = FindDataBlock(block.chartDataBlockName); } } return; }
public void CheckEachColumn(TplBlock block, GroupDataHolder holder, int currentRowIndex, System.Data.DataTable table, int valueIndex) { for (int i = 0; i < gCols; i++) { bool colInserted = false; // Check each cell in this columns for (int j = 0; j < block.lineList.Count; j++) { TplLine line = block.lineList [j]; TplCell cell = line.cellList [startCellIndex + i]; if (cell.align != GroupAlign.hGroup) { continue; } if (!colInserted) { bool needNew = cell.IsNeedNewCell(holder, cell.hgOption, 0, currentRowIndex, table, valueIndex); if (needNew) { InsertOneColumn(block, i, holder, table, valueIndex, false); colInserted = true; } } else { if ((cell.hgOption & InsertOption.BeforeChange) != 0) { continue; } // set last grouped value cell.lastGroupedValue = cell.GetGroupValue(holder, table, valueIndex); } } } }
private static SearchKey GetGroupedKeys(TplBlock block, TplLine line) { SearchKey root = null; SearchKey pkey = null; // get Line cell first for (int i = 0; i < line.cellList.Count; i++) { TplCell leftCell = line.cellList [i]; if (leftCell.align != GroupAlign.vGroup) { continue; } SearchKey key = new SearchKey(); key.colName = leftCell.tplGroupColName; if (root == null) { root = key; } if (pkey == null) { pkey = key; } else { pkey.nextKey = key; pkey = key; } } for (int i = 0; i < block.lineList.Count; i++) { TplLine l = block.lineList [i]; if (l.cellList.Count <= line.cellList.Count) { continue; } TplCell upCell = l.cellList [line.cellList.Count]; if (upCell.align != GroupAlign.hGroup) { continue; } SearchKey key = new SearchKey(); key.colName = upCell.tplGroupColName; if (root == null) { root = key; } if (pkey == null) { pkey = key; } else { pkey.nextKey = key; pkey = key; } } return(root); }
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); return; } if (text [0] != '{') { // as text cell.tplTextContent = text; return; } int i = text.IndexOf('}'); if (i > 0) { if (i + 1 != text.Length) { cell.tplTextContent = text.Substring(i + 1); } text = text.Substring(1, i - 1); } // using text as col name. cell.tplValueColName = text; Dictionary <string, string> pair = ParseKeyValuePair(text); // parse format string cell.tplFormat = GetPairValue(pair, "f"); if (!string.IsNullOrEmpty(cell.tplFormat)) { cell.tplFormat = cell.tplFormat.ToLower(); } // parse Merge option. cell.mOption = ParseMergeOption(GetPairValue(pair, "m")); // parse group options. if (GetPairValue(pair, "vg") != null) { cell.align = GroupAlign.vGroup; cell.tplGroupColName = GetPairValue(pair, "vg").Trim().ToUpper(); } else if (GetPairValue(pair, "hg") != null) { cell.align = GroupAlign.hGroup; cell.tplGroupColName = GetPairValue(pair, "hg").ToUpper(); line.containsHGroup = true; InsertOption option = GetLineInsertOption(GetPairValue(pair, "hgo")); if (option != InsertOption.afterChange && option != InsertOption.BeforeChange) { option = InsertOption.afterChange; } cell.hgOption = option; } // parse value string including formula. string v = GetPairValue(pair, "v"); if (string.IsNullOrEmpty(v)) { if (!string.IsNullOrEmpty(cell.tplGroupColName)) { cell.tplValueColName = cell.tplGroupColName; } return; } //pase default value cell.tplDefaultContent = GetPairValue(pair, "default"); i = v.IndexOf('('); if (i < 0) { cell.tplValueColName = v.Trim().ToUpper(); return; } cell.formula = new CellForumla(); cell.formula.formulaName = i == 0 ? "" : v.Substring(0, i).ToLower(); int i2 = v.IndexOf(')'); if (i2 < 0) { Console.WriteLine("Warning:: formula not closed. [" + v + "]"); v = v.Substring(i + 1); } else { v = v.Substring(i + 1, i2 - i - 1); } v = v.Trim(); string[] colList = v.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); SearchKey pkey = null; GroupValueSearchKey gkey = new GroupValueSearchKey(); gkey.formula = cell.formula.formulaName; cell.formula.keyList.Add(gkey); string colName = ""; for (int j = 0; j < colList.Length; j++) { string colPair = colList [j]; string[] cs = colPair.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries); if (j == 0) { colName = cs [0]; /* gkey.formula = "" ; */ gkey.valueColName = colName.Trim().ToUpper(); continue; } SearchKey key = new SearchKey(); key.colName = cs [0].ToUpper(); if (cs.Length > 1) { key.isFixedValue = true; key.keyValue = cs [1]; } if (key.colName == "%") { key = GetGroupedKeys(block, line); if (key == null) { continue; } } if (pkey == null) { gkey.key = key; pkey = key; } else { pkey.nextKey = key; pkey = key; } while (pkey.nextKey != null) { pkey = pkey.nextKey; } } block.gkeyList.Add(gkey.Copy()); }
public static ReportSheetTemplate ParseTemplate(Worksheet sheet, int sheetIndex) { /* Range topRange = RangeHelper.GetRange (sheet, 1, 1, 1, 1).EntireColumn ; */ ReportSheetTemplate tpl = new ReportSheetTemplate(); tpl.sheet = sheet; // Load pics PicturesCollection pictures = sheet.Pictures; List <Rectangle> pics = new List <Rectangle>(); for (int i = 0; i < pictures.Count; i++) { ExcelPicture picture = pictures [i]; pics.Add(new Rectangle(picture.Left, picture.Top, picture.Width, picture.Height)); } tpl.pics = pics; // clear pictures. sheet.Pictures.Clear(); int lastValuedRowIndex = 1; for (int rowIndex = 1; rowIndex < MAX_ROW_COUNT; rowIndex++) { Range range = RangeHelper.GetRange(sheet, 1, rowIndex, 1, 1); string text = (string)range.Value2; if (string.IsNullOrEmpty(text)) { continue; } lastValuedRowIndex = rowIndex; if (text.Equals("sql", StringComparison.CurrentCultureIgnoreCase)) { // here is a SQL. range = RangeHelper.GetRange(sheet, 2, rowIndex, 1, 1); string tableName = range.Value2 as string; if (string.IsNullOrEmpty(tableName)) { continue; } range = RangeHelper.GetRange(sheet, 3, rowIndex, 1, 1); string sql = range.Value2 as string; if (string.IsNullOrEmpty(sql)) { continue; } // add sheet prefix to tableName if (tableName.IndexOf('.') < 0) { tableName = "S" + sheetIndex + "." + tableName; } tpl.sqlList [tableName] = sql; continue; } Dictionary <string, string> blockParams = ParseKeyValuePair(text); TplBlock block = new TplBlock(); block.startColIndex = 2; block.startRowIndex = rowIndex; block.colCount = block.tplColumCount = int.Parse(blockParams ["cols"]); if (blockParams.ContainsKey("name")) { block.name = blockParams ["name"]; } else { block.name = "S" + sheetIndex + ".block" + (tpl.blockList.Count + 1); } // parse chart params if (blockParams.ContainsKey("ischart") && "true".Equals(blockParams ["ischart"])) { block.isChart = true; // parse dataBlock if (blockParams.ContainsKey("datablock")) { block.chartDataBlockName = blockParams["datablock"]; // add sheet prefix to tableName if (block.chartDataBlockName.IndexOf('.') < 0) { block.chartDataBlockName = "S" + sheetIndex + "." + block.chartDataBlockName; } } else { block.chartDataBlockName = "S" + sheetIndex + ".block2"; } // find chartDataBlock for (int i = 0; i < tpl.blockList.Count; i++) { TplBlock blk = tpl.blockList [i]; if (blk.name.Equals(block.chartDataBlockName)) { block.chartDataBlock = blk; } } if (blockParams.ContainsKey("seriesfrom") && "col".Equals(blockParams ["seriesfrom"])) { block.chartSeriesFrom = false; } } int blockRows = block.tplRowCount = int.Parse(blockParams ["rows"]); lastValuedRowIndex += blockRows; if (blockParams.ContainsKey("copy")) { block.copyOnly = "true".Equals(blockParams ["copy"]); } block.tableName = blockParams ["table"]; // add sheet prefix to tableName if (block.tableName.IndexOf('.') < 0) { block.tableName = "S" + sheetIndex + "." + block.tableName; } if (blockParams.ContainsKey("updateallrow")) { block.updateAllRow = "true".Equals(blockParams["updateallrow"]); } if (blockParams.ContainsKey("autofit") && blockParams["autofit"] == "true") { tpl.autoFit = true; } if (blockParams.ContainsKey("joinat")) { if (!int.TryParse(blockParams ["joinat"], out block.joinat)) { block.joinat = -1; } } //if (blockParams.ContainsKey("emptycount")) //{ // if (!int.TryParse(blockParams["emptycount"], out block.defaultEmptyRowsCount)) // block.defaultEmptyRowsCount = 0; //} if (blockParams.ContainsKey("emptytable")) { block.emptyTableName = blockParams["emptytable"]; // add sheet prefix to tableName if (block.emptyTableName.IndexOf('.') < 0) { block.emptyTableName = "S" + sheetIndex + "." + block.emptyTableName; } } if (blockParams.ContainsKey("coltable")) { block.tplColTableName = blockParams["coltable"]; // add sheet prefix to tableName if (block.tplColTableName.IndexOf('.') < 0) { block.tplColTableName = "S" + sheetIndex + "." + block.tplColTableName; } } block.tplRange = RangeHelper.GetRange(sheet, block.startColIndex, block.startRowIndex, block.colCount, blockRows); if (block.copyOnly) // Just return directly. { tpl.blockList.Add(block); continue; } for (int i = 0; i < blockRows; i++) { TplLine line = ParseLine(sheet, block, 3, i + block.startRowIndex, block.colCount); line.tpl = tpl; line.colIndex = 3; line.iOption = GetLineInsertOption(RangeHelper.GetCell(sheet, 2, i + block.startRowIndex).Value2 as string); line.tplCellCount = block.colCount; block.lineList.Add(line); } block.InitDColumn(); if (block.dColumn != null) { block.dColumn.tpl = tpl; } tpl.blockList.Add(block); } tpl.startRowIndex = lastValuedRowIndex + 5; return(tpl); }
public void InsertColumn(TplBlock block, GroupDataHolder holder, DataTable table, int valueIndex, bool hasData) { // do insert if (insertCount > 0) { if (hasData) { // block.startRowIndex ; Range colRange = RangeHelper.GetRange(tplRange.Worksheet, startColIndex + insertCount - gCols, block.startRowIndex, gCols, block.rowCount); // Insert new ; RangeHelper.InsertCopyRange(tplRange.Worksheet, colRange, gCols, block.rowCount, startColIndex + insertCount, block.startRowIndex, XlInsertShiftDirection.xlShiftToRight, tplLastColCount); } // Insert new Col in Template. Range tplColRange = RangeHelper.GetRange(tplRange.Worksheet, startColIndex + insertCount - gCols, block.tplRange.Row, gCols, block.tplRowCount); RangeHelper.InsertCopyRange(tplRange.Worksheet, tplColRange, gCols, block.tplRowCount, startColIndex + insertCount, tplColRange.Row, XlInsertShiftDirection.xlShiftToRight, tplLastColCount); // Refresh Line.TplRange ; RefreshLineTplRanges(block, gCols); block.tplColumCount += gCols; block.colCount += gCols; } // Insert cell into exsit lineList. for (int lineIndex = 0; lineIndex < block.lineList.Count; lineIndex++) { TplLine line = block.lineList [lineIndex]; for (int j = 0; j < gCols; j++) { int cellIndex = startCellIndex + (insertCount > 0 ? (insertCount - gCols) : 0) + j; TplCell cell = line.cellList [cellIndex]; /* if (cell.lastColIndex != nextCloIndex - (insertCount > 0 ? 1 : 0)) */ // if (cell.lastColIndex < nextCloIndex || cell.lastColIndex >= nextCloIndex + gCols) // continue ; // if (lineIndex == 2) // lineIndex = 2 ; if (insertCount > 0) { cell = cell.Copy(); cell.lastColIndex += gCols; line.cellList.Insert(cellIndex + gCols, cell); } if (cell.formula != null) { for (int keyIndex = 0; keyIndex < cell.formula.keyList.Count; keyIndex++) { GroupValueSearchKey gkey = cell.formula.keyList[keyIndex]; SearchKey key = gkey.key; while (key != null) { if (IsGroupedColumn(key.colName)) { key.keyValue = RangeHelper.GetColValue(table, valueIndex, key.colName); key.isFixedValue = true; } key = key.nextKey; } block.gkeyList.Add(gkey.Copy()); if (gkey.key != null) { gkey.key.FillKey(table, valueIndex); } block.holder.AddValue(block.countedMap, gkey, table, valueIndex); } } else if (cell.hgOption != InsertOption.never) { cell.tplTextContent = Convert.ToString(cell.GetValueByIndex(valueIndex, table)); } cell.align = GroupAlign.none; Console.WriteLine("Inserted hg Line[" + lineIndex + "]cell[" + cellIndex + "] = " + cell.formula); /* update Row Value */ if (lineIndex < block.rowCount) { Range cellRange = RangeHelper.GetCell(tplRange.Worksheet, startColIndex + (insertCount /* == 0 ? 0 : insertCount - 1*/) + j, block.startRowIndex + lineIndex); cell.WriteCell(tpl, holder, cellRange, table, valueIndex); } } } // Console.WriteLine ("---- End of " + valueIndex); // increment next nextCloIndex += gCols; insertCount += gCols; }
public void InsertOneColumn(TplBlock block, int colIndex, GroupDataHolder holder, DataTable table, int valueIndex, bool hasData) { if (hasData) { // block.startRowIndex ; Range colRange = RangeHelper.GetRange(tplRange.Worksheet, startColIndex + colIndex, block.startRowIndex, 1, block.rowCount); // Insert new ; RangeHelper.InsertCopyRange(tplRange.Worksheet, colRange, 1, block.rowCount, startColIndex + gCols + insertCount, block.startRowIndex, XlInsertShiftDirection.xlShiftToRight, tplLastColCount); } // Insert new Col in Template. Range tplColRange = RangeHelper.GetRange(tplRange.Worksheet, startColIndex + colIndex, block.tplRange.Row, 1, block.tplRowCount); RangeHelper.InsertCopyRange(tplRange.Worksheet, tplColRange, 1, block.tplRowCount, startColIndex + gCols + insertCount, tplColRange.Row, XlInsertShiftDirection.xlShiftToRight, tplLastColCount); // Refresh Line.TplRange ; RefreshLineTplRanges(block, 1); block.tplColumCount += 1; block.colCount += 1; // Insert cell into exsit lineList. for (int lineIndex = 0; lineIndex < block.lineList.Count; lineIndex++) { TplLine line = block.lineList[lineIndex]; int cellIndex = startCellIndex + colIndex; TplCell cell0 = line.cellList[cellIndex]; TplCell cell = cell0.Copy(); cell.lastColIndex += 1; line.cellList.Insert(startCellIndex + gCols + insertCount, cell); /* * if (cell.useExcelFormula) * { * cell.tplRange = cell0.tplRange ; * } */ if (cell.formula != null) { for (int keyIndex = 0; keyIndex < cell.formula.keyList.Count; keyIndex++) { GroupValueSearchKey gkey = cell.formula.keyList[keyIndex]; if (gkey.rKey == null) { SearchKey key0 = new SearchKey(); key0.colName = gkey.valueColName; gkey.rKey = ReusedKey.FindReusedKey(key0); } SearchKey key = gkey.key; while (key != null) { if (IsGroupedColumn(key.colName)) { key.keyValue = RangeHelper.GetColValue(table, valueIndex, key.colName); key.isFixedValue = true; } key = key.nextKey; } block.gkeyList.Add(gkey.Copy()); if (gkey.key != null) { gkey.key.FillKey(table, valueIndex); } block.holder.AddValue(block.countedMap, gkey, table, valueIndex); } } /* * else if (cell.hgOption != InsertOption.never) * { * // set fixed text * cell.tplTextContent = Convert.ToString(RangeHelper.GetColValue (table, valueIndex, cell.tplValueColName)) ; * } */ cell.align = GroupAlign.none; Console.WriteLine("Inserted hg Line[" + lineIndex + "]cell[" + cellIndex + "] = " + cell.formula); /* update Row Value */ if (lineIndex < block.rowCount) { Range cellRange = RangeHelper.GetCell(tplRange.Worksheet, startColIndex + gCols + insertCount, block.startRowIndex + lineIndex); cell.WriteCell(tpl, holder, cellRange, table, valueIndex); } } // Console.WriteLine ("---- End of " + valueIndex); // increment next nextCloIndex += 1; insertCount++; }
/* * private void ClearExcelReport(bool autoFit, string fileName, List<ReportSheetTemplate> tplList) * { * bool reusedFlag = false; * if (xlapp.Workbooks.Count > 0) * { * reusedFlag = true; * } * xlapp.Workbooks.Open(fileName, * Missing.Value, Missing.Value, Missing.Value, Missing.Value, * Missing.Value, Missing.Value, Missing.Value, Missing.Value, * Missing.Value, Missing.Value, Missing.Value, Missing.Value, * Missing.Value, Missing.Value); * * xlapp.DisplayAlerts = false; * * for (int i = 0; i < xlapp.ActiveWorkbook.Worksheets.Count && * i < tplList.Count; i++) * { * Worksheet worksheet = (Worksheet)xlapp.ActiveWorkbook.Worksheets[i + 1]; * * ReportSheetTemplate tpl = tplList[i]; * JoinTable(worksheet, tpl); * // Clear Data * Clear(worksheet, tpl.startRowIndex); * * if (autoFit || tpl.autoFit) * { * Range range = worksheet.get_Range("A1", "DZ1").EntireColumn; * range.AutoFit(); * } * // .GetType ().GetMethod ("AutoFit").Invoke (range, new object[0]) ; * * } * * // remove warnning sheet. * * IEnumerator e = xlapp.ActiveWorkbook.Worksheets.GetEnumerator(); * while (e.MoveNext()) * { * Worksheet sheet = (Worksheet)e.Current; * * if (sheet.Name.IndexOf("Warning") >= 0) * sheet.Delete(); * } * ((_Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).Activate(); * ((Worksheet)xlapp.ActiveWorkbook.Worksheets[1]).get_Range("A1", "A1").Activate(); * // only save activeWorkBook * xlapp.ActiveWorkbook.Save(); * // only close activeWorkbook ; * xlapp.ActiveWorkbook.Close(true, Missing.Value, Missing.Value); * // xlapp.Workbooks.Close (); * } */ public static void JoinTable(Spire.Xls.Worksheet sheet, ReportSheetTemplate tpl) { if (tpl.blockList.Count < 2) { return; } TplBlock firstBlock = tpl.blockList [1]; int blockRow = firstBlock.startRowIndex; int blocklastColum = firstBlock.startColIndex + firstBlock.colCount /* - * (firstBlock.dColumn == null ? 0 : firstBlock.dColumn.gCols)*/; int joinedRows = 0; for (int i = 2; i < tpl.blockList.Count; i++) { TplBlock block = tpl.blockList [i]; if (block.joinat >= 0 && block.rowCount > 0) { // CopyRangeToFirstTable CellRange range = RangeHelper.GetRange(sheet, block.startColIndex + block.joinat + 1, block.startRowIndex - joinedRows, block.colCount, block.rowCount); range.Copy( RangeHelper.GetRange(sheet, blocklastColum + 1, firstBlock.startRowIndex, block.colCount, block.rowCount)); /* range.EntireRow.Delete (Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp) ; */ // delete rows. for (int k = 0; k < block.rowCount; k++) { sheet.DeleteRow(block.startRowIndex - joinedRows); } joinedRows += block.rowCount; if (block.dColumn != null && block.dColumn.startCellIndex == block.joinat) { // Merge Joined Table Columns. for (int j = 0; j < block.lineList.Count; j++) { TplLine line = block.lineList [j]; if (!line.containsHGroup) { continue; } Boolean hasMerged = false; for (int k = 0; k < line.insertedRowList.Count; k++) { int rowIndex = line.insertedRowList [k]; rowIndex = rowIndex - block.startRowIndex + blockRow; CellRange leftRange = RangeHelper.GetRange(sheet, blocklastColum, rowIndex, 1, 1); if (leftRange.MergeArea != null) { leftRange = RangeHelper.GetRange(sheet, leftRange.MergeArea.Column, leftRange.MergeArea.Row, 1, 1); } CellRange rightRange = RangeHelper.GetRange(sheet, blocklastColum + 1, rowIndex, 1, 1); if (rightRange.MergeArea != null) { rightRange = RangeHelper.GetRange(sheet, rightRange.MergeArea.Column, rightRange.MergeArea.Row, 1, 1); } if (leftRange.Text.Equals(rightRange.Text)) { // Merge RangeHelper.GetRange(sheet, leftRange.Column, leftRange.Row, rightRange.Column + rightRange.Columns.Length - leftRange.Column, Math.Min(rightRange.Rows.Length, leftRange.Rows.Length) ).Merge(); hasMerged = true; } } if (!hasMerged) { break; } } // end for } // end if blocklastColum += block.colCount - block.joinat; } } }
public void FillTemplate() { for (int i = 0; i < blockList.Count; i++) { TplBlock block = blockList [i]; if (block.copyOnly) { Range range = RangeHelper.InsertCopyRange(sheet, block.tplRange, block.tplColumCount, block.tplRowCount, block.startColIndex, startRowIndex + rowCount, XlInsertShiftDirection.xlShiftDown); IEnumerator e = RangeHelper.GetRangeCells(range); while (e.MoveNext()) { Range cell = (Range)e.Current; if (cell.HasMerged) { continue; } string s = cell.Value2 as string; if (s != null && s.StartsWith("#") && s.Length > 1 && paramMap != null) { s = s.Substring(1); string[] s2 = s.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries); object pValue = null; paramMap.TryGetValue(s2 [0], out pValue); string format = ""; if (s2.Length > 1) { format = s2 [1].ToLower(); } RangeHelper.UpdateCellValue(this, cell, pValue, format); } CellRange origin = RangeHelper.GetRange(sheet, cell.Column, cell.Row - startRowIndex - rowCount + block.startRowIndex, 1, 1); if (origin.HasMerged) { // doMerge int col = origin.MergeArea.Column; int mWidth = origin.MergeArea.ColumnCount; int row = origin.MergeArea.Row + startRowIndex + rowCount - block.startRowIndex; int mHeight = origin.MergeArea.RowCount; CellRange mRange = RangeHelper.GetRange(sheet, col, row, mWidth, mHeight); if (!mRange.HasMerged) { mRange.Merge(); } } } rowCount += block.tplRowCount; } else { block.startRowIndex = startRowIndex + rowCount; if (block.isChart) { // chart block should be filled at last. return; } // check cloumn table first if (!string.IsNullOrEmpty(block.tplColTableName) && dset.Tables.Contains(block.tplColTableName)) { block.CreateDColumns(dset.Tables [block.tplColTableName]); } if (!dset.Tables.Contains(block.tableName)) { throw new ArgumentException("DataTable [" + block.tableName + "] of Block [" + block.name + "] not found in DataSet!"); } if (dset.Tables[block.tableName].Rows.Count <= 0 && block.emptyTableName != null) { rowCount += block.FillBlock(dset.Tables[block.emptyTableName]); } else { rowCount += block.FillBlock(dset.Tables [block.tableName]); } } } }