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;
}
}
}
