Open XML quick Reference

I am here put some code pieces, which might help to play with OPENXML for excel application
Note, this is ONLY a reference,  you should implement more checking and logic to satisfy your requirement.

First step

try to write all openXML code inside using  block, like below

Stream xlsFileStream = new FileStream("myfile.xlsx", FileMode.Open);
using (SpreadsheetDocument myspreadSheetDocument = SpreadsheetDocument.Open(ExportTemplateStream, true))
{
              //all your code write here
}

WorksheetPart and WorkSheet

 var sheets = myspreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>() .Elements<Sheet>().Where(s => s.Name == "My Sheet Name");
if (sheets.Count() == 0)
            {
                return null;
            }

  var relationshipId = sheets.First().Id.Value;

WorksheetPart myWorksheetPart=myspreadSheetDocument.WorkbookPart.GetPartById(relationshipId) as WorksheetPart

Worksheet myWorkSheet= myWorksheetPart.Worksheet;

Create new Row


var sheetData = myWorkSheet.GetFirstChild<SheetData>();

//Insert new row
int rowindex = //put rowindex
Row row = new Row { RowIndex = rowindex) };
sheetData.Append(row);

//Insert after a row
Row newRow = new Row { RowIndex = rowindex };
Row currentRow = sheetData.Elements<Row>().Where(x => x.RowIndex.Value == rowindex).FirstOrDefault();
sheetData.InsertAfter(newRow, currentRow );

Get Row

//here code will get Row of a particular sheet
 var sheet = myspreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>() .Elements<Sheet>().Where(s => s.Name == "My Sheet Name").First();

WorkSheetData WrkSheetData=sheet.GetFirstChild();

Row row=WrkSheetData.Elements<Row>().Where(x => x.RowIndex.Value == RowIndex).FirstOrDefault();

Create new Cell

one thing you needs to understand here is create empty cell even your text does not have any value, otherwise you will end up writing cell data in wrong cell column. Dont fully trust cell.reference :)

//Ensure there is a Row exists before create a cell. Explained above how to create a row

var rows = myworksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);

var row = rows.First();

Cell myCell = new Cell() {CellReference = "A1"};
myCell.DataType =CellValues.String;
CellValue cellValue = new CellValue("your text");
myCell.Append(cellValue);
row.Append(myCell);

Get a Cell
//this will help to get a cell from a particular row, Column

var row = myWorksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex).First();

string cellReference = "A1";

Cell myCell = row.Elements<Cell>()
                    .Where(c => string.Compare(c.CellReference.Value,cellReference, true) == 0).First();

adding Style

//get a style from one cell and apply it in another cell

//get style cell
Cell styleCell = //getting an existing cell is explained above

Cell newCell = //create a new Cell explained ablve

newCell.StyleIndex = styleCell.StyleIndex;

Last Row Index

int rowIndex = myWorkSheet.GetFirstChild<SheetData>().Elements().Max(x => x.RowIndex.Value);

Merge Cells

//Assume you want to merge cells C1 to C5

MergeCells mergeCells = new MergeCells();
myWorkSheet.InsertAfter(mergeCells, wrksheet.Elements<SheetData>().First());

MergeCell cellsToMerge = new MergeCell() { Reference = new DocumentFormat.OpenXml.StringValue("C1:C5") };

mergeCells.Append(cellsToMerge);

Apply Formula

Cell formulacell = //Cell which needs to apply formula
string strformula = "A1+A2";
CellFormula formula = new CellFormula(strformula);
formulacell.Append(formula);

Add New Sheet

Sheets allsheets = myWorkBookPart.Workbook.GetFirstChild<Sheets>();
string relationshipID = myWorkBookPart.GetIdOfPart(WrkSheetPart);

            uint intsheetID = 1;

            if (allsheets.Elements<Sheet>().Count() > 0)
            {
                intsheetID = allsheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

Sheet newSheet = new Sheet()

            {

                Id = relationshipID,

                SheetId = intsheetID,

                Name = "My Sheet Name"

            };

            allsheets.Append(newSheet);

Remove Sheet

var sheets = myWorkPart.Workbook.GetFirstChild<Sheets>().Elements().Where(s => s.Name == "mySheetName");

if (sheets.Count() > 0)

            {

                Sheet sheet = sheets.First();

                sheet.Remove();

            }

Copy Row

// here row index and cell reference will be a villan. make sure after copy complete you are giving correct rowindex and cell reference to new row

//frst you need to know the source Row to be copied

Row refRow = //get this row from same sheet or any other sheet. Getting row is explained above

Row insertAfterRow //get this row. New row will be created after this row

Row newRow = (Row)refRow.CloneNode(true);

newRow.RowIndex = //provide proper rowindex

sheetData.InsertAfter(newRow, insertAfterRow);

//row index and cell reference should be change for all rows below the added row

// Below code will adjust Rowindex and cell reference

//Assume new Row is added at 5th Row

int rowIndex=5

IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= rowIndex);

            foreach (Row ExistingRow in rows)

            {

                uint newRowIndex = System.Convert.ToUInt32(ExistingRow.RowIndex.Value + 1);

                foreach (Cell currentcell in ExistingRow.Elements<Cell>())

                {

                    string cellReference = currentcell.CellReference.Value;

                    currentcell.CellReference = new StringValue(cellReference.Replace(ExistingRow.RowIndex.Value.ToString(), newRowIndex.ToString()));

                }

                ExistingRow.RowIndex = new UInt32Value(newRowIndex);

            }