CODEWITHSUNDEEP
excelxlsxepplus
Cawboy
Cawboy

Reputation: 173

How can I delete a Column of XLSX file with EPPlus in web app

How can I delete a column of a .XLSX file with EPPlus in a web application?

I use EPplus for generating Excel reports and a stored procedure to get the data from database.

The problem is that I want to remove one of the columns of information in the report file by EPplus (stored procedure should not be changed.)

I would remove the additional column in and also want to change the page layout direction to (right to left), but it does not work

'----/// 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

'----/// Generate 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 stored 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

Upvotes: 1

Views: 4773

Answers (1)

Brahyam
Brahyam

Reputation: 311

The Insert and Remove Columns Method on EPPlus API is not yet implemented because it would not work well with large worksheets with the current design of the cell store.

The only way to delete a column is to move the cells yourself.

This topic is a discussion on EPPlus Codeplex Page.

Reference= http://epplus.codeplex.com/discussions/262729

Upvotes: 1

Related Questions