如何将csv文件转换为vb.net中的XLS文件

Question

在我的应用程序中，我将内容从一个csv复制到另一个csv文件。现在我开始知道我的outfile类型应该是xls而不是csv。无论是复制文件转换为XLS或直接复制到XLS是好的。有什么建议吗？

Answer 1

我使用NPOI在.NET中生成XLS文档。它使用起来非常简单，而且是免费的。

Answer 2

EPPlus是用于在.NET中生成excel文件的很好的开源库，从csv创建和优化文件相当容易。

Answer 3

这是一个可能的实现。任何CSV转换从文件到DataTable并将其转换为XLS文件与Google's ExcelLibrary（它`免费的，在你的项目中第一个加入到DLL的引用）：

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 

这里是一个如何的例子它的工作原理：

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.codeproject.com/KB/files/CSVtoTabletoCSV.aspx