/// <summary>
/// 移除合併儲存格
/// </summary>
/// <param name="worksheet"></param>
/// <param name="rowIndex"></param>
private void RemoveMergeCells(ref XSSFSheet worksheet, int rowIndex)
{
for (int i = 0; i < worksheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == rowIndex)
{
worksheet.RemoveMergedRegion(i);
}
}
}
private static CellRangeAddress GetMergedRegion(XSSFSheet sheet, int rowNum, short cellNum)
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
if (merged.IsInRange(rowNum, cellNum))
{
return(merged);
}
}
return(null);
}
/**
* Shift merged regions
*
* @param startRow the row to start Shifting
* @param endRow the row to end Shifting
* @param n the number of rows to shift
* @return an array of affected cell regions
*/
public List <CellRangeAddress> ShiftMerged(int startRow, int endRow, int n)
{
List <CellRangeAddress> ShiftedRegions = new List <CellRangeAddress>();
NPOI.Util.Collections.HashSet <int> removedIndices = new NPOI.Util.Collections.HashSet <int>();
//move merged regions completely if they fall within the new region boundaries when they are Shifted
int size = sheet.NumMergedRegions;
for (int i = 0; i < size; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
if (merged == null)
{
continue;
}
bool inStart = (merged.FirstRow >= startRow || merged.LastRow >= startRow);
bool inEnd = (merged.FirstRow <= endRow || merged.LastRow <= endRow);
//don't check if it's not within the Shifted area
if (!inStart || !inEnd)
{
continue;
}
//only shift if the region outside the Shifted rows is not merged too
if (!ContainsCell(merged, startRow - 1, 0) && !ContainsCell(merged, endRow + 1, 0))
{
merged.FirstRow = (merged.FirstRow + n);
merged.LastRow = (merged.LastRow + n);
//have to Remove/add it back
ShiftedRegions.Add(merged);
removedIndices.Add(i);
}
}
if (removedIndices.Count > 0)
{
sheet.RemoveMergedRegions(removedIndices);
}
//read so it doesn't get Shifted again
foreach (CellRangeAddress region in ShiftedRegions)
{
sheet.AddMergedRegion(region);
}
return(ShiftedRegions);
}
/// <summary>
/// 获取Table某个TD合并的列数和行数等信息。与Excel中对应Cell的合并行数和列数一致。
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="colIndex">列号</param>
/// <param name="colspan">TD中需要合并的行数</param>
/// <param name="rowspan">TD中需要合并的列数</param>
/// <param name="rowspan">此单元格是否被某个行合并包含在内。如果被包含在内，将不输出TD。</param>
/// <returns></returns>
private void GetTdMergedInfo(int rowIndex, int colIndex, out int colspan, out int rowspan, out bool isByRowMerged)
{
colspan = 1;
rowspan = 1;
isByRowMerged = false;
int regionsCuont = sht.NumMergedRegions;
CellRangeAddress region;
for (int i = 0; i < regionsCuont; i++)
{
region = sht.GetMergedRegion(i);
if (region.FirstRow == rowIndex && region.FirstColumn == colIndex)
{
colspan = region.LastColumn - region.FirstColumn + 1;
rowspan = region.LastRow - region.FirstRow + 1;
return;
}
else if (rowIndex > region.FirstRow && rowIndex <= region.LastRow && colIndex >= region.FirstColumn && colIndex <= region.LastColumn)
{
isByRowMerged = true;
}
}
}
/**
* Shift merged regions
*
* @param startRow the row to start Shifting
* @param endRow the row to end Shifting
* @param n the number of rows to shift
* @return an array of affected cell regions
*/
public List <CellRangeAddress> ShiftMerged(int startRow, int endRow, int n)
{
List <CellRangeAddress> ShiftedRegions = new List <CellRangeAddress>();
//move merged regions completely if they fall within the new region boundaries when they are Shifted
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
bool inStart = (merged.FirstRow >= startRow || merged.LastRow >= startRow);
bool inEnd = (merged.FirstRow <= endRow || merged.LastRow <= endRow);
//don't check if it's not within the Shifted area
if (!inStart || !inEnd)
{
continue;
}
//only shift if the region outside the Shifted rows is not merged too
if (!ContainsCell(merged, startRow - 1, 0) && !ContainsCell(merged, endRow + 1, 0))
{
merged.FirstRow = (merged.FirstRow + n);
merged.LastRow = (merged.LastRow + n);
//have to Remove/add it back
ShiftedRegions.Add(merged);
sheet.RemoveMergedRegion(i);
i = i - 1; // we have to back up now since we Removed one
}
}
//read so it doesn't get Shifted again
foreach (CellRangeAddress region in ShiftedRegions)
{
sheet.AddMergedRegion(region);
}
return(ShiftedRegions);
}
public CellRangeAddress GetMergedRegion(int index)
{
return(_sh.GetMergedRegion(index));
}
/// <summary>
/// 分頁Row拷貝
/// </summary>
/// <param name="worksheet"></param>
/// <param name="worksheet2"></param>
/// <param name="sourceRowNum"></param>
/// <param name="destinationRowNum"></param>
/// <param name="IsCoverRow"></param>
/// <param name="IsRemoveSrcRow"></param>
/// <param name="copyRowHeight"></param>
/// <param name="resetOriginalRowHeight"></param>
private void CopySheetRow(ref XSSFSheet worksheet, ref XSSFSheet worksheet2, int sourceRowNum, int destinationRowNum, bool IsCoverRow = false, bool IsRemoveSrcRow = false, bool copyRowHeight = true, bool resetOriginalRowHeight = true)
{
XSSFRow newRow = worksheet2.GetRow(destinationRowNum) as XSSFRow;
XSSFRow sourceRow = worksheet.GetRow(sourceRowNum) as XSSFRow;
XSSFCell oldCell, newCell;
int i;
if (newRow == null)
{
newRow = worksheet2.CreateRow(destinationRowNum) as XSSFRow;
}
// Loop through source columns to add to new row
for (i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
oldCell = sourceRow.GetCell(i) as XSSFCell;
newCell = newRow.GetCell(i) as XSSFCell;
if (newCell == null)
{
newCell = newRow.CreateCell(i) as XSSFCell;
}
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
for (i = 0; i < worksheet.NumMergedRegions; i++)
{
cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow -
cellRangeAddress.FirstRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet2.AddMergedRegion(newCellRangeAddress);
}
}
//複製行高到新列
if (copyRowHeight)
{
newRow.Height = sourceRow.Height;
}
//重製原始列行高
if (resetOriginalRowHeight)
{
sourceRow.Height = worksheet.DefaultRowHeight;
}
//清掉原列
if (IsRemoveSrcRow == true)
{
worksheet.RemoveRow(sourceRow);
}
}
