public _ExcelCellsBorder(int startRowNum, int startColNum, int endRowNum, int endColNum, ExcelBordersEdge bordersEdge, ExcelBorderWeight bordersWeight)
{
this.startRowNum = startRowNum;
this.startColNum = startColNum;
this.endRowNum = endRowNum;
this.endColNum = endColNum;
this.bordersEdge = bordersEdge;
this.bordersWeight = bordersWeight;
}
public void AddBorder(ExcelBordersIndex index, ExcelBorderWeight weight, ExcelLineStyle lineStyle)
{
ExcelBorderItem border = new ExcelBorderItem(index, weight, lineStyle);
ExcelBorderItem existingBorder = this[border.Index];
if (null == existingBorder)
{
Add(border);
}
else
{
existingBorder.LineStyle = border.LineStyle;
existingBorder.Weight = border.Weight;
}
}
/// <summary>
/// 设置边框，包括外部和内部
/// </summary>
/// <param name="startRow"></param>
/// <param name="startColumn"></param>
/// <param name="endRow"></param>
/// <param name="endColumn"></param>
/// <param name="styleLine"></param>
/// <param name="borderWeight"></param>
/// <param name="constants"></param>
public void SetCellBorder(int startRow, int startColumn, int endRow, int endColumn,
ExcelStyleLine styleLine = ExcelStyleLine.Continious, ExcelBorderWeight borderWeight = ExcelBorderWeight.Thin, Constants constants = Constants.xlAutomatic)
{
Range range = GetRange(startRow, startColumn, endRow, endColumn);
range.Select();
// 上边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeTop].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeTop].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeTop].ColorIndex = constants;
// 下边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeBottom].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeBottom].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeBottom].ColorIndex = constants;
// 左边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeLeft].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeLeft].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeLeft].ColorIndex = constants;
// 右边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeRight].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeRight].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeRight].ColorIndex = constants;
// 内部水平
range.Borders[(XlBordersIndex)ExcelBordersIndex.InsideHorizontal].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.InsideHorizontal].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.InsideHorizontal].ColorIndex = constants;
// 内部垂直
range.Borders[(XlBordersIndex)ExcelBordersIndex.InsideVertical].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.InsideVertical].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.InsideVertical].ColorIndex = constants;
}
/// <summary>
/// 设置单个单元格边框
/// </summary>
/// <param name="row">单元格行坐标</param>
/// <param name="column">单元格列坐标</param>
/// <param name="styleLine">线条形态</param>
/// <param name="borderWeight">线条粗细</param>
/// <param name="constants"></param>
public void SetCellBorder(int row, int column,
ExcelStyleLine styleLine = ExcelStyleLine.Continious, ExcelBorderWeight borderWeight = ExcelBorderWeight.Thin, Constants constants = Constants.xlAutomatic)
{
Range range = GetRange(row, column);
range.Select();
// 上边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeTop].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeTop].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeTop].ColorIndex = constants;
// 下边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeBottom].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeBottom].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeBottom].ColorIndex = constants;
// 左边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeLeft].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeLeft].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeLeft].ColorIndex = constants;
// 右边框
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeRight].LineStyle = styleLine;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeRight].Weight = borderWeight;
range.Borders[(XlBordersIndex)ExcelBordersIndex.EdgeRight].ColorIndex = constants;
}
public ExcelBorderItem(ExcelBordersIndex index, ExcelBorderWeight weight, ExcelLineStyle lineStyle)
{
Index = index;
Weight = weight;
LineStyle = lineStyle;
}
/// <summary>
/// 设置单元格外边框(上，下，右，左边框)
/// </summary>
/// <param name="range">Range对象</param>
/// <param name="bordersLineStyle">边框线条分隔</param>
/// <param name="bordersWeight">边框线粗细</param>
public void SetCellsOutline(Excel.Range range, ExcelBordersLineStyle bordersLineStyle, ExcelBorderWeight bordersWeight)
{
//Left
SetCellsBorder(range, ExcelBordersEdge.EdgeLeft, bordersLineStyle, bordersWeight);
//Right
SetCellsBorder(range, ExcelBordersEdge.EdgeRight, bordersLineStyle, bordersWeight);
//Top
SetCellsBorder(range, ExcelBordersEdge.EdgeTop, bordersLineStyle, bordersWeight);
//Bottom
SetCellsBorder(range, ExcelBordersEdge.EdgeBottom, bordersLineStyle, bordersWeight);
}
/// <summary>
/// 设置单元格边框(只能设置单个单元格的边框)
/// </summary>
/// <param name="range">Range对象</param>
/// <param name="bordersEdge">单元格边框范围</param>
/// <param name="ExcelBordersEdge">单元格边线</param>
/// <param name="bordersLineStyle">边框线条分隔</param>
/// <param name="bordersWeight">边框线粗细</param>
public void SetCellsBorder(Excel.Range range, ExcelBordersEdge bordersEdge, ExcelBordersLineStyle bordersLineStyle, ExcelBorderWeight bordersWeight)
{
//range.Select();
Excel.Border border = null;
switch (bordersEdge)
{
//左右顶底的线
case ExcelBordersEdge.EdgeLeft:
border = range.Borders[Excel.XlBordersIndex.xlEdgeLeft];
break;
case ExcelBordersEdge.EdgeRight:
border = range.Borders[Excel.XlBordersIndex.xlEdgeRight];
break;
case ExcelBordersEdge.EdgeTop:
border = range.Borders[Excel.XlBordersIndex.xlEdgeTop];
break;
case ExcelBordersEdge.EdgeBottom:
border = range.Borders[Excel.XlBordersIndex.xlEdgeBottom];
break;
//对角线
case ExcelBordersEdge.DiagonalDown:
border = range.Borders[Excel.XlBordersIndex.xlDiagonalDown];
break;
case ExcelBordersEdge.DiagonUp:
border = range.Borders[Excel.XlBordersIndex.xlDiagonalUp];
break;
//边框内部是横竖线(不包括边框)
case ExcelBordersEdge.InsideHorizontal:
border = range.Borders[Excel.XlBordersIndex.xlInsideHorizontal];
break;
case ExcelBordersEdge.InsideVertical:
border = range.Borders[Excel.XlBordersIndex.xlInsideVertical];
break;
case ExcelBordersEdge.LineStyleNone:
//所先范围内所有线都没有
range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone; //xlNone
range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
break;
}
if (border != null)
{
//XlLineStyle
Excel.XlLineStyle mXlLineStyle = Excel.XlLineStyle.xlContinuous;
switch (bordersLineStyle)
{
case ExcelBordersLineStyle.Continious:
mXlLineStyle = Excel.XlLineStyle.xlContinuous;
break;
case ExcelBordersLineStyle.Dash:
mXlLineStyle = Excel.XlLineStyle.xlDash;
break;
case ExcelBordersLineStyle.DashDot:
mXlLineStyle = Excel.XlLineStyle.xlDashDot;
break;
case ExcelBordersLineStyle.DashDotDot:
mXlLineStyle = Excel.XlLineStyle.xlDashDotDot;
break;
case ExcelBordersLineStyle.Dot:
mXlLineStyle = Excel.XlLineStyle.xlDot;
break;
case ExcelBordersLineStyle.Double:
mXlLineStyle = Excel.XlLineStyle.xlDouble;
break;
case ExcelBordersLineStyle.StyleNone:
mXlLineStyle = Excel.XlLineStyle.xlLineStyleNone;
break;
case ExcelBordersLineStyle.SlantDashDot:
mXlLineStyle = Excel.XlLineStyle.xlSlantDashDot;
break;
}
border.LineStyle = mXlLineStyle;
//XlBorderWeight
Excel.XlBorderWeight mXlBorderWeight = Excel.XlBorderWeight.xlThin;
switch (bordersWeight)
{
case ExcelBorderWeight.Hairline:
mXlBorderWeight = Excel.XlBorderWeight.xlHairline;
break;
case ExcelBorderWeight.Medium:
mXlBorderWeight = Excel.XlBorderWeight.xlMedium;
break;
case ExcelBorderWeight.Thick:
mXlBorderWeight = Excel.XlBorderWeight.xlThick;
break;
case ExcelBorderWeight.Thin:
mXlBorderWeight = Excel.XlBorderWeight.xlThin;
break;
}
border.Weight = mXlBorderWeight;
}//end if
}
/// <summary>
/// 设置某范围内所有单元格边框（不包含最左，最右边框,最顶部，最底部）
/// </summary>
/// <param name="StartRowNum">起始行</param>
/// <param name="StartColNum">起始列</param>
/// <param name="EndRowNum">结束行</param>
/// <param name="EndRowNum">结束列</param>
/// <param name="bordersWeight">边框线粗细</param>
public void SetAllCellsBorderExclusiveRLTB(int StartRowNum, int StartColNum, int EndRowNum, int EndColNum, ExcelBorderWeight bordersWeight)
{
for (int iRow = StartRowNum + 1; iRow <= EndRowNum; iRow++)
{
Range range = GetRange(iRow, StartColNum, iRow, EndColNum);
SetCellsBorder(range, ExcelBordersEdge.EdgeTop, ExcelBordersLineStyle.Continious, bordersWeight);
}
for (int iCol = StartColNum; iCol < EndColNum; iCol++)
{
Range range = GetRange(StartRowNum, iCol, EndRowNum, iCol);
SetCellsBorder(range, ExcelBordersEdge.EdgeRight, ExcelBordersLineStyle.Continious, bordersWeight);
}
}
