如何在Web应用程序中删除EPPlus的XLSX文件列

问题描述

如何在Web应用程序中删除EPPlus的 .XLSX 文件列？

I使用EPplus生成Excel报表和存储过程以从数据库获取数据。

问题是我要删除报告文件中的一列信息通过EPplus（存储过程不应该更改）。

我将删除其他列，也希望将页面布局方向更改为（从右到左），但是它不工作

 '---- //单击报告按钮/// ---- 
受保护的子btnExcel_Click（ByVal sender As Object，ByVal e As System.EventArgs）处理btnExcel.Click 
如果MYcondition为真然后
 GenerateXLSXFile（CreateDataTable（））
 End If 
 End Sub 
 
'---- //生成报表/// ---- 
 Private Sub GenerateXLSXFile（ByVal tbl As DataTable）
 Dim excelPackage = New ExcelPackage 
 Dim excelWorks heet = 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 
 
 
'---- ///更改PageLayout方向/// ----- ---------- 
 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;文件名= 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 
 
'---- ///为Exel创建数据表报告（使用存储过程）/// ---- 
私有函数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作为新的SqlCommand（我的选择命令，conn）
 
 cmd.CommandType = CommandType.StoredProcedure 
尝试
 conn.Open（）
 dataAdapter.SelectCommand = cmd 
 dataAdapter.Fill（dataTable）
 Catch ex As Exception 
最后
 conn.Clos e（）
结束尝试
 
返回dataTable 
结束函数
  

解决方案

EPPlus API中的插入和删除列方法尚未实现，因为它与当前设计的单元存储的大型工作表不能正常工作。

删除列的唯一方法是自己移动单元格。

本主题是关于EPPlus Codeplex页面的讨论。 / p>

参考=

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

解决方案

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

这篇关于如何在Web应用程序中删除EPPlus的XLSX文件列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！