|
I use EPplus for Genrate Excel Report and use StoreProcedure To get data from database.
The problem is that I want to remove one of the columns of information in the report file by EPplus
modified 7-Jan-13 9:11am.
|
|
|
|
|
cawboy_iran wrote: Stored procedure should not be changed.
Based on what you say, you need to write code to open excel sheet and then delete the column you don't want. Look into excel programming.
|
|
|
|
|
thanx for your reply...
I leave My Code Here
I would remove the additional Column in and also want to change the page layout direction to (right to left)
Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcel.Click
If "MYcondition is true" Then
GenerateXLSXFile(CreateDataTable())
End If
End Sub
Private Sub GenerateXLSXFile(ByVal tbl As DataTable)
Dim excelPackage = New ExcelPackage
Dim excelWorksheet = excelPackage.Workbook.Worksheets.Add("My_Worksheet")
excelWorksheet.View.ShowGridLines = False
excelWorksheet.Cells.Style.Border.Bottom.Style = Style.ExcelBorderStyle.Thick
excelWorksheet.Cells("A5").LoadFromDataTable(tbl, True)
excelWorksheet.Column(2).Hidden = True
excelWorksheet.View.PageLayoutView = excelWorksheet.View.RightToLeft
excelWorksheet.Cells("A5").Value = "header_1"
excelWorksheet.Cells("B5").Value = "header_2"
excelWorksheet.Cells("C5").Value = "header_3"
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment; filename=My_ExcelName.xlsx")
Dim stream As MemoryStream = New MemoryStream(excelPackage.GetAsByteArray())
Response.OutputStream.Write(stream.ToArray(), 0, stream.ToArray().Length)
Response.Flush()
Response.Close()
End Sub
Private Function CreateDataTable() As DataTable
Dim dataTable As New DataTable("tbl_Name")
Dim dataAdapter As New SqlDataAdapter()
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ProvidersCS").ToString)
Dim cmd As New SqlCommand("My Select Command", conn)
cmd.CommandType = CommandType.StoredProcedure
Try
conn.Open()
dataAdapter.SelectCommand = cmd
dataAdapter.Fill(dataTable)
Catch ex As Exception
Finally
conn.Close()
End Try
Return dataTable
End Function
|
|
|
|