class Program
    {
        static void Main(string[] args)
        {
            InsertText(@"..\..\Documents\Sheet6.xlsx", "Inserted Text");
        }
        // Given a document name and text, 
        // inserts a new work sheet and writes the text to cell "A1" of the new worksheet.

        public static void InsertText(string docName, string text)
        {
            // Open the document for editing.
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
            {
                // Get the SharedStringTablePart. If it does not exist, create a new one.
                SharedStringTablePart shareStringPart;
                if (spreadSheet.WorkbookPart.GetPartsOfType().Count() > 0)
                {
                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First();
                }
                else
                {
                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart();
                }

                // Insert the text into the SharedStringTablePart.
                int index = InsertSharedStringItem(text, shareStringPart);

                // Insert a new worksheet.
                WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

                // Insert cell A1 into the new worksheet.
                Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

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

                // Save the new worksheet.
                worksheetPart.Worksheet.Save();
            }
        }

        // 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 one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

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

            return i;
        }

        // Given a WorkbookPart, inserts a new worksheet.
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            uint sheetId = 1;
            if (sheets.Elements().Count() > 0)
            {
                sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }

        // 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)
        {
            InsertText(@"..\..\Documents\Sheet6.xlsx", "Inserted Text");
        }
        public static void InsertText(string docName, string text)
        {
            //Initialize a new Workboook object
            Workbook workbook = new Workbook();

            //Load the document
            workbook.LoadFromFile(docName);

            //Creat a new sheet and then name it
            var count = workbook.Worksheets.Count + 1;
            Worksheet sheet = workbook.Worksheets.Add("Sheet" + count);

            //Insert text to the cell A1
            sheet.Range["A1"].Text = text;

            //Save a new workbook to disk
            workbook.Save();
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            string docName = @"..\..\Documents\Sheet5.xlsx";
            string worksheetName = "Joe";
            string title = "New Chart";
            Dictionary data = new Dictionary();
            data.Add("abc", 1);
            InsertChartInSpreadsheet(docName, worksheetName, title, data);
        }
        // Given a document name, a worksheet name, a chart title, and a Dictionary collection of text keys
        // and corresponding integer data, creates a column chart with the text as the series and the integers as the values.
        private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title,
        Dictionary data)
        {
            // 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);

                // Add a new drawing to the worksheet.
                DrawingsPart drawingsPart = worksheetPart.AddNewPart();
                worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
                { Id = worksheetPart.GetIdOfPart(drawingsPart) });
                worksheetPart.Worksheet.Save();

                // Add a new chart and set the chart language to English-US.
                ChartPart chartPart = drawingsPart.AddNewPart();
                chartPart.ChartSpace = new ChartSpace();
                chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") });
                DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(
                    new DocumentFormat.OpenXml.Drawing.Charts.Chart());

                // Create a new clustered column chart.
                PlotArea plotArea = chart.AppendChild(new PlotArea());
                Layout layout = plotArea.AppendChild(new Layout());
                BarChart barChart = plotArea.AppendChild(new BarChart(new BarDirection()
                { Val = new EnumValue(BarDirectionValues.Column) },
                    new BarGrouping() { Val = new EnumValue(BarGroupingValues.Clustered) }));

                uint i = 0;

                // Iterate through each key in the Dictionary collection and add the key to the chart Series
                // and add the corresponding value to the chart Values.
                foreach (string key in data.Keys)
                {
                    BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(new Index()
                    {
                        Val =
         new UInt32Value(i)
                    },
                        new Order() { Val = new UInt32Value(i) },
                        new SeriesText(new NumericValue() { Text = key })));

                    StringLiteral strLit = barChartSeries.AppendChild(new CategoryAxisData()).AppendChild(new StringLiteral());
                    strLit.Append(new PointCount() { Val = new UInt32Value(1U) });
                    strLit.AppendChild(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(title));

                    NumberLiteral numLit = barChartSeries.AppendChild(
                        new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild(new NumberLiteral());
                    numLit.Append(new FormatCode("General"));
                    numLit.Append(new PointCount() { Val = new UInt32Value(1U) });
                    numLit.AppendChild(new NumericPoint() { Index = new UInt32Value(0u) }).Append
        (new NumericValue(data[key].ToString()));

                    i++;
                }

                barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) });
                barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) });

                // Add the Category Axis.
                CategoryAxis catAx = plotArea.AppendChild(new CategoryAxis(new AxisId()
                { Val = new UInt32Value(48650112u) }, new Scaling(new Orientation()
                {
                    Val = new EnumValue(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
                }),
                    new AxisPosition() { Val = new EnumValue(AxisPositionValues.Bottom) },
                    new TickLabelPosition() { Val = new EnumValue(TickLabelPositionValues.NextTo) },
                    new CrossingAxis() { Val = new UInt32Value(48672768U) },
                    new Crosses() { Val = new EnumValue(CrossesValues.AutoZero) },
                    new AutoLabeled() { Val = new BooleanValue(true) },
                    new LabelAlignment() { Val = new EnumValue(LabelAlignmentValues.Center) },
                    new LabelOffset() { Val = new UInt16Value((ushort)100) }));

                // Add the Value Axis.
                ValueAxis valAx = plotArea.AppendChild(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
                    new Scaling(new Orientation()
                    {
                        Val = new EnumValue(
                        DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
                    }),
                    new AxisPosition() { Val = new EnumValue(AxisPositionValues.Left) },
                    new MajorGridlines(),
                    new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
                    {
                        FormatCode = new StringValue("General"),
                        SourceLinked = new BooleanValue(true)
                    }, new TickLabelPosition()
                    {
                        Val = new EnumValue
        (TickLabelPositionValues.NextTo)
                    }, new CrossingAxis() { Val = new UInt32Value(48650112U) },
                    new Crosses() { Val = new EnumValue(CrossesValues.AutoZero) },
                    new CrossBetween() { Val = new EnumValue(CrossBetweenValues.Between) }));

                // Add the chart Legend.
                Legend legend = chart.AppendChild
(new Legend(new LegendPosition() { Val = new EnumValue(LegendPositionValues.Right) },
                    new Layout()));

                chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) });

                // Save the chart part.
                chartPart.ChartSpace.Save();

                // Position the chart on the worksheet using a TwoCellAnchor object.
                drawingsPart.WorksheetDrawing = new WorksheetDrawing();
                TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
                twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("9"),
                    new ColumnOffset("581025"),
                    new RowId("17"),
                    new RowOffset("114300")));
                twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("17"),
                    new ColumnOffset("276225"),
                    new RowId("32"),
                    new RowOffset("0")));

                // Append a GraphicFrame to the TwoCellAnchor object.
                DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
                    twoCellAnchor.AppendChild(new DocumentFormat.OpenXml.Drawing.
        Spreadsheet.GraphicFrame());
                graphicFrame.Macro = "";

                graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
                    new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties() { Id = new UInt32Value(2u), Name = "Chart 1" },
                    new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));

                graphicFrame.Append(new Transform(new Offset() { X = 0L, Y = 0L },
                                                                        new Extents() { Cx = 0L, Cy = 0L }));

                graphicFrame.Append(new Graphic(new GraphicData(new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) })
                { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }));

                twoCellAnchor.Append(new ClientData());

                // Save the WorksheetDrawing object.
                drawingsPart.WorksheetDrawing.Save();
            }

        }
    }

  class Program
    {
        static void Main(string[] args)
        {
            string docName = @"..\..\Documents\Sheet5.xlsx";
            string worksheetName = "Sheet1";
            string title = "New Chart";
            string dataRange = "A1:B3";
            InsertChartInSpreadsheet(docName, worksheetName, title, dataRange);
        }
        private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title, string dataRange)
        {
            //Initialize a new Workboook object
            Workbook workbook = new Workbook();

            //Load workbook
            workbook.LoadFromFile(@"..\..\Documents\Data.xlsx");


            //Get the worksheet by its Name
            Worksheet worksheet = workbook.Worksheets[worksheetName];

            //Add a chart to the worksheet
            Chart chart = worksheet.Charts.Add();
            chart.ChartType = ExcelChartType.BarStacked;

            //Set title of the chart
            chart.ChartTitle = title;

            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 6;
            chart.RightColumn = 11;
            chart.BottomRow = 29;

            //Set region of chart data
            chart.DataRange = worksheet.Range[dataRange];
            chart.SeriesDataFromRange = false;

            //Save the Excel file
            workbook.SaveToFile(docName);

        }
    }

