public static List <string> GetMusicNames(string excelPath)
{
List <string> names = new List <string>();
SlExcelReader slExcel = new SlExcelReader();
SlExcelData slExcelData = slExcel.ReadExcel(excelPath);
names = slExcelData.DataRows.SelectMany(l => l).Distinct().ToList();
names.AddRange(slExcelData.Headers);
return(names);
}
/// <summary>
///
/// </summary>
///
/// <param name="path"></param>
/// <returns></returns>
public SlExcelData ReadExcel(string path)
{
var data = new SlExcelData();
// Open the excel document
SpreadsheetDocument document = null;
List <Row> rows = new List <Row>();
try
{
rows = OpenExcel(path, data, out document);
}
catch (IOException)
{
throw new Exception("El archivo está abiero. Por favor ciérrelo.");
}
if (rows.Count <= 1 || document == null)
{
return(data);
}
foreach (var row in rows)
{
var dataRow = new List <string>();
var cellEnumerator = GetExcelCellEnumerator(row);
bool hayDatos = false;
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var text = ReadExcelCell(cell, document.WorkbookPart).Trim();
dataRow.Add(text);
if (!hayDatos & !string.IsNullOrEmpty(text))
{
hayDatos = true;
}
}
if (hayDatos)
{
data.DataRows.Add(dataRow);
}
}
document.Close();
return(data);
}
private static List <Row> OpenExcel(string path, SlExcelData data, out SpreadsheetDocument document)
{
List <Row> rows;
document = SpreadsheetDocument.Open(path, false);
var workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants <Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
Worksheet workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
var columns = workSheet.Descendants <Columns>().FirstOrDefault();
data.ColumnConfigurations = columns;
var sheetData = workSheet.Elements <SheetData>().First();
rows = sheetData.Elements <Row>().ToList();
return(rows);
}
public byte[] GenerateExcel(SlExcelData 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);
// Add header
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet
.InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var callData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, callData ?? string.Empty);
row.AppendChild(cell);
}
}
workbookpart.Workbook.Save();
document.Close();
return(stream.ToArray());
}
