asp.net生成excel文件的类
调用：Dim clsExcel As New clsCommonExcel2
clsExcel.createAndDowloadExcel(table, "sheet名称", "生成的excel名称", "1,3,5", 20000)
Imports Interop
Imports System.Web.HttpServerUtility
Imports Interop.Excel.Constants
Imports Interop.Excel.XlPasteType
Imports Interop.Excel.XlBordersIndex
Imports Interop.Excel.XlLineStyle
Imports Interop.Excel.XlBorderWeight
Imports Interop.Excel.XlUnderlineStyle
Public Class clsCommonExcel2
Inherits System.Web.UI.Page
''' <summary>
''' web服务器端生成excel文件
''' </summary>
''' <param name="table">数据集DataTable</param>
''' <param name="sheetName">excel的sheet名称</param>
''' <param name="newFileName">excel文件名称</param>
''' <param name="txtFormat">第1，3，5列要设为文本格式，则传入[1，3，5]</param>
''' <param name="cntPerSheet">每sheet的数据件数，超过则新生成sheet</param>
''' <remarks></remarks>
Public Sub createAndDowloadExcel(ByVal table As DataTable, ByVal sheetName As String, _
ByVal newFileName As String, ByVal txtFormat As String, _
ByVal cntPerSheet As Integer)
createExcelFile(table, sheetName, newFileName, txtFormat, cntPerSheet)
'回收进程
GC.Collect()
End Sub
Protected Sub createExcelFile(ByVal table As DataTable, ByVal sheetName As String, _
ByVal newFileName As String, ByVal txtFormat As String, _
ByVal cntPerSheet As Integer)
Dim app As Excel.Application
Dim workbook As Excel.Workbook
Dim worksheet As Excel.Worksheet
Dim arr(,) As Object
Dim cntSheet As Integer
Dim m As Integer
''删除既存文件
'System.IO.File.Delete(Server.MapPath(" + newFileName))
app = New Excel.Application
app.Visible = False
workbook = app.Workbooks.Add(1)
app.DisplayAlerts = False
workbook.SaveAs(Server.MapPath( + newFileName))
'计算sheet数
If table.Rows.Count Mod cntPerSheet = 0 Then
cntSheet = table.Rows.Count / cntPerSheet
Else
cntSheet = Int(table.Rows.Count / cntPerSheet) + 1
End If
For k = 0 To cntSheet - 1
ReDim arr(cntPerSheet, table.Columns.Count - 1)
For j As Integer = 0 To table.Columns.Count - 1
arr(0, j) = table.Columns(j).ColumnName
Next
workbook.Sheets(workbook.Sheets.Count).Select()
worksheet = workbook.Sheets.Add()
worksheet.Name = sheetName + "_" + (k + 1).ToString
m = 1
For i As Integer = k * cntPerSheet To (k + 1) * cntPerSheet - 1
If i < table.Rows.Count Then
For j As Integer = 0 To table.Columns.Count - 1
'arr(m, j) = table.Rows(i).Item(j) 防止excel单元格中信息以”-，=“开头
If Not IsNumeric(table.Rows(i).Item(j)) AndAlso table.Rows(i).Item(j).ToString.Length > 1 AndAlso _
(Left(table.Rows(i).Item(j).ToString, 1).Equals("-") OrElse Left(table.Rows(i).Item(j).ToString, 1).Equals("=")) Then
arr(m, j) = "'" + table.Rows(i).Item(j)
Else
arr(m, j) = table.Rows(i).Item(j)
End If
Next
m = m + 1
End If
Next
'格式
With worksheet
.Cells.Select()
With app.Selection.Font
.Name = "宋体"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
.Range("A1:" + num2letter(worksheet, table.Columns.Count) + "1").Select()
With app.Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
app.Selection.Font.Bold = True
With app.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Dim arrInx() As String = txtFormat.Split(",")
For i As Integer = 0 To arrInx.Length - 1
'设置文本格式
.Columns(num2letter(worksheet, CInt(arrInx(i))) + ":" + num2letter(worksheet, CInt(arrInx(i)))).Select()
app.Selection.NumberFormatLocal = "@"
Next
worksheet.Range("A1").Resize(cntPerSheet + 1, table.Columns.Count).Value = arr
.Columns("A:" + num2letter(worksheet, table.Columns.Count)).Select()
.Columns("A:" + num2letter(worksheet, table.Columns.Count)).EntireColumn.AutoFit()
End With
worksheet.Range("A1").Select()
Next
workbook.Sheets(workbook.Sheets.Count).Select()
app.ActiveWindow.SelectedSheets.Delete()
workbook.Sheets(1).Select()
app.DisplayAlerts = False
workbook.Save()
workbook.Close() 'add 2011.11.1
app.Quit()
app = Nothing
End Sub
'n必须介于1到256之间
Public Shared Function num2letter(ByVal worksheet As Excel.Worksheet, ByVal n As Integer) As String
If n >= 1 And n <= 256 Then
num2letter = IIf(n <= 26, Mid(worksheet.Cells(1, n).Address, 2, 1), Mid(worksheet.Cells(1, n).Address, 2, 2))
Else
num2letter = ""
End If
End Function
End Class
