Clase ExcelLogica

using DocumentFormat.OpenXml;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.IO;

using System.Reflection;

namespace Logica.Excel

{

    public static class ExcelLogica

    {

        public static List<object> Leer(string archivo)

        {

            var lista = new List<object>();

            List<List<string>> samples = new List<List<string>>();

            try

            {

                int selCount = 0;

                var document = SpreadsheetDocument.Open(archivo, false);

                var workbookPart = document.WorkbookPart;

                var sheets = workbookPart.Workbook.Descendants<Sheet>();

                var fila = 1;               

                foreach (Sheet sheet in sheets)

                {

                    var workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;

                    var sheetData = workSheet.Elements<SheetData>().First();

                    List<Row> rows = sheetData.Elements<Row>().ToList();

                    var columnas = rows.ElementAt(0).Elements<Cell>().Count();

                    var b = new string[columnas];

                   

                    foreach (Row row in rows)

                    {

                        var indice = 0;

                        b = new string[columnas];

                        foreach (Cell c in row.Elements<Cell>())

                        {

                            var value = "";

                            if (c != null)

                            {

                                value = c.InnerText;

                                if (c.DataType != null)

                                {

                                    switch (c.DataType.Value)

                                    {

                                        case CellValues.SharedString:

                                            // For shared strings, look up the value in the

                                            // shared strings table.

                                            var stringTable =

                                                workbookPart.GetPartsOfType<SharedStringTablePart>()

                                                .FirstOrDefault();

                                            // If the shared string table is missing, something

                                            // is wrong. Return the index that is in

                                            // the cell. Otherwise, look up the correct text in

                                            // the table.

                                            if (stringTable != null)

                                            {

                                                value =

                                                    stringTable.SharedStringTable

                                                    .ElementAt(int.Parse(value)).InnerText;

                                            }

                                            break;

                                        case CellValues.Boolean:

                                            switch (value)

                                            {

                                                case "0":

                                                    value = "FALSE";

                                                    break;

                                                default:

                                                    value = "TRUE";

                                                    break;

                                            }

                                            break;

                                    }

                                }

                                b[indice] = value;

                                indice++;

                            }

                        }

                        fila++;

                        lista.Add(b);

                    }

                }

            } catch (Exception ex)

            {}

            return lista;

        }

        public static byte[] GenerarOpenXml(DataTable t, string autofiltro)

        {

            MemoryStream stream = new MemoryStream();

            using (SpreadsheetDocument myWorkbook =

                    SpreadsheetDocument.Create(stream,

                    SpreadsheetDocumentType.Workbook, true))

            {

                WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();

                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

                var sheets = new Sheets();

                var sheet = new Sheet { Name = t.TableName, SheetId = 1, Id = relId };

                sheets.Append(sheet);

                SheetData sheetData = new SheetData(CreateSheetData(t));

                var workbook = new Workbook();

                workbook.Append(fileVersion);

                workbook.Append(sheets);

                var worksheet = new Worksheet();

                worksheet.Append(sheetData);

                AutoFilter autoFilter1 = null;

                if (autofiltro != null)

                {

                    autoFilter1 = new AutoFilter() { Reference = autofiltro };

                    worksheet.Append(autoFilter1);

                }

                worksheetPart.Worksheet = worksheet;

                myWorkbook.WorkbookPart.Workbook = workbook;

                myWorkbook.WorkbookPart.Workbook.Save();

                myWorkbook.Close();

                return stream.ToArray();

            }

        }

        public static byte[] GenerarOpenXml<T>(this IEnumerable<T> collection, string autofiltro)

        {

            var t = ToDataTable(collection, "Subdiario");

            return GenerarOpenXml(t, autofiltro);

        }

        public static MemoryStream BuildExcel(DataTable dataTable, string autofiltro)

        {

            MemoryStream stream = new MemoryStream();

            using (SpreadsheetDocument myWorkbook =

                    SpreadsheetDocument.Create(stream,

                    SpreadsheetDocumentType.Workbook, true))

            {

                WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();

                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                string relId = workbookPart.GetIdOfPart(worksheetPart);

                var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

                var sheets = new Sheets();

                var sheet = new Sheet { Name = dataTable.TableName, SheetId = 1, Id = relId };

                sheets.Append(sheet);

                SheetData sheetData = new SheetData(CreateSheetData(dataTable));

                var workbook = new Workbook();

                workbook.Append(fileVersion);

                workbook.Append(sheets);

                var worksheet = new Worksheet();

                worksheet.Append(sheetData);

                AutoFilter autoFilter1 = null;

                if (autofiltro != null)

                {

                    autoFilter1 = new AutoFilter() { Reference = autofiltro };

                    worksheet.Append(autoFilter1);

                }

                worksheetPart.Worksheet = worksheet;

                myWorkbook.WorkbookPart.Workbook = workbook;

                myWorkbook.WorkbookPart.Workbook.Save();

                myWorkbook.Close();

                return stream;

            }

        }

        private static List<OpenXmlElement> CreateSheetData(DataTable dataTable)

        {

            List<OpenXmlElement> elements = new List<OpenXmlElement>();

            var rowHeader = new Row();

            Cell[] cellsHeader = new Cell[dataTable.Columns.Count];

            for (int i = 0; i < dataTable.Columns.Count; i++)

            {

                cellsHeader[i] = new Cell();

                cellsHeader[i].DataType = CellValues.String;

                cellsHeader[i].CellValue = new CellValue(dataTable.Columns[i].ColumnName);

            }

            rowHeader.Append(cellsHeader);

            elements.Add(rowHeader);

            foreach (DataRow rowDataTable in dataTable.Rows)

            {

                var row = new Row();

                Cell[] cells = new Cell[dataTable.Columns.Count];

                for (int i = 0; i < dataTable.Columns.Count; i++)

                {

                    cells[i] = new Cell();

                    if (dataTable.Columns[i].DataType == System.Type.GetType("System.Decimal"))

                        cells[i].DataType = CellValues.Number;

                    else if (dataTable.Columns[i].DataType == System.Type.GetType("System.DateTime"))

                        cells[i].DataType = CellValues.Date;

                    else

                        cells[i].DataType = CellValues.String;

                    cells[i].CellValue = new CellValue(rowDataTable[i].ToString());

                }

                row.Append(cells);

                elements.Add(row);

            }

            return elements;

        }

        public static DataTable ToDataTable<T>(this IEnumerable<T> collection, string tableName)

        {

            DataTable tbl = ToDataTable(collection);

            tbl.TableName = tableName;

            return tbl;

        }

        public static DataTable ToDataTable<T>(this IEnumerable<T> collection)

        {

            DataTable dt = new DataTable();

            Type t = typeof(T);

            PropertyInfo[] pia = t.GetProperties();

            object temp;

            DataRow dr;

            for (int i = 0; i < pia.Length; i++)

            {

                dt.Columns.Add(pia[i].Name, Nullable.GetUnderlyingType(pia[i].PropertyType) ?? pia[i].PropertyType);

                dt.Columns[i].AllowDBNull = true;

            }

            //Populate the table

            foreach (T item in collection)

            {

                dr = dt.NewRow();

                dr.BeginEdit();

                for (int i = 0; i < pia.Length; i++)

                {

                    temp = pia[i].GetValue(item, null);

                    if (temp == null || (temp.GetType().Name == "Char" && ((char)temp).Equals('\0')))

                    {

                        dr[pia[i].Name] = (object)DBNull.Value;

                    }

                    else

                    {

                        dr[pia[i].Name] = temp;

                    }

                }

                dr.EndEdit();

                dt.Rows.Add(dr);

            }

            return dt;

        }

    }

}