/* * public static void JoinTable(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 * Range range = GetExcelRange(sheet, block.startColIndex + block.joinat + 1, * block.startRowIndex - joinedRows, * block.colCount,block.rowCount) ; * range.Copy ( * GetExcelRange(sheet, blocklastColum + 1, firstBlock.startRowIndex, block.colCount, block.rowCount)); * * * range.EntireRow.Delete (Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp) ; * 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 ; * * Range leftRange = GetExcelRange (sheet, blocklastColum, rowIndex, 1, 1) ; * if (leftRange.MergeArea != null) * leftRange = GetExcelRange (sheet, leftRange.MergeArea.Column, leftRange.MergeArea.Row, 1,1) ; * Range rightRange = GetExcelRange(sheet, blocklastColum + 1, rowIndex, 1, 1); * if (rightRange.MergeArea != null) * rightRange = GetExcelRange(sheet, rightRange.MergeArea.Column, rightRange.MergeArea.Row, 1, 1); * * if (leftRange.Text.Equals (rightRange.Text)) * { * // Merge * GetExcelRange(sheet, leftRange.Column, * leftRange.Row, * rightRange.Column + rightRange.Columns.Count - leftRange.Column, * * Math.Min(rightRange.Rows.Count, leftRange.Rows.Count) * ).Merge (true) ; * * hasMerged = true ; * } * } * * if (! hasMerged) * break ; * } // end for * } // end if * blocklastColum += block.colCount - block.joinat ; * * } * } * } */ public static void Clear(Spire.Xls.Worksheet sheet, int startRowIndex) { // sheet.get_Range ("A1", "B1").EntireColumn.Delete (XlDeleteShiftDirection.xlShiftToLeft) ; sheet.DeleteColumn(1); sheet.DeleteColumn(1); // sheet.DeleteColumn (1); // sheet.get_Range ("A1", "A1").EntireColumn.Delete (XlDeleteShiftDirection.xlShiftToLeft) ; // sheet.get_Range (RangeHelper.MakeCellName (1, 1), RangeHelper.MakeCellName (1, startRowIndex - 1)).EntireRow.Delete (Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp) ; for (int i = 1; i < startRowIndex - 1; i++) { sheet.DeleteRow(1); } }
/* * 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; } } }