Results 1 to 35 of 35

Thread: opening xml file to datagrid view for editing

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Post opening xml file to datagrid view for editing

    how can you open an xml file to a datagrid view for editing ?

  2. #2
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    How did you save that xml file in the first place?
    You need to be more especific then that.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: opening xml file to datagrid view for editing

    this is how i saved it
    Code:
    If MainForm.SaveFileDialog.ShowDialog = DialogResult.OK Then
                Dim writer As New XmlTextWriter(MainForm.SaveFileDialog.FileName, System.Text.Encoding.UTF8)
                writer.WriteStartDocument(True)
                writer.Formatting = Formatting.Indented
                writer.Indentation = 3
                writer.WriteStartElement("Table")
                For r As Integer = 0 To ActiveSheet.dgvScoreSheetTable.RowCount - 2
                    Dim c0 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(0).Value)
                    Dim c1 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(1).Value)
                    Dim c2 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(2).Value)
                    Dim c3 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(3).Value)
                    Dim c4 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(4).Value)
                    Dim c5 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(5).Value)
                    Dim c6 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(6).Value)
                    Dim c7 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(7).Value)
                    Dim c8 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(8).Value)
                    WriteData(c0, c1, c2, c3, c4, c5, c6, c7, c8, writer)
                Next
                writer.WriteEndElement()
                writer.WriteEndDocument()
                writer.Close()
            End If
    the file opens quite alright but when i want to edit it, it generates an error

  4. #4
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    Diferent aprouche to xml saving and reading, using a datatable as datasource of the datagridview control you have:

    main form code:

    Code:
    Public Class mainFrm
    
        Private SHEETCOUNT As Integer = 1
    
        Private Sub mainFrm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        End Sub
    
        Private Sub NewToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles NewToolStripMenuItem.Click
    
            CREATE_NEW_WORKSHEET()
    
        End Sub
    
        Private Sub CREATE_NEW_WORKSHEET()
    
            Dim NWRKSHEET As New NWSHEET
            With NWRKSHEET
                .Name = String.Format("NEWWRK{0}", SHEETCOUNT)
                .Text = String.Format("New worksheet {0}", SHEETCOUNT)
                .MdiParent = Me
                .Show()
            End With
            SHEETCOUNT += 1
    
        End Sub
    
        Private Sub SaveAsToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveAsToolStripMenuItem.Click
    
            Dim F = CType(ActiveMdiChild, NWSHEET)
            F.SaveAsXmlFile
    
        End Sub
    
        Private Sub OpenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles OpenToolStripMenuItem.Click
    
            Using OPENFL As New OpenFileDialog
                OPENFL.Filter = "Xml Files|*.xml"
                OPENFL.Title = "Select a xml File"
                OPENFL.ShowDialog()
    
                If OPENFL.FileName <> "" Then
    
                    Dim NWRKSHEET As New NWSHEET(OPENFL.FileName)
                    With NWRKSHEET
                        .Name = String.Format("NEWWRK{0}", SHEETCOUNT)
                        .Text = String.Format("New worksheet {0}", SHEETCOUNT)
                        .MdiParent = Me
                        .Show()
                    End With
                    SHEETCOUNT += 1
                End If
            End Using
    
        End Sub
    
        Private Sub SaveToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveToolStripMenuItem.Click
    
            Dim F = CType(ActiveMdiChild, NWSHEET)
            If F.ISNEW = True Then
    
                F.SaveAsXmlFile()
    
            Else
    
                F.SaveXmlFile()
    
            End If
    
        End Sub
    
        Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
    
            Application.Exit()
    
        End Sub
    End Class
    Worksheet code

    Code:
    Public Class NWSHEET
    
        Private TB As New DataTable("Scores")
        Friend ISNEW As Boolean
    
        'Creates a new empty worksheet
        Public Sub New()
    
            ' This call is required by the designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
            CREATE_TABLE_COLUMNS()
            ISNEW = True
    
        End Sub
    
        'Loads a existing work sheet
        Public Sub New(xml_Doc As String)
    
            ' This call is required by the designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
            CREATE_TABLE_COLUMNS()
            TB.ReadXml(xml_Doc)
            ISNEW = False
            Tag = xml_Doc
    
        End Sub
    
        Private Sub NWSHEET_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            'Set Datagridview datasource to the table
            DGV1.DataSource = TB
            'How to set datagriview column text
            DGV1.Columns(0).HeaderText = "Studant name"
            'Set total score column as readonly
            DGV1.Columns(3).ReadOnly = True
    
        End Sub
    
        'Creates and adds columns to the datatable
        Private Sub CREATE_TABLE_COLUMNS()
    
            'Create a datacolumn and define datatype and add it to a table
            Dim CL1 As New DataColumn
            With CL1
                .ColumnName = "C1"
                .DataType = Type.GetType("System.String")
            End With
            TB.Columns.Add(CL1)
    
            Dim CL2 As New DataColumn
            With CL2
                .ColumnName = "C2"
                .DataType = Type.GetType("System.Int32")
                .DefaultValue = 0
            End With
            TB.Columns.Add(CL2)
    
            Dim CL3 As New DataColumn
            With CL3
                .ColumnName = "C3"
                .DataType = Type.GetType("System.Int32")
                .DefaultValue = 0
            End With
            TB.Columns.Add(CL3)
    
            Dim CL4 As New DataColumn
            With CL4
                .ColumnName = "C4"
                .DataType = Type.GetType("System.Int32")
                .DefaultValue = 0
                'Use Expression to calculate the total score
                .Expression = "C2 + C3"
            End With
            TB.Columns.Add(CL4)
    
        End Sub
    
        Friend Sub SaveXmlFile()
    
            TB.WriteXml(Tag.ToString, True)
    
        End Sub
    
        Friend Sub SaveAsXmlFile()
    
            Using SVDLG As New SaveFileDialog
                SVDLG.Filter = "Xml file|*.xml"
                SVDLG.Title = "Save an Xml File"
                SVDLG.ShowDialog()
                If SVDLG.FileName <> "" Then
    
                    TB.WriteXml(SVDLG.FileName, True)
                    Tag = SVDLG.FileName
                    ISNEW = False
    
                End If
    
            End Using
    
    
        End Sub
    
    End Class
    Note that main form is a mdicontainer and only has a menustrip control and the new sheet form only has a datagrid called DGV1.
    Thats raw code, it can be improved in many points, its just to give you a ideia of how easy is to load xml to a table and save it back to a xml file.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: opening xml file to datagrid view for editing

    what if i want to use this kind of expression on a column how will i place it
    Code:
    Dim rows = dgvScoreSheetTable.Rows.
            Cast(Of DataGridViewRow)().OrderByDescending(Function(row) CInt(row.Cells(6).Value)).ToArray()
            For a = 0 To rows.GetUpperBound(0)
                rows(a).Cells(7).Value = a + 1
            Next

  6. #6
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    What do you actualy want to do?

  7. #7
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: opening xml file to datagrid view for editing

    Quote Originally Posted by Mike Storm View Post
    What do you actualy want to do?
    I agree. You have shown a solution, not a statement of the problem.

    If you want / have to use XML then show the XML file (or part of it) also.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  8. #8
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: opening xml file to datagrid view for editing

    People are scratching their heads because this is sort of a weirder question than you think.

    DataGridView was made for displaying database data. It has a concept of "tables". Tables are made of "Rows", and each row has "Columns" that contain values. The logic of what "a value" means is separated from what .NET considers as "variable types". It has concepts of "constraints" and "relations" that aren't modeled well by the object-oriented approach of .NET languages unless you do work to implement those features yourself.

    XML is a text format for representing object models. But it's a very bad format for representing object models, because for any given object there's more than one "obvious" way to represent the object. For example, consider this simple "Customer":
    Code:
    Public Class Customer
        Public Property Name As String
    End Class
    If you pick 100 developers and ask them to describe a customer with XML, you are going to get a 50/50 split between:
    Code:
    <Customer Name="Dale Cooper" />
    Code:
    <Customer>
        <Name>Dale Cooper</Name>
    </Customer>
    99% of developers in the world have standardized on JSON instead, because there's only one "sane" way to describe this Customer in JSON:
    Code:
    { "Name" : "Dale Cooper" }
    But that's a different story, we're using XML.

    "Objects" and "tables" have a lot in common. We can argue that "columns" are like "properties". So it ought to be true that we can convert from XML to objects, objects to tables, thus via transitive logic we think "objects", "tables", and "XML" should all be compatible.

    So DataTable and DataSet have some methods that will read XML and create tables/columns/rows for you. But because XML is ambiguous, there are a lot of limitations. If you want it to work best, your XML needs to provide a full schema so the code can generate the correct columns for each table. If you don't provide a schema, the "easy" code doesn't have a clue whether this XML represents an Integer, a String, or some other type entirely:
    Code:
    <Value>25</Value>
    So if you don't have a schema, the best you'll get out of the "easy" methods is "everything in your XML is considered a String, it's up to YOU to do conversions. Fun.

    (To be fair, JSON isn't much better. Oversimplifying, it recognizes String, Double, and Boolean as variable types and nothing else. So if you want Integer and Byte to be distinct types, you have work to do with JSON.)

    When you don't have a schema, it is YOUR job to do to the work to tell VB what type each XML element corresponds to. That generally means you can't use a simple method like ReadXml(). You have to follow this process:
    1. Define the DataTables you need, making sure to set up DataColumns with the right types.
    2. Open the XML document with your favorite XML parser (MS has provided three so far, and will add more when they decide people aren't confused anymore.)
    3. Parse each XML element into the appropriate DataRow of the appropriate DataTable in your DataSet.
    4. Profit!

    That means you either have to 1) follow the rules that ReadXML() expects, which means writing a schema for your XML or 2) do all the work yourself.

    So the question you're asking is overly broad. There's no one way to "get an XML document into a DataGridView". You have to start with, "This is my XML", then answer questions like, "Can you change the format?" or "Can you write a schema?".
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: opening xml file to datagrid view for editing

    Quote Originally Posted by Sitten Spynne View Post
    People are scratching their heads because this is sort of a weirder question than you think.

    DataGridView was made for displaying database data. It has a concept of "tables". Tables are made of "Rows", and each row has "Columns" that contain values. The logic of what "a value" means is separated from what .NET considers as "variable types". It has concepts of "constraints" and "relations" that aren't modeled well by the object-oriented approach of .NET languages unless you do work to implement those features yourself.

    XML is a text format for representing object models. But it's a very bad format for representing object models, because for any given object there's more than one "obvious" way to represent the object. For example, consider this simple "Customer":
    Code:
    Public Class Customer
        Public Property Name As String
    End Class
    If you pick 100 developers and ask them to describe a customer with XML, you are going to get a 50/50 split between:
    Code:
    <Customer Name="Dale Cooper" />
    Code:
    <Customer>
        <Name>Dale Cooper</Name>
    </Customer>
    99% of developers in the world have standardized on JSON instead, because there's only one "sane" way to describe this Customer in JSON:
    Code:
    { "Name" : "Dale Cooper" }
    But that's a different story, we're using XML.

    "Objects" and "tables" have a lot in common. We can argue that "columns" are like "properties". So it ought to be true that we can convert from XML to objects, objects to tables, thus via transitive logic we think "objects", "tables", and "XML" should all be compatible.

    So DataTable and DataSet have some methods that will read XML and create tables/columns/rows for you. But because XML is ambiguous, there are a lot of limitations. If you want it to work best, your XML needs to provide a full schema so the code can generate the correct columns for each table. If you don't provide a schema, the "easy" code doesn't have a clue whether this XML represents an Integer, a String, or some other type entirely:
    Code:
    <Value>25</Value>
    So if you don't have a schema, the best you'll get out of the "easy" methods is "everything in your XML is considered a String, it's up to YOU to do conversions. Fun.

    (To be fair, JSON isn't much better. Oversimplifying, it recognizes String, Double, and Boolean as variable types and nothing else. So if you want Integer and Byte to be distinct types, you have work to do with JSON.)

    When you don't have a schema, it is YOUR job to do to the work to tell VB what type each XML element corresponds to. That generally means you can't use a simple method like ReadXml(). You have to follow this process:
    1. Define the DataTables you need, making sure to set up DataColumns with the right types.
    2. Open the XML document with your favorite XML parser (MS has provided three so far, and will add more when they decide people aren't confused anymore.)
    3. Parse each XML element into the appropriate DataRow of the appropriate DataTable in your DataSet.
    4. Profit!

    That means you either have to 1) follow the rules that ReadXML() expects, which means writing a schema for your XML or 2) do all the work yourself.

    So the question you're asking is overly broad. There's no one way to "get an XML document into a DataGridView". You have to start with, "This is my XML", then answer questions like, "Can you change the format?" or "Can you write a schema?".
    your post is quite ambiguous to understand but I have gotten what you mean

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: opening xml file to datagrid view for editing

    ok defining my question more vividly read this
    Code:
    'This code is in the parent form
    Public Class MainForm
     
    Private Sub NewToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewToolStripMenuItem1.Click
           'create a new scoresheet
            Dim Nsheet As New ScoresheetForm
            Nsheet.MdiParent = MainForm
            Nsheet.Mymainform = MainForm
            Nsheet.WindowState = FormWindowState.Maximized
            Nsheet.Show()
        End Sub
    
     Private Sub OpenToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenToolStripMenuItem1.Click
            OpenScoreSheet()
        End Sub
    
     Private Sub SaveAsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveAsToolStripMenuItem.Click
            SaveScoreSheet()
        End Sub
    End Class
    Code:
    'This code is in the child form 
    Public Class ScoresheetForm
    
    #Region "Student Info"
        Dim m_ca1 As Integer
        Dim m_ca2 As Integer
        Dim m_ca3 As Integer
        Dim m_exam As Integer
    #End Region
    
    Private Sub dgvScoreSheetTable_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvScoreSheetTable.RowValidated
            Dim i As Integer
            i = e.RowIndex
            Try
                'Sum all C.A's and Give the total
                m_ca1 = CInt(dgvScoreSheetTable.Rows(i).Cells(2).Value)
                m_ca2 = CInt(dgvScoreSheetTable.Rows(i).Cells(3).Value)
                m_ca3 = CInt(dgvScoreSheetTable.Rows(i).Cells(4).Value)
                m_exam = CInt(dgvScoreSheetTable.Rows(i).Cells(5).Value)
                dgvScoreSheetTable.Rows(i).Cells(6).Value = m_ca1 + m_ca2 + m_ca3 + m_exam
            Catch ex As Exception
                MsgBox(ex.Message & ". " & "Input type in current row not a value")
            End Try
            'determine the grade
            Try
                Select Case CInt(Me.dgvScoreSheetTable.Rows(i).Cells(6).Value)
                    Case Is <= 39
                        Me.dgvScoreSheetTable.Rows(i).Cells(8).Value = "E"
                    Case 40 To 54
                        Me.dgvScoreSheetTable.Rows(i).Cells(8).Value = "D"
                    Case 55 To 64
                        Me.dgvScoreSheetTable.Rows(i).Cells(8).Value = "C"
                    Case 65 To 74
                        Me.dgvScoreSheetTable.Rows(i).Cells(8).Value = "B"
                    Case Is >= 75
                        Me.dgvScoreSheetTable.Rows(i).Cells(8).Value = "A"
                    Case Else
                        Me.dgvScoreSheetTable.Rows(i).Cells(8).Value = "~"
                End Select
            Catch ex As Exception
                MsgBox(ex.Message & " " & "The total column from the file you are opening contains invalid characters or the file you are opening is less than the size of the collection")
                Return
            End Try
            Dim rows = dgvScoreSheetTable.Rows.
            Cast(Of DataGridViewRow)().OrderByDescending(Function(row) CInt(row.Cells(6).Value)).ToArray()
            For a = 0 To rows.GetUpperBound(0)
                rows(a).Cells(7).Value = a + 1
            Next
        End Sub
    End Class
    Code:
    'This code is in a module
    Imports System.Xml
    Imports System.Data
    Module ScoreSheetModule
        
        Public ReadOnly Property ActiveSheet As ScoresheetForm
            Get
                Return DirectCast(MainForm.ActiveMdiChild, ScoresheetForm)
            End Get
        End Property
    
        Public Sub OpenScoreSheet()
            'show the open file dialog
            If MainForm.OpenFileDialog.ShowDialog = DialogResult.OK Then
                'Load the selected scoresheet into the child form
                Dim NSheet As New ScoresheetForm
                NSheet.MdiParent = MainForm
                NSheet.Mymainform = MainForm
                NSheet.dgvScoreSheetTable.Columns.Clear()
                Dim xmlFile As XmlReader
                'read the file with xml reader
                xmlFile = XmlReader.Create(MainForm.OpenFileDialog.FileName, New XmlReaderSettings())
                Dim ds As New DataSet
                ds.ReadXml(xmlFile)
                NSheet.dgvScoreSheetTable.DataSource = ds.Tables(0)
                NSheet.Text = MainForm.OpenFileDialog.FileName
                'Load the scoresheet child form into the main form
                NSheet.Show()
            End If
        End Sub
    
        Public Sub SaveScoreSheet()
            'show the SaveFileDialog
            If MainForm.SaveFileDialog.ShowDialog = DialogResult.OK Then
                Dim writer As New XmlTextWriter(MainForm.SaveFileDialog.FileName, System.Text.Encoding.UTF8)
                writer.WriteStartDocument(True)
                writer.Formatting = Formatting.Indented
                writer.Indentation = 3
                writer.WriteStartElement("Table")
                For r As Integer = 0 To ActiveSheet.dgvScoreSheetTable.RowCount - 2
                    Dim c0 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(0).Value)
                    Dim c1 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(1).Value)
                    Dim c2 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(2).Value)
                    Dim c3 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(3).Value)
                    Dim c4 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(4).Value)
                    Dim c5 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(5).Value)
                    Dim c6 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(6).Value)
                    Dim c7 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(7).Value)
                    Dim c8 As String = CStr(ActiveSheet.dgvScoreSheetTable.Rows(r).Cells(8).Value)
                    WriteData(c0, c1, c2, c3, c4, c5, c6, c7, c8, writer)
                Next
                writer.WriteEndElement()
                writer.WriteEndDocument()
                writer.Close()
            End If
        End Sub
    
        Public Sub WriteData(ByVal _Admin As String, ByVal _Name As String, ByVal _ca1 As String, ByVal _ca2 As String, ByVal _ca3 As String, _
                               ByVal _exam As String, ByVal _total As String, ByVal _position As String, ByVal _grade As String, ByVal writer As XmlTextWriter)
            writer.WriteStartElement("Record")
            writer.WriteStartElement("ADMIN_NUMBER")
            writer.WriteString(_Admin)
            writer.WriteEndElement()
            writer.WriteStartElement("NAME")
            writer.WriteString(_Name)
            writer.WriteEndElement()
            writer.WriteStartElement("C.A.1")
            writer.WriteString(_ca1)
            writer.WriteEndElement()
            writer.WriteStartElement("C.A.2")
            writer.WriteString(_ca2)
            writer.WriteEndElement()
            writer.WriteStartElement("C.A.3")
            writer.WriteString(_ca3)
            writer.WriteEndElement()
            writer.WriteStartElement("EXAM")
            writer.WriteString(_exam)
            writer.WriteEndElement()
            writer.WriteStartElement("TOTAL")
            writer.WriteString(_total)
            writer.WriteEndElement()
            writer.WriteStartElement("POSITION")
            writer.WriteString(_position)
            writer.WriteEndElement()
            writer.WriteStartElement("GRADE")
            writer.WriteString(_grade)
            writer.WriteEndElement()
            writer.WriteEndElement()
        End Sub
    
    End Module
    i think if you run this code you will see what I'm talking about

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: opening xml file to datagrid view for editing

    can someone please explain to me something here? Everyone is just shying away from my question.
    If there is something I haven't done then you'll spit it out for me

  12. #12
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: opening xml file to datagrid view for editing

    Quote Originally Posted by TATARPRO View Post
    can someone please explain to me something here? Everyone is just shying away from my question.
    If there is something I haven't done then you'll spit it out for me
    You originally asked about opening an XML file to edit it. You suggested using a DGV for that purpose. I pointed out that that might not be the best approach and asked that you describe the problem without a 'solution' and to show the XML file or portion.

    Good luck.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  13. #13
    Addicted Member
    Join Date
    Jul 2017
    Location
    Exeter, UK
    Posts
    180

    Re: opening xml file to datagrid view for editing

    I think it has been suggested that you process the XML manually within your OpenScoreSheet() sub instead of trying to directly read in into a data table. If it was me, I would do something like this, to get each 'value' and then populate the DGV for each row, or add to the data table.

    Code:
    Dim document As XmlReader = New XmlTextReader('your file name')
    While (document.Read())
           Dim type As XmlNodeType = document.NodeType
           If (type = XmlNodeType.Element) Then 
                 Select Case document.Name.ToUpper
                            Case "ADMIN_NAME"
                                m_admin = document.ReadInnerXml  ' You can convert each string value to the correct type as required, int, decimal etc.
                            Case "C.A.1"
                                m_CA1element = document.ReadInnerXml)
                            ' Etc. Etc.
                 End Select 
           End If
    End While
    Last edited by bmwpete; Sep 14th, 2017 at 08:21 AM. Reason: forgot code tag

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: opening xml file to datagrid view for editing

    this is what is saved in the Xml file. it was just a test not the actual data
    Code:
    <?xml version="1.0" encoding="UTF-8" standalone="true"?>
    
    -<Table>
    
    
    -<Record>
    
    <ADMIN_NUMBER>5565</ADMIN_NUMBER>
    
    <NAME>yyghjj</NAME>
    
    <C.A.1>65</C.A.1>
    
    <C.A.2>5</C.A.2>
    
    <C.A.3/>
    
    <EXAM/>
    
    <TOTAL>70</TOTAL>
    
    <POSITION>3</POSITION>
    
    <GRADE>B</GRADE>
    
    </Record>
    
    
    -<Record>
    
    <ADMIN_NUMBER/>
    
    <NAME/>
    
    <C.A.1>55</C.A.1>
    
    <C.A.2>5</C.A.2>
    
    <C.A.3/>
    
    <EXAM/>
    
    <TOTAL>60</TOTAL>
    
    <POSITION>5</POSITION>
    
    <GRADE>C</GRADE>
    
    </Record>
    
    
    -<Record>
    
    <ADMIN_NUMBER>456</ADMIN_NUMBER>
    
    <NAME>thh</NAME>
    
    <C.A.1>57</C.A.1>
    
    <C.A.2>5</C.A.2>
    
    <C.A.3/>
    
    <EXAM/>
    
    <TOTAL>62</TOTAL>
    
    <POSITION>4</POSITION>
    
    <GRADE>C</GRADE>
    
    </Record>
    
    
    -<Record>
    
    <ADMIN_NUMBER>667</ADMIN_NUMBER>
    
    <NAME>kttth</NAME>
    
    <C.A.1>65</C.A.1>
    
    <C.A.2>86</C.A.2>
    
    <C.A.3>21</C.A.3>
    
    <EXAM/>
    
    <TOTAL>172</TOTAL>
    
    <POSITION>1</POSITION>
    
    <GRADE>A</GRADE>
    
    </Record>
    
    
    -<Record>
    
    <ADMIN_NUMBER>888</ADMIN_NUMBER>
    
    <NAME>ygjjj</NAME>
    
    <C.A.1>6</C.A.1>
    
    <C.A.2>45</C.A.2>
    
    <C.A.3>4</C.A.3>
    
    <EXAM/>
    
    <TOTAL>55</TOTAL>
    
    <POSITION>6</POSITION>
    
    <GRADE>C</GRADE>
    
    </Record>
    
    
    -<Record>
    
    <ADMIN_NUMBER/>
    
    <NAME/>
    
    <C.A.1/>
    
    <C.A.2>56</C.A.2>
    
    <C.A.3>45</C.A.3>
    
    <EXAM/>
    
    <TOTAL>101</TOTAL>
    
    <POSITION>2</POSITION>
    
    <GRADE>A</GRADE>
    
    </Record>
    
    </Table>

  15. #15
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    Re: opening xml file to datagrid view for editing

    Here is an example. Create a form and add a button and DataGridView. Use the default names. Use this code
    Code:
    Public Class Form1
    
        Private xe As XElement
        Private myRecs As Records
    
        Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
            Stop 'examine xe and myRecs
            ' myRecs.Save("PATH HERE")
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Me.Button1.Visible = False
            'test data
            xe = <Table>
                     <Record>
                         <ADMIN_NUMBER>5565</ADMIN_NUMBER>
                         <NAME>yyghjj</NAME>
                         <C.A.1>65</C.A.1>
                         <C.A.2>5</C.A.2>
                         <C.A.3/>
                         <EXAM/>
                         <TOTAL>70</TOTAL>
                         <POSITION>3</POSITION>
                         <GRADE>B</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER/>
                         <NAME/>
                         <C.A.1>55</C.A.1>
                         <C.A.2>5</C.A.2>
                         <C.A.3/>
                         <EXAM/>
                         <TOTAL>60</TOTAL>
                         <POSITION>5</POSITION>
                         <GRADE>C</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER>456</ADMIN_NUMBER>
                         <NAME>thh</NAME>
                         <C.A.1>57</C.A.1>
                         <C.A.2>5</C.A.2>
                         <C.A.3/>
                         <EXAM/>
                         <TOTAL>62</TOTAL>
                         <POSITION>4</POSITION>
                         <GRADE>C</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER>667</ADMIN_NUMBER>
                         <NAME>kttth</NAME>
                         <C.A.1>65</C.A.1>
                         <C.A.2>86</C.A.2>
                         <C.A.3>21</C.A.3>
                         <EXAM/>
                         <TOTAL>172</TOTAL>
                         <POSITION>1</POSITION>
                         <GRADE>A</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER>888</ADMIN_NUMBER>
                         <NAME>ygjjj</NAME>
                         <C.A.1>6</C.A.1>
                         <C.A.2>45</C.A.2>
                         <C.A.3>4</C.A.3>
                         <EXAM/>
                         <TOTAL>55</TOTAL>
                         <POSITION>6</POSITION>
                         <GRADE>C</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER/>
                         <NAME/>
                         <C.A.1/>
                         <C.A.2>56</C.A.2>
                         <C.A.3>45</C.A.3>
                         <EXAM/>
                         <TOTAL>101</TOTAL>
                         <POSITION>2</POSITION>
                         <GRADE>A</GRADE>
                     </Record>
                 </Table>
    
            myRecs = New Records(xe) 'put a breakpoint here and step through
            DataGridView1.DataSource = myRecs.theRecords
        End Sub
    End Class
    
    Class Records
        Public theRecords As New List(Of Record)
        Private theXE As XElement
    
        Public Sub New(fileXE As XElement)
            Me.Load(fileXE)
        End Sub
    
        ''' <summary>
        ''' load from file
        ''' </summary>
        ''' <param name="path">path</param>
        ''' <remarks></remarks>
        Public Sub New(path As String)
            Dim xe As XElement = XElement.Load(path)
            Me.Load(xe)
        End Sub
    
        Public Sub Save(path As String)
            Me.theXE.Save(path)
        End Sub
    
        Private Sub Load(xe As XElement)
            Me.theXE = xe
            For Each el As XElement In xe.Elements
                Dim foo As New Record(el)
                Me.theRecords.Add(foo)
            Next
        End Sub
    
        Class Record
            Private this As XElement
            Public Sub New(recXE As XElement)
                Me.this = recXE
            End Sub
    
            Public Property Name() As String
                Get
                    Return Me.this.<NAME>.Value
                End Get
                Set(ByVal value As String)
                    Me.this.<NAME>.Value = value
                End Set
            End Property
    
            Public Property AdminNumber() As String
                Get
                    Return Me.this.<ADMIN_NUMBER>.Value
                End Get
                Set(ByVal value As String)
                    Me.this.<ADMIN_NUMBER>.Value = value
                End Set
            End Property
    
            Public Property Total() As String
                Get
                    Return Me.this.<TOTAL>.Value
                End Get
                Set(ByVal value As String)
                    Me.this.<TOTAL>.Value = value
                End Set
            End Property
    
        End Class
    End Class
    Click button 1. Make changes to the DGV and then close the app. There is a breakpoint in the close so you can see what happened. I only added a few of the properties to give you an idea of what to do
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  16. #16
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    170

    Re: opening xml file to datagrid view for editing

    why not so ?
    Code:
    Public Class Form1
        Private xe As XElement
        Dim xmlfile As String = "F:\Download\Test.xml"
        Dim ds As New DataSet()
    '--------- load ------
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'test data
            xe = <Table>
                     <Record>
                         <ADMIN_NUMBER>5565</ADMIN_NUMBER>
                         <NAME>yyghjj</NAME>
                         <C.A.1>65</C.A.1>
                         <C.A.2>5</C.A.2>
                         <C.A.3/>
                         <EXAM/>
                         <TOTAL>70</TOTAL>
                         <POSITION>3</POSITION>
                         <GRADE>B</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER/>
                         <NAME/>
                         <C.A.1>55</C.A.1>
                         <C.A.2>5</C.A.2>
                         <C.A.3/>
                         <EXAM/>
                         <TOTAL>60</TOTAL>
                         <POSITION>5</POSITION>
                         <GRADE>C</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER>456</ADMIN_NUMBER>
                         <NAME>thh</NAME>
                         <C.A.1>57</C.A.1>
                         <C.A.2>5</C.A.2>
                         <C.A.3/>
                         <EXAM/>
                         <TOTAL>62</TOTAL>
                         <POSITION>4</POSITION>
                         <GRADE>C</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER>667</ADMIN_NUMBER>
                         <NAME>kttth</NAME>
                         <C.A.1>65</C.A.1>
                         <C.A.2>86</C.A.2>
                         <C.A.3>21</C.A.3>
                         <EXAM/>
                         <TOTAL>172</TOTAL>
                         <POSITION>1</POSITION>
                         <GRADE>A</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER>888</ADMIN_NUMBER>
                         <NAME>ygjjj</NAME>
                         <C.A.1>6</C.A.1>
                         <C.A.2>45</C.A.2>
                         <C.A.3>4</C.A.3>
                         <EXAM/>
                         <TOTAL>55</TOTAL>
                         <POSITION>6</POSITION>
                         <GRADE>C</GRADE>
                     </Record>
                     <Record>
                         <ADMIN_NUMBER/>
                         <NAME/>
                         <C.A.1/>
                         <C.A.2>56</C.A.2>
                         <C.A.3>45</C.A.3>
                         <EXAM/>
                         <TOTAL>101</TOTAL>
                         <POSITION>2</POSITION>
                         <GRADE>A</GRADE>
                     </Record>
                 </Table>
    
            Dim s As String = xe.ToString
            Dim SR As New StringReader(s)
    
            ds.ReadXml(SR)
            DataGridView1.DataSource = ds.Tables(0)
        End Sub
    ' ----- save -----
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            ds.Tables(0).TableName = "MyDataTable"
            ds.Tables(0).WriteXml(xmlfile)
            MsgBox("File xml saved")
        End Sub
    End Class
    Last edited by patel45; Sep 15th, 2017 at 05:44 AM.

  17. #17
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    HI, i had a litle free time so i decided to leave you some code using a Dataset and Datatables to create xml files:

    VS 2017 Community .Net Framework 4.5

    Datasets, tables to xml

    1° Create a windiws forms project

    2° Add the folwing folders in the Solution Explorer:

    - Classes
    - Forms
    - Modules
    - Usercontrols

    Done

    3° Use Form1 that was automaticly addd to the form as the application main form
    rename it to MainForm and drag it in to Forms folder

    Add:

    1 Menustrip control (Remove what ever you wont be using)

    - Right click on it and Insert standart Items "Optional, you insert your owne items and icons"
    - Add new item, name it WdsLstItem, set text to Windows
    - Add another item, name it TablesMenu, set text to Tables add folowing sub menu items
    - New
    - From design
    - Insert a ToolStripMenuSeparator
    - Add column (Add the folowing subitems)
    - Boolean
    - Char
    - DateTime
    - Decimal
    - Int32
    - Int64
    - String
    - Insert a ToolStripMenuSeparator
    - Delete Table
    - Save to open in Excel

    1 ToolsStrip control (Remove what ever you wont be using)

    - Right click on it and Insert standart Items "Optional, you insert your owne items and icons"

    - Set the folwing properties

    - DoubleBuffered = True
    - IsMdiContainer = True
    - Size
    -Width = 1018
    -Height = 700
    - StartPosition = CenterScreen "Optional, you can ignore this or set it to something else"
    - MenuStrip = WdsLstItem

    Done

    4° Add form, with the name WB_FORM again drag it to Forms folder in Solution Explorer

    - In the designer set the folwing properties

    - BAckColor = White "Optional, you can ignore this or set it to something else"
    - DoubleBuffered = True
    - Size "Optional, you can ignore this or set it to something else"
    -Width = 600
    -Height = 600

    Add:

    1 TabControl and name it TBCONTAINER

    Remove all Tabpages

    - In the designer set the folwing properties
    Alignment = Bottom "Optional, you can ignore this or set it to something else"
    Anchor = Top, Bottom, Left, Right
    Location
    X = 6
    Y = 4
    Size
    -Width = 574
    -Height = 552

    Done

    5° Add form, with the name TBDESIGN_FORM drag it to Forms folder in Solution Explorer

    Add:

    2 GroupBoxes
    - On the first one set text to Table info
    - Add 1 Label, set the text property to Name
    - Add 1 Textbox, name it to NAME_TBX
    - Add 3 checkboxes as folows:
    - Name: AAR_CKBX | Text: Allow add row
    - Name: ADR_CKBX | Text: Allow delete row
    - Name: RHV_CKBX | Text: Row header visible
    - On the second one set text to Table info
    - Add 1 Datagridview set name to DESIGNDGV, and add the folowing columns
    - Column Name: "CL1" | Column HeaderText: Name |ColumnType: DataGridViewTextBoxColumn
    - Column Name: "CL2" | Column HeaderText: Caption |ColumnType: DataGridViewTextBoxColumn
    - Column Name: "CL3" | Column HeaderText: Data type |ColumnType: DataGridViewComboBoxColumn
    - Column Name: "CL4" | Column HeaderText: Default value |ColumnType: DataGridViewTextBoxColumn
    - Column Name: "CL5" | Column HeaderText: Expression |ColumnType: DataGridViewTextBoxColumn
    - Column Name: "CL6" | Column HeaderText: Read only |ColumnType: DataGridViewCheckBoxColumn
    - Column Name: "CL7" | Column HeaderText: Alignment |ColumnType: DataGridViewComboBoxColumn
    - Column Name: "CL8" | Column HeaderText: Width |ColumnType: DataGridViewTextBoxColumn
    - Column Name: "CL9" | Column HeaderText: CellFormat |ColumnType: DataGridViewTextBoxColumn
    - Column Name: "CL10" | Column HeaderText: Ucase |ColumnType: DataGridViewCheckBoxColumn

    1 Button, name it CREATETB_BT, set Text to Create

    6° Add a User Control and drag in to the Usercontrols folder in Solution Explorer

    Add:

    1 Datagridview and name it TABLEDGV
    - BackgroundColor = White
    - DataGridViewCellStyle{Alignlement=MiddleCenter}
    - BorderStyle = None

    2 ContextMenuStrip name it TABLEMENU add the folwing items
    - Rename table (Add the folowing subitems)
    - ToolStripTextBox, name it TBNAME_TBX
    - Comit
    - Insert a ToolStripMenuSeparator
    - Align (Add the folowing subitems)
    - Middle Left
    - Middle Center
    - Middle Right
    - Add column (Add the folowing subitems)
    - Boolean
    - Char
    - DateTime
    - Decimal
    - Int32
    - Int64
    - String
    - Rename column (Add the folowing subitems)
    - ToolStripTextBox, name it CLNAME_TBX
    - Comit
    - Insert another ToolStripMenuSeparator
    - Remove table
    - Remove column

    On the Usercontrol properties set ContextMenuStrip to TABLEMENU


    Done

    7° Add a Module, name it GLOBAL_MD, drag in to the Modules folder in Solution Explorer

    8° Add a Class, name it WORKBOOK_CLS, drag in to the Classes folder in Solution Explorer

    MainForm code:

    Code:
    
    Public Class MainFrm
    
        Private Sub MainFrm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            'This properties can be changed on designer, that means this code can be removed
    
            APP_MAINTLSTRIP.GripStyle = ToolStripGripStyle.Hidden
            APP_MAINTLSTRIP.RenderMode = ToolStripRenderMode.System
    
            'End section
    
            'This creates a new Workbook every time application starts 
            Dim NEW_WORKBOOK As New WORKBOOK_CLS
    
        End Sub
    
        Private Sub NewToolStripButton_Click(sender As Object, e As EventArgs) Handles NewToolStripButton.Click
    
            Dim NEW_WORKBOOK As New WORKBOOK_CLS
    
        End Sub
    
        Private Sub NewToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles NewToolStripMenuItem.Click
    
            Dim NEW_WORKBOOK As New WORKBOOK_CLS
    
        End Sub
    
        Private Sub FromDesignToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles FromDesignToolStripMenuItem.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                Dim FRM As New TBDESIGN_FORM With {
                    .Tag = CType(ActiveMdiChild.Tag, WORKBOOK_CLS)
                }
                FRM.Show()
    
            End If
    
        End Sub
    
        Private Sub SaveToolStripButton_Click(sender As Object, e As EventArgs) Handles SaveToolStripButton.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).SAVE_WORKBOOK()
    
            End If
    
        End Sub
    
        Private Sub SaveToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveToolStripMenuItem.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).SAVE_WORKBOOK()
    
            End If
    
        End Sub
    
        Private Sub SaveAsToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveAsToolStripMenuItem.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).SAVE_WORKBOOK()
    
            End If
    
        End Sub
    
        Private Sub OpenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles OpenToolStripMenuItem.Click
    
            OPEN_WRKBOOK()
    
        End Sub
    
        Private Sub OpenToolStripButton_Click(sender As Object, e As EventArgs) Handles OpenToolStripButton.Click
    
            OPEN_WRKBOOK()
    
        End Sub
    
        Private Sub HelpToolStripButton_Click(sender As Object, e As EventArgs) Handles HelpToolStripButton.Click
    
        End Sub
    
        Private Sub BooleanToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles BooleanToolStripMenuItem.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
            End If
    
        End Sub
    
        Private Sub CharToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles CharToolStripMenuItem1.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
            End If
    
        End Sub
    
        Private Sub DateTimeToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles DateTimeToolStripMenuItem1.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
            End If
    
        End Sub
    
        Private Sub DecimalToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles DecimalToolStripMenuItem1.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
            End If
    
        End Sub
    
        Private Sub Int32ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles Int32ToolStripMenuItem1.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
            End If
    
        End Sub
    
        Private Sub StringToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles StringToolStripMenuItem1.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
            End If
    
        End Sub
    
        Private Sub NewToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles NewToolStripMenuItem1.Click
    
            If TypeOf ActiveMdiChild Is WB_FORM Then
    
                DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).ADD_DEFAULT_TABLE()
    
            End If
    
        End Sub
    
        Private Sub FromTemplateToolStripMenuItem_Click(sender As Object, e As EventArgs)
    
    
    
        End Sub
    
        Private Sub DeleteTableToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DeleteTableToolStripMenuItem.Click
    
            DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).REMOVE_TABLE()
    
        End Sub
    
        Private Sub SaveToOpenInExcelToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveToOpenInExcelToolStripMenuItem.Click
    
            DirectCast(ActiveMdiChild.Tag, WORKBOOK_CLS).SAVE_FOR_EXCELL()
    
        End Sub
    
    End Class
    Last edited by Mike Storm; Sep 16th, 2017 at 10:26 PM.

  18. #18
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    WORKBOOK_CLS code:

    Code:
    Public Class WORKBOOK_CLS
        Implements IDisposable
    
        ''' <summary>
        ''' Add a Dataset that will hold the Workbook tables
        ''' </summary>
        Private WBDATASET As DataSet
    
        ''' <summary>
        '''Returns True if its a newly created workbook, False 
        '''if open from a previws saved file
        ''' </summary>
        Private IsNEW As Boolean = False
    
        ''' <summary>
        ''' Returns True if any table in the workbook has been changed 
        ''' or if tables where added
        ''' </summary>
        Private WBHasCHANGES As Boolean = False
    
        ''' <summary>
        ''' Stores file name
        ''' </summary>
        Private WBFULLNAME As String
    
        ''' <summary>
        ''' Stores the form where the workbook is open
        ''' </summary>
        Private WBPARENT As Form
    
        ''' <summary>
        ''' Stores the corrent table "Like ActiveControl or ActiveMdiChild"
        ''' </summary>
        Private ACTIVETABLE As String
    
    #Region "Workbook accessible properties"
    
        Friend ReadOnly Property WORKBOOK_PARENT As Form
            Get
                Return WBPARENT
            End Get
        End Property
    
        Friend Property ACTIVE_TABLE As String
            Get
                Return ACTIVETABLE
            End Get
            Set(value As String)
                ACTIVETABLE = value
            End Set
        End Property
    
        Friend Property WBHas_CHANGES As Boolean
            Get
                Return WBHasCHANGES
            End Get
            Set(value As Boolean)
                WBHasCHANGES = value
            End Set
        End Property
    
    
    #End Region
    
    #Region "Create new workbook"
    
        ''' <summary>
        ''' New workbook with 1 standart table
        ''' </summary>
        Friend Sub New()
    
            Try
    
                IsNEW = True
    
                WORKBOOK_COUNT += 1
    
                WBDATASET = New DataSet(String.Format("Workbook{0}", WORKBOOK_COUNT))
    
                Dim FRM As New WB_FORM With {
                                    .Text = String.Format("Workbook {0}", WORKBOOK_COUNT),
                                    .MdiParent = MainFrm,
                                    .Tag = Me}
    
                FRM.Show()
                ADD_DEFAULT_TABLE()
    
                WBPARENT = FRM
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        ''' <summary>
        ''' Opens a saved workbook or creates a new one from a template
        ''' </summary>
        ''' <param name="File_Name">File full name</param>
        Friend Sub New(FILE_NAME As String)
    
            Try
    
                WBDATASET = New DataSet
                WBDATASET.ReadXml(FILE_NAME)
                Dim FL As New IO.FileInfo(FILE_NAME)
    
                Dim FRM As New WB_FORM
                WBPARENT = FRM
    
                With FRM
    
                    .Text = FL.Name
                    .Name = WBDATASET.DataSetName
                    .MdiParent = MainFrm
    
                    For Each T As DataTable In WBDATASET.Tables
    
                        Dim UC As New TABLEVIEWER_UC(WBDATASET, T.TableName)
                        Dim TBPAGE As New TabPage(T.ExtendedProperties.Item("Caption").ToString) With {
                                                    .Tag = T.TableName}
                        TBPAGE.Controls.Add(UC)
                        .TBCONTAINER.TabPages.Add(TBPAGE)
    
                    Next
    
                    .Tag = Me
                    .Show()
                    WBFULLNAME = FILE_NAME
                    IsNEW = False
    
                    If WBDATASET.Tables.Count > 0 Then
    
                        DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedIndex = 0
                        ACTIVE_TABLE = DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedTab.Tag.ToString
    
                    End If
    
                End With
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
    #End Region
    
    
    #Region "Save/SaveAs/Save for open in excell"
    
        ''' <summary>
        ''' Saves the active workbook
        ''' </summary>
        ''' <returns></returns>
        Friend Function SAVE_WORKBOOK() As Boolean
    
            Try
    
                If IsNEW Then
    
                    Using SVDLG As New SaveFileDialog
    
                        SVDLG.Filter = "mXml file|*.mXml|Xml files |*.xml"
                        SVDLG.ShowDialog()
    
                        If SVDLG.FileName <> "" Then
    
                            WBDATASET.WriteXml(SVDLG.FileName, XmlWriteMode.WriteSchema)
    
                            WBFULLNAME = SVDLG.FileName
                            IsNEW = False
                            Dim FL As New IO.FileInfo(SVDLG.FileName)
                            WBPARENT.Text = FL.Name
                            WBHasCHANGES = False
    
                            Return True
    
                        Else
    
                            Return False
    
                        End If
    
                    End Using
    
                Else
    
                    WBDATASET.WriteXml(WBFULLNAME, XmlWriteMode.WriteSchema)
                    WBHasCHANGES = False
                    Return True
    
                End If
    
                WBHasCHANGES = False
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return False
            End Try
    
        End Function
    
        ''' <summary>
        ''' Saves the active table
        ''' Only the active table will be saved to a new file
        ''' </summary>
        Friend Sub SAVE_FOR_EXCELL()
    
            Try
    
                Using SVDLG As New SaveFileDialog
    
                    SVDLG.Filter = "Xml files |*.xml"
                    SVDLG.ShowDialog()
    
                    If SVDLG.FileName <> "" Then
    
                        WBDATASET.Tables(ACTIVE_TABLE).WriteXml(SVDLG.FileName, True)
    
                        WBFULLNAME = SVDLG.FileName
                        IsNEW = False
                        Dim FL As New IO.FileInfo(SVDLG.FileName)
                        WBPARENT.Text = FL.Name
                        WBHasCHANGES = False
    
                    End If
    
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
    
    #End Region
    
    #Region "Tables"
    
    
        ''' <summary>
        ''' Generate sheet name
        ''' </summary>
        ''' <returns></returns>
        Private Function GET_TABLENAME() As String
    
            Try
    
                Dim TABLESCOUNT = 1
    
                If WBDATASET.Tables.Count > 0 Then
    
                    Dim CHK As Boolean = True
    
                    Do While CHK = True
    
    
                        If WBDATASET.Tables.Contains(String.Format("Table{0}", TABLESCOUNT)) Then
    
                            TABLESCOUNT += 1
    
                        Else
    
                            Return String.Format("Table{0}", TABLESCOUNT)
                            CHK = False
    
                        End If
    
                    Loop
    
                Else
    
                    Return String.Format("Table{0}", TABLESCOUNT)
    
                End If
    
                TABLESCOUNT = 0
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return Nothing
            End Try
    
        End Function
    
        ''' <summary>
        ''' Adds a standart table that contains only one column
        ''' </summary>
        Friend Sub ADD_DEFAULT_TABLE()
    
            Try
    
                Using TB As New DataTable(GET_TABLENAME)
    
                    'Setting table extended properties to configure the datagridview control and Tabpage text
                    TB.ExtendedProperties.Add("AllowAddRow", "True")
                    TB.ExtendedProperties.Add("AllowDeleteRow", "True")
                    TB.ExtendedProperties.Add("Caption", TB.TableName)
                    TB.ExtendedProperties.Add("RowHdrVisible", "True")
                    '---------------------------------------------------------------------
    
                    Using CL As New DataColumn
    
                        With CL
    
                            'Setting column extended properties to configure datagridview columns Alignment/Width/CellFormat
                            .ExtendedProperties.Add("Alignment", "16")
                            .ExtendedProperties.Add("Width", "100")
                            .ExtendedProperties.Add("Cell_Format", "None")
                            .ExtendedProperties.Add("Ucase", "False")
                            '----------------------------------------------------------------------------------------------
    
                            .ColumnName = "CL1"
                            .DataType = Type.GetType("System.String")
                            .Caption = "CL1"
    
                        End With
    
                        TB.Columns.Add(CL)
    
                    End Using
    
    
                    WBDATASET.Tables.Add(TB)
    
                    Dim UC As New TABLEVIEWER_UC(WBDATASET, TB.TableName)
    
                    Dim TBPAGE As New TabPage(TB.ExtendedProperties.Item("Caption").ToString) With {
                        .Tag = TB.TableName
                    }
                    TBPAGE.Controls.Add(UC)
    
                    DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.TabPages.Add(TBPAGE)
                    DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedTab = TBPAGE
                    ACTIVE_TABLE = TB.TableName
    
                    If WBDATASET.Tables.Count > 1 Then
    
                        WBHasCHANGES = True
    
                    End If
    
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        ''' <summary>
        ''' Adds a new table from design
        ''' </summary>
        ''' <param name="TBL"></param>
        Friend Sub NEW_DESIGN(TBL As DataTable)
    
            Try
    
                TBL.TableName = String.Format(GET_TABLENAME)
    
                If TBL.ExtendedProperties("Caption") Is "User design" Then
    
                    TBL.ExtendedProperties("Caption") = TBL.TableName
    
                End If
    
                WBDATASET.Tables.Add(TBL)
    
                Dim UC As New TABLEVIEWER_UC(WBDATASET, TBL.TableName)
                Dim TBPAGE As New TabPage(TBL.ExtendedProperties.Item("Caption").ToString) With {
                    .Tag = TBL.TableName
                }
                TBPAGE.Controls.Add(UC)
    
                DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.TabPages.Add(TBPAGE)
                DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedTab = TBPAGE
    
    
                ACTIVE_TABLE = TBL.TableName
    
    
                WBHasCHANGES = True
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        ''' <summary>
        ''' Renames a datatable (changes table Caption)
        ''' </summary>
        ''' <param name="NEW_NAME"></param>
        Friend Function RENAME_TABLE(NEW_NAME As String) As Boolean
    
            Try
    
                Dim CAPTION_TAKEN As Boolean = False
    
                For Each TB As DataTable In WBDATASET.Tables
    
                    If TB.ExtendedProperties("Caption").ToString = NEW_NAME Then
    
                        CAPTION_TAKEN = True
    
                    End If
    
                Next
    
                If CAPTION_TAKEN Then
    
                    MessageBox.Show(String.Format("There is already a table with the name {0}!!!", NEW_NAME), "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    Return False
    
                Else
    
                    WBDATASET.Tables.Item(ACTIVE_TABLE).ExtendedProperties("Caption") = NEW_NAME
                    DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedTab.Text = NEW_NAME
                    Return True
    
                    WBHasCHANGES = True
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return Nothing
            End Try
    
        End Function
    
        ''' <summary>
        ''' Removes  datatable from the dataset
        ''' </summary>
        Friend Sub REMOVE_TABLE()
    
            Try
    
                If WBDATASET.Tables.Count > 1 Then
    
                    Dim RSLT As DialogResult = MessageBox.Show(String.Format("Do you want to delete table {0}", WBDATASET.Tables(ACTIVE_TABLE).ExtendedProperties("Caption")), "Table to xml...", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
    
                    If RSLT = DialogResult.OK Then
    
                        If String.IsNullOrEmpty(ACTIVE_TABLE) = False Then
    
                            WBDATASET.Tables.Remove(ACTIVE_TABLE)
    
                            DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedTab.Controls.Clear()
                            DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.TabPages.Remove(DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedTab)
    
                            WBHasCHANGES = True
    
                        End If
    
                    End If
    
                Else
    
                    MessageBox.Show("The workbook has to contain at least one table!!!", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
    #Region "Columns"
    
        ''' <summary>
        ''' Generate column name
        ''' </summary>
        ''' <returns></returns>
        Private Function GET_COLUMNNAME() As String
    
            Try
    
                Dim CLCOUNT As Integer = 1
    
                Dim CHK As Boolean = True
    
                Do While CHK = True
    
                    If WBDATASET.Tables(ACTIVE_TABLE).Columns.Contains(String.Format("CL{0}", CLCOUNT)) Then
    
                        CLCOUNT += 1
    
                    Else
    
                        Return String.Format("CL{0}", CLCOUNT)
                        CHK = False
    
                    End If
    
                Loop
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Function
    
        ''' <summary>
        ''' Changes column extendedproperty value
        ''' </summary>
        ''' <param name="CL_NAME">Column name</param>
        ''' <param name="EXT_PROPERTY">Extended property name</param>
        ''' <param name="NEWVL">New value</param>
        ''' <returns></returns>
        Friend Function CHANGE_PROPERTIES(CL_NAME As String, EXT_PROPERTY As String, NEWVL As String) As Boolean
    
            Try
    
                WBDATASET.Tables(ACTIVE_TABLE).Columns(CL_NAME).ExtendedProperties.Item(EXT_PROPERTY) = NEWVL
    
                WBHasCHANGES = True
                Return True
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return False
            End Try
    
        End Function
    
        ''' <summary>
        ''' Renames a column
        ''' </summary>
        ''' <param name="CLNAME">Column real name</param>
        ''' <param name="NEW_CAPTION">New header text to be dysplayed in datagridview column header</param>
        ''' <returns></returns>
        Friend Function RENAME_COLUMN(CLNAME As String, NEW_CAPTION As String) As Boolean
    
            Try
    
                If WBDATASET.Tables(ACTIVE_TABLE).Columns.Contains(CLNAME) Then
    
                    Dim CAPTION_TAKEN As Boolean = False
    
                    For Each CL As DataColumn In WBDATASET.Tables(ACTIVE_TABLE).Columns
    
                        If CL.Caption = NEW_CAPTION Then
    
                            CAPTION_TAKEN = True
    
                        End If
    
                    Next
    
                    If CAPTION_TAKEN Then
    
                        MessageBox.Show("The name chosen is already in use.", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                        Return False
    
                    Else
    
                        WBDATASET.Tables(ACTIVE_TABLE).Columns(CLNAME).Caption = NEW_CAPTION
    
                        WBHasCHANGES = True
    
                        Return True
    
                    End If
    
                Else
    
                    Return False
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return False
            End Try
    
        End Function
    
        ''' <summary>
        ''' Adds a new column to the active table
        ''' </summary>
        ''' <param name="CL_DATATYPE"></param>
        Friend Sub NEW_COLUMN(CL_DATATYPE As String)
    
            Try
    
                Using CL As New DataColumn
    
                    With CL
    
                        .Caption = GET_COLUMNNAME()
                        .ColumnName = GET_COLUMNNAME()
                        .DataType = Type.GetType(String.Format("System.{0}", CL_DATATYPE))
    
                    End With
    
                    WBDATASET.Tables(ACTIVE_TABLE).Columns.Add(CL)
                    WBHasCHANGES = True
    
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        ''' <summary>
        ''' Removes a column
        ''' </summary>
        ''' <param name="CLNAME">Column name</param>
        Friend Sub REMOVE_COLUMN(CLNAME As String)
    
            Try
    
                If WBDATASET.Tables(ACTIVE_TABLE).Columns.Count > 1 Then
    
                    Dim RSLT As DialogResult = MessageBox.Show(String.Format("Do you want to delete table {0}",
                                                                             WBDATASET.Tables(ACTIVE_TABLE).Columns(CLNAME).Caption),
                                                               "Table to xml...", MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
    
                    If RSLT = DialogResult.OK Then
    
                        WBDATASET.Tables(ACTIVE_TABLE).Columns.Remove(CLNAME)
                        WBHasCHANGES = True
    
                    End If
    
                Else
    
                    MessageBox.Show("The table has to contain at least one column!!!", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
    #End Region
    
    #End Region
    
    #Region "IDisposable Support"
        Private disposedValue As Boolean ' To detect redundant calls
    
        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not disposedValue Then
                If disposing Then
                    ' TODO: dispose managed state (managed objects).
                    WBDATASET.Dispose()
                End If
    
                ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
                ' TODO: set large fields to null.
            End If
            disposedValue = True
        End Sub
    
        ' TODO: override Finalize() only if Dispose(disposing As Boolean) above has code to free unmanaged resources.
        'Protected Overrides Sub Finalize()
        '    ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub
    
        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
            Dispose(True)
            ' TODO: uncomment the following line if Finalize() is overridden above.
            ' GC.SuppressFinalize(Me)
        End Sub
    #End Region
    End Class

  19. #19
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    TBDESIGN_FORM code:

    Code:
    Imports System.Text
    
    Public Class TBDESIGN_FORM
    
        Private Sub CREATETB_BT_Click(sender As Object, e As EventArgs) Handles CREATETB_BT.Click
    
            CREATE_TABLE()
            Close()
    
        End Sub
    
    #Region "Form events"
    
        Private Sub TBDESIGN_FORM_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            'List of data types, not all of them!
            CType(DESIGNDGV.Columns(2), DataGridViewComboBoxColumn).Items.AddRange({"Boolean", "Char", "DateTime", "Decimal", "Int32", "String"})
    
            'Column alignement
            CType(DESIGNDGV.Columns("CL7"), DataGridViewComboBoxColumn).DataSource = New BindingSource(ColumnAlign, Nothing)
            CType(DESIGNDGV.Columns("CL7"), DataGridViewComboBoxColumn).DisplayMember = "Key"
            CType(DESIGNDGV.Columns("CL7"), DataGridViewComboBoxColumn).ValueMember = "Value"
    
        End Sub
    
    #End Region
    
    #Region "DESIGNDGV events"
    
        Private Sub DESIGNDGV_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DESIGNDGV.RowPostPaint
    
            Dim RNMB As Integer = e.RowIndex + 1
            Using BRSH As SolidBrush = New SolidBrush(DESIGNDGV.RowHeadersDefaultCellStyle.ForeColor)
                e.Graphics.DrawString(RNMB.ToString, DESIGNDGV.DefaultCellStyle.Font, BRSH, e.RowBounds.Location.X + 20, e.RowBounds.Location.Y + 4)
            End Using
    
        End Sub
    
    #End Region
    
    #Region "Methodes"
    
        Private Sub CREATE_TABLE()
    
            Try
    
                'Lock Datagridview
                DESIGNDGV.ReadOnly = True
                DESIGNDGV.AllowUserToAddRows = False
                'On design error found cancel
                Dim CancelTB As Boolean = False
                'Error/s info
                Dim MSG_STR As New StringBuilder
    
                If DESIGNDGV.Rows.Count > 0 Then
    
                    Using TB As New DataTable("TempTB")
    
                        'Setting table extended properties to configure the datagridview control and Tabpage text
                        TB.ExtendedProperties.Add("AllowAddRow", AAR_CKBX.Checked.ToString)
    
                        TB.ExtendedProperties.Add("AllowDeleteRow", ADR_CKBX.Checked.ToString)
    
                        TB.ExtendedProperties.Add("Caption", If(String.IsNullOrWhiteSpace(NAME_TBX.Text),
                                                  "User design", NAME_TBX.Text))
    
                        TB.ExtendedProperties.Add("RowHdrVisible", RHV_CKBX.Checked.ToString)
                        '---------------------------------------------------------------------
    
                        For Each RW As DataGridViewRow In DESIGNDGV.Rows
    
                            If String.IsNullOrEmpty(CType(RW.Cells(0).Value, String)) Or IsNumeric(RW.Cells(0).Value) Then
    
                                CancelTB = True
    
                                If MSG_STR.ToString.Length = 0 Then
    
                                    MSG_STR.Append(String.Format("Column name is empty or not valid on row {0}", RW.Index + 1))
    
                                Else
    
                                    MSG_STR.AppendLine()
                                    MSG_STR.Append(String.Format("Column name is empty or not valid on row {0}", RW.Index + 1))
    
                                End If
    
                            ElseIf IsALPHANUMR(CType(RW.Cells(0).Value, String)) = False Then
    
                                If MSG_STR.ToString.Length = 0 Then
    
                                    MSG_STR.Append(String.Format("Column name '{0}' is not a valid on row {1}", RW.Cells(0).Value, RW.Index + 1))
    
                                Else
    
                                    MSG_STR.AppendLine()
                                    MSG_STR.Append(String.Format("Column name '{0}' is not a valid on row {1}", RW.Cells(0).Value, RW.Index + 1))
    
                                End If
    
                            ElseIf String.IsNullOrEmpty(CType(RW.Cells(2).Value, String)) Then
    
                                CancelTB = True
    
                                If MSG_STR.ToString.Length = 0 Then
    
                                    MSG_STR.Append(String.Format("Column data type can not be empty on row {0}", RW.Index + 1))
    
                                Else
    
                                    MSG_STR.AppendLine()
                                    MSG_STR.Append(String.Format("Column data type can not be empty on row {0}", RW.Index + 1))
    
                                End If
    
                            Else
    
                                Using CL As New DataColumn
    
                                    With CL
    
                                        'Setting column extended properties to configure datagridview columns Alignment/Width/CellFormat
                                        .ExtendedProperties.Add("Alignment", If(ColumnAlign.Values.Contains(CInt(RW.Cells(6).Value)),
                                                                RW.Cells(6).Value.ToString, CType(16, String)))
    
                                        .ExtendedProperties.Add("Width", If(Integer.TryParse(CType(RW.Cells(7).Value, String), Nothing),
                                                                CType(RW.Cells(7).Value, String), CType(100, String)))
    
                                        .ExtendedProperties.Add("Cell_Format", If(String.IsNullOrEmpty(CType(RW.Cells(8).Value, String)),
                                                                "", CType(RW.Cells(8).Value, String)))
    
                                        .ExtendedProperties.Add("Ucase", If(CBool(RW.Cells(9).Value),
                                                                CType(RW.Cells(9).Value, String), "False"))
                                        '----------------------------------------------------------------------------------------------
    
                                        .Caption = CType(RW.Cells(1).Value, String)
                                        .ColumnName = CType(RW.Cells(0).Value, String)
                                        .DataType = Type.GetType(String.Format("System.{0}", RW.Cells(2).Value))
    
                                        If Not String.IsNullOrWhiteSpace(CType(RW.Cells(3).Value, String)) Then
                                            .DefaultValue = RW.Cells(3).Value
                                        End If
    
                                        .Expression = CType(RW.Cells(4).Value, String)
                                        .ReadOnly = CBool(RW.Cells(5).Value)
    
    
                                    End With
    
                                    TB.Columns.Add(CL)
    
                                End Using
    
                            End If
    
                        Next
    
                        If CancelTB Then
    
                            MessageBox.Show(MSG_STR.ToString, "Errors found:", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
                        Else
    
                            If Tag IsNot Nothing Then
    
                                DirectCast(Tag, WORKBOOK_CLS).NEW_DESIGN(TB)
    
                            End If
    
    
                        End If
    
                    End Using
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
            DESIGNDGV.ReadOnly = False
            DESIGNDGV.AllowUserToAddRows = True
    
        End Sub
    
        Private Sub DESIGNDGV_RowsAdded(sender As Object, e As DataGridViewRowsAddedEventArgs) Handles DESIGNDGV.RowsAdded
    
            DESIGNDGV.Rows(e.RowIndex).Cells(2).Value = "String"
    
        End Sub
    
    #End Region
    
    End Class

  20. #20
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    WB_FORM code:

    Code:
    Imports System.ComponentModel
    
    Public Class WB_FORM
    
        Private Sub WB_FORM_Closed(sender As Object, e As EventArgs) Handles Me.Closed
    
            If Tag IsNot Nothing Then
    
                DirectCast(Tag, WORKBOOK_CLS).Dispose()
    
            End If
    
        End Sub
    
        Private Sub WB_FORM_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
    
            If DirectCast(Tag, WORKBOOK_CLS).WBHas_CHANGES Then
    
                Dim RSLT As DialogResult = MessageBox.Show("Do you want to save changes?", "Table to xml...", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)
    
                If RSLT = DialogResult.Yes Then
    
                    If DirectCast(Tag, WORKBOOK_CLS).SAVE_WORKBOOK() = False Then
    
                        e.Cancel = True
    
                    End If
    
                ElseIf RSLT = DialogResult.Cancel Then
    
                    e.Cancel = True
    
                End If
    
            End If
    
        End Sub
    
        Private Sub TBCONTAINER_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TBCONTAINER.SelectedIndexChanged
    
            If TBCONTAINER.TabCount > 0 Then
    
                DirectCast(Tag, WORKBOOK_CLS).ACTIVE_TABLE = TBCONTAINER.SelectedTab.Tag.ToString
    
            End If
    
        End Sub
    
    End Class
    GLOBAL_MD code:

    Code:
    Imports System.Runtime.CompilerServices
    
    Module GLOBAL_MD
    
        ''' <summary>
        ''' Count's created workbook's in the current session
        ''' </summary>
        Friend WORKBOOK_COUNT As Integer = 0
    
        ''' <summary>
        ''' Column align
        ''' </summary>
        Friend ColumnAlign As New Dictionary(Of String, Integer)
    
        ''' <summary>
        ''' To execute on application startup
        ''' </summary>
        Friend Sub EXECUTEonSTART()
    
            Try
    
                ColumnAlign.Add("Middle Left", 16)
                ColumnAlign.Add("Middle Center", 32)
                ColumnAlign.Add("Middle Right", 64)
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        ''' <summary>
        ''' Opens a existing workbook file
        ''' </summary>
        Friend Sub OPEN_WRKBOOK()
    
            Try
    
                Using OFDLG As New OpenFileDialog
    
                    OFDLG.Filter = "mXml file|*.mXml|Xml files |*.xml"
                    OFDLG.Multiselect = False
    
                    If OFDLG.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
    
                        Dim WB As New WORKBOOK_CLS(OFDLG.FileName)
    
                    End If
    
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        ''' <summary>
        ''' First leter of a string to uppercase
        ''' </summary>
        ''' <param name="TXT"></param>
        ''' <returns></returns>
        Friend Function FIRST_LETTER_ToUpper(TXT As String) As String
    
            Try
    
                If String.IsNullOrWhiteSpace(TXT) And IsNumeric(TXT) = False Then
    
                    Return TXT
    
                Else
    
                    Return Char.ToUpper(TXT(0)) & TXT.Substring(1)
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return TXT
            End Try
    
        End Function
    
        ''' <summary>
        ''' Based on post 7 from the folwing thread in VBForums
        ''' http://www.vbforums.com/showthread.php?610866-Checking-a-textbox-to-see-if-it-is-alphanumeric
        ''' </summary>
        ''' <param name="TXT"></param>
        ''' <returns></returns>
        Friend Function IsALPHANUMR(TXT As String) As Boolean
    
            Try
    
                If TXT.All(Function(ch) Char.IsLetterOrDigit(ch)) Then
    
                    Return True
    
                Else
    
                    Return False
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return Nothing
            End Try
    
        End Function
    
        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="VL"></param>
        ''' <param name="DType"></param>
        ''' <returns></returns>
        Friend Function IsDATA_Type(VL As String, DType As String) As Boolean
    
            Try
    
                If DType = "System.Int32" Or DType = "System.Int64" Then
    
                    If Not Integer.TryParse(VL, Nothing) Then
    
                        Return False
    
                    Else
    
                        Return True
    
                    End If
    
                ElseIf DType = "System.DateTime" Then
    
                    If Date.TryParse(VL, Nothing) Then
    
                        Return True
    
                    Else
    
                        Return False
    
                    End If
    
                ElseIf DType = "System.TimeSpan" Then
    
                    If TimeSpan.TryParse(VL, Nothing) Then
    
                        Return True
    
                    Else
    
                        Return False
    
                    End If
    
                ElseIf DType = "System.Decimal" Then
    
                    If Decimal.TryParse(VL, Nothing) Then
    
                        Return True
    
                    Else
    
                        Return False
    
                    End If
    
                Else
    
                    Return True
    
                End If
    
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return Nothing
            End Try
    
        End Function
    
    End Module
    
    Module App_Extensions
    
        ''' <summary>
        ''' Obtained at VBForums
        ''' Unchanged code
        ''' http://www.vbforums.com/showthread.php?612740-How-to-make-DataGridView-render-fast
        ''' </summary>
        ''' <param name="aDGV"></param>
        ''' <param name="setting"></param>
        <Extension()>
        Public Sub DoubleBuffered(aDGV As DataGridView, Optional setting As Boolean = True)
            'usage: SomeDataGridView.DoubleBuffered(True)
            Dim dgvType As Type = aDGV.GetType
            Dim propInfo As Reflection.PropertyInfo
            propInfo = dgvType.GetProperty("DoubleBuffered",
                                           Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic)
    
            propInfo.SetValue(aDGV, setting, Nothing)
        End Sub
    
    End Module

  21. #21
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    TABLEVIEWER_UC code:

    Code:
    
    Public Class TABLEVIEWER_UC
    
        Friend BSOURSE As New BindingSource
        Private ACTV_CL As Integer
    #Region "Usercontrol Events"
    
        Friend Sub New(DST As DataSet, Table_Name As String)
    
    
            ' This call is required by the designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
    
            BSOURSE.DataSource = DST
            BSOURSE.DataMember = Table_Name
    
            TABLEDGV.DataSource = BSOURSE
            TABLEDGV.DoubleBuffered(True)
    
            'Use the table and columns extended properties to configure how the datagridview displayes data
            TABLEDGV.AllowUserToAddRows = CBool(DST.Tables(Table_Name).ExtendedProperties("AllowAddRow"))
            TABLEDGV.AllowUserToDeleteRows = CBool(DST.Tables(Table_Name).ExtendedProperties("AllowDeleteRow"))
            TABLEDGV.RowHeadersVisible = CBool(DST.Tables(Table_Name).ExtendedProperties("RowHdrVisible"))
    
            For I As Integer = 0 To TABLEDGV.Columns.Count - 1
    
    
    
                If DST.Tables(Table_Name).Columns.Item(I).Caption.Length > 0 Then
    
                    TABLEDGV.Columns(I).HeaderText = DST.Tables(Table_Name).Columns.Item(I).Caption
    
                End If
    
                Dim ALGN = DST.Tables(Table_Name).Columns.Item(I).ExtendedProperties("Alignment")
                If ALGN.ToString.Length > 0 Then
    
                    If ColumnAlign.Values.Contains(CInt(ALGN)) Then
    
                        TABLEDGV.Columns(I).DefaultCellStyle.Alignment = CType(ALGN, DataGridViewContentAlignment)
    
                    End If
    
                End If
    
                Dim CL_WIDTH As Integer = CInt(DST.Tables(Table_Name).Columns.Item(I).ExtendedProperties("Width"))
                If CL_WIDTH > 0 Then
    
                    TABLEDGV.Columns(I).Width = CL_WIDTH
    
                End If
    
                Dim CELL_FORMAT As String = CType(DST.Tables(Table_Name).Columns.Item(I).ExtendedProperties("Cell_Format"), String)
                If CELL_FORMAT IsNot String.Empty Then
    
                    TABLEDGV.Columns(I).DefaultCellStyle.Format = CELL_FORMAT
    
                End If
    
            Next
    
            'End section
    
        End Sub
    
        Private Sub TABLEVIEWER_UC_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            'Docks style on parent control
            Dock = DockStyle.Fill
    
        End Sub
    
    #End Region
    
    #Region "Contextmenu"
    
        Private Sub RemoveTableToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RemoveTableToolStripMenuItem.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).REMOVE_TABLE()
    
        End Sub
    
        Private Sub ComitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ComitToolStripMenuItem.Click
    
            If Not String.IsNullOrWhiteSpace(TBNAME_TBX.Text) Then
    
                DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).RENAME_TABLE(TBNAME_TBX.Text)
                TBNAME_TBX.Text = String.Empty
    
            Else
    
                MessageBox.Show("The new name can not be empty!!!", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    
            End If
    
    
        End Sub
    
        Private Sub LeftToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles LeftToolStripMenuItem.Click
    
            Try
    
                If ACTV_CL > -1 Then
    
                    If DirectCast(MainFrm.ActiveMdiChild.Tag,
                        WORKBOOK_CLS).CHANGE_PROPERTIES(TABLEDGV.Columns(ACTV_CL).DataPropertyName,
                                                        "Alignment", CInt(DataGridViewContentAlignment.MiddleLeft).ToString) Then
    
                        TABLEDGV.Columns(ACTV_CL).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
    
                    Else
    
                        MessageBox.Show("Wasn't possible to perform the changes...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
    
                    End If
    
                Else
    
                    MessageBox.Show("Select the column by placing the mouse over one of it's cell and right click...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        Private Sub CenterToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CenterToolStripMenuItem.Click
    
            Try
    
                If ACTV_CL > -1 Then
    
                    If DirectCast(MainFrm.ActiveMdiChild.Tag,
                        WORKBOOK_CLS).CHANGE_PROPERTIES(TABLEDGV.Columns(ACTV_CL).DataPropertyName,
                                                        "Alignment", CInt(DataGridViewContentAlignment.MiddleCenter).ToString) Then
    
                        TABLEDGV.Columns(ACTV_CL).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
    
                    Else
    
                        MessageBox.Show("Wasn't possible to perform the changes...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
                    End If
    
                Else
    
                    MessageBox.Show("Select the column by placing the mouse over one of it's cell and right click...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        Private Sub RightToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RightToolStripMenuItem.Click
    
            Try
    
                If ACTV_CL > -1 Then
    
                    If DirectCast(MainFrm.ActiveMdiChild.Tag,
                        WORKBOOK_CLS).CHANGE_PROPERTIES(TABLEDGV.Columns(ACTV_CL).DataPropertyName,
                                                        "Alignment", CInt(DataGridViewContentAlignment.MiddleRight).ToString) Then
    
                        TABLEDGV.Columns(ACTV_CL).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
    
                    Else
    
                        MessageBox.Show("Was not possible to perform the changes...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
                    End If
    
                Else
    
                    MessageBox.Show("Select the column by placing the mouse over one of it's cell and right click...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        Private Sub BooleanToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles BooleanToolStripMenuItem.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
        End Sub
    
        Private Sub CharToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles CharToolStripMenuItem1.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
        End Sub
    
        Private Sub DateTimeToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles DateTimeToolStripMenuItem1.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
        End Sub
    
        Private Sub DecimalToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles DecimalToolStripMenuItem1.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
        End Sub
    
        Private Sub Int32ToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles Int32ToolStripMenuItem1.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
        End Sub
    
        Private Sub StringToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles StringToolStripMenuItem1.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).NEW_COLUMN(CType(sender, ToolStripMenuItem).Text)
    
        End Sub
    
        Private Sub ComitToolStripMenuItem1_Click(sender As Object, e As EventArgs) Handles ComitToolStripMenuItem1.Click
    
            Try
    
                If ACTV_CL > -1 Then
    
                    If Not String.IsNullOrWhiteSpace(CLNAME_TBX.Text) Then
    
    
                        If DirectCast(MainFrm.ActiveMdiChild.Tag,
                        WORKBOOK_CLS).RENAME_COLUMN(TABLEDGV.Columns(ACTV_CL).DataPropertyName,
                                                    CLNAME_TBX.Text) Then
    
                            TABLEDGV.Columns(ACTV_CL).HeaderText = CLNAME_TBX.Text
    
                        Else
    
                            MessageBox.Show("Wasn't possible To perform the changes...!!!", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    
                        End If
    
                    Else
    
                        MessageBox.Show("The new name can not be empty!!!", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    
                    End If
    
                Else
    
                        MessageBox.Show("Select the column by placing the mouse over one of it's cell and right click...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        Private Sub RemoveColumnToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RemoveColumnToolStripMenuItem.Click
    
            DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).REMOVE_COLUMN(TABLEDGV.Columns.Item(ACTV_CL).DataPropertyName)
    
        End Sub
    
    #End Region
    
    #Region "TABLEDGV events"
    
        Private Sub TABLEDGV_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles TABLEDGV.RowPostPaint
    
            If TABLEDGV.RowHeadersVisible Then
    
                Dim RNMB As Integer = e.RowIndex + 1
                Using BRSH As SolidBrush = New SolidBrush(TABLEDGV.RowHeadersDefaultCellStyle.ForeColor)
                    e.Graphics.DrawString(RNMB.ToString, TABLEDGV.DefaultCellStyle.Font, BRSH, e.RowBounds.Location.X + 20, e.RowBounds.Location.Y + 4)
                End Using
    
            End If
    
        End Sub
    
        Private Sub TABLEDGV_MouseDown(sender As Object, e As MouseEventArgs) Handles TABLEDGV.MouseDown
    
            If e.Button = MouseButtons.Right Then
    
                Dim R As DataGridView.HitTestInfo = TABLEDGV.HitTest(e.X, e.Y)
    
                ACTV_CL = R.ColumnIndex
    
            End If
    
        End Sub
    
        Private Sub TABLEDGV_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles TABLEDGV.CellValueChanged
    
            Try
    
                If e.RowIndex > -1 Then
    
                    Dim UCASECOLUMN As Boolean = CBool(DirectCast(BSOURSE.DataSource, DataSet).Tables(BSOURSE.DataMember).Columns.Item(
                TABLEDGV.Columns(e.ColumnIndex).DataPropertyName).ExtendedProperties("Ucase"))
    
                    If UCASECOLUMN Then
    
                        TABLEDGV.Rows(e.RowIndex).Cells(e.ColumnIndex).Value = FIRST_LETTER_ToUpper(CType(TABLEDGV.Rows(e.RowIndex).Cells(e.ColumnIndex).Value, String))
    
                    End If
    
                End If
    
                If TABLEDGV.IsCurrentCellDirty Then
    
                    DirectCast(MainFrm.ActiveMdiChild.Tag, WORKBOOK_CLS).WBHas_CHANGES = True
                    Validate()
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    
        Private Sub TABLEDGV_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles TABLEDGV.DataError
    
            MessageBox.Show("The data entered is not valid!!!", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
        End Sub
    
    #End Region
    
    End Class
    Last edited by Mike Storm; Sep 17th, 2017 at 12:36 AM.

  22. #22
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    Application events:

    Code:
    Imports Microsoft.VisualBasic.ApplicationServices
    
    Namespace My
        ' The following events are available for MyApplication:
        ' Startup: Raised when the application starts, before the startup form is created.
        ' Shutdown: Raised after all application forms are closed.  This event is not raised if the application terminates abnormally.
        ' UnhandledException: Raised if the application encounters an unhandled exception.
        ' StartupNextInstance: Raised when launching a single-instance application and the application is already active. 
        ' NetworkAvailabilityChanged: Raised when the network connection is connected or disconnected.
        Partial Friend Class MyApplication
    
            Private Sub MyApplication_Startup(sender As Object, e As StartupEventArgs) Handles Me.Startup
    
                EXECUTEonSTART()
    
            End Sub
    
        End Class
    
    End Namespace

  23. #23
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    170

    Re: opening xml file to datagrid view for editing

    Thank you Mike, can you post a link to this project ?

  24. #24
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    Sure, give a few minutes, i have to finish something i will upload it.

  25. #25
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    You can dowload it from here:

    Link removed...

    In the project i added also another function to save columns width under the event ColumnWidthChanged



    Code:
      Private Sub TABLEDGV_ColumnWidthChanged(sender As Object, e As DataGridViewColumnEventArgs) Handles TABLEDGV.ColumnWidthChanged
    
            Try
    
                Dim STR1 As String = CType(e.Column.Width, String)
                Dim STR2 As String = CType(DirectCast(BSOURSE.DataSource, DataSet).
                    Tables(BSOURSE.DataMember).Columns(e.Column.Index).ExtendedProperties("Width"), String)
    
                If CBool(String.Compare(STR1, STR2)) Then
    
                    If Not DirectCast(MainFrm.ActiveMdiChild.Tag,
                    WORKBOOK_CLS).CHANGE_PROPERTIES(TABLEDGV.Columns(e.Column.Index).DataPropertyName,
                                                    "Width", TABLEDGV.Columns(e.Column.Index).Width.ToString) Then
    
                        MessageBox.Show("Wasn't possible to perform the changes...", "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
    
                    End If
    
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Data.ToString & vbNewLine & ex.StackTrace, "Table to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    Last edited by Mike Storm; Sep 17th, 2017 at 06:08 AM.

  26. #26
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    170

    Re: opening xml file to datagrid view for editing

    thank you Mike, why I'm not able to load this xml with your project ?
    Code:
    <?xml version="1.0" encoding="utf-8" ?>
    <Employees>
     <Employee>
        <EmpId>1</EmpId>
        <Name>Sam</Name>   
        <Sex>Male</Sex>
        <Phone Type="Home">423-555-0124</Phone>
        <Phone Type="Work">424-555-0545</Phone>
       <Address>
          <Street>7A Cox Street</Street>
          <City>Acampo</City>
          <State>CA</State>
          <Zip>95220</Zip>
          <Country>USA</Country>
        </Address>
     </Employee>
     <Employee>
        <EmpId>2</EmpId>
        <Name>Lucy</Name>
        <Sex>Female</Sex>
        <Phone Type="Home">143-555-0763</Phone>
        <Phone Type="Work">434-555-0567</Phone>
        <Address>
          <Street>Jess Bay</Street>
          <City>Alta</City>
          <State>CA</State>
          <Zip>95701</Zip>
          <Country>USA</Country>
        </Address>
     </Employee>
     <Employee>
        <EmpId>3</EmpId>
        <Name>Kate</Name>
        <Sex>Female</Sex>
        <Phone Type="Home">166-555-0231</Phone>
        <Phone Type="Work">233-555-0442</Phone>
        <Address>
          <Street>23 Boxen Street</Street>
          <City>Milford</City>
          <State>CA</State>
          <Zip>96121</Zip>
          <Country>USA</Country>
        </Address>
     </Employee>
     <Employee>
        <EmpId>4</EmpId>
        <Name>Chris</Name>
        <Sex>Male</Sex>
        <Phone Type="Home">564-555-0122</Phone>
        <Phone Type="Work">442-555-0154</Phone>
        <Address>
          <Street>124 Kutbay</Street>
          <City>Montara</City>
          <State>CA</State>
          <Zip>94037</Zip>
          <Country>USA</Country>
        </Address>
     </Employee>
    </Employees>
    Last edited by patel45; Sep 17th, 2017 at 04:03 AM.

  27. #27
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    I'm gona have a look, but thats likely do to the extended properties i use.
    Give a few minutes

  28. #28
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    By the way, how did you save this file?
    You didnt use DataSetWriteXml("FileName", XmlWriteMode.WriteSchema)?

  29. #29
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    170

    Re: opening xml file to datagrid view for editing

    DataTable.WriteXml("FileName")

  30. #30
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    Ok, like i said the 1° is the extended properties thats this project requires, the second is thats in fact 3 tables, or 1 table and 2 child tables.
    If i use DataTable.ReadXml(OFDLG.FileName) it gives me a error "DataTable does not support schema inference "

    this is the code i used:

    Code:
    Friend Sub OPEN_TABLE()
    
            Try
                Using TB As New DataTable()
    
                    Using OFDLG As New OpenFileDialog
    
                        OFDLG.Filter = "mXml file|*.mXml|Xml files |*.xml"
                        OFDLG.Multiselect = False
    
                        If OFDLG.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
    
                            TB.ReadXml(OFDLG.FileName)
                            WBDATASET.Tables.Add(TB)
    
                        End If
    
                    End Using
    
                End Using
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    If i modify the code i had to open a xml file originaly to this:

    Code:
     ''' <summary>
        ''' Opens a saved workbook or creates a new one from a template
        ''' </summary>
        ''' <param name="File_Name">File full name</param>
        Friend Sub New(FILE_NAME As String)
    
            Try
    
                WBDATASET = New DataSet
                WBDATASET.ReadXml(FILE_NAME)
                Dim FL As New IO.FileInfo(FILE_NAME)
    
                Dim FRM As New WB_FORM
                WBPARENT = FRM
    
                With FRM
    
                    .Text = FL.Name
                    .Name = WBDATASET.DataSetName
                    .MdiParent = MainFrm
    
                    For Each T As DataTable In WBDATASET.Tables
    
                        Dim UC As New TABLEVIEWER_UC(WBDATASET, T.TableName)
                        'T.ExtendedProperties.Item("Caption").ToString
    
                        Dim TBPAGE As New TabPage(T.TableName) With {
                                                    .Tag = T.TableName}
                        TBPAGE.Controls.Add(UC)
                        .TBCONTAINER.TabPages.Add(TBPAGE)
    
                    Next
    
                    .Tag = Me
                    .Show()
                    WBFULLNAME = FILE_NAME
                    IsNEW = False
    
                    If WBDATASET.Tables.Count > 0 Then
    
                        DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedIndex = 0
                        ACTIVE_TABLE = DirectCast(MainFrm.ActiveMdiChild, WB_FORM).TBCONTAINER.SelectedTab.Tag.ToString
    
                    End If
    
                End With
    
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbNewLine & ex.StackTrace, "Simple sable to xml...", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    
        End Sub
    also had to comment the folwing code under TABLEVIEWER_UC

    Code:
    'Dim ALGN = DST.Tables(Table_Name).Columns.Item(I).ExtendedProperties("Alignment")
                'If ALGN.ToString.Length > 0 Then
    
                'If ColumnAlign.Values.Contains(CInt(ALGN)) Then
    
                'TABLEDGV.Columns(I).DefaultCellStyle.Alignment = CType(ALGN, DataGridViewContentAlignment)
    
                ' End If
    
                'End If
    It creates me 3 tables.
    So i wonder if this is not a dataset with a Parent table and 2 childtables with relationship's?
    In that case to open it in my project you will need to do some adjustmens in one of the projects.
    This was made save and read from a dataset.

  31. #31
    Addicted Member
    Join Date
    May 2017
    Location
    Italy
    Posts
    170

    Re: opening xml file to datagrid view for editing

    OK Mike, I can now load my files and save changes.
    Thanks

  32. #32
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    A single table with out schema would look like this

    Code:
    <?xml version="1.0" standalone="yes"?>
    <DataSetName>
      <TableName>
        <EmpId>1</EmpId>
        <Name>Sam</Name>
        <Sex>Male</Sex>
        <Home phone>423-555-0124</Home phone>
        <Work phone>424-555-0545</Work phone>
        <Address>7A Cox Street</Address>
        <City>Acampo</City>
        <State>CA</State>
        <Zip>95220</Zip>
        <Country>USA</Country>
      </TableName>
    </DataSetName>
    With the schema and extended properties:

    Code:
    <?xml version="1.0" standalone="yes"?>
    <Workbook1>
      <xs:schema id="Workbook1" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">
        <xs:element name="Workbook1" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="Table2" msprop:AllowDeleteRow="True" msprop:RowHdrVisible="True" msprop:Caption="Table2" msprop:AllowAddRow="True">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="CL1" msdata:Caption="EmpID" msprop:Cell_Format="" msprop:Alignment="64" msprop:Ucase="False" msprop:Width="62" type="xs:int" minOccurs="0" />
                    <xs:element name="CL2" msdata:Caption="Name" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL3" msdata:Caption="Sex" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL4" msdata:Caption="Home phone" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL5" msdata:Caption="Work phone" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL6" msdata:Caption="Address" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL7" msdata:Caption="City" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL8" msdata:Caption="State" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL9" msdata:Caption="Zip" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                    <xs:element name="CL10" msdata:Caption="Country" msprop:Cell_Format="" msprop:Alignment="16" msprop:Ucase="False" msprop:Width="100" type="xs:string" minOccurs="0" />
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      <Table2>
        <CL1>1</CL1>
        <CL2>Sam</CL2>
        <CL3>Male</CL3>
        <CL4>423-555-0124</CL4>
        <CL5>424-555-0545</CL5>
        <CL6>7A Cox Street</CL6>
        <CL7>Acampo</CL7>
        <CL8>CA</CL8>
        <CL9>95220</CL9>
        <CL10>USA</CL10>
      </Table2>
    </Workbook1>

  33. #33
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    Link to updated project:

    Zip file, Bin folder deleted

    Corrected the problem with the missing extended properties in files created out of the application

  34. #34

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    257

    Re: opening xml file to datagrid view for editing

    ok Mike thanks alot for taking your time to help me out.i really appreciate this. I'll run the code and see

  35. #35
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: opening xml file to datagrid view for editing

    You welcome, i think thats a better way to deal with tables and store them in xml files then use a unbound datagridview.
    Improve the code to your needs, correct any errors that i may have not paid atention to.

    Use the project in the last link, code has been modified since i post it the first time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width