private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxRange source, QualifiedPointDefinition destination) { var outputStream = new MemoryStream(); try { using (var excel = new ExcelPackage(input)) { var sourceWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase)); if (sourceWorksheet == null) { return(InsertResult.CreateErroResult( $"source sheet '{sheetName}' not found", new InsertResultData { Context = context, InputStream = input, OutputStream = input })); } var destinationWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(destination.WorkSheet, StringComparison.OrdinalIgnoreCase)); if (destinationWorksheet == null) { return(InsertResult.CreateErroResult( $"Destination sheet '{sheetName}' not found", new InsertResultData { Context = context, InputStream = input, OutputStream = input })); } sourceWorksheet.Cells[source.Start.Row, source.Start.Column, source.End.Row, source.End.Column].Copy(destinationWorksheet.Cells[destination.Point.Row, destination.Point.Column]); excel.SaveAs(outputStream); return(InsertResult.CreateSuccessResult(new InsertResultData { Context = context, InputStream = input, OutputStream = outputStream })); } } catch (Exception ex) { return(InsertResult.FromException( ex, new InsertResultData { Context = context, InputStream = input, OutputStream = input })); } }
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxRange source, QualifiedPointDefinition destination, XlsxCellStyle headerStyle, XlsxCellStyle valueStyle) { var outputStream = new MemoryStream(); try { using (var excel = new ExcelPackage(input)) { var sourceWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase)); if (sourceWorksheet == null) { return(InsertResult.CreateErroResult( $"source sheet '{sheetName}' not found", new InsertResultData { Context = context, InputStream = input, OutputStream = input })); } var destinationWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(destination.WorkSheet, StringComparison.OrdinalIgnoreCase)); if (destinationWorksheet == null) { return(InsertResult.CreateErroResult( $"Destination sheet '{sheetName}' not found", new InsertResultData { Context = context, InputStream = input, OutputStream = input })); } var startDestination = destination.Point.Clone(); var x = destination.Point.Column - 1; var sourceRange = sourceWorksheet.Cells[source.Start.Row, source.Start.Column, source.End.Row, source.End.Column]; for (var column = 0; column < sourceRange.Columns; column++) { destination.Point = new XlsxPoint { Column = x, Row = destination.Point.Row }; for (var row = 0; row < sourceRange.Rows; row++) { destination.Point.Offset(1, 0); destinationWorksheet.Cells[destination.Point.Row, destination.Point.Column].Value = sourceWorksheet.Cells[source.Start.Row + row, source.Start.Column + column].Value; } destination.Point.Offset(0, 1); } XlsxCellStyle headerStyleToUse = excel.CreateStyle(headerStyle); var headerRange = destinationWorksheet.Cells[startDestination.Row, startDestination.Column, startDestination.Row, destination.Point.Column]; headerRange.StyleName = headerStyleToUse.Name; XlsxCellStyle valueStyleToUse = excel.CreateStyle(valueStyle); var valueRange = destinationWorksheet.Cells[startDestination.Row + 1, startDestination.Column, destination.Point.Row - 1, destination.Point.Column]; valueRange.StyleName = valueStyleToUse.Name; excel.SaveAs(outputStream); return(InsertResult.CreateSuccessResult(new InsertResultData { Context = context, InputStream = input, OutputStream = outputStream })); } } catch (Exception ex) { return(InsertResult.FromException( ex, new InsertResultData { Context = context, InputStream = input, OutputStream = input })); } }