Sunday, April 10, 2016

ASP.Net C# Exporting and Importing Excel Data

Here is a simple example using C# to export data to an Excel file and to read data from an Excel file. This example demonstrates the following:

- MySafeInfo Data API
- Json.NET (Newtonsoft.Json)
- EPPlus
- Response.BinaryWrite
- WebClient.DownloadData
- StreamReader
- Serialization
- Custom Extension Methods

Code-Behind
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Utilities;

namespace Web.excel
{
    public partial class _default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // empty
        }

        public void DownloadFile(byte[] Data, string FileType = "application/csv", string FileName = "data.csv")
        {
            // headers
            Response.BufferOutput = true;
            Response.ClearHeaders();

            // explicitly append preamble for csv files so excel recognizes utf-8 encoding
            if (FileType.ToLower().Contains("csv"))
            {
                Response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());
            }

            // encoding
            Response.ContentEncoding = System.Text.Encoding.UTF8;

            // content type
            Response.ContentType = FileType;

            // content disposition
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=\"{0}\"", FileName));

            // data
            Response.BinaryWrite(Data);

            // end
            Response.End();
        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            // variables
            string Result = string.Empty;
            string Url = "https://mysafeinfo.com/api/data?list=states&format=json&alias=nm=name,ab=code,cp=capital,yr=year";
            List<State> States = new List<State>();
            DataTable DT = new DataTable();
            byte[] Data = null;

            // download json data
            using (WebClient client = new WebClient())
            {
                Result = System.Text.UTF8Encoding.UTF8.GetString(client.DownloadData(Url));
            }

            // deserialize json data into a List<State>
            // Json.NET (Newtonsoft.Json)
            // https://www.nuget.org/packages/Newtonsoft.Json
            States = Newtonsoft.Json.JsonConvert.DeserializeObject<List<State>>(Result);

            // convert List<State> to a DataTable using ToDataTable extension method
            DT = States.ToDataTable();

            // convert DataTable to byte[] using ToExcel extension method 
            // EPPlus
            // https://www.nuget.org/packages/EPPlus
            Data = DT.ToExcel(WorksheetName: "States");

            // download excel file
            DownloadFile(Data: Data, FileType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", FileName: "states.xlsx");
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            // variables
            DataTable DT = new DataTable();
            List<State> States = new List<State>();

            // read excel file in to a StreamReader
            using (StreamReader sr = new StreamReader(Server.MapPath("states.xlsx")))
            {
                // read the underlying stream and convert to a DataTable using ToDataTable extension method
                DT = sr.BaseStream.ToDataTable(HasHeader: true);
            }

            // convert DataTable to a List<State> using ToList extension method
            States = DT.ToList<State>().ToList();

            // output serialized List<State> so we can see that the excel file was read successfully
            lblOutput.Text = States.Serialize();

            // show panel
            pnlOutput.Visible = true;
        }

        // state class
        public class State
        {
            public State()
            {
                Name = string.Empty;
                Code = string.Empty;
                Capital = string.Empty;
                Year = string.Empty;
            }

            public string Name { get; set; }
            public string Code { get; set; }
            public string Capital { get; set; }
            public string Year { get; set; }
        }
    }
}

ASPX Page
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="default.aspx.cs" Inherits="Web.excel._default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <!-- title -->
    <title>Excel Demo</title>

    <!-- jquery -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>

    <!-- bootstrap -->
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet" />
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>

    <!-- custom style -->
    <style>
        body {
            margin: 25px !important;
        }

        .form-group {
            width: 800px;
        }
    </style>
</head>

