导出到Excel(对于大量数据)时出现的问题(在VB.net0上 [英] issue on exporting to Excel (for large amount data)(On VB.net0
问题描述
当我将30,000条记录导出到vb.net中的Excel中时
它非常慢.还需要4分钟.
然后我用了一个线程进行导出，它的速度很慢，
给出异常(错误索引:一些数字)，在23,000条记录之后调用线程.
还有其他出口方法吗?除了
这个
bcoz慢...
我的代码是打击
=================
导入Microsoft.Office.Interop
公共子ExportXL()
试试
昏暗的saveAsFile为布尔=假
如果filePath<> "然后
saveAsFile = deleteExistingFile(filePath)
如果结束
Dim colArray()as String = New String(){"A"，"B"，"C"，"D"，"E"，"F"，"G"，"H"，"I"，"J ，" K，" L，" M，" N，" O，" P，" Q，" R，" S，" T，" U，" V， "W"，"X"，"Y"，"Z"}
将excelApp变暗为新的Excel.Application()
将excelBook变暗为Excel.Workbook = excelApp.Workbooks.Add
将excelWorksheet设为Excel.Worksheet = CType(excelBook.Worksheets(1)，Excel.Worksheet)
昏暗的colIndex，rowIndex为整数
昏暗的直流电作为System.Data.DataColumn
excelWorksheet.Range(colArray(0)+"1").Value ="Sl"
excelWorksheet.Range(colArray(0)+"1").Font.Bold = True
对于dtEda1.Columns中的每个dc
colIndex + = 1
excelWorksheet.Range(colArray(colIndex)+"1").Value = dc.ColumnName
excelWorksheet.Range(colArray(colIndex)+"1").Font.Bold = True
下一个
Dim dr As System.Data.DataRow
colIndex = 0
rowIndex = 0
对于dtEda1.Rows中的每个dr
rowIndex = rowIndex +1
colIndex = 1
excelWorksheet.Cells(rowIndex + 1，1)= rowIndex
''对于d = 0到dr.Table.Columns.Count-1
''colIndex = colIndex + 1
''excelWorksheet.Cells(rowIndex + 1，colIndex)= dr(d)
''下一个
对于dtEda1.Columns中的每个dc
colIndex = colIndex + 1
excelWorksheet.Cells(rowIndex + 1，colIndex)= dr(dc.ColumnName)
下一个
下一个
excelWorksheet.Columns.AutoFit()
如果saveAsFile然后
试试
excelBook.SaveAs(filePath)
异常捕获
结束尝试
如果结束
excelApp.Visible = True
异常捕获
MsgBox(导出失败-" +例如消息)
终于
dtEda1 =没什么
returnVal =导出完成"
结束尝试
结束子
使用rgrds
lijo John v
when i export 30,000 records into Excel in vb.net
its very slow. it takes 4 more minutes.
then i used one thread for exporting, its slow and
giving exception(bad Index: some number)invoking with a thread after 23,000 records .
and ther is any other method to export? other than
this
bcoz its slow...
My code is blow
=================
Imports Microsoft.Office.Interop
Public Sub ExportXL()
Try
Dim saveAsFile As Boolean = False
If filePath <> "" Then
saveAsFile = deleteExistingFile(filePath)
End If
Dim colArray() As String = New String() {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
Dim excelApp As New Excel.Application()
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
Dim colIndex, rowIndex As Integer
Dim dc As System.Data.DataColumn
excelWorksheet.Range(colArray(0) + "1").Value = "Sl"
excelWorksheet.Range(colArray(0) + "1").Font.Bold = True
For Each dc In dtEda1.Columns
colIndex += 1
excelWorksheet.Range(colArray(colIndex) + "1").Value = dc.ColumnName
excelWorksheet.Range(colArray(colIndex) + "1").Font.Bold = True
Next
Dim dr As System.Data.DataRow
colIndex = 0
rowIndex = 0
For Each dr In dtEda1.Rows
rowIndex = rowIndex + 1
colIndex = 1
excelWorksheet.Cells(rowIndex + 1, 1) = rowIndex
''For d = 0 To dr.Table.Columns.Count - 1
'' colIndex = colIndex + 1
'' excelWorksheet.Cells(rowIndex + 1, colIndex) = dr(d)
''Next
For Each dc In dtEda1.Columns
colIndex = colIndex + 1
excelWorksheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
excelWorksheet.Columns.AutoFit()
If saveAsFile Then
Try
excelBook.SaveAs(filePath)
Catch ex As Exception
End Try
End If
excelApp.Visible = True
Catch ex As Exception
MsgBox("Export Failed- " + ex.Message)
Finally
dtEda1 = Nothing
returnVal = "Export Complete"
End Try
End Sub
with rgrds
lijo John v
推荐答案
当我将30,000条记录导出到Excel中时vb.net
它非常慢.还需要4分钟.
when i export 30,000 records into Excel in vb.net
its very slow. it takes 4 more minutes.
每秒超过100条记录，您期望什么?
That''s more than 100 records a second, what did you expect ?
其运行缓慢且
给出异常(错误索引:一些数字)，在23,000条记录后调用线程.
its slow and
giving exception(bad Index: some number)invoking with a thread after 23,000 records .
我认为那意味着您的数据是错误的，您是否仔细查看了导致错误的记录并考虑了如何编写可预测此类错误并恢复的代码?
I assume that means your data is bad, have you looked closely at the record that causes the error and considered how to write code that anticipates such an error and recovers ?
hi，
当我将30,000条记录导出到vb.net中的Excel中时
它非常慢.还需要4分钟.
然后我用了一个线程进行导出，它的速度很慢，
给出异常(错误索引:一些数字)，在23,000条记录之后调用线程.
还有其他出口方法吗?除了
这个
bcoz慢...
我的代码很打击
================(如果我使用线程调用此方法，则会产生错误
在(大约)23,000条记录之后.如果我不使用线程，则其工作
但是我需要线程bcoz不能为此仅浪费4-5个MNT.0
而且我认为它的速度慢..这段代码在写到Excel时，
我们正在写每个单元格.对于5列的30,000条记录
我们正在访问操作系统30,000 * 5次，(文件大小约为6MB)
那就是为什么要服用4-5毫特.
是吗?还有其他解决方案吗?
导入Microsoft.Office.Interop
公共子ExportXL()
试试
昏暗的saveAsFile为布尔=假
如果filePath<> "然后
saveAsFile = deleteExistingFile(filePath)
如果结束
Dim colArray()as String = New String(){"A"，"B"，"C"，"D"，"E"，"F"，"G"，"H"，"I"，"J ，" K，" L，" M，" N，" O，" P，" Q，" R，" S，" T，" U，" V， "W"，"X"，"Y"，"Z"}
将excelApp变暗为新的Excel.Application()
将excelBook变暗为Excel.Workbook = excelApp.Workbooks.Add
将excelWorksheet设为Excel.Worksheet = CType(excelBook.Worksheets(1)，Excel.Worksheet)
昏暗的colIndex，rowIndex为整数
昏暗的直流电作为System.Data.DataColumn
excelWorksheet.Range(colArray(0)+"1").Value ="Sl"
excelWorksheet.Range(colArray(0)+"1").Font.Bold = True
对于dtEda1.Columns中的每个dc
colIndex + = 1
excelWorksheet.Range(colArray(colIndex)+"1").Value = dc.ColumnName
excelWorksheet.Range(colArray(colIndex)+"1").Font.Bold = True
下一个
Dim dr As System.Data.DataRow
colIndex = 0
rowIndex = 0
对于dtEda1.Rows中的每个dr
rowIndex = rowIndex +1
colIndex = 1
excelWorksheet.Cells(rowIndex + 1，1)= rowIndex
''对于d = 0到dr.Table.Columns.Count-1
''colIndex = colIndex + 1
''excelWorksheet.Cells(rowIndex + 1，colIndex)= dr(d)
''下一个
对于dtEda1.Columns中的每个dc
colIndex = colIndex + 1
excelWorksheet.Cells(rowIndex + 1，colIndex)= dr(dc.ColumnName)
下一个
下一个
excelWorksheet.Columns.AutoFit()
如果saveAsFile然后
试试
excelBook.SaveAs(filePath)
异常捕获
结束尝试
如果结束
excelApp.Visible = True
异常捕获
MsgBox(导出失败-" +例如消息)
终于
dtEda1 =没什么
returnVal =导出完成"
结束尝试
结束Sub
when i export 30,000 records into Excel in vb.net
its very slow. it takes 4 more minutes.
then i used one thread for exporting, its slow and
giving exception(bad Index: some number)invoking with a thread after 23,000 records .
and ther is any other method to export? other than
this
bcoz its slow...
My code is blow
================= (If i invoke this method with a thread its gives error
after (around)23,000 of records. if i am not using thread its working
But i need thread bcoz can''t waste 4-5 mnts for this only.0
And I think its slow bcoz.. This code At th time of writing to Excel,
we are writing to each cell. For a 30,000 records with 5 column
we are accessing OS 30,000*5 times,(file is around 6MB)
thats why taking 4-5 mnts.
is it? Any Other Solution?
Imports Microsoft.Office.Interop
Public Sub ExportXL()
Try
Dim saveAsFile As Boolean = False
If filePath <> "" Then
saveAsFile = deleteExistingFile(filePath)
End If
Dim colArray() As String = New String() {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
Dim excelApp As New Excel.Application()
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
Dim colIndex, rowIndex As Integer
Dim dc As System.Data.DataColumn
excelWorksheet.Range(colArray(0) + "1").Value = "Sl"
excelWorksheet.Range(colArray(0) + "1").Font.Bold = True
For Each dc In dtEda1.Columns
colIndex += 1
excelWorksheet.Range(colArray(colIndex) + "1").Value = dc.ColumnName
excelWorksheet.Range(colArray(colIndex) + "1").Font.Bold = True
Next
Dim dr As System.Data.DataRow
colIndex = 0
rowIndex = 0
For Each dr In dtEda1.Rows
rowIndex = rowIndex + 1
colIndex = 1
excelWorksheet.Cells(rowIndex + 1, 1) = rowIndex
''For d = 0 To dr.Table.Columns.Count - 1
'' colIndex = colIndex + 1
'' excelWorksheet.Cells(rowIndex + 1, colIndex) = dr(d)
''Next
For Each dc In dtEda1.Columns
colIndex = colIndex + 1
excelWorksheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
excelWorksheet.Columns.AutoFit()
If saveAsFile Then
Try
excelBook.SaveAs(filePath)
Catch ex As Exception
End Try
End If
excelApp.Visible = True
Catch ex As Exception
MsgBox("Export Failed- " + ex.Message)
Finally
dtEda1 = Nothing
returnVal = "Export Complete"
End Try
End Sub
这篇关于导出到Excel(对于大量数据)时出现的问题(在VB.net0上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
