Solved

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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