Solved

How do I add data to an excel sheet using asp.net?

Posted on 2014-04-12
3
117 Views
Last Modified: 2016-06-20
I am having trouble getting data from my database to export it to excel. On excel it only shows the cells that I named.

Imports System
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel


Partial Class GridViewEvent
    Inherits System.Web.UI.Page

    Protected Sub btnExcel_Click(sender As Object, e As System.EventArgs) Handles btnExcel.Click
        Dim appXL As Excel.Application
        Dim wbXl As Excel.Workbook
        Dim shXL As Excel.Worksheet
        Dim raXL As Excel.Range
        ' Start Excel and get Application object.
        appXL = CreateObject("Excel.Application")
        appXL.Visible = True
        ' Add a new workbook.
        wbXl = appXL.Workbooks.Add
        shXL = wbXl.ActiveSheet
        ' Add table headers going cell by cell.
        shXL.Cells(1, 1).Value = "Event ID"
        shXL.Cells(1, 2).Value = "Publication Effective Date"
        shXL.Cells(1, 3).Value = "Publication Expiration Date"
        shXL.Cells(1, 4).Value = "Email Signature"
        ' Format A1:D1 as bold, vertical alignment = center.
        With shXL.Range("A1", "D1")
            .Font.Bold = True
            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
        End With
        
        ' AutoFit columns A:D.
        raXL = shXL.Range("A1", "D1")
        raXL.EntireColumn.AutoFit()
        ' Make sure Excel is visible and give the user control
        ' of Excel's lifetime.
        appXL.Visible = True
        appXL.UserControl = True
        ' Release object references.
        raXL = Nothing
        shXL = Nothing
        wbXl = Nothing
        appXL.Quit()
        appXL = Nothing
        Exit Sub
Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
    End Sub
End Class

Open in new window

0
Comment
Question by:ryanbecker24
3 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 250 total points
ID: 39997380
Hi,

First of all, reviewing your code i see you only create the headers and then you autofit them. There is no line code that will populate the excel with actual data.

I've been using the following code in ASP.NET MVC project. I cannot give reference of where i found it because i cannot remember where i initially got it. I think it is a nice solution as it does not rely to Office objects.

Also the code I provide is in C#, but i do not think it will be difficult to convert in VB.

        public void ExportToExcel<T>(List<T> list)
        {
            int columnCount = 0;

            DateTime StartTime = DateTime.Now;

            StringBuilder rowData = new StringBuilder();

            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            rowData.Append("<Row ss:StyleID=\"s62\">");
            foreach (PropertyInfo p in properties)
            {
                if (p.PropertyType.Name != "EntityCollection`1" && p.PropertyType.Name != "EntityReference`1" && p.PropertyType.Name != p.Name)
                {
                    columnCount++;
                    rowData.Append("<Cell><Data ss:Type=\"String\">" + p.Name + "</Data></Cell>");
                }
                else
                    break;

            }
            rowData.Append("</Row>");

            foreach (T item in list)
            {
                rowData.Append("<Row>");
                for (int x = 0; x < columnCount; x++) //each (PropertyInfo p in properties)
                {
                    object o = properties[x].GetValue(item, null);
                    string value = o == null ? "" : o.ToString();
                    rowData.Append("<Cell><Data ss:Type=\"String\">" + value + "</Data></Cell>");

                }
                rowData.Append("</Row>");
            }

            var sheet = @"<?xml version=""1.0""?>
                    <?mso-application progid=""Excel.Sheet""?>
                    <Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
                        xmlns:o=""urn:schemas-microsoft-com:office:office""
                        xmlns:x=""urn:schemas-microsoft-com:office:excel""
                        xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
                        xmlns:html=""http://www.w3.org/TR/REC-html40"">
                        <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">
                            <Author>MSADMIN</Author>
                            <LastAuthor>MSADMIN</LastAuthor>
                            <Created>2011-07-12T23:40:11Z</Created>
                            <Company>Microsoft</Company>
                            <Version>12.00</Version>
                        </DocumentProperties>
                        <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">
                            <WindowHeight>6600</WindowHeight>
                            <WindowWidth>12255</WindowWidth>
                            <WindowTopX>0</WindowTopX>
                            <WindowTopY>60</WindowTopY>
                            <ProtectStructure>False</ProtectStructure>
                            <ProtectWindows>False</ProtectWindows>
                        </ExcelWorkbook>
                        <Styles>
                            <Style ss:ID=""Default"" ss:Name=""Normal"">
                                <Alignment ss:Vertical=""Bottom""/>
                                <Borders/>
                                <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>
                                <Interior/>
                                <NumberFormat/>
                                <Protection/>
                            </Style>
                            <Style ss:ID=""s62"">
                                <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""
                                    ss:Bold=""1""/>
                            </Style>
                        </Styles>
                        <Worksheet ss:Name=""Sheet1"">
                            <Table ss:ExpandedColumnCount=""" + (properties.Count() + 1) + @""" ss:ExpandedRowCount=""" + (list.Count() + 1) + @""" x:FullColumns=""1""
                                x:FullRows=""1"" ss:DefaultRowHeight=""15"">
                                " + rowData.ToString() + @"
                            </Table>
                            <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
                                <PageSetup>
                                    <Header x:Margin=""0.3""/>
                                    <Footer x:Margin=""0.3""/>
                                    <PageMargins x:Bottom=""0.75"" x:Left=""0.7"" x:Right=""0.7"" x:Top=""0.75""/>
                                </PageSetup>
                                <Print>
                                    <ValidPrinterInfo/>
                                    <HorizontalResolution>300</HorizontalResolution>
                                    <VerticalResolution>300</VerticalResolution>
                                </Print>
                                <Selected/>
                                <Panes>
                                    <Pane>
                                        <Number>3</Number>
                                        <ActiveCol>2</ActiveCol>
                                    </Pane>
                                </Panes>
                                <ProtectObjects>False</ProtectObjects>
                                <ProtectScenarios>False</ProtectScenarios>
                            </WorksheetOptions>
                        </Worksheet>
                    </Workbook>";

            System.Diagnostics.Debug.Print(StartTime.ToString() + " - " + DateTime.Now);
            System.Diagnostics.Debug.Print((DateTime.Now - StartTime).ToString());

            string attachment = "attachment; filename=Report.xml";

            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.Write(sheet);
            Response.ContentType = "application/ms-excel";
            Response.End();

        }

