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
Solved

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

Posted on 2014-04-12
3
140 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

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.…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

792 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