/// <summary> /// 插入复制单元格区域 /// </summary> /// <param name="sheet">工作表</param> /// <param name="orign">原始区域</param> /// <param name="targetColumn">目标起始列</param> /// <param name="targetRow">目标起始行</param> /// <param name="columns">复制列数</param> /// <param name="rows">复制行数</param> /// <param name="direction">复制方向</param> /// <param name="lastColumnCount">末尾剩余列数(向右复制时先移动,再新增)</param> /// <returns></returns> public static CellRange InsertCopyRange(Worksheet sheet, CellRange orign, int targetColumn, int targetRow, int columns, int rows, InsertRangeDirection direction, int lastColumnCount = 1) { InsertCopyRangeCallTimes++; var destRange = GetRange(sheet, targetColumn, targetRow, columns, rows); if (direction == InsertRangeDirection.Right) { //向右复制时先把剩余列向右移动,并解除目标区域合并 var orignColumnRange = GetRange(sheet, targetColumn, targetRow, lastColumnCount, rows); var destColumnRange = GetRange(sheet, targetColumn + columns, targetRow, lastColumnCount, rows); orignColumnRange.Move(destColumnRange, true, false); destRange = GetRange(sheet, targetColumn, targetRow, columns, rows); destRange.UnMerge(); } orign.Copy(destRange, false, true); #region 制行高,列宽 var orignStartColumn = orign.Column; var orignStartRow = orign.Row; switch (direction) { case InsertRangeDirection.Right: //复制列宽 for (var i = 0; i < columns; i++) { var orignEntireCol = GetEntireCol(sheet, i + orignStartColumn); var targetEntireCol = GetEntireCol(sheet, (i + targetColumn) + 1); try { targetEntireCol.ColumnWidth = orignEntireCol.ColumnWidth; } catch (Exception exception2) { Console.WriteLine("Set ColumnWidth Error: " + exception2); } } break; case InsertRangeDirection.Down: //复制行高 for (var j = 0; j < rows; j++) { var orignEntireRow = GetEntireRow(sheet, j + orignStartRow); var targetEntireRow = GetEntireRow(sheet, j + targetRow); try { targetEntireRow.RowHeight = orignEntireRow.RowHeight; } catch (Exception exception) { Console.WriteLine("Set RowHeight Error: " + exception); } } break; } #endregion 制行高,列宽 return(destRange); }
/* * 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; } } }
/// <summary> /// sheet合并到一个Excel中 /// </summary> /// <param name="model"></param> public static void MergeExcel(ExcelMergeModel model) { string saveName = model.SaveName; if (saveName == null) { MessageBox.Show("保存路径没有填写!!!"); return; } Workbook newbook = new Workbook(); //newbook.SaveToFile("d:/123.xls", ExcelVersion.Version2013); Spire.Xls.Workbook tempbook = null; //创建一个新的workbook对象 newbook.Version = Spire.Xls.ExcelVersion.Version2013; //删除文档中的工作表(新创建的文档默认包含3张工作表) newbook.Worksheets.Clear(); if (newbook.Worksheets.Count == 0) { newbook.CreateEmptySheet(); } newbook.SaveToFile(saveName, ExcelVersion.Version2013); IList <FileNameCustom> fileNameCustoms = model.Files; if (model.MergeModel == MergeSytle.MoreSheet) { tempbook = new Workbook(); foreach (FileNameCustom custom in fileNameCustoms) { tempbook.LoadFromFile(custom.FilePath); //使用AddCopy方法,将文档中的所有工作表添加到新的workbook foreach (Worksheet sheet in tempbook.Worksheets) { newbook.Worksheets.AddCopy(sheet); } } newbook.SaveToFile(saveName, ExcelVersion.Version2013); } else if (model.MergeModel == MergeSytle.OneSheet) { //实例化一个Workbook类,加载Excel文档 for (int index = 0; index < fileNameCustoms.Count; index++) { FileNameCustom fileNameCustom = fileNameCustoms[index]; tempbook = new Workbook(); try { tempbook.LoadFromFile(fileNameCustom.FilePath); } catch (Exception e) { MessageBox.Show("文件有问题,可能有批注:" + fileNameCustom.FilePath); continue; } for (int a = 0; a < tempbook.Worksheets.Count; a++) { //获取第1、2张工作表 Worksheet sheet1 = tempbook.Worksheets[a]; if (newbook.Worksheets.Count <= a) { newbook.CreateEmptySheet(sheet1.Name); } Worksheet newsheet = newbook.Worksheets[a]; //复制第2张工作表内容到第1张工作表的指定区1域中 CellRange range1 = sheet1.AllocatedRange; if (range1.RowCount > model.ReduceStartRowCount + model.ReduceEndRowCount + 1) { //CellRange range = sheet1.Range[model.ReduceStartRowCount + 1, range1.CellsCount, range1.RowCount - model.ReduceEndRowCount, range1.CellsCount]; int cellCount = range1.CellsCount; if (cellCount > 256) { cellCount = 254; } int rowCount = range1.RowCount; if (rowCount > 20000) { rowCount = 20000; } CellRange range = sheet1.Range[model.ReduceStartRowCount + 1, 1, rowCount - model.ReduceEndRowCount, cellCount]; if (newsheet.LastRow == -1) { range.Copy(newsheet.Range[newsheet.LastRow + 2, 1]); try { } catch (Exception e) { MessageBox.Show("文件有问题:" + fileNameCustom.FilePath); } } else { range.Copy(newsheet.Range[newsheet.LastRow + 1, 1]); try { } catch (Exception e) { MessageBox.Show("非常严重问题:" + fileNameCustoms[index - 1].FilePath); return; } } } } //break; } //保存并运行文档 newbook.SaveToFile(saveName, ExcelVersion.Version2013); } }
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; //合并后起始列更新 } } } }