public SLExcelData ReadExcel(string filePath, string sheetTitle, bool hasHeader)
{
var data = new SLExcelData();
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false))
{
// Open the excel document
List <Row> rows;
WorkbookPart workbookPart = doc.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants <Sheet>();
var sheet = sheets.First();
if (string.IsNullOrEmpty(sheetTitle) == false)
{
var query1 = from _sheet in workbookPart.Workbook.Descendants <Sheet>()
where _sheet.Name.Value.ToString().Equals(sheetTitle, StringComparison.InvariantCultureIgnoreCase)
select _sheet;
sheet = query1.FirstOrDefault();
}
data.SheetName = sheet.Name;
var 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();
int dataStartIndex = 0;
// Read the header
if (rows.Count > 0 && hasHeader == true)
{
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);
}
dataStartIndex = 1;
}
// Read the sheet data
if (rows.Count > dataStartIndex)
{
for (var i = dataStartIndex; 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);
}
}
}
return(data);
}
}
}
public SLExcelData ReadExcel(string filePath)
{
var data = new SLExcelData();
/*
* // Check if the file is excel
* if (file.ContentLength <= 0)
* {
* data.Status.Message = "You uploaded an empty file";
* return data;
* }
*
* if (file.ContentType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
* {
* data.Status.Message = "Please upload a valid excel file of version 2007 and above";
* return data;
* }
*/
// Open the excel document
WorkbookPart workbookPart; List <Row> rows;
try
{
//var document = SpreadsheetDocument.Open(file.InputStream, false);
var document = SpreadsheetDocument.Open(filePath, false);
workbookPart = document.WorkbookPart;
var sheets = workbookPart.Workbook.Descendants <Sheet>();
var sheet = sheets.First();
data.SheetName = sheet.Name;
var 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();
}
catch (Exception e)
{
data.Status.Message = "Unable to open the file";
return(data);
}
// Read the header
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);
}
}
// Read the sheet data
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);
}
}
}
return(data);
}
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 cellIdex = 0;
Row row = null;
if (data.Headers.Count > 0)
{
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
}
// 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());
}