/// <summary>
/// Método para leer un archivo Excel
/// </summary>
/// <param name="nombreArchivo">Nombre del archivo</param>
/// <returns>Objeto tipo SlExcelWorkBook</returns>
public ExcelWorkBook ReadExcel(string nombreArchivo)
{
var data = new ExcelWorkBook();
var sheetNumber = 0;
using (var document = SpreadsheetDocument.Open(nombreArchivo, false))
{
WorkbookPart workbookPart = document.WorkbookPart;
int totalSheets = workbookPart.WorksheetParts.Count() - 1;
for (int sheetPosition = totalSheets; sheetPosition >= 0; sheetPosition--)
{
var worksheetPart = workbookPart.WorksheetParts.ToList()[sheetPosition];
var sheet = new ExcelData();
ReadSheet(worksheetPart, sheet, workbookPart, sheetNumber);
data.Sheets.Add(sheet);
sheetNumber++;
}
document.Close();
}
return(data);
}
/// <summary>
/// Método para leer hoja
/// </summary>
/// <param name="worksheetPart">Objeto tipo WorksheetPart</param>
/// <param name="data">Objeto tipo SlExcelData</param>
/// <param name="workbookPart">Objeto tipo WorkbookPart</param>
/// <param name="sheetNumber">Número de hoja</param>
private void ReadSheet(WorksheetPart worksheetPart, ExcelData data, WorkbookPart workbookPart, int sheetNumber)
{
try
{
data.SheetName = workbookPart.Workbook.Descendants <Sheet>().ElementAt(sheetNumber).Name;
var workSheet = worksheetPart.Worksheet;
var columns = workSheet.Descendants <Columns>().FirstOrDefault();
data.ColumnConfigurations = columns;
var sheetData = workSheet.Elements <SheetData>().First();
List <Row> rows = sheetData.Elements <Row>().ToList();
if (rows.Count > 0)
{
var row = rows[0];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
data.Headers.Add(text);
}
}
if (rows.Count > 1)
{
for (var i = 1; i < rows.Count; i++)
{
var dataRow = new List <string>();
data.DataRows.Add(dataRow);
var row = rows[i];
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, workbookPart).Trim();
dataRow.Add(text);
}
}
}
}
catch (Exception e)
{
data.Status.Message = "No se puede abrir el documento";
}
}
/// <summary>
/// Método para generar Excel
/// </summary>
/// <param name="data">Datos</param>
/// <returns>Arreglo de bytes</returns>
public byte[] GenerateExcel(ExcelData data)
{
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(GetColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
}
}
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(GetColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
