Dailycode.info

Short solution for short problems

Get String from Excel with DocumentFormat.OpenXml (v2.5.5631.0)

Getting a value from a cell is very easy with the openxml library. But getting a string value isn't that straight forward. You will have to use the SharedStringTablePart. More info on this here: https://msdn.microsoft.com/en-us/library/office/gg278314.aspx

In this example I want to get the value from cell A2 on a worksheet in the excel called test:

Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(localExcel, False)
 Dim workbookPart As WorkbookPart
workbookPart = doc.WorkbookPart
Dim stringTable = workbookPart.GetPartsOfType(Of SharedStringTablePart).FirstOrDefault()
Dim clientSheet As Sheet = doc.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name.ToString.ToLower = "test").FirstOrDefault
Dim worksheetPart As WorksheetPart = CType(workbookPart.GetPartById(clientSheet.Id),WorksheetPart)
 
Dim value As String = GetCellValue(stringTable, worksheetPart, "A2")
If Not value = "" Then
clientNumber = value
End If
End Using

Now the GetCellValue will get the string from the sharedStringTable. The index of the string can be found in the theCell.Cellvalue.Text:

Public Function GetCellValue(ByVal stringTable As SharedStringTablePart,ByVal worksheetPart As WorksheetPart,
ByVal addressName As String) As String
Dim value As String = Nothing
 Dim theCell As Cell = worksheetPart.Worksheet.Descendants(Of Cell).Where(Function(c) c.CellReference = addressName).FirstOrDefault
 
 If theCell IsNot Nothing Then
  If theCell.DataType IsNot Nothing Then
Select Case theCell.DataType.Value
     Case CellValues.SharedString
Dim index = Integer.Parse(theCell.CellValue.Text)
      If stringTable IsNot Nothing Then
       value = stringTable.SharedStringTable.ElementAt(index).InnerText
      End If
Case Else
      value = theCell.InnerText
    End Select
   End If
  End If
  Return value
End Function


Read and write windows forms textboxes into DTO

Without using any kind of existing binding protocols, these functions will fill the controls with the corresponding fields from a class (DTO in this case).

The most important requirement in this case is that you name the textfields like your DTO properties. you can use a random prefix for the text fields. Contain the text fields in a panel or group box. Then for eg. each group box use a different container. In my case I had several tab pages, for each tab page I use a prefix txt1 up to txt5. So a text field is called txt1CREATED or ...

The first function is to Fill the fields on the form. You provide a DTO class, a container and a number that is equal to the number of prefix characters.

    Private Sub FillDataFieldsOnForm(Of T)(ByVal DTO As T, ByVal collection As Control.ControlCollection, ByVal i As Short)

        Dim myType As Type = GetType(T)

 

        For Each txt In collection

            If (TypeOf txt Is DevExpress.XtraEditors.TextEdit) Then

                Try

                    Dim name As String = DirectCast(txt, DevExpress.XtraEditors.TextEdit).Name.Substring(i)

                    Dim myProp As PropertyInfo = myType.GetProperty(name)

 

                    DirectCast(txt, DevExpress.XtraEditors.TextEdit).EditValue = myProp.GetValue(DTO, Nothing)

                Catch ex As Exception

                    DirectCast(txt, DevExpress.XtraEditors.TextEdit).EditValue = ex.Message

                End Try

            End If

        Next txt

 

    End Sub

Here you can see an implementation of this function:

FillDataFieldsOnForm(kKna1, grpHeader.Controls, 3)

Now when you want to read the controls, its equally easy:

ReadDataFieldsOnForm(Of KNB1)(kKnb1, grpCustomerMasterData, 4)

The function for to do this is:

    Private Sub ReadDataFieldsOnForm(Of T)(ByVal DTO As T, ByVal grp As DevExpress.XtraEditors.GroupControl, ByVal i As Short)

        Dim myType As Type = GetType(T)

 

        For Each txt In grp.Controls

            If (TypeOf txt Is DevExpress.XtraEditors.TextEdit) Then

                Try

                    Dim name As String = DirectCast(txt, DevExpress.XtraEditors.TextEdit).Name.Substring(i)

                    Dim myProp As PropertyInfo = myType.GetProperty(name)

 

                    myProp.SetValue(DTO, DirectCast(txt, DevExpress.XtraEditors.TextEdit).EditValue, Nothing)

                Catch ex As Exception

                End Try

            End If

        Next txt

 

    End Sub


Opening a window with ShowDialog in a window that is opened with ShowDialog.

I had a simple form that opened a search form. From this search form it was possible to open a sub search form. What happened was when you opened the sub search form, and found a value, selected it and returned to the (parent)search form, it ended up in the close handler of this form and closed it. After some searching around, Ii found out that the Dialog Result of the sub search form was effecting the dialog result of the search form. The solution I found was to explicitly set the dialog result of the search form to none after the sub search form returned its result.

