How to remove .XLSX file column using EPPlus in web application?
I use EPplus to generate Excel reports and a stored procedure to retrieve data from a database.
The problem is that I want to delete one of the information columns in the EPplus report file (the stored procedure should not be changed.)
I would remove the extra column and also want to change the direction of the page layout (from 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
source share