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