asp.net - 更快地创建 CSV 文件

Question

我通过获取数据表然后遍历该数据表并写入 CSV 文件的每一行来创建 CSV 文件。我的数据源通常有大约 65,000 行。从浏览器下载此过程需要几分钟时间。问题是在本地和开发上它不会花费太长时间，但在客户端上它们通常会超时。

有没有更快的方法来生成这个文件？

Function GenerateCSVFile() As String
    Dim stuPro As New studentProvider.StudentProvider
    Dim emailCenter As New EmailCenter
    Dim strFileName As String = System.IO.Path.GetRandomFileName().Replace(".", "")
    Dim strResult As String = ""

    Dim dtStudent As Data.DataTable
    Dim paymentYear As String = ""
    dtStudent = stuPro.generateDataFile()

    If dtStudent.Rows.Count > 0 Then

        Using sw As New System.IO.StreamWriter(Server.MapPath("Temp/" + strFileName + ".csv"))
            Try
                Dim lineValue As String = ""

                lineValue += "Academic Year, StudentID, SSN, First, Middle, Last"

                sw.WriteLine(lineValue)

                For i As Integer = 0 To dtStudent.Rows.Count - 1

                    lineValue = dtStudent.Rows(i)("fy").ToString
                    lineValue += "," & dtStudent.Rows(i)("uniq_stu_id").ToString
                    lineValue += "," & dtStudent.Rows(i)("ssn").ToString
                    lineValue += "," & dtStudent.Rows(i)("fname").ToString
                    lineValue += "," & dtStudent.Rows(i)("mname").ToString
                    lineValue += "," & dtStudent.Rows(i)("lname").ToString
                    sw.WriteLine(lineValue)

                Next
            Catch ex As Exception
                strResult += ex.ToString
            Finally
                sw.Close()
            End Try

        End Using

        Dim strFriendlyName As String = Date.Now.ToString("MM-dd-yyyy") & ".csv"

        If String.IsNullOrEmpty(strResult) Then

            Dim fs As System.IO.FileStream = Nothing

            fs = System.IO.File.Open(Server.MapPath("Temp/" + strFileName + ".csv"), System.IO.FileMode.Open)
            Dim btFile(fs.Length) As Byte
            fs.Read(btFile, 0, fs.Length)
            fs.Close()

            With Response
                .AddHeader("Content-disposition", "attachment;filename=" & strFriendlyName)
                .ContentType = "application/octet-stream"
                .BinaryWrite(btFile)
                .End()
            End With
        End If
    Else
        strResult = "No records found for specified academic year"
    End If

    Return strResult
End Function

更新代码

Function GenerateCSVFile() As String
    Dim startDate As Date = Date.Now
    Dim enddate As Date = Nothing
    Dim stuPro As New studentProvider.StudentProvider
    Dim emailCenter As New EmailCenter
    Dim strFileName As String = System.IO.Path.GetRandomFileName().Replace(".", "")
    Dim strResult As String = ""

    Dim dtStudent As Data.DataTable
    Dim paymentYear As String = ""
    dtStudent = stuPro.generateDataFile(Session("VendorID"), txtAcademicYear.Text.Trim)

    If dtStudent.Rows.Count > 0 Then

        With Response

            Dim strFriendlyName As String = Date.Now.ToString("MM-dd-yyyy") & ".csv"
            .AddHeader("Content-disposition", "attachment;filename=" & strFriendlyName)
            .ContentType = "application/octet-stream"

            Dim lineValue As StringBuilder = New StringBuilder

            lineValue.Append("Academic Year, StudentID, SSN, First, Middle, Last")

            .Write(lineValue.ToString)

            For i As Integer = 0 To dtStudent.Rows.Count - 1

                lineValue = New StringBuilder
                lineValue.Append(dtStudent.Rows(i)("fy").ToString)
                lineValue.Append("," & dtStudent.Rows(i)("uniq_stu_id").ToString)
                lineValue.Append("," & dtStudent.Rows(i)("ssn").ToString)
                lineValue.Append("," & dtStudent.Rows(i)("fname").ToString)
                lineValue.Append("," & dtStudent.Rows(i)("mname").ToString)
                lineValue.Append("," & dtStudent.Rows(i)("lname").ToString)

                .Write(lineValue.ToString)

            Next
            enddate = Date.Now

            MsgBox(DateDiff(DateInterval.Second, startDate, enddate))

            .End()
        End With
    Else
        strResult = "No records found for specified academic year"
    End If
    Return strResult
End Function

Answer 1

您正在写入一个临时文件，读入该文件，然后他们将该文件的内容写入响应。跳过临时文件，一次一行直接写入响应。这将使浏览器不会认为您的服务器超时，使速度更快，并减少您的应用消耗的内存量。

之后，研究如何对此 Web 请求启用缓存，以便 ASP.NET 无需在短时间内多个用户请求时重新创建 CSV。