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