<body>
    <form id="form1" runat="server">
    <div>
        <p>
            <asp:Button ID="btnDownload" Text="Download Excel File" CssClass="btn btn-default" Width="200px" OnClick="btnDownload_Click" ClientIDMode="Static" runat="server" />
        </p>

        <p>
            <asp:Button ID="btnUpload" Text="Read Excel File" CssClass="btn btn-default" Width="200px" OnClick="btnUpload_Click" ClientIDMode="Static" runat="server" />
        </p>

        <asp:PlaceHolder ID="pnlOutput" Visible="false" runat="server">
            <p>
                <pre><asp:Literal ID="lblOutput" runat="server" /></pre>
            </p>
        </asp:PlaceHolder>
    </div>
    </form>
</body>
</html>


Extensions
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
using System.Xml.Serialization;
using System.IO;
using System.Collections.Specialized;
using System.Web;
using System.ComponentModel;
using System.Xml;
using System.Xml.Linq;
using System.Web.UI;
using System.Text.RegularExpressions;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Linq.Expressions;
using System.Net;
using OfficeOpenXml;

namespace Utilities
{
    public static class Extensions
    {
        private static Dictionary<Type, IList<PropertyInfo>> typeDictionary = new Dictionary<Type, IList<PropertyInfo>>();

        public static IList<PropertyInfo> GetPropertiesForType<T>()
        {
            //variables
            var type = typeof(T);

            //get types
            if (!typeDictionary.ContainsKey(typeof(T)))
            {
                typeDictionary.Add(type, type.GetProperties().ToList());
            }

            //return
            return typeDictionary[type];
        }

        public static T ToObject<T>(this DataRow row) where T : new()
        {
            //variables
            IList<PropertyInfo> properties = GetPropertiesForType<T>();

            //return
            return CreateItemFromRow<T>(row, properties);
        }

        public static IList<T> ToList<T>(this DataTable table) where T : new()
        {
            //variables
            IList<T> result = new List<T>();

            //foreach
            foreach (DataRow row in table.Rows)
            {
                result.Add(row.ToObject<T>());
            }

            //return
            return result;
        }

        private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
        {
            //variables
            T item = new T();

            //foreach
            foreach (var property in properties)
            {
                //make sure a column exists in the table with this property name
                if (row.Table.Columns.Contains(property.Name))
                {
                    //get the value from the current data row
                    object value = row[property.Name];

                    //set property accordingly
                    if (value != null & value != DBNull.Value)
                    {
                        SetProperty<T>(item, property.Name, value);
                    }
                }
            }

            //return
            return item;
        }

        public static string GetProperty<T>(this T obj, string Property)
        {
            //reflection
            PropertyInfo propertyInfo = obj.GetType().GetProperty(Property, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
            object property = null;

            //make sure property is valid
            if (propertyInfo != null)
            {
                property = propertyInfo.GetValue(obj, null);
            }

            //return value
            if (property != null)
            {
                return property.ToString();
            }
            else
            {
                return string.Empty;
            }
        }

        public static T SetProperty<T>(this T obj, string Property, object Value)
        {
            //reflection
            PropertyInfo prop = obj.GetType().GetProperty(Property, BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);

            //trim strings
            if (Value.GetType() == typeof(string))
            {
                Value = Value.ToString().Trim();
            }

            //make sure property is valid
            if (prop != null && prop.CanWrite)
            {
                prop.SetValue(obj, Value, null);
            }

            //return
            return obj;
        }

        public static string Serialize<T>(this T obj)
        {
            // Json.NET (Newtonsoft.Json)
            // https://www.nuget.org/packages/Newtonsoft.Json
            return Newtonsoft.Json.JsonConvert.SerializeObject(obj, Newtonsoft.Json.Formatting.Indented);
        }

        public static T Deserialize<T>(string data) where T : new()
        {
            // Json.NET (Newtonsoft.Json)
            // https://www.nuget.org/packages/Newtonsoft.Json
            return Newtonsoft.Json.JsonConvert.DeserializeObject<T>(data);
        }

        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            // variables
            PropertyDescriptorCollection Properties = TypeDescriptor.GetProperties(typeof(T));
            object[] values = new object[Properties.Count];
            DataTable DT = new DataTable();

            // columns
            foreach (PropertyDescriptor PropertyInfo in Properties)
            {
                // data column
                DataColumn DataColumn = new DataColumn();

                // name
                DataColumn.ColumnName = PropertyInfo.Name;

                // data type
                if (PropertyInfo.PropertyType.Name.Contains("Nullable"))
                {
                    DataColumn.DataType = typeof(String);
                }
                else
                {
                    DataColumn.DataType = PropertyInfo.PropertyType;
                }

                // add to table
                DT.Columns.Add(DataColumn);
            }

            // rows
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = Properties[i].GetValue(item);
                }
                DT.Rows.Add(values);
            }

