Read from Excel - write to CSV in different column order-CodePudding

I need to understand if there is a possibility, within VB.NET, to be able to read the columns of an Excel file and write them out to a CSV file in a different order.

In practice, the Excel file we are sent has 6 columns: "amount", "branch", stock "," proposal "," quantity "," type ". The company management system accepts the text file with the columns in a different order: "branch", "stock", "amount", "quantity", "type", "proposal". This creates a problem for me because when I go to convert it my ERP fails to recognize that the column is in a different position.

I arrive at the concrete question, I would like to have the possibility to read the columns and make it possible through a script to be able to position them according to the position I decide.

I tried this code for import and convert to txt, but I need another script:

Imports System.IO
Imports ExcelDataReader
Imports System.Text

Public Class Form1

Dim tables As DataTableCollection



Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "(*.xls)|*.xls|(*.xls)|*.xlsx"}
        If ofd.ShowDialog() = DialogResult.OK Then
            txtFileName.Text = ofd.FileName
            Using Stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read)
                Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(Stream)
                    Dim result As DataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
                                                             .ConfigureDataTable = Function(__) New ExcelDataTableConfiguration() With {
                                                             .UseHeaderRow = True}})
                    tables = result.Tables
                    cboSheet.Items.Clear()
                    For Each table As DataTable In tables
                        cboSheet.Items.Add(table.TableName)
                    Next
                End Using
            End Using
        End If
    End Using

End Sub

Private Sub cboSheet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSheet.SelectedIndexChanged

    Dim dt As DataTable = tables(cboSheet.SelectedItem.ToString())
    dgProposte.DataSource = dt

End Sub


Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    Dim writer As TextWriter = New StreamWriter("C:\Users\antonio\Desktop\Prova.txt")

    For i As Integer = 0 To dgProposte.Rows.Count - 2 Step  1
        For j As Integer = 0 To dgProposte.Columns.Count - 1 Step  1
            writer.Write(vbTab & dgProposte.Rows(i).Cells(j).Value.ToString() & vbTab & "")
        Next

        writer.WriteLine("")

    Next
    writer.Close()
    MessageBox.Show("Dati Esportati")

End Sub

CodePudding user response：

Why you don't map a DTO of your data table?

Public Class MioDto
    Property campoUno As String
    Property campoDue As String
    '...ecc
End Class

and then you can fill a dto in a cicle or so...

Dim a As New MioDto() With {.campoUno="...", campoDue="..."}

or if you want you can use

https://github.com/AutoMapper/AutoMapper

When you have a Dto class filled you can use it for generate your txt with youor preferred order.

CodePudding user response：

The tables that you get from importing the Excel sheet(s) have their column names set, and you can index the column by its name.

So, and with a little adjustment to factor out some methods:

Imports System.IO
Imports ExcelDataReader

Public Class Form1

    Dim tables As DataTableCollection

    Private Sub WriteToCsv(tableName As String, filename As String)
        Dim columnWriteOrder = {"branch", "stock", "amount", "quantity", "type", "proposal"}

        Using writer As TextWriter = New StreamWriter(filename)

            Dim tbl = tables(tableName)

            For i As Integer = 0 To dgProposte.Rows.Count - 2

                Dim vals As New List(Of String)

                For j As Integer = 0 To columnWriteOrder.Length - 1
                    Dim val = tbl.Rows(i).Item(columnWriteOrder(j)).ToString()
                    vals.Add(val)
                Next

                writer.WriteLine(String.Join(vbTab, vals))

            Next

        End Using

    End Sub

    Private Sub PopulateSheetNames()
        cboSheet.Items.Clear()

        For Each table As DataTable In tables
            cboSheet.Items.Add(table.TableName)
        Next

    End Sub

    Private Sub cboSheet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSheet.SelectedIndexChanged
        If cboSheet.SelectedIndex >= 0 Then
            Dim tableName = cboSheet.SelectedItem.ToString()
            Dim dt As DataTable = tables(tableName)
            dgProposte.DataSource = dt
        End If

    End Sub

    Private Sub bnLoad_Click(sender As Object, e As EventArgs) Handles bnLoad.Click

        Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "(*.xlsx)|*.xlsx|(*.xls)|*.xls", .InitialDirectory = "C:\temp"}
            If ofd.ShowDialog() <> DialogResult.OK Then
                Exit Sub
            End If

            txtFileName.Text = ofd.FileName

            Using Stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read)
                Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(Stream)

                    Dim edsc = New ExcelDataSetConfiguration() With {
                                                             .ConfigureDataTable = Function(__) New ExcelDataTableConfiguration() With {
                                                             .UseHeaderRow = True}}
                    Dim result As DataSet = reader.AsDataSet(edsc)

                    tables = result.Tables

                End Using
            End Using

            PopulateSheetNames()

        End Using

    End Sub

    Private Sub bnSaveAsCsv_Click(sender As Object, e As EventArgs) Handles bnSaveAsCsv.Click

        If cboSheet.SelectedIndex < 0 Then
            MessageBox.Show("Please select a sheet name.", "No sheet name selected", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        Dim sheetName = cboSheet.SelectedItem.ToString()

        If Not String.IsNullOrEmpty(sheetName) Then
            WriteToCsv(sheetName, "C:\temp\Prova.csv")
            MessageBox.Show("Dati Esportati.", "Dati Esportati", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

    End Sub

End Class

I changed the names of the buttons because "Button1" and "Button2" are not descriptive.

(I set the .InitialDirectory of the OpenFileDialog because it was convenient for me.)