Imports System.Data.OleDb
Imports System.Data
Imports System.Text
Imports System.IO
''' <summary>
''' CSV文件读写
''' </summary>
''' <remarks></remarks>
Public NotInheritable Class Csv
Public Shared Encoding As Encoding = Encoding.GetEncoding("GB2312")
Private Shared ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Text;{1}'"
Public Shared Function OpenCsvFile(Optional ByVal Filter As String = "CSV (逗号分隔)(*.csv)|*.csv") As String
Dim Path As String = String.Empty
Using Open As New OpenFileDialog
Open.Multiselect = False
Open.Filter = Filter
Open.InitialDirectory = Application.StartupPath
If Open.ShowDialog = 1 Then Path = Open.FileName
End Using
Return Path
End Function
#Region "Read"
''' <summary>
''' 读取CSV文件
''' </summary>
''' <param name="FilePath">CSV文件路径</param>
''' <param name="HDR">是否有列标题</param>
''' <returns>返回的DataTable</returns>
''' <remarks></remarks>
Public Shared Function Read(ByVal FilePath As String, Optional ByVal HDR As Boolean = True) As DataTable
Dim con As New OleDbConnection(String.Format(ConnectionString, Path.GetDirectoryName(FilePath), IIf(HDR, String.Empty, "HDR=no;")))
Dim Name As String = Path.GetFileNameWithoutExtension(FilePath)
Dim dt As New DataTable(Name)
Using da As New OleDbDataAdapter(String.Format("select * from [{0}.csv]", Name), con)
Try
da.Fill(dt)
Catch ex As OleDbException
MsgBox(ex.Message)
End Try
End Using
con.Dispose()
Return dt
End Function
''' <summary>
''' 读取CSV字符串
''' </summary>
''' <param name="CsvString">Csv格式字符串</param>
''' <returns>返回的DataTable</returns>
''' <remarks></remarks>
Public Shared Function StringRead(ByVal CsvString As String) As DataTable
Dim dt As New DataTable
On Error Resume Next
Dim str() As String = Split(CsvString, vbCrLf)
Dim cs() As String = Split(str(0), ","c)
For i = 0 To cs.Length - 1
dt.Columns.Add(cs(i), GetType(String))
Next
For i = 1 To str.Length - 1
Dim Cells() As String = Split(str(i), ","c)
If Cells.Length <> dt.Columns.Count Then
Continue For
Else
dt.Rows.Add(Split(str(i), ","c))
End If
Next
'dt.PrimaryKey = New DataColumn() {dt.Columns(0)}
Return dt
End Function
''' <summary>
''' 读取CSV文件
''' </summary>
''' <param name="FilePath">CSV文件路径</param>
''' <returns>返回的DataTable</returns>
''' <remarks></remarks>
Public Shared Function Read2(ByVal FilePath As String) As DataTable
Using sr As New StreamReader(FilePath, Encoding)
Return StringRead(sr.ReadToEnd)
sr.Close()
End Using
End Function
#End Region
#Region "Write"
''' <summary>
''' 保存DataTable到CSV文件
''' </summary>
''' <param name="Path">CSV文件路径</param>
''' <param name="Table">要保存的DataTable</param>
''' <remarks></remarks>
Public Shared Sub Save(ByVal Path As String, ByVal Table As DataTable)
Using sw As New StreamWriter(Path, False, Encoding)
sw.Write(CsvString(Table)) : sw.Flush() : sw.Close()
End Using
End Sub
''' <summary>
''' 保存DataRow数组到CSV文件
''' </summary>
''' <param name="Path">CSV文件路径</param>
''' <param name="Rows">要保存的DataRow数组</param>
''' <remarks></remarks>
Public Shared Sub Save(ByVal Path As String, ByVal Rows() As DataRow)
Using sw As New StreamWriter(Path, False, Encoding)
sw.Write(CsvString(Rows)) : sw.Flush() : sw.Close()
End Using
End Sub
''' <summary>
''' 保存字符串到CSV文件
''' </summary>
''' <param name="Path">CSV文件路径</param>
''' <param name="s">要保存字符串</param>
''' <remarks></remarks>
Public Shared Sub Save(ByVal Path As String, ByVal s As String)
Using sw As New StreamWriter(Path, False, Encoding)
sw.Write(s) : sw.Flush() : sw.Close()
End Using
End Sub
#End Region
#Region "Utils"
Private Shared Function CsvString(ByVal Table As DataTable) As StringBuilder
Dim str As New StringBuilder
If Table.Columns.Count = 0 Then Return str
For Each c As DataColumn In Table.Columns
str.Append(CellString(c.ColumnName) & ",")
Next
str.Remove(str.Length - 1, 1).Append(vbCrLf)
For Each r As DataRow In Table.Rows
str.AppendLine(RowString(r))
Next
Return str
End Function
Private Shared Function CsvString(ByVal Rows() As DataRow) As StringBuilder
Dim str As New StringBuilder
For Each r As DataRow In Rows
str.AppendLine(RowString(r))
Next
Return str
End Function
Private Shared Function RowString(ByVal Row As DataRow) As String
Dim str As New StringBuilder
For Each s In Row.ItemArray
str.Append(CellString(s.ToString) & ",")
Next
Return str.Remove(str.Length - 1, 1).ToString
End Function
''' <summary>
''' CSV字符串转换
''' </summary>
''' <param name="s">要转换的字符串</param>
''' <returns>返回转换后的字符串</returns>
''' <remarks></remarks>
Public Shared Function CellString(ByVal s As String) As String
If String.IsNullOrWhiteSpace(s) Then Return String.Empty
Dim str As New StringBuilder(s)
If IsAddChr34(s) Then
str.Replace(Chr(34), Chr(34) & Chr(34))
str.Insert(0, Chr(34))
str.Append(Chr(34))
End If
Return str.ToString
End Function
Private Shared Function IsAddChr34(ByVal s As String) As Boolean
Return s.IndexOf(Chr(10)) <> -1 OrElse s.IndexOf(Chr(13)) <> -1 _
OrElse s.IndexOf(Chr(34)) <> -1 OrElse s.IndexOf(Chr(44)) <> -1
End Function
#End Region
End Class
http://knab.ws/blog/index.php?/archives/10-CSV-file-parser-and-writer-in-C-Part-2.html
http://blog.bossma.cn/csharp/a-fast-csv-reader-tool-user-csharp/