public static WorksheetValues Read(Stream stream)
{
var worksheetValues = new WorksheetValues();
if (stream == null)
{
return(worksheetValues);
}
using (var excelPackage = new ExcelPackage(stream))
{
var worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault();
if (worksheet == null)
{
return(worksheetValues);
}
worksheetValues.Headers = GetFieldHeaders(worksheet);
var start = worksheet.Dimension.Start;
var end = worksheet.Dimension.End;
// start at the 2nd row (first row is the header)
for (var rowIndex = start.Row + 1; rowIndex <= end.Row; rowIndex++)
{
var record = new Dictionary <string, WorksheetValue>();
for (var columnIndex = start.Column; columnIndex <= worksheetValues.Headers.Count; columnIndex++)
{
var value = new WorksheetValue
{
Row = rowIndex,
Column = columnIndex,
Value = worksheet.GetValue(rowIndex, columnIndex)
};
record.Add(worksheetValues.Headers[columnIndex - 1], value);
}
worksheetValues.Content.Add(record);
}
}
return(worksheetValues);
}
public static byte[] Update(Stream stream, WorksheetValues worksheetValues)
{
if (stream == null)
{
return(null);
}
using (var excelPackage = new ExcelPackage(stream))
{
var worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault();
if (worksheet == null)
{
return(null);
}
// clear all comments
foreach (var comment in worksheet.Comments.Cast <ExcelComment>().ToList())
{
worksheet.Comments.Remove(comment);
}
// header errors
var col = worksheetValues.Headers.Count + 1;
foreach (var header in worksheetValues.MissingHeaders)
{
worksheet.SetValue(1, col, header);
var cell = worksheet.Cells[1, col];
cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(Color.Red);
cell.AddComment(worksheetValues.MissingHeaderComment, "SYSTEM");
col++;
}
// add error column
var errorColumn = worksheetValues.Headers.Count + 1;
var errorHeaderCell = worksheet.Cells[1, errorColumn];
errorHeaderCell.Value = "ERROR!";
errorHeaderCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
errorHeaderCell.Style.Fill.BackgroundColor.SetColor(Color.Red);
errorHeaderCell.Style.Font.Bold = true;
// cell errors
var cellErrors = worksheetValues.Content.SelectMany(x => x.Values).Where(x => x.HasError).ToList();
foreach (var cellError in cellErrors)
{
var cell = worksheet.Cells[cellError.Row, cellError.Column];
cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(Color.Red);
cell.AddComment(cellError.Error, "SYSTEM");
worksheet.SetValue(cellError.Row, errorColumn, 1);
}
return(excelPackage.GetAsByteArray());
}
}
