C# Snippets
Shape_AddShape
Public Shared Function Shape_AddShape(ByVal objAutoShapeType As Office.MsoAutoShapeType, _
ByVal sngleft As Single, _
ByVal sngtop As Single, _
ByVal sngwidth As Single, _
ByVal sngheight As Single, _
Optional ByVal enumPlacement As Excel.XlPlacement = _
Excel.XlPlacement.xlMove) As Excel.Shape
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim objshape As Excel.Shape
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
objshape = objshapes.AddShape(Type:=objAutoShapeType, _
Left:=sngleft, _
Top:=sngtop, _
Width:=sngwidth, _
Height:=sngheight)
objshape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue
objshape.Placement = enumPlacement
AddShape = objshape
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("AddShape", "clsShape", _
"add the shape '" & objAutoShapeType.ToString & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_ColourBackgroundGet
Public Shared Function Shape_ColourBackgroundGet(ByVal objShape As Excel.Shape) As Integer
Try
If clsError.ErrorFlag() = True Then Exit Function
ColourBackgroundGet = objShape.Fill.BackColor.SchemeColor
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("ColourBackgroundGet", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_CoverShape
Public Shared Function Shape_CoverShape(ByVal sngLeft As Single, _
ByVal sngTop As Single, _
ByVal sngWidth As Single, _
ByVal sngHeight As Single, _
Optional ByVal enumPlacement As Excel.XlPlacement = _
Excel.XlPlacement.xlMove) As Excel.Shape
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim sshapename As String
Dim objshape As Excel.Shape
objshape = AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, sngLeft, sngTop, sngWidth, sngHeight)
Call clsShape.Format(objshape, 23)
objshape.Fill.Transparency = 1.0#
objshape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
objshape.ZOrder(Office.MsoZOrderCmd.msoBringToFront)
objshape.Placement = enumPlacement
CoverShape = objshape
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("CoverShape", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_Format
Public Shared Sub Shape_Format(ByVal objShape As Excel.Shape, _
ByVal iSchemeColour As Integer, _
Optional ByVal bIncludeLine As Boolean = True)
Try
If clsError.ErrorFlag() = True Then Exit Sub
objShape.Fill.Solid()
objShape.Fill.ForeColor.SchemeColor = iSchemeColour
If bIncludeLine = False Then
objShape.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("Format", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Shape_FormatText
Public Shared Sub Shape_FormatText(ByVal objShape As Excel.Shape, _
Optional ByVal sFontName As String = "Arial", _
Optional ByVal iFontSize As Integer = 10)
Try
If clsError.ErrorFlag() = True Then Exit Sub
With objShape.TextFrame.Characters(1, objShape.TextFrame.Characters.Count)
.Font.Name = sFontName
.Font.Size = iFontSize
End With
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("FormatText", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Shape_GroupSelected
Public Function Shape_GroupSelected(ByVal objShape As Excel.Shape) _
As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim sshapeselected As String
sshapeselected = clsShape.Type2Return(objShape)
If sshapeselected = "Group" Then GroupSelected = True
If sshapeselected = "AutoShape" Then GroupSelected = False
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("GroupSelected", "clsShape", _
"determine if the shape '" & objShape.Name & "' is a group.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_InsertImageFromResource
public static void insertImageFromResources(
string cellReference,
Excel.Worksheet worksheet,
string imageName,
int offsetPixel = 0,
bool alignCenter = false)
{
try
{
object o = Properties.Resources.ResourceManager.GetObject(imageName);
System.Drawing.Image image = null;
float left, top, cellHeight;
string fileName = Constants.FOLDER_LOCAL_PATH + imageName + ".png";
if (o == null)
{
return;
}
if (o is System.Drawing.Image)
{
image = o as System.Drawing.Image;
}
if (System.IO.Directory.Exists(Constants.FOLDER_LOCAL_PATH) == false)
{
System.IO.Directory.CreateDirectory(Constants.FOLDER_LOCAL_PATH);
}
System.IO.File.Delete(fileName);
image.Save(fileName, System.Drawing.Imaging.ImageFormat.Png);
if (worksheet == null)
{
return;
}
Excel.Range cells = worksheet.get_Range(cellReference);
- left = System.Convert.ToSingle(cells.Left);
top = System.Convert.ToSingle(cells.Top);
cellHeight = System.Convert.ToSingle(cells.Height);
if (alignCenter && cellHeight > image.Height)
{
top += (cellHeight - image.Height) / 2;
}
// cells.RowHeight = CommonConstant.SINGLE_COMPANY_ROW_HEIGHT_TWO;
cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
System.Windows.Forms.Clipboard.SetDataObject(image, true);
Excel.Shape oInsertedShape;
oInsertedShape = worksheet.Shapes.AddPicture(
fileName,
Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue,
left, top, image.Width, image.Height);
oInsertedShape.LockAspectRatio = Microsoft.Office.Core.MsoTriState.msoTrue;
oInsertedShape.Width = System.Convert.ToSingle(image.Width * 0.75188);
if (imageName == "Horizontal_Bar")
{
oInsertedShape.Width = oInsertedShape.Width - 2;
}
if (imageName == "barclays_logo")
{
oInsertedShape.Top = oInsertedShape.Top + 5; // +12; // 14;
}
if (imageName == "absa_logo")
{
oInsertedShape.Top = oInsertedShape.Top + 5; // +12; // 14;
}
if (imageName == "Vertical_Bar")
{
oInsertedShape.Top = oInsertedShape.Top + 4;
}
cells.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
catch (System.Exception ex)
{
ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
}
}
Shape_IsATextBox
Public Shared Function Shape_IsATextBox(ByVal objShape As Excel.Shape) As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
ShapeIsATextBox = True
'tests to see if you can obtain the length of any text
If objShape.TextFrame.Characters.Text.Length = 0 Then
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
ShapeIsATextBox = False
End Try
End Function
Shape_LineAdd
Public Shared Function Shape_LineAdd(ByVal sngLeftStart As Single, _
ByVal sngTopStart As Single, _
ByVal sngLeftFinish As Single, _
ByVal sngTopFinish As Single) As String
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
objshapes.AddLine(sngLeftStart, sngTopStart, sngLeftFinish, sngTopFinish).Select()
LineAdd = clsShape.SelectedNameGet()
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("LineAdd", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_LineHorizontalAdd
Public Shared Function Shape_LineHorizontalAdd(ByVal sngLeftStart As Single, _
ByVal sngTopStart As Single, _
ByVal sngLength As Single) As String
Try
If clsError.ErrorFlag() = True Then Exit Function
LineHorizontalAdd = clsShape.LineAdd(sngLeftStart, sngTopStart, _
sngLeftStart + sngLength, sngTopStart)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("LineHorizontalAdd", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_LineHorizontalOrVertical
Public Shared Function Shape_LineHorizontalOrVertical(ByVal objShape As Excel.Shape) _
As String
Try
If clsError.ErrorFlag() = True Then Exit Function
If (objShape.Height = 0) Then
LineHorizontalOrVertical = "Horizontal"
Exit Function
End If
If (objShape.Width = 0) Then
LineHorizontalOrVertical = "Vertical"
Exit Function
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("LineHorizontalOrVertical", "clsShape", _
"determine if this line is horizontal or vertical.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_LineVerticalAdd
Public Shared Function Shape_LineVerticalAdd(ByVal sngLeftStart As Single, _
ByVal sngTopStart As Single, _
ByVal sngLength As Single) As String
Try
If clsError.ErrorFlag() = True Then Exit Function
LineVerticalAdd = clsShape.LineAdd(sngLeftStart, sngTopStart, _
sngLeftStart, sngTopStart + sngLength)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("LineVerticalAdd", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_OrderBringToFront
Public Shared Sub Shape_OrderBringToFront(ByVal objShape As Excel.Shape)
Try
'seems to cause an error if the shape is already at the front ?
objShape.ZOrder(Microsoft.Office.Core.MsoZOrderCmd.msoBringToFront)
Catch objCOMException As System.Runtime.InteropServices.COMException
End Try
End Sub
Shape_PositionReturn
Public Shared Function Shape_PositionReturn(ByVal sngLeft As Single, _
ByVal sngTop As Single, _
ByVal sngWidth As Single, _
ByVal sngHeight As Single, _
ByVal sngAccuracy As Single) _
As Excel.Shape
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim ishapecount As Integer
Dim objshape As Excel.Shape
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
For ishapecount = 1 To objshapes.Count
objshape = objshapes.Item(ishapecount)
If Math.Abs(objshape.Left - sngLeft) < sngAccuracy And _
Math.Abs(objshape.Top - sngTop) < sngAccuracy And _
Math.Abs(objshape.Height - sngHeight) < sngAccuracy And _
Math.Abs(objshape.Width - sngWidth) < sngAccuracy Then
PositionReturn = objshape
Exit Function
Else
'Call MsgBox("Difference in Left is : " & Math.Abs(objshape.Left - sngLeft) & _
' vbCrLf & _
' "Difference in Top is : " & Math.Abs(objshape.Top - sngTop) & _
' vbCrLf & _
' "Difference in Height is : " & Math.Abs(objshape.Height - sngHeight) & _
' vbCrLf & _
' "Difference in Width is : " & Math.Abs(objshape.Width - sngWidth))
PositionReturn = Nothing
End If
Next
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PositionReturn", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_PositionSizeExists
Public Shared Function Shape_PositionSizeExists(ByVal sngLeft As Single, _
ByVal sngTop As Single, _
ByVal sngWidth As Single, _
ByVal sngHeight As Single, _
ByVal sngAccuracy As Single) _
As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim ishapecount As Integer
Dim objshape As Excel.Shape
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
For ishapecount = 1 To objshapes.Count
objshape = objshapes.Item(ishapecount)
If Math.Abs(objshape.Left - sngLeft) < sngAccuracy And _
Math.Abs(objshape.Top - sngTop) < sngAccuracy And _
Math.Abs(objshape.Height - sngHeight) < sngAccuracy And _
Math.Abs(objshape.Width - sngWidth) < sngAccuracy Then
PositionSizeExists = True
Exit Function
Else
'Call MsgBox("Difference in Left is : " & Math.Abs(objshape.Left - sngLeft) & _
' vbCrLf & _
' "Difference in Top is : " & Math.Abs(objshape.Top - sngTop) & _
' vbCrLf & _
' "Difference in Height is : " & Math.Abs(objshape.Height - sngHeight) & _
' vbCrLf & _
' "Difference in Width is : " & Math.Abs(objshape.Width - sngWidth))
PositionSizeExists = False
End If
Next
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PositionSizeExists", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_PositionSizeGetRef
Public Shared Sub Shape_PositionSizeGetRef(ByVal objShape As Excel.Shape, _
ByRef sngLeft As Single, _
ByRef sngTop As Single, _
ByRef sngWidth As Single, _
ByRef sngHeight As Single)
Try
If clsError.ErrorFlag() = True Then Exit Sub
sngLeft = objShape.Left
sngTop = objShape.Top
sngHeight = objShape.Height
sngWidth = objShape.Width
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("PositionSizeGetRef", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Shape_Return
Public Shared Function Shape_Return(ByVal sShapeName As String, _
Optional ByVal iShapeNo As Integer = -1) As Excel.Shape
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
If iShapeNo = -1 Then
ShapeReturn = objshapes.Item(sShapeName)
End If
If sShapeName.Length = 0 Then
ShapeReturn = objshapes.Item(iShapeNo)
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("ShapeReturn", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_Selected
Public Shared Function Shape_Selected() As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim sTypeReturn As String
sTypeReturn = TypeName(gApplicationExcel.Selection)
Select Case sTypeReturn
Case "GroupObject" : Selected = True
Case "Rectangle" : Selected = True
Case "TextBox" : Selected = True
Case "Line" : Selected = True
Case "Oval" : Selected = True
Case "Range" : Selected = False
Case Else
Call MsgBox("Should this be added to the list of shapes : " & sTypeReturn)
Selected = False
End Select
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("Selected", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_SelectedNameGet
Public Shared Function Shape_SelectedNameGet() As String
Try
If clsError.ErrorFlag() = True Then Exit Function
If clsShape.Selected = True Then
SelectedNameGet = clszLateBindingExcel.SelectionNameReturn
Else
SelectedNameGet = "Nothing"
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("SelectedNameGet", "clsShape", _
"return the name of the shape currently selected.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_SelectedShapeReturn
Public Shared Function Shape_SelectedShapeReturn() As Excel.Shape
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim objshape As Excel.Shape
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
SelectedShapeReturn = objshapes.Item(clszLateBindingExcel.SelectionNameReturn)
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("SelectedShapeReturn", "clsShape", _
"return the selected shape object.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_SelectShape
Public Shared Function Shape_SelectShape(ByVal sShapeName As String, _
Optional ByVal iShapeNo As Integer = -1) _
As String
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
If iShapeNo = -1 Then
objshapes.Item(sShapeName).Select()
SelectShape = objshapes.Item(sShapeName).Name
End If
If sShapeName.Length = 0 Then
objshapes.Item(iShapeNo).Select()
SelectShape = objshapes.Item(iShapeNo).Name
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("SelectShape", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_Shift
Public Shared Sub Shape_Shift(ByVal objShape As Excel.Shape, _
ByVal sDirection As String, _
ByVal sngShiftAmount As Single, _
Optional ByVal iNumberTimes As Integer = 1)
Try
If clsError.ErrorFlag() = True Then Exit Sub
Dim sngshiftdistance As Single
Select Case sDirection
Case "UP" : objShape.Top = objShape.Top - (iNumberTimes * sngShiftAmount)
Case "DOWN" : objShape.Top = objShape.Top + (iNumberTimes * sngShiftAmount)
Case "LEFT" : objShape.Left = objShape.Left - (iNumberTimes * sngShiftAmount)
Case "RIGHT" : objShape.Left = objShape.Left + (iNumberTimes * sngShiftAmount)
End Select
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("Shift", "clsShape", _
"shift the shape '" & objShape.Name & "' in the '" & sDirection & "'" & _
" a distance of '" & sngShiftAmount & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Shape_TextDefine
Public Shared Sub Shape_TextDefine(ByVal objShape As Excel.Shape, _
ByVal sText As String)
Try
If clsError.ErrorFlag() = True Then Exit Sub
objShape.TextFrame.Characters.Text = sText
objShape.TextFrame.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
objShape.TextFrame.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("TextDefine", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Shape_TextGet
Public Shared Function Shape_TextGet(ByVal objShape As Excel.Shape) As String
Try
If clsError.ErrorFlag() = True Then Exit Function
TextGet = objShape.TextFrame.Characters.Text
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("TextGet", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_TextSet
Public Shared Sub Shape_TextSet(ByVal objShape As Excel.Shape, _
ByVal sText As String)
Try
If clsError.ErrorFlag() = True Then Exit Sub
objShape.TextFrame.Characters.Text = sText
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("TextSet", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Shape_Type2Return
Public Shared Function Shape_Type2Return(ByVal objShape As Excel.Shape) As String
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim stemp As String
Select Case objShape.Type
Case Office.MsoShapeType.msoAutoShape : stemp = "AutoShape"
Case Office.MsoShapeType.msoCallout : stemp = "CallOut"
Case Office.MsoShapeType.msoCanvas : stemp = "Canvas"
Case Office.MsoShapeType.msoChart : stemp = "Chart"
Case Office.MsoShapeType.msoComment : stemp = "Comment"
Case Office.MsoShapeType.msoDiagram : stemp = "Diagram"
Case Office.MsoShapeType.msoEmbeddedOLEObject : stemp = "EmbeddedOLE"
Case Office.MsoShapeType.msoFormControl : stemp = "FormControl"
Case Office.MsoShapeType.msoFreeform : stemp = "FreeForm"
Case Office.MsoShapeType.msoGroup : stemp = "Group"
Case Office.MsoShapeType.msoLine : stemp = "Line"
Case Office.MsoShapeType.msoLinkedOLEObject : stemp = "LinkedOLE"
Case Office.MsoShapeType.msoLinkedPicture : stemp = "LinkedPicture"
Case Office.MsoShapeType.msoMedia : stemp = "Media"
Case Office.MsoShapeType.msoOLEControlObject : stemp = "OLEControlObject"
Case Office.MsoShapeType.msoPicture : stemp = "Picture"
Case Office.MsoShapeType.msoPlaceholder : stemp = "Placeholder"
Case Office.MsoShapeType.msoScriptAnchor : stemp = "ScriptAnchor"
Case Office.MsoShapeType.msoShapeTypeMixed : stemp = "ShapeTypeMixed"
Case Office.MsoShapeType.msoTable : stemp = "Table"
Case Office.MsoShapeType.msoTextBox : stemp = "TextBox"
Case Office.MsoShapeType.msoTextEffect : stemp = "TextEffect"
End Select
Type2Return = stemp
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("Type2Return", "clsShape", _
"returns the type of the shape '" & objShape.Name & "'.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shape_UnGroup
Public Shared Function Shape_UnGroup(ByVal objShape As Excel.Shape) _
As String()
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objshaperange As Excel.ShapeRange
Dim ishapecount As Integer
Dim asShapeNames() As String
objshaperange = objShape.Ungroup()
ReDim asShapeNames(objshaperange.Count - 1)
For ishapecount = 1 To objshaperange.Count
objShape = objshaperange.Item(ishapecount)
asShapeNames(ishapecount - 1) = objShape.Name
Next
UnGroup = asShapeNames
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("UnGroup", "clsShape", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shapes_Clear
public static void clearSheetContent(Excel.Worksheet worksheet)
{
try
{
worksheet.Rows.Clear();
worksheet.Columns.Clear();
foreach (Excel.Shape shape in worksheet.Shapes)
{
if (shape != null)
{
shape.Delete();
}
}
worksheet.Cells.MergeCells = false;
}
catch (System.Exception ex)
{
ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
}
}
Shapes_Group
Public Shared Function Shapes_Group(ByVal ParamArray asShapeArray() As String) _
As Excel.Shape
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim sshapename As String
Dim ishapecount As Integer
Dim objshaperange As Excel.ShapeRange
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
ishapecount = 0
For Each sshapename In asShapeArray
If ishapecount = 0 Then
objshapes.Item(sshapename).Select()
Else
objshapes.Item(sshapename).Select(False)
End If
ishapecount = ishapecount + 1
Next
If clsShapes.Selected = True Then
objshaperange = clszLateBindingExcel.SelectionShapeRange
Group = objshaperange.Group()
End If
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("Group", "clsShapes", _
"group the array of shapes.", _
mobjCOMException, mobjException)
End If
End Try
End Function
Shapes_SelectAll
Public Shared Sub Shapes_SelectAll(Optional ByVal sWbkName As String = "")
Try
If clsError.ErrorFlag() = True Then Exit Sub
Dim objwsh As Excel.Worksheet
Dim objshapes As Excel.Shapes
Dim objShape As Excel.Shape
Dim ishapecount As Integer
objwsh = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
objshapes = CType(objwsh.Shapes, Excel.Shapes)
'for each shape does not work !!
For ishapecount = 1 To objshapes.Count
objshapes.Item(ishapecount).Select(False)
Next ishapecount
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("SelectAll", "clsShapes", _
"select all the shapes on the worksheet.", _
mobjCOMException, mobjException)
End If
End Try
End Sub
Shapes_Selected
Public Shared Function Shapes_Selected() As Boolean
Try
If clsError.ErrorFlag() = True Then Exit Function
Dim sTypeReturn As String
sTypeReturn = TypeName(gApplicationExcel.Selection)
Select Case sTypeReturn
Case "DrawingObjects" : Selected = True
Case Else
Selected = False
End Select
Catch objCOMException As System.Runtime.InteropServices.COMException
mobjCOMException = objCOMException
Catch objException As Exception
mobjException = objException
Finally
If gbDEBUG_EXCEL = True Or _
((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then
Call clsError.Handle("Selected", "clsShapes", _
"", _
mobjCOMException, mobjException)
End If
End Try
End Function
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top