            // return
            return DT;
        }

        public static bool HasValue(this string Value)
        {
            return !Value.IsBlank();
        }

        public static bool IsBlank(this string Value)
        {
            bool ReturnValue = true;

            if (Value != null)
            {
                ReturnValue = Value.Trim().Length == 0;
            }

            return ReturnValue;
        }

        public static byte[] ToExcel(this DataTable DT, string WorksheetName = "Data", bool AutoFormatDates = false)
        {
            // EPPlus
            // https://www.nuget.org/packages/EPPlus

            // variables
            byte[] Data = null;
            ExcelPackage ExcelPackage = new ExcelPackage();
            ExcelWorksheet ExcelWorksheet = ExcelPackage.Workbook.Worksheets.Add(WorksheetName.HasValue() ? WorksheetName : "Data");

            // load data table with column headings
            ExcelWorksheet.Cells["A1"].LoadFromDataTable(DT, true);

            // auto-format dates
            if (AutoFormatDates)
            {
                var DateColumns = from DataColumn d in DT.Columns where d.DataType == typeof(DateTime) || d.ColumnName.Contains("Date") select d.Ordinal + 1;

                foreach (var Column in DateColumns)
                {
                    ExcelWorksheet.Cells[2, Column, DT.Rows.Count + 1, Column].Style.Numberformat.Format = "mm/dd/yyyy";
                }
            }

            // get data as byte array
            Data = ExcelPackage.GetAsByteArray();

            // cleanup
            ExcelPackage.Dispose();

            // return byte array
            return Data;
        }

        public static DataTable ToDataTable(this Stream Data, bool HasHeader = true)
        {
            // EPPlus
            // https://www.nuget.org/packages/EPPlus

            // variables
            DataTable DT = new DataTable();
            var RowStart = HasHeader ? 2 : 1;

            using (ExcelPackage ExcelPackage = new ExcelPackage())
            {
                // load data from stream
                ExcelPackage.Load(Data);

                // worksheet
                ExcelWorksheet ExcelWorksheet = ExcelPackage.Workbook.Worksheets.First();

                // create column headings
                foreach (var Cell in ExcelWorksheet.Cells[1, 1, 1, ExcelWorksheet.Dimension.End.Column])
                {
                    DT.Columns.Add(HasHeader ? Cell.Text : string.Format("Column {0}", Cell.Start.Column));
                }

                // copy data from each row to the data table
                for (int RowNumber = RowStart; RowNumber <= ExcelWorksheet.Dimension.End.Row; RowNumber++)
                {
                    // variables
                    ExcelRange Row = ExcelWorksheet.Cells[RowNumber, 1, RowNumber, ExcelWorksheet.Dimension.End.Column];
                    DataRow DataRow = DT.NewRow();

                    // copy the data from each cell to the data row
                    foreach (ExcelRangeBase cell in Row)
                    {
                        DataRow[cell.Start.Column - 1] = cell.Text;
                    }

                    // add row to data table
                    DT.Rows.Add(DataRow);
                }
            }

            // return data table
            return DT;
        }
    }
}

Click here to see a fully functional demo.

Matt Pavey is a Microsoft Certified software developer who specializes in ASP.Net, VB.Net, C#, AJAX, LINQ, XML, XSL, Web Services, SQL, jQuery, and more. Follow on Twitter @matthewpavey

0 comments: