Remove From My Forums

Export to Excel with Macro

Question
0

Sign in to vote

User-1971168174 posted

Hi Support,

I am showing data in GridView which shows data as per query entered by user in multiline textbox above.

But now the requirement is when export button is clicked I need to export data as well as query in macro , so that user can refresh data next time in the excel itself.

Please help me with any working sample.

Thanks in Advance!

Wednesday, October 7, 2020 6:27 AM

Answers

User-1330468790 posted

Hi Manish,

In short, EPPlus supports Creating, Reading and Writing VBA. Execution/Interpretation of VBA code is not supported.

Then, the only thing you need to make sure is that the user should have VBE installed with Excel.

The solution for you to export the data to excel with Macro is that you use EPPlus to construct a sheet and VBA project in the excel work book.

After that, users will load the excel file to their computers and enable the macro for the excel.

Tips:

file extension should be .xlsm and content type should be "application/vnd.ms-excel.sheet.macroEnabled.12" (see this link)
The VBA project must be created in the workbook using codes : excel.Workbook.CreateVBAProject();
You could access the VBA code using "excel.Workbook.VbaProject.Modules["module_name"].Code" or directly using "excel.Workbook.CodeModule.Code"

More details, you could refer to below codes:

ASPX Page:

<form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
                <Columns>
                    <asp:BoundField DataField="Id" HeaderText="ID" />
                    <asp:BoundField DataField="Name" HeaderText="Name" />
                </Columns>
            </asp:GridView>
        </div>
        <asp:Button ID="BtnExport" runat="server" Text="Export To Excel" OnClick="BtnExport_Click" />
    </form>

Code behind:

// Simulation of the data
        private static DataTable _gridviewDT;
        public static DataTable GridviewDT
        {
            get
            {
                if (_gridviewDT is null)
                {
                    _gridviewDT = new DataTable();

                    _gridviewDT.Columns.Add("Id", typeof(int));
                    _gridviewDT.Columns.Add("Name", typeof(string));

                    _gridviewDT.Rows.Add(1, "name1");
                    _gridviewDT.Rows.Add(2, "name2");
                    _gridviewDT.Rows.Add(3, "name3");
                    _gridviewDT.Rows.Add(4, "name4");
                    _gridviewDT.Rows.Add(5, "name5");

                }

                return _gridviewDT;
            }
            set
            {
                _gridviewDT = value;
            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGridView();
            }
        }

        protected void BindGridView()
        {
            GridView1.DataSource = GridviewDT;
            GridView1.DataBind();
        }

        protected void BtnExport_Click(object sender, EventArgs e)
        {
            // Add values and you could also add styles within some extra codes
            ExcelPackage excel = new ExcelPackage();
            var workSheet = excel.Workbook.Worksheets.Add("Data");
            var totalCols = GridView1.Rows[0].Cells.Count;
            var totalRows = GridView1.Rows.Count;
            var headerRow = GridView1.HeaderRow;
            for (var i = 1; i <= totalCols; i++)
            {
                workSheet.Cells[1, i].Value = headerRow.Cells[i - 1].Text;
            }
            for (var j = 1; j <= totalRows; j++)
            {
                for (var i = 1; i <= totalCols; i++)
                {
                    var DataRow = GridviewDT.Rows[j - 1];
                    workSheet.Cells[j + 1, i].Value = DataRow[i - 1].ToString();
                }
            }

            // Add macro
            excel.Workbook.CreateVBAProject();
            StringBuilder vbaCode = new StringBuilder();

            vbaCode.AppendLine("Private Sub Workbook_Open()");
            vbaCode.AppendLine("    MsgBox \"Hello\"");
            vbaCode.AppendLine("End Sub");

            //If you want to add code to some specific module, uncomment the below line
            //excel.Workbook.VbaProject.Modules["Data"].Code = "VBA Codes"
            excel.Workbook.CodeModule.Code = vbaCode.ToString();

            using (var memoryStream = new MemoryStream())
            {
                Response.ContentType = "application/vnd.ms-excel.sheet.macroEnabled.12";
                Response.AddHeader("content-disposition", "attachment;  filename=DataWithMacro.xlsm");
                excel.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }

Result:

page:

Excel:

Hope helps.

Best regards,

Sean

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Thursday, October 8, 2020 9:46 AM

Answered by:

Export to Excel with Macro

Question

Answers