Other Improved Ways To Export Data To Excel

 /// <summary>
        /// Exports to excel. 
        /// http://www.codeproject.com/Articles/9380/Export-a-DataSet-to-Microsoft-Excel-without-the-us
        /// http://bytes.com/topic/c-sharp/answers/497730-streamwriter-export-excel
        /// </summary>
        /// <param name="source">The source.</param>
        /// <param name="fileName">Name of the file.</param>
        /// <exception cref="System.Exception"></exception>
        public void ExportToExcel(DataSet source, string fileName)
        {

            //get response, set content type.
            HttpResponse response = HttpContext.Current.Response;
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", "attachment;filename=\"" +
            fileName + "\"");
            response.Clear();
            System.IO.StreamWriter excelDoc;
            excelDoc = new System.IO.StreamWriter(response.OutputStream);


            /// excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n<Workbook " +
                  "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
                  " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
                  "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
                  "office:spreadsheet\">\r\n <Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
                  "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
                  "\r\n <Protection/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
                  "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
                  "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
                  " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"Decimal\">\r\n <NumberFormat " +
                  "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
                  "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
                  "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
                  "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
                  "</Styles>\r\n ";
            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;
            /*
           <xml version>
           <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">
           <Styles>
           <Style ss:ID="Default" ss:Name="Normal">
             <Alignment ss:Vertical="Bottom"/>
             <Borders/>
             <Font/>
             <Interior/>
             <NumberFormat/>
             <Protection/>
           </Style>
           <Style ss:ID="BoldColumn">
             <Font x:Family="Swiss" ss:Bold="1"/>
           </Style>
           <Style ss:ID="StringLiteral">
             <NumberFormat ss:Format="@"/>
           </Style>
           <Style ss:ID="Decimal">
             <NumberFormat ss:Format="0.0000"/>
           </Style>
           <Style ss:ID="Integer">
             <NumberFormat ss:Format="0"/>
           </Style>
           <Style ss:ID="DateLiteral">
             <NumberFormat ss:Format="mm/dd/yyyy;@"/>
           </Style>
           </Styles>
           <Worksheet ss:Name="Sheet1">
           </Worksheet>
           </Workbook>
           */
            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            for (int x = 0; x < source.Tables[0].Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
                excelDoc.Write("</Data></Cell>");
            }
            excelDoc.Write("</Row>");
            foreach (DataRow x in source.Tables[0].Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "
                for (int y = 0; y < source.Tables[0].Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  
                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                            //The Following Code puts the date stored in XMLDate 
                            //to the format above
                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date
                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);


            //flush and finish response
            excelDoc.Flush();
            excelDoc.Close();
            response.End();
        }

And to call it:


 /// <summary>
        /// Handles the Click event of the lbtnExportToExcel control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
        protected void lbtnExportToExcel_Click(object sender, EventArgs e)
        {
            CommunicationsFilter filter = new CommunicationsFilter();

            filter = FilterControl.GetSearchFilter();
            int totalRowCount;
            List<SubscriberData> subData = CommDataManager.GetSubscribers(filter, 0, out totalRowCount);


            DataSet ds = new DataSet(); ds.Tables.Add(ConvertSubscriberDataToDataView(subData).ToTable());
            //ExportToExcel(ds, "MobileAlertsExport.xls");           

           

           
        }




  /// <summary>
        /// Converts the subscriber data to data view.
        /// </summary>
        /// <param name="subcriberData">The subcriber data.</param>
        /// <returns></returns>
        private DataView ConvertSubscriberDataToDataView(List<SubscriberData> subcriberData)
        {
            DataTable dt = new DataTable();

            // Define column headers
            dt.Columns.Add("First Name");
            dt.Columns.Add("Middle Initial");
            dt.Columns.Add("Last Name");
            dt.Columns.Add("Account Number");
            dt.Columns.Add("OPCO Abbrv Name");
            dt.Columns.Add("OPCO Full Name");
            dt.Columns.Add("Email");

            foreach (SubscriberData subscriber in subcriberData)
            {
                DataRow dr = dt.NewRow();

                dr["First Name"] = subscriber.FirstName;
                dr["Middle Initial"] = subscriber.MiddleInitial;
                dr["Last Name"] = subscriber.LastName;
                dr["Account Number"] = subscriber.SubscriberAccount.AccountNumberCheckDigit;
                dr["OPCO Abbrv Name"] = subscriber.OpCo.AbbreviatedName;
                dr["OPCO Full Name"] = subscriber.OpCo.FullName;
                dr["Email"] = subscriber.Email;

                dt.Rows.Add(dr);
            }

            //Load the datatable into a Dataview to accomodate easy sorting
            DataView dv = new DataView(dt);
            return dv;
        }

And for an even more native xlsx output check out http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm# which uses DocumentFormat.OpenXML.dll

Advertisements

Object DataSource With Paramaters

Prefix the Business Logic Layer method that accepts parameters with the following. otherwise the object DataSource will not identify this method that accepts parameters:

 [DataObjectMethod(DataObjectMethodType.Select)]

Example:

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static DataTable GetInvestorFilingDataTable(InvestorDataFeedType feedType)
    {

        List<InvestorFilingData> entireList = new List<InvestorFilingData>();
    
        entireList = DAL.GetInvestorFilingData(feedType);

        DataTable datatable = ListToDataTable(entireList);

        return datatable;

    }        

Then to pass params to this method within your datasource specify SelectParameter’s:

<asp:ObjectDataSource ID="DataSourceFilingsDT" runat="server" SelectMethod="GetInvestorFilingDataTable"
    TypeName="BLL" OnSelected="DataSourceFilingsDT_Selected" 
    OldValuesParameterFormatString="original_{0}" 
    onselecting="DataSourceFilingsDT_Selecting">
    <SelectParameters>
        <asp:Parameter Name="feedType" Type="Object" />
    </SelectParameters>
</asp:ObjectDataSource>

To pass values to the method within the DataSource:

 protected void DataSourceFilingsDT_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        // Change the Parameter of feedType within GetInvestorFilingDataTable
        e.InputParameters["feedType"] = DataToDisplay;
    }

More info here.

Convert List to DataTable – (ListToDataTable function)

using System.Data;
using System.Reflection;

private static DataTable ListToDataTable<T>(List<T> list)
        {
            DataTable dt = new DataTable();

            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));
            }
            foreach (T t in list)
            {
                DataRow row = dt.NewRow();
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    row[info.Name] = info.GetValue(t, null);
                }
                dt.Rows.Add(row);
            }
            return dt;
        }

Example implementation:

        List<InvestorFilingData> lst = FilingsLogic.GetInvestorFiling();
        DataTable dt = ListToDataTable(lst);

Example use: When you have an object bound to a GridView and want to sort the columns.