分批汇出EXCEL

EXCEL 汇出的时候，如果一时间汇出大量的数据的话，会影响服务器的性能及效率，故分批汇出，每一批50000条数据（每页EXCEL支持65536行），具体实现如下

 

 

Public Shared Sub ExportBatch(dtSource As DataTable, strFileName As String)
Dim workbook As New HSSFWorkbook()
CreateFolder(strFileName.Substring(0, strFileName.LastIndexOf("\")))
Dim iCount As Integer = 0

'每批汇出行数
Dim sheetRows As Integer = 50000
For c As Integer = 0 To dtSource.Rows.Count - 1 Step sheetRows
'For c As Integer = 0 To 10
If c = iCount * sheetRows Then
iCount = iCount + 1
Dim sheet As HSSFSheet = workbook.CreateSheet()

'填充表头
Dim dataRow As HSSFRow = sheet.CreateRow(0)
For Each column As DataColumn In dtSource.Columns
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName)
Next

'填充内容
'判断是否是最后一页，如果不是的话，取50000的倍数，如果是，则取datatable的最大条数
Dim rowCounts As Integer = IIf(dtSource.Rows.Count > iCount * sheetRows, iCount * sheetRows, dtSource.Rows.Count)
'每一批（第一页）都是由第一行开始
Dim rowIndex As Integer = 1
For i As Integer = c To rowCounts - 1
dataRow = sheet.CreateRow(rowIndex)
For j As Integer = 0 To dtSource.Columns.Count - 1
dataRow.CreateCell(j).SetCellValue(dtSource.Rows(i)(j).ToString())
Next
rowIndex += 1
Next

'保存
Using ms As New MemoryStream()
Using fs As New FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Write)
workbook.Write(ms)
ms.Flush()
ms.Position = 0
Dim data As Byte() = ms.ToArray()
fs.Write(data, 0, data.Length)
fs.Flush()
End Using
End Using
End If

System.Threading.Thread.Sleep(10000)

Next
End Sub