How to convert csv file to XLS file in vb.net

In my application, I am copying the content from one csv to another csv file. Now I came to know that my outfile type should be xls not c开发者_如何学Csv. Either copied file converted to xls or directly copy into xls is fine. Any suggestions please?

I use NPOI to generate XLS documents in .NET. It's very easy to use and it's FREE.

Here is a possible implementation. It converts any csv from file to a DataTable and converts that to a xls-file with Google's ExcelLibrary (it`s free, add a reference to the dll in your project first):

Class FileHandler
    Public Sub New()
    End Sub

    Public Sub New(ByVal sFilename As String)
        FileInf = New FileInfo(sFilename)
    End Sub

    Public Property FileInf() As FileInfo
        Get
            Return m_FileInf
        End Get
        Set(ByVal value As FileInfo)
            m_FileInf = value
        End Set
    End Property
    Private m_FileInf As FileInfo

    Private mvHeaderRow As Integer = -1
    Public Property HeaderRow() As Integer
        Get
            Return mvHeaderRow
        End Get
        Set(ByVal value As Integer)
            mvHeaderRow = value
        End Set
    End Property

    Public Property DataRow1() As Integer
        Get
            Return m_DataRow1
        End Get
        Set(ByVal value As Integer)
            m_DataRow1 = value
        End Set
    End Property
    Private m_DataRow1 As Integer

    Public Property Delimiter() As String
        Get
            Return m_Delimiter
        End Get
        Set(ByVal value As String)
            m_Delimiter = value
        End Set
    End Property
    Private m_Delimiter As String

    Public Property MaxRows() As Integer
        Get
            Return m_MaxRows
        End Get
        Set(ByVal value As Integer)
            m_MaxRows = value
        End Set
    End Property
    Private m_MaxRows As Integer


    Public Function CSVToTable() As DataTable
        Try
            ' trap if the fileinfo has not been added to the object
            If FileInf Is Nothing Then
                Return Nothing
            End If

            Dim dtData As New DataTable()
            Dim oTR As TextReader = IO.File.OpenText(FileInf.FullName)
            Dim sLine As String = Nothing
            Dim arData As String()
            'array of strings to load the data into for each line read in
            Dim drData As DataRow
            Dim iRows As Integer = 0

            'get the header row
            If mvHeaderRow > -1 Then
                For i As Integer = 0 To (mvHeaderRow + 1) - 1
                    sLine = CleanString(oTR.ReadLine())
                Next
            Else
                'get the first row to count the columns
                sLine = CleanString(oTR.ReadLine())
            End If
            'create the columns in the table
            CreateColumns(dtData, sLine)

            'bail if the table failed
            If dtData.Columns.Count = 0 Then
                Return Nothing
            End If

            'reset the text reader
            oTR.Close()
            oTR = IO.File.OpenText(FileInf.FullName)

            'get the first data line
            For i As Integer = 0 To (DataRow1 + 1) - 1
                sLine = CleanString(oTR.ReadLine())
            Next
            While True
                'populate the string array with the line data
                arData = sLine.Split(New String() {Delimiter}, StringSplitOptions.None)
                'load thedatarow
                drData = dtData.NewRow()
                For i As Integer = 0 To dtData.Columns.Count - 1
                    'test for additional fields - this can happen if there are stray commas
                    If i < arData.Length Then
                        drData(i) = arData(i)
                    End If
                Next
                'only get the top N rows if there is a max rows value > 0
                iRows += 1
                If MaxRows > 0 AndAlso iRows > MaxRows Then
                    Exit While
                End If

                'add the row to the table
                dtData.Rows.Add(drData)

                'read in the next line
                sLine = CleanString(oTR.ReadLine())
                If sLine Is Nothing Then
                    Exit While
                End If
            End While
            oTR.Close()
            oTR.Dispose()
            dtData.AcceptChanges()
            Return dtData
        Catch Exc As Exception
            Throw Exc
        End Try
    End Function

    Private Function CleanString(ByVal sLine As String) As String
        Try
            If sLine Is Nothing Then
                Return Nothing
            End If
            sLine = sLine.Replace("'", "''")
            sLine = sLine.Replace("""", "")
            Return sLine
        Catch Exc As Exception
            Throw Exc
        End Try
    End Function

    Private Sub CreateColumns(ByVal oTable As DataTable, ByVal sLine As String)
        Try
            Dim oCol As DataColumn
            Dim sTemp As String
            Dim iCol As Integer = 0
            Dim arData As String() = sLine.Split(New String() {Delimiter}, StringSplitOptions.None)
            For i As Integer = 0 To arData.Length - 1
                'get the header labels from the row
                sTemp = String.Empty
                If mvHeaderRow <> -1 Then
                    sTemp = arData(i)
                End If

                'deal with the empty string (may be missing from the row)
                If (sTemp.Trim()).Length = 0 Then
                    sTemp = String.Format("ColName_{0}", i.ToString())
                End If

                'Deal with duplicate column names in the title row
                iCol = oTable.Columns.Count + 100
                While oTable.Columns.Contains(sTemp)
                    sTemp = String.Format("ColName_{0}", iCol.ToString())
                End While

                oCol = New DataColumn(sTemp, System.Type.[GetType]("System.String"))
                oTable.Columns.Add(oCol)
            Next
        Catch Exc As Exception
            Throw Exc
        End Try
    End Sub

Here is an example on how it works:

Dim ds As New DataSet("DS")
Dim dt As New DataTable("DT")
Dim handler As New FileHandler("C:\Temp\MyExcelFile.csv")
dt = handler.CSVToTable
ds.Tables.Add(dt)

ExcelLibrary.DataSetHelper.CreateWorkbook("C:\Temp\MyExcelFile.xls", ds)

Inspired by: http://www.codeproject.com/KB/files/CSVtoTabletoCSV.aspx

EPPlus is good open source library for generating excel files in .NET, it would be fairly easy to create and excel file from a csv.