- 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: