Visual Basic .NET (VB.NET) 实现大量数据产出.csv并转换为.xlsx格式文档

Visual Basic .NET (VB.NET) 实现大量数据查询产出.csv并转换为.xlsx格式文档

原因

使用C1FlexGrid控件查询塞值后发现对于大量数据而言不能直接转出.xls/.xlsx格式文档
（出现数据超过最大行65536的错误提示）

使用语言：VB.NET

解决办法

基础代码：数据查询，基础数据表dt，调用函数方法
（注意：“\test.csv”，这里.csv文档名称对应文档内sheetname，如果需要设置请注意一下。）

Dim dt As DataTable = New DataTable()
Dim conn As SqlConnection = New SqlConnection(cnstr)
//用于数据库连接和关闭释放资源
conn.Open()
//SqlDataAdapter用于检索和保存数据
Dim objDataAdapter As SqlDataAdapter = New SqlDataAdapter()
Dim strSql As String ="select * from table"
objDataAdapter = New SqlDataAdapter(strSql, conn)
//Fill（可更改DataSet中的数据以匹配数据源中的数据）,相当于有一个dt数据表
objDataAdapter.Fill(dt)
conn.Close()
conn.Dispose()
//汇出文档系统路径
Dim savefilepath As String = System.Windows.Forms.Application.StartupPath
//使用，分别对应dt，csv路径，xlsx路径
Dim YN As Boolean = dtToxlsx(dt, savefilepath & "\test.csv", savefilepath & "\test.xlsx")
//整合下文两个方法
Public Function dtToxlsx(ByVal dt As DataTable, ByVal csvFilePath As String, ByVal xlsxFilePath As String) As Boolean
        '将数据表写入CSV文件
        dtTocsv(dt, csvFilePath)
        '将CSV文件转换为XLSX文件
        csvToxlsx(csvFilePath, xlsxFilePath)
        Return True
    End Function

把dt转为.csv文档

StringBuilder类型变量会初始化一段长度，供后续对该变量进行增加。

Public Function dtTocsv(ByVal dt As DataTable, ByVal filePath As String) As Boolean
		//strColumns里放需要设置的列名
        Dim strColumns() As String = New String() {"标题1", "标题2"}
        Using fileStream = New FileStream(filePath, System.IO.FileMode.CreateNew, System.IO.FileAccess.Write)
            Using streamWriter = New StreamWriter(fileStream, System.Text.Encoding.Default)
                //写入列名
                streamWriter.WriteLine(String.Join(",", strColumns))
                For i As Integer = 0 To dt.Rows.Count - 1
                    Dim strRow As New StringBuilder()
                    For j As Integer = 0 To dt.Columns.Count - 1
                        Dim columnValue As String = dt.Rows(i)(j).ToString()
                        //处理包含逗号或引号的数据
                        If columnValue.Contains(",") OrElse columnValue.Contains("""") Then
                            columnValue = """" & columnValue.Replace("""", """""") & """"
                        End If
                        strRow.Append(columnValue)
                        //如果不是最后一列，添加逗号
                        If j < dt.Columns.Count - 1 Then
                            strRow.Append(",")
                        End If
                    Next
                    streamWriter.WriteLine(strRow)
                Next
            End Using
        End Using
    End Function

.csv转.xlsx格式

csvFileName ：.csv路径及名称
xlsxFileName ：.xlsx要存放的路径及名称

Private Sub csvToxlsx(ByVal csvFileName As String, ByVal xlsxFileName As String)
        ' 创建一个新的Excel应用程序实例
        Dim eole = CreateObject("Excel.application")
        Dim excelApp As New Excel.Application
        ' 打开CSV文件
        Dim workbook As Excel.Workbook = excelApp.Workbooks.Open(csvFileName)
        ' 保存工作簿为xlsx格式,51通用參數
        workbook.SaveAs(xlsxFileName, 51)
        ' 关闭工作簿
        workbook.Close()
        ' 退出Excel应用程序
        excelApp.Quit()
        ' 删除原始的CSV文件
        Kill(csvFileName)
        ' 释放对象
        workbook = Nothing
        excelApp = Nothing
    End Sub

总结

对于需要产出文档的大量数据dt而言，将对应数据以csv文本格式汇出，最大优势就是简单快捷。
其次只需要通过Excel实例组件另存为想要的其他文档形式即可。

Visual Basic .NET (VB.NET) 实现大量数据查询产出.csv并转换为.xlsx格式文档