class Program
    {
        static void Main(string[] args)
        {
            string docName = @"..\..\documents\Sheet1.xlsx";
            string worksheetName = "John";
            string firstCellName = "A1";
            string lastCellName = "A3";
            string resultCell = "A4";
            CalculateSumOfCellRange(docName, worksheetName, firstCellName, lastCellName, resultCell);
        }
        private static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
        {
            // Open the document for editing.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
            {
                IEnumerable sheets = document.WorkbookPart.Workbook.Descendants().Where(s => s.Name == worksheetName);
                if (sheets.Count() == 0)
                {
                    // The specified worksheet does not exist.
                    return;
                }

                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                Worksheet worksheet = worksheetPart.Worksheet;

                // Get the row number and column name for the first and last cells in the range.
                uint firstRowNum = GetRowIndex(firstCellName);
                uint lastRowNum = GetRowIndex(lastCellName);
                string firstColumn = GetColumnName(firstCellName);
                string lastColumn = GetColumnName(lastCellName);

                double sum = 0;

                // Iterate through the cells within the range and add their values to the sum.
                foreach (Row row in worksheet.Descendants().Where(r => r.RowIndex.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
                {
                    foreach (Cell cell in row)
                    {
                        string columnName = GetColumnName(cell.CellReference.Value);
                        if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0)
                        {
                            sum += double.Parse(cell.CellValue.Text);
                        }
                    }
                }

                // Get the SharedStringTablePart and add the result to it.
                // If the SharedStringPart does not exist, create a new one.
                SharedStringTablePart shareStringPart;
                if (document.WorkbookPart.GetPartsOfType().Count() > 0)
                {
                    shareStringPart = document.WorkbookPart.GetPartsOfType().First();
                }
                else
                {
                    shareStringPart = document.WorkbookPart.AddNewPart();
                }

                // Insert the result into the SharedStringTablePart.
                int index = InsertSharedStringItem("Result: " + sum, shareStringPart);

                Cell result = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart);

                // Set the value of the cell.
                result.CellValue = new CellValue(index.ToString());
                result.DataType = new EnumValue(CellValues.SharedString);

                worksheetPart.Worksheet.Save();
            }
        }

        // Given a cell name, parses the specified cell to get the row index.
        private static uint GetRowIndex(string cellName)
        {
            // Create a regular expression to match the row index portion the cell name.
            Regex regex = new Regex(@"\d+");
            Match match = regex.Match(cellName);

            return uint.Parse(match.Value);
        }
        // Given a cell name, parses the specified cell to get the column name.
        private static string GetColumnName(string cellName)
        {
            // Create a regular expression to match the column name portion of the cell name.
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellName);

            return match.Value;
        }
        // Given two columns, compares the columns.
        private static int CompareColumn(string column1, string column2)
        {
            if (column1.Length > column2.Length)
            {
                return 1;
            }
            else if (column1.Length < column2.Length)
            {
                return -1;
            }
            else
            {
                return string.Compare(column1, column2, true);
            }
        }
        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create it.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements())
            {
                if (item.InnerText == text)
                {
                    // The text already exists in the part. Return its index.
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }
        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            string docName = @"..\..\documents\Sheet1.xlsx";
            string worksheetName = "Jone";
            string firstCellName = "A1";
            string lastCellName = "A3";
            string resultCell = "A4";
            CalculateSumOfCellRange(docName, worksheetName, firstCellName, lastCellName, resultCell);
        }
        private static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
        {
            //Initialize a new Workboook object
            Workbook workbook = new Workbook();

            //Load workbook
            workbook.LoadFromFile(docName);

            // Caculate abosulte value function
            string Formula = "=SUM(" + firstCellName + ":" + lastCellName + ")";// =SUM(A1:A3)
            var formulaResult = workbook.CaculateFormulaValue(Formula);
            String value = formulaResult.ToString();

            //Set the Value of A4
            workbook.Worksheets[0].Range[resultCell].Value = "Result :" + value;

            //Save the Excel file
            workbook.SaveToFile(@"..\..\documents\Calculate the sum of a range of cells.xlsx");
        }
    }