/// <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()); }