Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-04-12
3
Medium Priority
?
183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 1000 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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

722 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