2022-03-27

Read columns in Excel - write to CSV in different order

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


No comments:

Post a Comment