none
RefEdit operations RRS feed

  • Question

  • Hello fellows,

    I would like to know how i can select several cells (not a matrix only cells separated) in an excel sheet, with a RefEdit control for then do some operations, as sums and multiplications.

    Thank you 

    Thursday, January 24, 2019 12:49 PM

All replies

  • Hello,

    If this is RefEdit for AutoDesk, use their forums. I can't say if that will be helpful but it's always best to start with the company who is responsible for something like in this case RefEdit. If RefEdit is not a AutoDesk item then you need to be more specific.

    Thinking just Excel, there are a handful of ways to access cells via Excel automation, using OleDb or Open Office XML for Excel or third party library such as SpreadSheetLight (most examples are in C# but works great with VB.NET).

    • When working with OleDb a SELECT statement is used e.g. SELECT F1,F2,F3 FROM [Sheet1$A1:C11] where in this case the WorkSheet first row is data, if not data Fx are replaced with field names in the first row.
    • With Excel automation you can target cells using a Range e.g. xlCells = xlWorkSheet.Range("A1") where in this case xlCells and xlWorkSheet are child objects of Excel.Application/Excel.WorkBook/Excel.WorkSheets. There is also another syntax shown below.
    Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
    

    Example

    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module ExcelDemoIteratingData
        ''' <summary>
        ''' Place all rows/columns into a DataTable.
        ''' Must know ahead of time how many columns there are which is not
        ''' shown here but instead I knew when creating this demo there are x rows
        ''' and x columns.
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="SheetName"></param>
        ''' <remarks></remarks>
        Public Function OpenExcel_1(ByVal FileName As String, ByVal SheetName As String) As DataTable
            Dim dt As New DataTable
    
            If IO.File.Exists(FileName) Then
    
                Dim Proceed As Boolean = False
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
                '
                ' For/Next finds our sheet
                '
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If Proceed Then
    
                    dt.Columns.AddRange(
                        New DataColumn() _
                        {
                            New DataColumn With {.ColumnName = "FirstName"},
                            New DataColumn With {.ColumnName = "LastName"},
                            New DataColumn With {.ColumnName = "Age", .DataType = GetType(Integer)}
                        }
                    )
    
                    Dim xlUsedRange = xlWorkSheet.UsedRange
    
                    Try
    
                        Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
    
                        If ExcelArray IsNot Nothing Then
                            Console.WriteLine("Length: {0}", ExcelArray.Length)
    
                            ' Get bounds of the array.
                            Dim bound0 As Integer = ExcelArray.GetUpperBound(0)
                            Dim bound1 As Integer = ExcelArray.GetUpperBound(1)
    
                            Console.WriteLine("Dimension 0: {0}", bound0)
                            Console.WriteLine("Dimension 1: {0}", bound1)
    
                            ' Loop over all elements.
    
                            For j As Integer = 1 To bound0
                                '
                                ' Uncomment to see values
                                '
                                'For x As Integer = 1 To bound1
                                '    Dim s1 As String = CStr(array(j, x))
                                '    Console.Write(s1)
                                '    Console.Write(" "c)
                                'Next
                                'Console.WriteLine()
    
    
                                If (ExcelArray(j, 1) IsNot Nothing) AndAlso (ExcelArray(j, 2) IsNot Nothing) Then
                                    dt.Rows.Add(New Object() _
                                        {
                                            ExcelArray(j, 1),
                                            ExcelArray(j, 2),
                                            ExcelArray(j, 3)
                                        }
                                    )
                                End If
                            Next
    
                        End If
                    Finally
                        ReleaseComObject(xlUsedRange)
                    End Try
    
                Else
                    MessageBox.Show(SheetName & " not found.")
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlCells)
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
            End If
    
            Return dt
    
        End Function
        Private Sub ReleaseComObject(ByVal sender As Object)
            Try
                If sender IsNot Nothing Then
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sender)
                    sender = Nothing
                End If
            Catch ex As Exception
                sender = Nothing
            End Try
        End Sub
    End Module
    

    An example of automation to read three cells

    Option Strict On
    Option Infer Off
    
    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Module OpenWorkBookSimple
        Public Sub OpenExcelSimple(ByVal FileName As String, ByVal SheetName As String)
    
            If IO.File.Exists(FileName) Then
    
                Dim Proceed As Boolean = False
    
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
                xlWorkSheets = xlWorkBook.Sheets
    
                '
                ' For/Next finds our sheet
                '
                For x As Integer = 1 To xlWorkSheets.Count
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Proceed = True
                        Exit For
                    End If
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
                If Proceed Then
                    Dim sb As New Text.StringBuilder
    
                    Dim Cells As String() = {"A1", "B2", "B3", "B4"}
                    For Each cell As String In Cells
                        Try
                            xlCells = xlWorkSheet.Range(cell)
                            sb.AppendLine(String.Format("{0} = '{1}'", cell, xlCells.Value))
                        Catch ex As Exception
                            ReleaseExcelObject(xlCells)
                        End Try
                    Next
                    ' show sb.ToString()
                Else
                    ' sheet not found
                End If
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseExcelObject(xlCells)
                ReleaseExcelObject(xlWorkSheets)
                ReleaseExcelObject(xlWorkSheet)
                ReleaseExcelObject(xlWorkBook)
                ReleaseExcelObject(xlWorkBooks)
                ReleaseExcelObject(xlApp)
            Else
                ' file does not exists
            End If
        End Sub
        Private Sub ReleaseExcelObject(ByVal excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Module
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 24, 2019 1:09 PM
    Moderator
  • Hi,

    see the following link:

    https://blogs.msdn.microsoft.com/gabhan_berry/2008/06/11/how-to-code-a-net-refedit-control/

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 25, 2019 7:50 AM