Open in new window


Now this is a void method, That will get any List as an argument and it will populate it with data and prompt you to download.

Usage example:

Lets say you have a DBContext object with a table(named MyTable) which you want to export in Excel.

Dim myList = DBContext.MyTable.ToList
ExportToExcel(myList)

Open in new window


The namespaces you need to Import are:

System.Text
System.Reflection
System.Collections.Generic

I hope you are ok with converting this to VB.

Giannia
0
 
LVL 11

Assisted Solution

by:Salim Fayad
Salim Fayad earned 250 total points
ID: 40031404
You have 2 possibilities other than the COM object (which poses a lot of problems):
1. Either you can use OpenXML
2. Or you can write your GridView (or create a dynamic one), and then you change contenttype of your Response to "application/vnd.ms-excel". This is a generic code that we did:
public static void M_ExportDataToExcel( System.Data.DataTable dtObj , string filename )
        {
            if( dtObj != null )
                if( dtObj.Rows.Count > 0 )
                {

                    for( int i = 0 ; i < dtObj.Rows.Count ; i++ )
                        for( int j = 0 ; j < dtObj.Columns.Count ; j++ )
                        {
                            if( dtObj.Columns[j].DataType.Name.Equals("String") )
                            {
                                dtObj.Rows[i][j] = "&nbsp;" + dtObj.Rows[i][j].ToString();
                            }
                        }

                    // clean up the response.object
                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.Charset = "";

                    // set the response mime type for excel
                    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                    HttpContext.Current.Response.AddHeader("Content-Disposition" , "attachment;filename=\"" + filename + "\".xls");

                    // create a string writer
                    using( StringWriter sw = new StringWriter() )
                    {
                        using( HtmlTextWriter htw = new HtmlTextWriter(sw) )
                        {
                            // instantiate a datagrid
                            DataGrid dgObj = new DataGrid();
                            dgObj.DataSource = dtObj;
                            dgObj.DataBind();

                            if( dgObj != null )
                                dgObj.HeaderStyle.Font.Bold = true;
                            dgObj.RenderControl(htw);
                            HttpContext.Current.Response.Write(sw.ToString());
                            HttpContext.Current.Response.Flush();
                            HttpContext.Current.Response.Clear();
                            HttpContext.Current.Response.End();
                        }
                    }
                }
        }

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now