void insertBorderData(ExcelBorderData borderData)
{
Range range = GetRange(borderData.Range);
foreach (ExcelBorderItem item in borderData.Borders)
{
XlLineStyle lineSyle = (XlLineStyle)item.LineStyle;
XlBorderWeight weight = (XlBorderWeight)item.Weight;
if (item.Index == ExcelBordersIndex.xlAround)
{
range.BorderAround(lineSyle, weight);
}
else if (item.Index == ExcelBordersIndex.xlInside)
{
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = lineSyle;
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = lineSyle;
range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = weight;
range.Borders[XlBordersIndex.xlInsideVertical].Weight = weight;
}
else
{
XlBordersIndex index = (XlBordersIndex)item.Index;
range.Borders[index].Weight = weight;
range.Borders[index].LineStyle = lineSyle;
}
}
}
private static object GetBorder(Range cell, XlBordersIndex index)
{
var border = cell.Borders[index];
var result = new Dictionary <string, object>();
var style = (XlLineStyle)border.LineStyle;
result["style"] = style;
if (style == XlLineStyle.xlLineStyleNone)
{
return(result);
}
result["tint"] = Math.Round((double)border.TintAndShade, 4);
result["weight"] = (XlBorderWeight)border.Weight;
try
{
result["theme"] = border.ThemeColor;
}
catch
{
result["rgb"] = OleToHex(border.Color);
}
return(result);
}
public static void FormatBorder(Range range, Color color, XlBordersIndex edge,
XlLineStyle style = XlLineStyle.xlContinuous,
XlBorderWeight weight = XlBorderWeight.xlThin)
{
var borders = range.Borders[edge];
borders.LineStyle = style;
borders.Color = XlColor(color);
borders.TintAndShade = 0;
borders.Weight = weight;
}
public void SetBorder(VTBorderWeight borderWeight, VTBorderIndex borderIndex = VTBorderIndex.All)
{
if (borderIndex == VTBorderIndex.All)
{
Range.Borders.Weight = GetBorderWeight(borderWeight);
}
else
{
XlBordersIndex xlBorderIndex = GetBorderIndex(borderIndex);
Range.Borders[xlBorderIndex].Weight = GetBorderWeight(borderWeight);
}
}
public void SetBorder(VTBorderStyle borderStyle, VTBorderIndex borderIndex = VTBorderIndex.All)
{
if (borderIndex == VTBorderIndex.All)
{
Range.Borders.LineStyle = GetBorderStyle(borderStyle);
}
else
{
XlBordersIndex xlBorderIndex = GetBorderIndex(borderIndex);
Range.Borders[xlBorderIndex].LineStyle = GetBorderStyle(borderStyle);
}
}
private void AddExternalThickBorders(Range oRng, double weight)
{
var bis = new XlBordersIndex[]
{
XlBordersIndex.xlEdgeTop,
XlBordersIndex.xlEdgeRight,
XlBordersIndex.xlEdgeBottom,
XlBordersIndex.xlEdgeLeft
};
foreach (var bor in bis)
{
oRng.Borders[bor].LineStyle = XlLineStyle.xlContinuous;
oRng.Borders[bor].Weight = weight;
}
}
// Установка одной границы ячейки
public void SetCellBorder(string address, XlBordersIndex index, XlLineStyle lineStyle, XlBorderWeight weight)
{
object fRange = _sheet.GetType().InvokeMember(
"Range", BindingFlags.GetProperty, null, _sheet, new object[] { address });
object border = fRange.GetType().InvokeMember(
"Borders", BindingFlags.GetProperty, null, fRange, new object[] { index });
border.GetType().InvokeMember(
"LineStyle", BindingFlags.SetProperty, null, border, new object[] { lineStyle });
border.GetType().InvokeMember(
"Weight", BindingFlags.SetProperty, null, border, new object[] { weight });
border = null;
}
/// <summary>
/// Gets a Border Object by XlBordersIndex
/// </summary>
/// <param name="BordersIndex"></param>
/// <returns></returns>
public XlBorder this[XlBordersIndex bordersIndex]
{
get
{
object[] paramArray = new object[1];
paramArray[0] = bordersIndex;
object returnValue = InstanceType.InvokeMember("Item", BindingFlags.GetProperty | BindingFlags.OptionalParamBinding, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
if (null == returnValue)
{
return(null);
}
XlBorder newClass = new XlBorder(this, returnValue);
ListChildReferences.Add(newClass);
return(newClass);
}
}
private XlBordersIndex Border(int x)
{
XlBordersIndex r = new XlBordersIndex();
switch (x)
{
case 07: r = XlBordersIndex.xlEdgeLeft; break;
case 08: r = XlBordersIndex.xlEdgeTop; break;
case 09: r = XlBordersIndex.xlEdgeBottom; break;
case 10: r = XlBordersIndex.xlEdgeRight; break;
case 11: r = XlBordersIndex.xlInsideVertical; break;
case 12: r = XlBordersIndex.xlInsideHorizontal; break;
}
return(r);
}
public static void Cell_Border(string cell, string direction, string style, string colour)
{
XlLineStyle xlstyle = new XlLineStyle();
XlBordersIndex xldirection = new XlBordersIndex();
Color xlcolour = new Color();
switch (colour)
{
case "Black":
xlcolour = Color.Black;
break;
case "Blue":
xlcolour = Color.Blue;
break;
case "Orange":
xlcolour = Color.Orange;
break;
case "Red":
xlcolour = Color.Red;
break;
}
switch (direction)
{
case "InsideHorizontal":
xldirection = XlBordersIndex.xlInsideHorizontal;
break;
case "InsideVertical":
xldirection = XlBordersIndex.xlInsideVertical;
break;
case "Bottom":
xldirection = XlBordersIndex.xlEdgeBottom;
break;
case "Left":
xldirection = XlBordersIndex.xlEdgeLeft;
break;
case "Right":
xldirection = XlBordersIndex.xlEdgeRight;
break;
case "Top":
xldirection = XlBordersIndex.xlEdgeTop;
break;
}
switch (style)
{
case "Continuous":
xlstyle = XlLineStyle.xlContinuous;
break;
case "Dot":
xlstyle = XlLineStyle.xlDot;
break;
case "Double":
xlstyle = XlLineStyle.xlDouble;
break;
}
workSheet.Range(cell).Borders[xldirection].LineStyle = xlstyle;
workSheet.Range(cell).Borders[xldirection].Color = ToDouble(xlcolour);
}
public void SetBorder(Range range, XlBordersIndex index, XlLineStyle lineStyle, XlBorderWeight weight)
{
range.Borders[index].LineStyle = lineStyle;
range.Borders[index].Weight = weight;
}
public static void FormatBorder(Range range, Color color, XlBordersIndex edge,
XlLineStyle style = XlLineStyle.xlContinuous,
XlBorderWeight weight = XlBorderWeight.xlThin)
{
var borders = range.Borders[edge];
borders.LineStyle = style;
borders.Color = XlColor(color);
borders.TintAndShade = 0;
borders.Weight = weight;
}
public Border this[XlBordersIndex Index]
{
get
{
_objaParameters = new object[1] { Index };
object objBorder = _objBorders.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, _objBorders, _objaParameters);
if (objBorder == null)
return null;
else
return new Border(objBorder);
}
}
/// <summary>
/// Toggles a border of the specified cells and applies the settings if it is toggled on.
/// </summary>
/// <param name="cellSelection">Indicates the cells to be changed. Works just like Excel. Example: "A:A,1:1,A2:B3,F4,F5"</param>
/// <param name="borderToToggle">Indicates the border that will be toggled.</param>
/// <param name="weightToSet">Indicates the weight the border will be set to if it is being toggled on.</param>
/// <param name="styleToSet">Indicates the line style the border will be set to if it is being toggled on.</param>
public void SetBorders(string cellSelection, XlBordersIndex bordersToToggle, Microsoft.Office.Interop.Excel.XlBorderWeight weightToSet, XlLineStyle styleToSet)
{
SetBorders(ReportGlobals.defaultWorkbookName, ReportGlobals.defaultSheetIndex, cellSelection, bordersToToggle, weightToSet, styleToSet);
}
public void SetBorder(Range range, XlBordersIndex index, XlBorderWeight weight)
{
SetBorder(range, index, XlLineStyle.xlContinuous, weight);
}
/// <summary>
/// Toggles a border of the specified cells and applies the settings if it is toggled on.
/// </summary>
/// <param name="workbookName">The stored index of the workbook.</param>
/// <param name="sheetIndex">The sheet number you wish to alter (note: excel sheets begin with number 1, not 0).</param>
/// <param name="cellSelection">Indicates the cells to be changed. Works just like Excel. Example: "A:A,1:1,A2:B3,F4,F5"</param>
/// <param name="borderToToggle">Indicates the border that will be toggled.</param>
/// <param name="weightToSet">Indicates the weight the border will be set to if it is being toggled on.</param>
/// <param name="styleToSet">Indicates the line style the border will be set to if it is being toggled on.</param>
public void SetBorders(string workbookName, int sheetIndex, string cellSelection, XlBordersIndex borderToToggle, Microsoft.Office.Interop.Excel.XlBorderWeight weightToSet, XlLineStyle styleToSet)
{
List <Range> rangesToAlter = GetRanges(workbookName, sheetIndex, cellSelection);
foreach (Range rangeToAlter in rangesToAlter)
{
if ((XlLineStyle)rangeToAlter.Borders[borderToToggle].LineStyle != XlLineStyle.xlLineStyleNone)
{
rangeToAlter.Borders[borderToToggle].LineStyle = XlLineStyle.xlLineStyleNone;
}
else
{
rangeToAlter.Borders[borderToToggle].LineStyle = styleToSet;
rangeToAlter.Borders[borderToToggle].Weight = weightToSet;
}
}
}
/// <summary>
/// CellBorder constructor.
/// </summary>
///
/// <param name="borderIndex">
/// This border's index.
/// </param>
///
/// <param name="color">
/// This border's color.
/// </param>
///
/// <param name="weight">
/// This border's weight.
/// </param>
public CellBorder(XlBordersIndex borderIndex, XlBorderWeight weight = XlBorderWeight.xlMedium, XlRgbColor?color = null)
{
this.BorderIndex = borderIndex;
this.Color = color;
this.Weight = weight;
}
