OpenXml - How to create Excle file with formula cell - step by step

Watch this example on youtube:



Just copy and paste - to see how to create Excel file with formula in memory using ASP.NET:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.IO.Packaging;



            MemoryStream fs = new MemoryStream();
            SpreadsheetDocument xl = SpreadsheetDocument.Create(fs, SpreadsheetDocumentType.Workbook);
            WorkbookPart wbp = xl.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();

            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();
            SheetData sd = new SheetData();

            Row r = new Row();
            r.RowIndex = (UInt32)1;
            Cell c = new Cell() { CellReference = "A1" };
            c.DataType = CellValues.Number;
            c.CellValue = new CellValue("10");
            r.Append(c);

            Cell c2 = new Cell() { CellReference = "B1" };
            c2.DataType = CellValues.Number;
            c2.CellValue = new CellValue("30");
            r.Append(c2);

            Cell cFormula = new Cell() { CellReference = "C1" };
            CellFormula cf = new CellFormula();
            cf.Text = "SUM(A1:B1)";
            cFormula.Append(cf);
            r.Append(cFormula);
            sd.Append(r);
            ws.Append(sd);

            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "some name";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);

            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();

            Response.Clear();
            byte[] dt = fs.ToArray();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=fileName.xlsx"));
            Response.BinaryWrite(dt);
            Response.End();
        }