Update on EPPlus licensing. When this post was written, EPPlus was LGPL and free to use anywhere. As of version 5, released in early 2020, EPPlus moved to the Polyform Noncommercial license, and its maintainers state that any use inside a commercial company requires a purchased commercial license. Versions 4.x and earlier are still LGPL. Version 5 and later also require you to set ExcelPackage.LicenseContext explicitly or they throw a LicenseException in debug builds. The code below still works, but check which version you are pulling from NuGet and what your licensing position is. If you need a permissively licensed alternative, ClosedXML and NPOI are both worth a look.
- 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;
}
}
}