ASP.NET Discussion Boards

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.

Sandeep Mewara
Microsoft ASP.NET MVP 2012 & 2013

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

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)

VB

'----/// Click Report Button ///----
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

'----/// Genrate Report ///----
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)
        
       '-----/// Hide a Column ///---------
	excelWorksheet.Column(2).Hidden = True    
	

       '----/// Change PageLayout Direction ///---------------	
        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

'----/// Create Data Table for Exel Report (use store procedure) ///----
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