如何CSV文件转换为XLS文件在vb.net [英] How to convert csv file to XLS file in vb.net

问题描述

在我的应用程序，我复制从一个CSV到另一个CSV文件中的内容。现在我才知道，我的OUTFILE类型应该是XLS不CSV格式。无论是复制的文件转换为XLS或直接复制到XLS是好的。有什么建议吗？

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 csv. Either copied file converted to xls or directly copy into xls is fine. Any suggestions please?

推荐答案

下面是一个可能的实现。它可以转换任何CSV从文件到DataTable并将其转换为XLS文件与谷歌的ExcelLibrary （它了自由，在项目中第一次添加的引用的DLL）：

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)

灵感来自： HTTP：//www.$c$cproject.com /KB/files/CSVtoTabletoCSV.aspx

这篇关于如何CSV文件转换为XLS文件在vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！