For example: Lets say my search form is called from a main form using the ShowDialog. In this search form we call a second search form of type SearchSubForm.

Dim frmSubSearchForm As SearchSubForm = new SearchSubForm()

If frmSubSearchForm.ShowDialog = DialogResult.OK Then

'Do something 

End If

 

This code could generate the problem. So to solve this, you can set the dialog result of the search form to none like this:

Dim frmSubSearchForm As SearchSubForm = new SearchSubForm()

If frmSubSearchForm.ShowDialog = DialogResult.OK Then

       Me.DialogResult = DialogResult.None

'Do something 

End If

This solves the problem. I did not had the time to look into this problem in dept. It could be that it has something to do with the main page state or some other problem, but unfortunately I will not have time to investigate it.


Degree symbol gets converted into a strange symbol.

I was developing an application to read the html from a given URL and get some text out of it. The application was for temperature (using degree Celsius) meters that post their findings on a simple html page. Since the structure is configurable, we had to look for a dynamic solution. So we get the html and look for a string that is between 2 given tags.

All went well until I had some troubles with the WebClient.DownloadString method. It works the first time, but hangs the second time. So I decided to use a streamreader on the webresponse.

This works good, you can close the connections and read as much as you like. But then I figured out that the degree sign: ° was converted into some weird symbol.

The reason was that if you do not use a specific encoding for the stream reader, it uses utf-8 encoding, which replaces the degree sign. The solution is simple, but it took me a while to find out which encoding did the trick. At the end it was the Default encoding that worked for me!

He is the code example:

    Private Function GetHTML() As String

        Dim urlCheck As Uri = New Uri(txtUrl.Text)

        Dim request As HttpWebRequest = CType(WebRequest.Create(urlCheck), HttpWebRequest)

        'Timeout is set to 3 seconds.

        request.Timeout = 3000

        Dim sb As StringBuilder = New StringBuilder

        Dim response As HttpWebResponse

        Try

            'If the url doesn't exist the exeption will be thrown after 2 seconds.

            'This will prevent your system from hanging.

            response = CType(request.GetResponse(), HttpWebResponse)

            If response.StatusCode = HttpStatusCode.Found Or response.StatusCode = HttpStatusCode.OK Then

                 Dim receiveStream As Stream = response.GetResponseStream()

                Dim encode As Encoding = System.Text.Encoding.Default

                ' Pipes the response stream to a higher level stream reader with the required encoding format.

                Dim readStream As New StreamReader(receiveStream, encode)

                Dim read(256) As [Char]

                ' Reads 256 characters at a time.   

                Dim count As Integer = readStream.Read(read, 0, 256)

                While count > 0

                    ' Dumps the 256 characters to a string and displays the string to the console.

                    Dim str As New [String](read, 0, count)

                    sb.Append(str)

                    count = readStream.Read(read, 0, 256)

                End While

                ' Releases the resources of the Stream.

                readStream.Close()

                ' Releases the resources of the response.

                response.Close()

            End If

            'request.EndGetResponse(response)

            Return sb.ToString

        Catch ex As Exception

            'If the site is not found, then an exception will be trown

            Return "No response"

        End Try

    End Function

 


Read html from an url using VB.Net

 

If you are planning on reading html from a given url, you can have the problem that the system hangs when the url is not found. Especially when the url is a local url on the network. It tends to hang. To resolve this, you can use the HttpWebRequest. I created a simple implementation that will check if the url exists and reads the html to a text field:

        Dim urlCheck As Uri = New Uri(txtUrl.Text)

        Dim request As HttpWebRequest = CType(WebRequest.Create(urlCheck), HttpWebRequest)

        'Timeout is set to 2 seconds.

        request.Timeout = 2000

        Dim response As HttpWebResponse

        Try

            'If the url doesn't exist the exeption will be thrown after 2 seconds.

            'This will prevent your system from hanging.

            response = CType(request.GetResponse(), HttpWebResponse)

            If response.StatusCode = HttpStatusCode.Found Or response.StatusCode = HttpStatusCode.OK Then

                Try

                    If Not String.IsNullOrEmpty(txtUrl.Text) Then

                        sUrl = txtUrl.Text

                        Dim webClient As System.Net.WebClient = New System.Net.WebClient()

                        Dim result As String = webClient.DownloadString(sUrl)

                        result = result.Replace(vbCrLf, "")

                        webClient.Dispose()

                        txtResult.text = result

                    Else

                        MessageBox.Show("Url is required!")

                        Return ""

                    End If

                Catch ex As Exception

                    MessageBox.Show("Error: " & ex.Message)

                End Try

            End If

        Catch ex As Exception

            'If the site is not found, then an exception will be trown

        End Try

