internal static bool IsQuoteSht(Excel.Worksheet XlSh) // Returns true if a sheet complies with all the quote requirements
{
string[] LineCodes = { "#BillStart", "BillEnd", "#BSTStart", "#BSTEnd" };
string[] CellNames = { "PoDate", "PoNo", "PoStatus", "PoAmount", "QBranch", "InvNo", "InvDate", "InvNo" }; //Check only required names
Excel.Range LineCodeRange = XlSh.Range["A:A"];
Excel.XlColorIndex TabColor = XlSh.Tab.ColorIndex;
foreach (string LineCode in LineCodes) // Loop through line codes
{
var FindRange = LineCodeRange.Find(What: LineCode);
if (FindRange == null)
{
XlSh.Tab.ColorIndex = TabColor;
return(false);
}
}
foreach (string CellName in CellNames) // Loop through cell names
{
try
{
var NamedRange = XlSh.Names.Item(CellName);
}
catch
{
XlSh.Tab.ColorIndex = TabColor;
return(false);
}
}
XlSh.Tab.Color = Excel.XlRgbColor.rgbAquamarine;
return(true);
}
/// <summary>
/// 绘制指定单元格的边框
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="isDrawTop">是否画上外框</param>
/// <param name="isDrawBottom">是否画下外框</param>
/// <param name="isDrawLeft">是否画左外框</param>
/// <param name="isDrawRight">是否画右外框</param>
/// <param name="isDrawHInside">是否画水平内框</param>
/// <param name="isDrawVInside">是否画垂直内框</param>
/// <param name="isDrawDiagonalDown">是否画斜向下线</param>
/// <param name="isDrawDiagonalUp">是否画斜向上线</param>
/// <param name="lineStyle">线类型</param>
/// <param name="borderWeight">线粗细</param>
/// <param name="color">线颜色</param>
public void CellsDrawFrame(int startRow, int startColumn, int endRow, int endColumn, bool isDrawTop, bool isDrawBottom, bool isDrawLeft,
bool isDrawRight, bool isDrawHInside, bool isDrawVInside, bool isDrawDiagonalDown, bool isDrawDiagonalUp, Microsoft.Office.Interop.Excel.XlLineStyle lineStyle,
Microsoft.Office.Interop.Excel.XlBorderWeight borderWeight, Microsoft.Office.Interop.Excel.XlColorIndex color)
{
//获取画边框的单元格
Range range = m_pExcelApp.get_Range(m_pExcelApp.Cells[startRow, startColumn], m_pExcelApp.Cells[endRow, endColumn]);
//清除所有边框
//range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.无;
//range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.无;
//range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.无;
//range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.无;
//range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle.无;
//range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = LineStyle.无;
//range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle.无;
//range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle.无;
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlLineStyleNone;
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlLineStyleNone;
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlLineStyleNone;
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlLineStyleNone;
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlLineStyleNone;
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone;
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone;
//以下是按参数画边框
if (isDrawTop)
{
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeTop].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeTop].ColorIndex = color;
}
if (isDrawBottom)
{
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeBottom].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeBottom].ColorIndex = color;
}
if (isDrawLeft)
{
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeLeft].ColorIndex = color;
}
if (isDrawRight)
{
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlEdgeRight].Weight = borderWeight;
range.Borders[XlBordersIndex.xlEdgeRight].ColorIndex = color;
}
if (isDrawVInside)
{
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideVertical].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideVertical].ColorIndex = color;
}
if (isDrawHInside)
{
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;
range.Borders[XlBordersIndex.xlInsideHorizontal].ColorIndex = color;
}
if (isDrawDiagonalDown)
{
range.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalDown].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalDown].ColorIndex = color;
}
if (isDrawDiagonalUp)
{
range.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;
range.Borders[XlBordersIndex.xlDiagonalUp].Weight = borderWeight;
range.Borders[XlBordersIndex.xlDiagonalUp].ColorIndex = color;
}
Marshal.ReleaseComObject(range);
}
public void SetBorderAroundInRange(string range, ExcelFile.XlLineStyle lineStyle, ExcelFile.XlBorderWeight weight, ExcelFile.XlColorIndex colorIndex)
{
_excel.get_Range(range).BorderAround(lineStyle, weight, colorIndex);
}
