/// <summary>
/// 获取合并单元格的首格。
/// </summary>
private static ICell GetMergedRegion(this ISheet sheet, ICell cell)
{
List <CellRangeAddress> list = new List <CellRangeAddress>(sheet.NumMergedRegions);
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress m = sheet.GetMergedRegion(i);
try
{
if (m.IsInRange(cell.RowIndex, cell.ColumnIndex))
{
IRow row = sheet.GetRow(m.FirstRow);
if (row != null)
{
return(row.GetCell(m.FirstColumn, MissingCellPolicy.RETURN_BLANK_AS_NULL));
}
}
}
catch
{
continue;
}
}
return(null);
}
/// <summary>
/// 移除合并单元格并返回合并区域“列索引及列宽”
/// </summary>
/// <param name="row">行</param>
/// <returns></returns>
private Dictionary <int, int> ClearMergeRegion(IRow row, int fromCol, int endCol)
{
List <int> regions = new List <int>();
Dictionary <int, int> dict = new Dictionary <int, int>();
ISheet sheet = row.Sheet;
int colCount = 0;
for (int i = fromCol; i <= endCol; i++)
{
for (int j = 0; j < sheet.NumMergedRegions; j++)
{
CellRangeAddress region = sheet.GetMergedRegion(j);
if (region.IsInRange(row.RowNum, i))
{
colCount = region.LastColumn - region.FirstColumn + 1;
regions.Add(j);
dict.Add(i, colCount);
i += colCount;
if (i > row.LastCellNum)
{
break;
}
}
}
}
//避免删除过程中指针问题
regions = regions.OrderByDescending(p => p).ToList();
foreach (var index in regions)
{
sheet.RemoveMergedRegion(index);
}
return(dict);
}
//static string _ColLetter( int col /* 0 origin */) {
// // col = [0...25]
// if( col >= 0 && col <= 25 )
// return ((char)('A' + col)).ToString();
// return "";
//}
//static string ColLetter( int col /* 1 Origin */) {
// if( col < 1 || col > 256 )
// throw new ExcelBadUsageException( "Column out of range; must be between 1 and 256" ); // Excel limits
// col--; // make 0 origin
// // good up to col ZZ
// int col2 = (col / 26) - 1;
// int col1 = (col % 26);
// return _ColLetter( col2 ) + _ColLetter( col1 );
//}
#endregion
#region " RowValues "
private object[] RowValues(int rowNum, int startCol, int numberOfCols)
{
if (mSheet == null)
{
return(null);
}
if (numberOfCols == 1)
{
IRow row = mSheet.GetRow(rowNum);
ICell cell = HSSFCellUtil.GetCell(row, startCol);
return(new object[] { NPOIUtils.GetCellValue(cell) });
}
else
{
var values = new List <object>();
CellRangeAddress range = new CellRangeAddress(rowNum, rowNum, startCol, startCol + numberOfCols - 1);
for (var i = startCol; i < numberOfCols; ++i)
{
var cell = mSheet.GetRow(rowNum).GetCell(i, MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (range.IsInRange(rowNum, i))
{
values.Add((cell != null) ? cell.ToString() : null);
}
}
return(values.ToArray());
}
}
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);
}
public static CellRangeAddress getMergedRegion(ISheet sheet, int rowNum, int cellNum)
{
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
if (merged.IsInRange(rowNum, cellNum))
{
return(merged);
}
}
return(null);
}
/// <summary>
/// 获取单元格所属合并区域
/// </summary>
/// <param name="cell">单元格对象</param>
/// <returns>合并区域</returns>
public static CellRangeAddress GetRange(ICell cell)
{
ISheet sheet = cell.Row.Sheet;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (region.IsInRange(cell.RowIndex, cell.ColumnIndex))
{
return(region);
}
}
return(null);
}
/// <summary>
/// 获取合并单元格的合并行。
/// </summary>
private static CellRangeAddress GetCellRange(this ISheet sheet, ICell cell)
{
List <CellRangeAddress> list = new List <CellRangeAddress>(sheet.NumMergedRegions);
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress m = sheet.GetMergedRegion(i);
if (m.IsInRange(cell.RowIndex, cell.ColumnIndex))
{
return(m);
}
}
return(null);
}
/// <summary>
/// 获取合并信息
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>合并信息</returns>
private static MergedRegionInfo GetRangeInfo(this ICell cell)
{
var sheet = cell.Sheet;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress range = sheet.GetMergedRegion(i);
if (range.IsInRange(cell.RowIndex, cell.ColumnIndex))
{
return(new MergedRegionInfo(i, range.FirstRow, range.LastRow, range.FirstColumn,
range.LastColumn));
}
}
return(new MergedRegionInfo(-1, cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex));
}
/// <summary>
/// 根据数据合并某列的多行
/// </summary>
/// <returns></returns>
public static bool MergeRowsByCellData(ISheet exSheet, int rowIndex, int rowCount, int colIndex, int mergedColumns = 1)
{
//1、删除区域中所有的合并域（否则导出Excel需要修改）
for (int col = colIndex; col < colIndex + mergedColumns; col++)
{
for (int row = rowIndex; row < rowIndex + rowCount; row++)
{
//性能问题会受影响：NPOI没有提供Remove(CellRangeAddress)或更多接口，只能通过索引删除
for (int i = 0; i < exSheet.NumMergedRegions; i++)
{
CellRangeAddress region = exSheet.GetMergedRegion(i);
if (region.IsInRange(row, col))
{
exSheet.RemoveMergedRegion(i);
}
}
}
}
//2、找出行的不同值
List <int> indexlist = new List <int>();
indexlist.Add(rowIndex);
string tmpObj = exSheet.GetRow(rowIndex).GetCell(colIndex).ToString();
for (int i = 1; i < rowCount; i++)
{
//获取相邻列值不同的序号
ICell cell = exSheet.GetRow(i).GetCell(colIndex);
if (cell.ToString() != tmpObj)
{
indexlist.Add(rowIndex + i);
tmpObj = cell.ToString();
}
}
indexlist.Add(rowIndex + rowCount);
for (int i = 0; i < indexlist.Count - 1; i++)
{
if (indexlist[i + 1] - indexlist[i] > 1 || mergedColumns > 1)
{
exSheet.AddMergedRegion(new CellRangeAddress(indexlist[i], indexlist[i + 1] - 1, colIndex, colIndex + mergedColumns - 1));
}
}
return(true);
}
/// <summary>
/// 根据数据合并某列的多行
/// </summary>
/// <param name="dataTable"></param>
/// <param name="field"></param>
/// <param name="exSheet"></param>
/// <param name="rowIndexBase"></param>
/// <param name="rowCount"></param>
/// <returns></returns>
public static bool MergeRowsByData(DataTable dataTable, Field field, ISheet exSheet, int rowIndexBase, int rowCount)
{
//1、删除区域中所有的合并域（否则导出Excel需要修改）
for (int col = field.ColIndex; col < field.ColIndex + field.ColSpan; col++)
{
for (int row = rowIndexBase; row < rowIndexBase + rowCount; row++)
{
//性能问题会受影响：NPOI没有提供Remove(CellRangeAddress)或更多接口，只能通过索引删除
for (int i = 0; i < exSheet.NumMergedRegions; i++)
{
CellRangeAddress region = exSheet.GetMergedRegion(i);
if (region.IsInRange(row, col))
{
exSheet.RemoveMergedRegion(i);
}
}
}
}
//2、找出行的不同值
List <int> indexlist = new List <int>();
indexlist.Add(rowIndexBase);
for (int i = 1; i < dataTable.Rows.Count; i++)
{
//获取相邻列值不同的序号
if (dataTable.Rows[i][field.Name] != dataTable.Rows[i - 1][field.Name])
{
indexlist.Add(rowIndexBase + i);
}
}
indexlist.Add(rowIndexBase + rowCount);
for (int i = 0; i < indexlist.Count - 1; i++)
{
if (indexlist[i + 1] - indexlist[i] > 1 || field.ColSpan > 1)
{
exSheet.AddMergedRegion(new CellRangeAddress(indexlist[i], indexlist[i + 1] - 1, field.ColIndex, field.ColIndex + field.ColSpan - 1));
}
}
return(true);
}