After some testing and debugging work I came up with an even more stable solution, since the DownloadString method can give trouble. I decided to use the StreamReader solution directly on the response. It also saves an extra WebRequest. I implemented here in an extensions method for a string (c# this time):

public static class Extensions

{

    public static string GetHTMLForURL(this String s)

    {

        HttpWebRequest webRequest = WebRequest.Create(s) as HttpWebRequest;

        webRequest.Timeout = 2000; 

        HttpWebResponse response = (HttpWebResponse)webRequest.GetResponse();

        if (response.StatusCode == HttpStatusCode.Found || response.StatusCode == HttpStatusCode.OK)

        {

            using (StreamReader sr = new StreamReader(response.GetResponseStream()))

            {

                //This is an arbitrary size for this example.

                char[] c = null;

                StringBuilder sb = new StringBuilder();

                while (sr.Peek() >= 0)

                {

                    c = new char[5];

                    sr.Read(c, 0, c.Length);

                    //The output will look odd, because

                    //only five characters are read at a time.

                    sb.Append(c);

                }

                return sb.ToString();

            }

        }

        else

        {

            return s;

        }

    }

}


Then implement the method like this:

txtResult.Text = txtUrl.Text.GetHTMLForURL();

More info on this you can find here: http://nevmehta.blogspot.com/2006/11/controlsclear-doesnt-dispose-in-win.html

A simple extension example can be found here:  http://dotnetbyexample.blogspot.com/2007/11/string-extension-methods.html


 


Override combobox item style. (Windows.Forms)

 

I was thinking of a way to show the status of items in a dropdown list in a nice way.

After some googling around I found a pretty nice solution. You can change the color of the text depending on certain values or even draw a rectangle or circle in front of the items. I choose the last solution. A green item would mean that it’s correctly in the database, blue means it’s in the database but with some modifications to be done. Red means it’s not yet in the database.

Here’s how it looks:

 

The implementation needs 2 things. First you have to set the DrawMode to ‘OwnerDrawVariable’.

Then you can override the DrawItem event. Here’s an example of the implementation I did. It is in VB.net this time since my current workplace has VB.Net written programs.

Private Sub ComboBox1_DrawItem(ByVal sender As Object, _

        ByVal e As System.Windows.Forms.DrawItemEventArgs) _

        Handles cmbTable.DrawItem

 

        Dim size As Single

        Dim myFont As System.Drawing.Font

        Dim family As FontFamily

 

        Dim rectColor As New System.Drawing.Color

        Dim tableName As String = dsTables.Tables(0).Rows(e.Index)("TABLE_NAME")

        If tableDiffThenInDBHashtable.Contains(tableName) Then

            size = 9

            rectColor = System.Drawing.Color.Blue

            family = Me.Font.FontFamily

        ElseIf tableInDBHashtable.Contains(tableName) Then

            size = 8

            rectColor = System.Drawing.Color.Green

            family = Me.Font.FontFamily

        Else

            size = 9

            rectColor = System.Drawing.Color.Red

            family = Me.Font.FontFamily

        End If

 

        ' Draw the background of the item.

        e.DrawBackground()

 

        ' Create a square filled with the color.

        Dim rectangle As Rectangle = New Rectangle(2, e.Bounds.Top + 2, _

            e.Bounds.Height, e.Bounds.Height - 4)

        e.Graphics.FillRectangle(New SolidBrush(rectColor), rectangle)

 

        ' Draw each string in the datasource, using a size, color, and font for each item.

        myFont = New Font(family, size, FontStyle.Regular)

        e.Graphics.DrawString(dsTables.Tables(0).Rows(e.Index)("TABLE_NAME"), myFont, System.Drawing.Brushes.Black, _

            New RectangleF(e.Bounds.X + rectangle.Width, e.Bounds.Y, _

            e.Bounds.Width, e.Bounds.Height))

 

        ' Draw the focus rectangle if the mouse hovers over an item.

        e.DrawFocusRectangle()

    End Sub

 

I have filled some hash tables to quickly check the value of the item and decide which color to appoint.

Now it looks like this:

 


Binding doesn't track (pick up) the change if you programatically edit a control.

I'm working on a windows forms application that uses binding to update the contents of the controls. Works like a charm.
When the form got a little more complicated and controls where addedd dynamically, it got a little tricky.
When I changed the value of the controls programatically, the change was not picked up by the bindings. I didn't had to look very long for a solution.
I just added a .Focus or a .Select and then the binding picks up the change.
By example:
 
txtVersion.Text = versionNum.ToString
txtVersion.Select()
or
txtVersion.Text = versionNum.ToString
txtVersion.Focus()