1. EXCEL文件的生成.
2.EXCEL文件的下载.
1. EXCEL文件的生成.
Private Sub ExcelOutProc()
Dim printBookPath As String = ""
Dim printBookName As String = ""
Dim excelApp As Excel.Application
Dim excelBook As Excel.Workbook
Dim excelSheet As Excel.Worksheet
Dim strTemplateBookName As String
Dim sheetName As String
Dim nStartRowNo As Integer = 0
Dim blnAddHeadFlag As Boolean = True
Dim nTitleBakColorIndex As Integer = 15
'临时数组定义
Dim Field_Value(,) As String
Dim nDataStartRowNo As String = 10
strTemplateBookName = System.Configuration.ConfigurationSettings.AppSettings("TEMPLATE_FILE")
strTemplateBookName += "Test.xls"
printBookPath = System.Configuration.ConfigurationSettings.AppSettings("DOWNLOAD_FILE")
printBookName = "Test_" + Date.Now().ToString("yyyyMMddHHmmss") + ".xls"
sheetName = "TestSheet"
Try
'模板文件存在检查
If System.IO.File.Exists(strTemplateBookName) = False Then
'Error
Return
End If
'拷贝模板文件
FileCopy(strTemplateBookName, printBookPath + printBookName)
System.IO.File.SetAttributes(printBookPath + printBookName, IO.FileAttributes.Normal)
excelApp = New Excel.Application
excelBook = excelApp.Workbooks.Open(printBookPath + printBookName)
excelSheet = excelBook.Sheets(sheetName)
excelSheet.Range("B1").Value = "Title"
ReDim Field_Value(10, 10)
'先给一数组赋值
For cntRow As Integer = 0 To 10
For cntCol As Integer = 0 To 10
Field_Value(cntRow, cntCol) = CStr(cntRow) + "--" + CStr(cntCol)
Next
Next
'将数组值赋予EXCEL
excelSheet.Range("B" + CStr(nDataStartRowNo) + ":K" + CStr(nDataStartRowNo + nStartRowNo)).Value = Field_Value
'设置边框
excelSheet.Range("B" + CStr(nDataStartRowNo) + ":K" + CStr(nDataStartRowNo + nStartRowNo)).Borders.LineStyle = Excel.XlLineStyle.xlContinuous
'标题设置
'设置边框
excelSheet.Range("B1" + ":K" + CStr(1)).Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
'设置背景色
excelSheet.Range("B1" + ":K" + CStr(1 + 1)).Interior.ColorIndex = nTitleBakColorIndex
Catch ex As Exception
Finally
If Not excelBook Is Nothing Then
excelBook.Save()
excelBook.Close()
End If
If Not excelApp Is Nothing Then
excelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
excelSheet = Nothing
excelBook = Nothing
excelApp = Nothing
GC.Collect()
End If
End Try
'文件下载操作
DownloadFile(printBookName, Me.Response)
End Sub
2.EXCEL文件的下载.文件下载需要做二次提交来实现!
Private Sub DownloadFile(ByVal strFileName As String, ByVal Response As HttpResponse)
Dim filePath As String
Try
'Web.config文件定义路径读取
filePath = System.Configuration.ConfigurationSettings.AppSettings("DOWNLOAD_TEST")
Response.ContentType = "application/octet-stream"
Response.AddHeader("Content-Disposition", "attachment; FileName=" & strFileName)
Response.Buffer = True
Response.WriteFile(filePath & strFileName)
Response.Flush()
Response.Clear()
Response.Close()
Response.End()
Catch ex As Exception
End Try
End Sub