We help IT Professionals succeed at work.

VB.net DatagridView export to Fixed Length

triphen
triphen asked
on
Hello Experts,

I have a DataGridView that has been populated by SQL query. I want to export the data in the row in a fixed length format. (ie: 1 coloumn is 5 char, 2nd column is 7 chars, 3rd column is 2 chars, etc) Currently I have my code exporting to CSV, see below. I am lost on how to do fixed length....help?


        Try 'Export the results
            Dim filestartdate As String
            Dim fileenddate As String
            filestartdate = cal_StartDate.SelectionRange.Start.ToString("MM-dd-yyyy")
            fileenddate = cal_EndDate.SelectionRange.Start.ToString("MM-dd-yyyy")
            Dim exportpath As String
            Dim companyname As String
            Dim storenum As String
            companyname = dgv_SysInfo.Item(0, 0).Value.ToString
            storenum = dgv_SysInfo.Item(1, 0).Value.ToString
            exportpath = frm_Config.txt_ExportPath.Text
            Dim headers = (From header As DataGridViewColumn In dgv_Payroll.Columns.Cast(Of DataGridViewColumn)() _
          Select header.HeaderText).ToArray
            Dim rows = From row As DataGridViewRow In dgv_Payroll.Rows.Cast(Of DataGridViewRow)() _
                       Where Not row.IsNewRow _
                       Select Array.ConvertAll(row.Cells.Cast(Of DataGridViewCell).ToArray, Function(c) If(c.Value IsNot Nothing, c.Value.ToString, ""))
            Using sw As New IO.StreamWriter(exportpath & "\" & storenum & "-" & companyname & "-" & filestartdate & "   " & fileenddate & ".csv")
                sw.WriteLine(String.Join(",", headers))
                For Each r In rows
                    sw.WriteLine(String.Join(",", r))
                Next
            End Using
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Error: Exception", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            Exit Sub
        End Try

Open in new window

Comment
Watch Question

View Solutions Only

Danielle GoodrichEngineering Test Technician
Commented:
Have you considered using the .PadLeft or .PadRight methods on your strings to make them a uniform length?
Retired
Distinguished Expert 2017
Commented:
Hi triphen;

Something like the following should give you what you need.
==================================================
'' Test DataTable with Test Data
Dim dt As New DataTable 
Dim col As New DataColumn("Name", GetType(String))
dt.Columns.Add(col)
col = New DataColumn("Age", GetType(String))
dt.Columns.Add(col)
col = New DataColumn("Gender", GetType(String))
dt.Columns.Add(col)
'' Fill the DataTable
Dim dr As DataRow = dt.NewRow()
dr(0) = "Joseph"
dr(1) = "45"
dr(2) = "Male"
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = "Alice"
dr(1) = "30"
dr(2) = "Female"
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = "Jonathan"
dr(1) = "33"
dr(2) = "Male"
dt.Rows.Add(dr)
'' TestData Ends
==================================================

'' Start of actual code
'' The width array is the actual width for each column in the row
Dim width() As String = {"10", "2", "7"} 
'' Holds all the formatted string before writing to the file
Dim lines As New List(Of String)
'' Used so that the begining of each line does not get a comma
Dim firstTime As Boolean = True
'' Enumerate through the DataTable
For Each row As DataRow In dt.AsEnumerable()
    '' Initialize the variables used in the loop
    Dim line As String = String.Empty
    firstTime = True
    '' Format each row of the DataTable
    For i As Integer = 0 To dt.Columns.Count() - 1
        '' Only add a comma if it is not the begining of the line
        If Not firstTime Then 
            line += ","
        End If
        '' Width for this column is gotten from the width array
        line += String.Format("{0,-" + width(i)  + "}", row(i).Trim())
        '' We turn this on after processing the first column in the row
        firstTime = False
    Next
    '' Add the formatted line to the lines List
    lines.Add(line)
Next

'' Now you can write all the lines to the fileSystem

Open in new window