class Program
    {
        static void Main(string[] args)
        {
            GetSheetInfo(@"..\..\Documents\Sheet4.xlsx");
        }
        public static void GetSheetInfo(string fileName)
        {
            // Open file as read-only.
            using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(fileName, false))
            {
                S sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets;

                // For each sheet, display the sheet information.
                foreach (E sheet in sheets)
                {
                    foreach (A attr in sheet.GetAttributes())
                    {
                        Console.WriteLine("{0}: {1}", attr.LocalName, attr.Value);
                    }
                }
            }
            Console.ReadLine();
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            GetSheetInfo(@"..\..\Documents\Sheet4.xlsx");
        }
        private static void GetSheetInfo(string fileName)
        {
            //Initialize a new Workboook object
            Workbook workbook = new Workbook();

            //Load the document
            workbook.LoadFromFile(fileName);
           
            //Loop through all Sheets in the workbook
            foreach (Worksheet Sheet in workbook.Worksheets)
            {
                //Get Name,ID and Index of Sheet
                Console.WriteLine("Sheet Name: {0}", Sheet.Name);
                Console.WriteLine("Sheet Index: {0}", Sheet.Index);
                Console.WriteLine("Sheet ID: {0}", Sheet.SheetId);
                Console.ReadKey();
            }
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            string docName = @"..\..\Documents\Sheet3.xlsx";
            string sheetName = "Sheet1";
            string colName = "A";
            uint rowIndex = 3;
            DeleteTextFromCell(docName, sheetName, colName, rowIndex);
        }
        // Given a document, a worksheet name, a column name, and a one-based row index,
        // deletes the text from the cell at the specified column and row on the specified worksheet.
        public static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
        {
            // Open the document for editing.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
            {
                IEnumerable sheets = document.WorkbookPart.Workbook.GetFirstChild().Elements().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {
                    // The specified worksheet does not exist.
                    return;
                }
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

                // Get the cell at the specified column and row.
                Cell cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
                if (cell == null)
                {
                    // The specified cell does not exist.
                    return;
                }

                cell.Remove();
                worksheetPart.Worksheet.Save();
            }
        }

        // Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
        private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
        {
            IEnumerable rows = worksheet.GetFirstChild().Elements().Where(r => r.RowIndex == rowIndex);
            if (rows.Count() == 0)
            {
                // A cell does not exist at the specified row.
                return null;
            }

            IEnumerable cells = rows.First().Elements().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
            if (cells.Count() == 0)
            {
                // A cell does not exist at the specified column, in the specified row.
                return null;
            }

            return cells.First();
        }

        // Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
        // reference the specified SharedStringItem and removes the item.
        private static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
        {
            bool remove = true;

            foreach (var part in document.WorkbookPart.GetPartsOfType())
            {
                Worksheet worksheet = part.Worksheet;
                foreach (var cell in worksheet.GetFirstChild().Descendants())
                {
                    // Verify if other cells in the document reference the item.
                    if (cell.DataType != null &&
                        cell.DataType.Value == CellValues.SharedString &&
                        cell.CellValue.Text == shareStringId.ToString())
                    {
                        // Other cells in the document still reference the item. Do not remove the item.
                        remove = false;
                        break;
                    }
                }

                if (!remove)
                {
                    break;
                }
            }

            // Other cells in the document do not reference the item. Remove the item.
            if (remove)
            {
                SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
                if (shareStringTablePart == null)
                {
                    return;
                }

                SharedStringItem item = shareStringTablePart.SharedStringTable.Elements().ElementAt(shareStringId);
                if (item != null)
                {
                    item.Remove();

                    // Refresh all the shared string references.
                    foreach (var part in document.WorkbookPart.GetPartsOfType())
                    {
                        Worksheet worksheet = part.Worksheet;
                        foreach (var cell in worksheet.GetFirstChild().Descendants())
                        {
                            if (cell.DataType != null &&
                                cell.DataType.Value == CellValues.SharedString)
                            {
                                int itemIndex = int.Parse(cell.CellValue.Text);
                                if (itemIndex > shareStringId)
                                {
                                    cell.CellValue.Text = (itemIndex - 1).ToString();
                                }
                            }
                        }
                        worksheet.Save();
                    }

                    document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
                }
            }
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            string docName = @"..\..\Documents\sheet3.xlsx";
            string sheetName = "Sheet1";
            string colName = "A";
            uint rowIndex = 3;
            DeleteTextFromCell(docName, sheetName, colName, rowIndex);
        }
        public static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
        {
            //Initialize a new Workboook object
            Workbook workbook = new Workbook();

            //Load the document
            workbook.LoadFromFile(docName);

            //Obtain the worksheet by passing its Name
            Worksheet sheet = workbook.Worksheets[sheetName];

            //Remove value from cell
            sheet.Range[colName + rowIndex].Value = "";

            //Save a new workbook to disk
            workbook.Save();
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            CreateSpreadsheetWorkbook(@"..\..\Documents\Sheet2.xlsx");
        }
        public static void CreateSpreadsheetWorkbook(string filepath)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                AppendChild(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.
                GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }
    }

class Program
    {
        static void Main(string[] args)
        {
            CreateSpreadsheetWorkbook(@"..\..\Documents\Sheet2.xlsx");
        }
        public static void CreateSpreadsheetWorkbook(string filepath)
        {
            //Initialize a new Workboook object
            Workbook workbook = new Workbook();

            //Specific workbook version
            workbook.Version = ExcelVersion.Version2010;

            //Clear all sheets in workbook
            workbook.Worksheets.Clear();

            //Append a new sheet to worksheet and then name it My Sheet
            workbook.Worksheets.Create("My Sheet");

            //Save workbook to disk
            workbook.SaveToFile(filepath);
        }
    }

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