With reference to Richard deeming solution 1. I finally got solution
Here is my Code:
Dim file As FileInfo = New FileInfo("ClaimForms.xls")
Using pck As New ExcelPackage(file)
Dim wsClaims As ExcelWorksheet = pck.Workbook.Worksheets.Add("Sheet1")
wsClaims.TabColor = Color.Blue
Dim wslineitems As ExcelWorksheet = pck.Workbook.Worksheets.Add("Sheet2")
wslineitems.TabColor = Color.Blue
Dim dsExcel As DataSet = dbnet.GetAllDetails()
Dim dtClaims As DataTable = dsExcel.Tables(0)
Dim dtLineItems As DataTable = dsExcel.Tables(1)
wsClaims.Cells("A1").LoadFromDataTable(dtClaims, True)
wslineitems.Cells("A1").LoadFromDataTable(dtLineItems, True)
wsClaims.Cells.AutoFitColumns()
wslineitems.Cells.AutoFitColumns()
Using rng As ExcelRange = wsClaims.Cells(1, 1, 1, dtClaims.Columns.Count)
rng.Style.Font.Bold = True
rng.Style.Fill.PatternType = ExcelFillStyle.Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue)
rng.Style.Font.Color.SetColor(System.Drawing.Color.Yellow)
End Using
Using rng As ExcelRange = wslineitems.Cells(1, 1, 1, dtLineItems.Columns.Count)
rng.Style.Font.Bold = True
rng.Style.Fill.PatternType = ExcelFillStyle.Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Blue)
rng.Style.Font.Color.SetColor(System.Drawing.Color.Yellow)
End Using
Dim result As MemoryStream = New MemoryStream
pck.SaveAs(result)
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Dim fileName As String = String.Format("ClaimReport_{0:yyyyMMdd_HH_mm}.xlsx", DateTime.Now)
Dim disposition As String = String.Format("attachment; filename=""{0}""", fileName)
Response.AppendHeader("Content-Disposition", disposition)
result.WriteTo(Response.OutputStream)
'Response.BinaryWrite(pck.GetAsByteArray())
'Response.TransmitFile(file.Name)
' Response.Flush()
Response.End()
End Using
I deleted the Excelpackage.dll which I downloaded from EEplus website. I added new dll called EEplus.dll. Everything works perfectly well.