Es importante agregar una referencia a Windows Base.
La clase para lectura de XLSx utiliza tambien esta clase que tiene algunos metodos utiles para hacer uso de las facilidades de Reflection en .NET
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Reflection;
namespace F.Runtime.Reflection
{
/// <summary>
/// Descripción breve de Reflecter.
/// </summary>
public sealed class Reflecter
{
/// <summary>
/// Get a DataType from a string Value, this will try to find the best fit
/// </summary>
/// <param name="value">value</param>
/// <returns></returns>
public static Type GetClosestDataType(string value)
{
Type result = value.GetType();
int v1;
double v2;
Single v3;
DateTime v4;
bool v5;
float v6;
if (int.TryParse(value, out v1)) {
result = v1.GetType();
}
else
if (double.TryParse (value , out v2)){
result = v2.GetType ();
}
else
if(Single.TryParse (value, out v3)){
result = v3.GetType();
}
else
if (DateTime.TryParse(value.ToString (), out v4))
{
result = v4.GetType();
}
else
if (bool.TryParse(value, out v5))
{
result = v5.GetType();
}
else
if (float.TryParse(value, out v6))
{
result = v6.GetType();
}
else
{
result = typeof (string);
}
return result;
}
/// <summary>
///
/// </summary>
/// <param name="obj"></param>
/// <param name="Property"></param>
/// <returns></returns>
public static object GetPropertyValue(object obj, string Property)
{
try
{
PropertyInfo pr =
obj.GetType().GetProperty(Property);
if (pr != null)
{
return pr.GetValue (obj, null);
}
}
catch (Exception)
{
}
return null;
}
/// <summary>
/// Coloca un valor a una propiedad
/// </summary>
/// <param name="obj"></param>
/// <param name="Property"></param>
/// <param name="value"></param>
public static void SetPropertyValue(object obj, string Property, object value)
{
try
{
PropertyInfo pr =
obj.GetType().GetProperty(Property) ;
if (pr!=null){
pr.SetValue(obj, value, null);
}
}
catch (Exception) {
}
}
/// <summary>
/// Represents an object into XML file
/// </summary>
/// <param name="obj"></param>
/// <param name="path"></param>
/// <returns></returns>
public static string ObjectToXML(object obj, string path)
{
System.IO.StreamWriter S = new System.IO.StreamWriter(path);
System.Xml.Serialization.XmlSerializer X = new System.Xml.Serialization.XmlSerializer(obj.GetType());
X.Serialize(S, obj);
return S.ToString();
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <param name="l"></param>
/// <param name="instance"></param>
public static void DataReaderToList<T>(System.Data.Common.DbDataReader dr, List<T> l, T instance)
{
if (dr != null)
{
if (!dr.IsClosed && dr.HasRows )
{
if (l == null)
l = new List<T>();
while (dr.Read ())
{
object objT = new object();
objT = instance.GetType().Assembly.CreateInstance(instance.GetType().FullName);
BindColumns(objT, dr);
l.Add((T)objT);
}
}
}
}
/// <summary>
/// Bild a List of Objects From
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <param name="l"></param>
/// <param name="instance"></param>
public static void DataTableToList<T>(DataTable dt, List<T> l, T instance)
{
if (dt!=null)
{
if (dt.Rows.Count > 0)
{
if (l == null)
l = new List<T>();
foreach (DataRow r in dt.Rows)
{
object objT = new object();
objT = instance.GetType().Assembly.CreateInstance(instance.GetType().FullName);
BindColumns(objT, r);
l.Add((T)objT);
}
}
}
}
/// <summary>
/// Copy Properties from One object to other object
/// </summary>
/// <param name="targetObj"></param>
/// <param name="sourceObj"></param>
public static void BindColumns(object targetObj, object sourceObj)
{
if (targetObj != null)
{
PropertyInfo[] targetprops = targetObj.GetType().GetProperties();
Dictionary<string, PropertyInfo> sourceprops = new Dictionary<string, PropertyInfo>();
foreach (PropertyInfo P in sourceObj.GetType().GetProperties()) {
if (!sourceprops.ContainsKey (P.Name.ToLower () )){
sourceprops.Add(P.Name.ToLower (), P);
}
}
foreach (PropertyInfo P in targetprops )
{
try
{
if (sourceprops.ContainsKey (P.Name.ToLower () ))
{
if (sourceprops[P.Name.ToLower () ].PropertyType.Equals (P.PropertyType )||(P.PropertyType.Name.StartsWith ("Nullable") && P.PropertyType.IsValueType && P.PropertyType.IsGenericType ))
{
P.SetValue(targetObj, sourceprops[P.Name.ToLower ()].GetValue (sourceObj,null), null);
}
}
}
catch
{
}
}
}
}
/// <summary>
/// Bind columns method to set columns of a DataRow into properties of an object
/// </summary>
/// <param name="obj">Object to be setted</param>
/// <param name="r">DataRow to be mapped</param>
public static void BindColumns(object obj, DataRow r)
{
if (obj != null)
{
foreach (PropertyInfo P in obj.GetType().GetProperties())
{
try
{
if (r.Table.Columns.Contains(P.Name))
{
if (!DBNull.Value.Equals(r[P.Name]) && r[P.Name] != null)
{
if (P.PropertyType.Name.StartsWith("Nullable") && P.PropertyType.IsValueType && P.PropertyType.IsGenericType)
{
obj.GetType().GetProperty(P.Name).SetValue(obj, r[P.Name], null);
}
else{
obj.GetType().GetProperty(P.Name).SetValue(obj, Convert.ChangeType(r[P.Name], P.PropertyType), null);
}
}
}
}
catch
{
}
}
}
}
/// <summary>
/// Bind columns method to set columns of a DataRow into properties of an object
/// </summary>
/// <param name="obj">Object to be setted</param>
/// <param name="r">DataRow to be mapped</param>
public static void BindColumns(object obj, DbDataReader r)
{
if (obj != null)
{
foreach (PropertyInfo P in obj.GetType().GetProperties())
{
try
{
if (r.GetSchemaTable ().Columns.Contains(P.Name))
{
if (!DBNull.Value.Equals(r[P.Name]) && r[P.Name] != null)
{
if (P.PropertyType.Name.StartsWith("Nullable") && P.PropertyType.IsValueType && P.PropertyType.IsGenericType)
{
obj.GetType().GetProperty(P.Name).SetValue(obj, r[P.Name], null);
}
else
{
obj.GetType().GetProperty(P.Name).SetValue(obj, Convert.ChangeType(r[P.Name], P.PropertyType), null);
}
}
}
}
catch
{
}
}
}
}
/// <summary>
/// Clone object method, clones only the
/// </summary>
/// <param name="obj">Object to be cloned</param>
/// <returns></returns>
public static object Clone(object obj)
{
object obj2 = obj.GetType().Assembly.CreateInstance(obj.GetType().FullName);
foreach (FieldInfo f in obj.GetType().GetFields())
{
try
{
obj2.GetType().GetField(f.Name).SetValue(obj2, f.GetValue(obj));
}
catch {
}
}
foreach (PropertyInfo P in obj.GetType().GetProperties())
{
try
{
obj2.GetType().GetField(P.Name).SetValue(obj2, P.GetValue(obj, null));
}
catch {
}
}
return obj2;
}
/// <summary>
/// Converts object properties into array of values
/// </summary>
/// <param name="obj"></param>
/// <param name="T"></param>
/// <returns></returns>
public static object[] ToArray(object obj)
{
int i = 0;
object val;
object[] values = new object[obj.GetType().GetFields().Length];
foreach (FieldInfo f in obj.GetType().GetFields())
{
try
{
val = f.GetValue(obj);
values[i] = val;
}
catch
{
values[i] = null;
}
i++;
}
return values;
}
/// <summary>
/// Converts object properties into array of values
/// </summary>
/// <param name="obj"></param>
/// <param name="T"></param>
/// <returns></returns>
public static Dictionary<string, object> PropertiesToDictionary(object obj)
{
int i = 0;
Dictionary<string, object> values = new Dictionary<string, object>();
foreach (PropertyInfo p in obj.GetType().GetProperties())
{
try
{
values.Add(p.Name, p.GetValue(obj, null));
}
catch
{
}
i++;
}
return values;
}
/// <summary>
/// Discover the property names from one object
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static string[] PropertiesNames(object obj)
{
int i = 0;
string[] names = new string[obj.GetType().GetProperties().Length];
foreach (PropertyInfo p in obj.GetType().GetProperties())
{
try
{
names[i] = p.Name;
}
catch
{
names[i] = null;
}
i++;
}
return names;
}
/// <summary>
/// Converts object properties into array of values
/// </summary>
/// <param name="obj"></param>
/// <param name="T"></param>
/// <returns></returns>
public static object[] PropertiesToArray(object obj)
{
int i = 0;
object val;
object[] values = new object[obj.GetType().GetProperties().Length];
foreach (PropertyInfo p in obj.GetType().GetProperties())
{
try
{
val = p.GetValue(obj, null);
values[i] = val;
}
catch
{
values[i] = null;
}
i++;
}
return values;
}
/// <summary>
/// Build a DataTable Definition from an object properties definition
/// </summary>
/// <param name="obj">Object</param>
/// <returns></returns>
public static DataTable BuildDataTable(object obj)
{
DataTable T = new DataTable();
if (obj != null)
{
foreach (FieldInfo f in obj.GetType().GetFields())
{
T.Columns.Add(f.Name, f.FieldType);
}
}
return T;
}
/// <summary>
/// Build a Data table from one Object
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static DataTable BuildDataTableByProperties(object obj)
{
DataTable T = new DataTable();
if (obj != null)
{
foreach (PropertyInfo f in obj.GetType().GetProperties())
{
T.Columns.Add(f.Name, f.PropertyType);
}
}
return T;
}
/// <summary>
/// Add a DataRow to a DataTable taking as parameter an object
/// </summary>
/// <param name="obj"></param>
public static void AddRow(object obj, DataTable T)
{
T.Rows.Add(ToArray(obj));
}
/// <summary>
/// Add a DataRow to a DataTable taking as parameter an object
/// </summary>
/// <param name="obj"></param>
public static void AddRowByProperties(object obj, DataTable T)
{
T.Rows.Add(PropertiesToArray(obj));
}
/// <summary>
/// Convert an object into a DataRow
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public static DataRow ToDataRow(object obj)
{
DataRow Dr;
DataTable T = BuildDataTable(obj);
Dr = T.NewRow();
Dr.ItemArray = ToArray(obj);
return Dr;
}
/// <summary>
/// Convert an object collection into a DataTable
/// </summary>
/// <param name="Collection"></param>
/// <returns></returns>
public static DataTable ToDataTable(object[] Collection)
{
DataTable T = null;
if (Collection != null)
{
if (Collection.Length > 0)
{
T = BuildDataTable(Collection[0]);
foreach (object obj in Collection)
{
AddRow(obj, T);
}
}
}
return T;
}
/// <summary>
/// Converts an object array into a datatable using the object properties
/// </summary>
/// <param name="Collection">collection of objects</param>
/// <returns></returns>
public static DataTable ToDataTableByProperties(object[] Collection)
{
DataTable T = null;
if (Collection != null)
{
if (Collection.Length > 0)
{
T = BuildDataTableByProperties(Collection[0]);
foreach (object obj in Collection)
{
AddRowByProperties(obj, T);
}
}
}
return T;
}
}
}
Clase para leer archivos XLSx
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Xml;
using System.IO;
using System.IO.Packaging;
namespace F.Text.Excel
{
/// <summary>
/// Excel formating namespaces
/// </summary>
internal static class ExcelNamespaces
{
internal static XNamespace excelNamespace = XNamespace.Get("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
internal static XNamespace excelRelationshipsNamepace = XNamespace.Get("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
/// <summary>
/// Cell object, that represent a Cell in a Worksheet
/// </summary>
public class Cell
{
#region Properties
/// <summary>
/// Column in the Worksheet (A,B,C,...)
/// </summary>
public string Column { get; set; }
/// <summary>
/// To be calculated
/// </summary>
public int ColumnNumber { get; set; }
/// <summary>
/// Row number 1,2,3... N
/// </summary>
public int Row { get; set; }
/// <summary>
/// Cell content
/// </summary>
public string Data { get; set; }
/// <summary>
/// Cell content datatype
/// </summary>
public string DataType { get; set; }
#endregion
#region Constructors
/// <summary>
/// Default constructor
/// </summary>
public Cell() {
}
/// <summary>
/// Constructor with all the initializers
/// </summary>
/// <param name="columnNumber">Column number</param>
/// <param name="column">Column Name</param>
/// <param name="row">Row Number</param>
/// <param name="data">Cell content</param>
public Cell(int columnNumber,string column, int row, string data)
{
this.Column = column;
this.Row = row;
this.Data = data;
this.ColumnNumber = ColumnNumber;
}
/// <summary>
/// Constructor with file reading initializers
/// </summary>
/// <param name="column">Column Name</param>
/// <param name="row">Row Number</param>
/// <param name="data">Cell content</param>
public Cell(string column, int row, string data)
{
this.Column = column;
this.Row = row;
this.Data = data;
}
#endregion
#region Public Methods
/// <summary>
/// Overrides the ToString() Object method, in order to expose object content
/// </summary>
/// <returns></returns>
public override string ToString()
{
return string.Format("{0}:{1} - {2}", Row, Column, Data);
}
#endregion
}
/// <summary>
/// Row Object that represent a row in a Worksheet and contains a collection of cells
/// </summary>
public class Row
{
#region Properties
public int RowNumber { get; set; }
public List<Cell> Cells { get; set; }
#endregion
#region Constructors
public Row(int row, List<Cell> cells) {
RowNumber = row;
Cells = cells;
}
/// <summary>
/// Default constructor
/// </summary>
public Row() {
}
#endregion
#region Public Methods
/// <summary>
/// Overrides the ToString() Object method, in order to expose object content
/// </summary>
/// <returns></returns>
public override string ToString()
{
string result = string.Empty;
if (Cells != null && Cells.Count > 0) {
foreach (Cell c in Cells) {
result += "{" +
c.ToString() + "};";
}
}
return result;
}
#endregion
}
/// <summary>
/// XLSx Utility Class, for Reading and writting purposes
/// </summary>
public class Xlsx
{
#region Properties
/// <summary>
///
/// </summary>
public bool FirstRowAsColumnsNames
{
get;
set;
}
private static string sheetsPath = "/xl/worksheets/sheet{0}.xml";
/// <summary>
/// Path in XLSx File to find the sheets, remembering that a XLSx is a packed file
/// </summary>
public static string SheetsPath
{
get {
return sheetsPath;
}
set{
sheetsPath = value;
}
}
public static string sharedStringsContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml";
/// <summary>
/// XLSx content type to find the shared strings (Excel sheets content)
/// </summary>
public static string SharedStringsContentType
{
get {
return sharedStringsContentType;
}
set {
sharedStringsContentType = value;
}
}
#endregion
#region Prvate Methods
/// <summary>
/// Get the shared strings where we can find the Cells data
/// </summary>
/// <param name="SharedStringsElement">Shared strings element</param>
/// <param name="sharedStrings">Shared string indexed output dictionary</param>
private static void GetSharedStrings(XElement SharedStringsElement, Dictionary<int, string> sharedStrings)
{
IEnumerable<XElement> sharedStringsElements = from s in SharedStringsElement.Descendants(ExcelNamespaces.excelNamespace + "t")
select s;
int Counter = 0;
foreach (XElement sharedString in sharedStringsElements)
{
sharedStrings.Add(Counter, sharedString.Value);
Counter++;
}
}
/// <summary>
/// Obtains the worksheet part of the worksheet number
/// </summary>
/// <param name="worksheetID">Workheet ID (number of the sheet)</param>
/// <param name="allParts">Parts of the file, where we can find the worksheet part</param>
/// <returns></returns>
private static XElement GetWorksheet(int worksheetID, PackagePartCollection allParts)
{
PackagePart worksheetPart = (from part in allParts
where part.Uri.OriginalString.Equals(String.Format(sheetsPath, worksheetID))
select part).Single();
return XElement.Load(XmlReader.Create(worksheetPart.GetStream()));
}
/// <summary>
/// Get the index number for a value in the sharedstrings
/// </summary>
/// <param name="value">value</param>
/// <returns></returns>
private static int IndexOfNumber(string value)
{
for (int counter = 0; counter < value.Length; counter++)
{
if (char.IsNumber(value[counter]))
{
return counter;
}
}
return 0;
}
#endregion
#region Public Methods
/// <summary>
/// Read a XLSx Worksheet into a Entity Collection
/// </summary>
/// <typeparam name="T">Entity Type</typeparam>
/// <param name="instance"></param>
/// <param name="fileName"></param>
/// <param name="sheetNumber"></param>
/// <returns></returns>
public List<T> ToCollection<T>(T instance, string fileName, int sheetNumber) {
List<T> result=new List<T> ();
List<Row> rows = ReadSheet(fileName, sheetNumber);
if (rows != null && rows.Count> 0)
{
Row header = rows[0];
for (int r=1;r<rows.Count ;r++)
{
T itm = (T)instance.GetType().Assembly.CreateInstance(instance.GetType().FullName);
for (int c = 0; c < header.Cells.Count; c++ )
{
try
{
Cell CELL = rows[r].Cells.Find(Cl => Cl.Column == header.Cells[c].Column);
if (CELL != null)
{
F.Runtime.Reflection.Reflecter.SetPropertyValue(
itm,
header.Cells[c].Data,
Convert.ChangeType(
CELL.Data,
itm.GetType().GetProperty(header.Cells[c].Data).PropertyType
)
);
}
}
catch (Exception ex) {
}
}
result.Add (itm);
}
}
return result;
}
/// <summary>
/// Read a XLSx Worksheet into a DataTable
/// </summary>
/// <param name="fileName">XLSx File Path</param>
/// <param name="sheetNumber">Number of the Worksheet to read</param>
/// <returns></returns>
public System.Data.DataTable ToDataTable(string fileName, int sheetNumber) {
System.Data.DataTable dt = new System.Data.DataTable();
List<Row> result= ReadSheet(fileName, sheetNumber);
if (FirstRowAsColumnsNames)
{
if (result != null && result.Count > 1)
{
Row header = result[0];
Row firstRow = result[1];
if (header.Cells != null && header.Cells.Count > 0
&& firstRow.Cells != null && firstRow.Cells.Count > 0
)
{
foreach (Cell c in header.Cells)
{
Cell CELL = firstRow.Cells.Find(Cl => Cl.Column == c.Column);
if (CELL != null)
{
dt.Columns.Add(new System.Data.DataColumn()
{
ColumnName = c.Data,
DataType = F.Runtime.Reflection.Reflecter.GetClosestDataType(CELL.Data)
}
);
}
}
for (int r = 1; r < result.Count; r++)
{
var Values = new object[header.Cells.Count];
for (int c = 0; c < header.Cells.Count; c++)
{
Cell CELL = result[r].Cells.Find(Cl => Cl.Column == header.Cells[c].Column);
if (CELL != null)
{
Values[c] = Convert.ChangeType(
CELL.Data,
dt.Columns[header.Cells[c].Data].DataType
);
}
else
{
Values[c] = null;
}
}
dt.Rows.Add(Values);
}
}
}
else
throw new Exception("The ResultSet is not Valid to Build a DataTable. It must have at least two rows.");
}
else {
Row header = result[0];
if (header.Cells != null && header.Cells.Count > 0
)
{
for (int c = 0; c < header.Cells.Count;c++ )
{
dt.Columns.Add(new System.Data.DataColumn()
{
ColumnName = "Column" + (c+1).ToString (),
DataType = F.Runtime.Reflection.Reflecter.GetClosestDataType(header.Cells [c].Data )
}
);
}
for (int r = 0; r < result.Count; r++)
{
var Values = new object[header.Cells.Count];
for (int c = 0; c < header.Cells.Count; c++)
{
Cell CELL = result[r].Cells.Find(Cl => Cl.Column == header.Cells[c].Column);
if (CELL != null)
{
Values[c] = Convert.ChangeType(
CELL.Data,
dt.Columns["Column"+(c+1).ToString ()].DataType
);
}
else
{
Values[c] = null;
}
}
dt.Rows.Add(Values);
}
}
}
return dt;
}
/// <summary>
/// Reads a XLSX Worksheet getting a Row Collection
/// </summary>
/// <param name="fileName">XLSx File Path</param>
/// <param name="sheetNumber">Number of the Worksheet to read</param>
/// <returns></returns>
public List<Row > ReadSheet(string fileName, int sheetNumber)
{
List<Row> result = new List<Row>();
List<Cell> sheetCells = new List<Cell>();
Package xlsxPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite);
try
{
#region Get File Parts
PackagePartCollection allParts = xlsxPackage.GetParts();
PackagePart sharedStringsPart = (from part in allParts
where part.ContentType.Equals(sharedStringsContentType)
select part).Single();
XElement sharedStringsElement = XElement.Load(XmlReader.Create(sharedStringsPart.GetStream()));
Dictionary<int, string> sharedStrings = new Dictionary<int, string>();
GetSharedStrings(sharedStringsElement, sharedStrings);
#endregion
#region Read Sheet
XElement worksheetElement = GetWorksheet(sheetNumber, allParts);
IEnumerable<XElement> cells = from c in worksheetElement.Descendants(ExcelNamespaces.excelNamespace + "c")
select c;
string cellPosition = string.Empty ;
int index = 0;
string column = string.Empty ;
int row = 0;
#region Parse Cells
foreach (XElement cell in cells)
{
cellPosition = cell.Attribute("r").Value;
index = IndexOfNumber(cellPosition);
column = cellPosition.Substring(0, index);
row = Convert.ToInt32(cellPosition.Substring(index, cellPosition.Length - index));
if (cell.HasElements)
{
if (cell.Attribute("t") != null && cell.Attribute("t").Value == "s")
{
// Shared value
int valueIndex = Convert.ToInt32(cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value);
sheetCells.Add(new Cell(column, row, sharedStrings[valueIndex]));
}
else
{
string value = cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value;
sheetCells.Add(new Cell(column, row, value));
}
}
else
{
sheetCells.Add(new Cell(column, row, ""));
}
}
#endregion
#endregion
}
finally
{
try
{
xlsxPackage.Close();
}
finally {
#region Transform parsed Cells into rows
if (sheetCells != null && sheetCells.Count > 0)
{
int rows = sheetCells.Max(m => m.Row);
for (int r = 1; r <= rows; r++)
{
Row R = new Row(r, sheetCells.FindAll(p => p.Row == r));
result.Add(R);
}
}
#endregion
}
}
return result ;
}
#endregion
}
}
