none
Formula cells in excel using openXML RRS feed

  • Question

  • Hi,

    Please explain how to create formula cells in excelsheet using openXML in C#.

     

    thanks,

    Elangovan p

    Wednesday, December 14, 2011 5:20 AM

Answers

  • Hi Elangovan,

    Thank you for posting.

    Below is the console application which I have coded and it works well on my side:

    using System;
    using System.Linq;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Collections.Generic;
    using System.IO;
     
    namespace OpenXmlExcelFillDataCS
    {
        class Program
        {
            static void Main(string[] args)
            {
                // create the Excel file contains a worksheet named test
                CreateSpreadSheet("test");
     
                // get the empty Data.xlsx file from bin folder
                string path = AppDomain.CurrentDomain.BaseDirectory + "DataTemplate.xlsx";
                if (File.Exists(path))
                {
                    File.Copy(path, "Data.xlsx"true);
                    {
                        FillData("Data.xlsx""Sheet2");
                    }
                }
                else
                {
                    Console.WriteLine("please create the template file: " + path);
                }
            }
     
            private static void FillData(string path, string sheetName)
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(
                   path, true))
                {
                    // find sheets by sheet name
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    if (sheets.Count() == 0)
                    {
                        // The specified worksheet does not exist.
                        return;
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                    Worksheet worksheet = worksheetPart.Worksheet;
                    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
     
                    // fill data to Cell F4
                    Row row1 = new Row()
                    {
                        RowIndex = (UInt32Value)4U,
                        Spans = new ListValue<StringValue>() { InnerText = "5:6" }
                    };
                                    
                    //CellFormula cellformula = new CellFormula();
                    //cellformula.Text = "SUM(A1:A2)";
     
                    //Cell cell = new Cell()
                    //{
                    //    CellReference = "F4",
                    //    DataType = new EnumValue<CellValues>(CellValues.Number),
                    //    CellFormula = cellformula
                    //};
     
                    Cell cell = new Cell() { CellReference = "E4" };
                    CellFormula cellformula = new CellFormula();
                    cellformula.Text = "SUM(A1,C5)";
                    CellValue cellValue = new CellValue();
                    cellValue.Text = "0";
                    cell.Append(cellformula);
                    cell.Append(cellValue);
     
                    Cell cell1 = new Cell() { CellReference = "F4" };
                    CellValue cellValue1 = new CellValue();
                    cellValue1.Text = "222";
                    cell1.Append(cellValue1);
     
                    // create formula in E4
                    row1.Append(cell);
                    // fill data to F4
                    row1.Append(cell1);
     
                    // fill data to Cell C5
                    Row row2 = new Row() { RowIndex = (UInt32Value)5U };
                    Cell cell2 = new Cell() { CellReference = "C5" };
                    CellValue cellValue2 = new CellValue();
                    cellValue2.Text = "111";
                    cell2.Append(cellValue2);
                    row2.Append(cell2);
     
                    // append rows to SheetData elment
                    sheetData.Append(row1);
                    sheetData.Append(row2);
     
                    // save worksheet
                    worksheet.Save();
                }
            }
     
            private static void CreateSpreadSheet(string sheetName)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(
       System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"),
       SpreadsheetDocumentType.Workbook))
                {
                    // create the workbook
                    spreadSheet.AddWorkbookPart();
                    spreadSheet.WorkbookPart.Workbook = new Workbook();     // create the worksheet
                    spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
     
                    // create sheet data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
     
                    // create row
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
     
                    // create cell with data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(
                          new Cell() { CellValue = new CellValue("100") });
     
                    // save worksheet
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
     
                    // create the worksheet to workbook relation
                    spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                    spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
                    {
                        Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
                        SheetId = 1,
                        Name = sheetName
                    });
     
                    spreadSheet.WorkbookPart.Workbook.Save();
                }
            }
        }
    }
    

    You can refer to the function FillData, it contains the code snippet about inserting formula. Hope this can help you can just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, December 15, 2011 10:05 AM
  • Elangovan P, if we want to create the formula based on other sheets, you can set the cell forumula like this:

    cellFormula1.Text = "SUM(A1,Sheet2!B1)";

    Hope this can give you the hint.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 4:14 AM

All replies

  • Hi Elangovan,

    Thank you for posting.

    Below is the console application which I have coded and it works well on my side:

    using System;
    using System.Linq;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Spreadsheet;
    using System.Collections.Generic;
    using System.IO;
     
    namespace OpenXmlExcelFillDataCS
    {
        class Program
        {
            static void Main(string[] args)
            {
                // create the Excel file contains a worksheet named test
                CreateSpreadSheet("test");
     
                // get the empty Data.xlsx file from bin folder
                string path = AppDomain.CurrentDomain.BaseDirectory + "DataTemplate.xlsx";
                if (File.Exists(path))
                {
                    File.Copy(path, "Data.xlsx"true);
                    {
                        FillData("Data.xlsx""Sheet2");
                    }
                }
                else
                {
                    Console.WriteLine("please create the template file: " + path);
                }
            }
     
            private static void FillData(string path, string sheetName)
            {
                using (SpreadsheetDocument document = SpreadsheetDocument.Open(
                   path, true))
                {
                    // find sheets by sheet name
                    IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                    if (sheets.Count() == 0)
                    {
                        // The specified worksheet does not exist.
                        return;
                    }
                    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                    Worksheet worksheet = worksheetPart.Worksheet;
                    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
     
                    // fill data to Cell F4
                    Row row1 = new Row()
                    {
                        RowIndex = (UInt32Value)4U,
                        Spans = new ListValue<StringValue>() { InnerText = "5:6" }
                    };
                                    
                    //CellFormula cellformula = new CellFormula();
                    //cellformula.Text = "SUM(A1:A2)";
     
                    //Cell cell = new Cell()
                    //{
                    //    CellReference = "F4",
                    //    DataType = new EnumValue<CellValues>(CellValues.Number),
                    //    CellFormula = cellformula
                    //};
     
                    Cell cell = new Cell() { CellReference = "E4" };
                    CellFormula cellformula = new CellFormula();
                    cellformula.Text = "SUM(A1,C5)";
                    CellValue cellValue = new CellValue();
                    cellValue.Text = "0";
                    cell.Append(cellformula);
                    cell.Append(cellValue);
     
                    Cell cell1 = new Cell() { CellReference = "F4" };
                    CellValue cellValue1 = new CellValue();
                    cellValue1.Text = "222";
                    cell1.Append(cellValue1);
     
                    // create formula in E4
                    row1.Append(cell);
                    // fill data to F4
                    row1.Append(cell1);
     
                    // fill data to Cell C5
                    Row row2 = new Row() { RowIndex = (UInt32Value)5U };
                    Cell cell2 = new Cell() { CellReference = "C5" };
                    CellValue cellValue2 = new CellValue();
                    cellValue2.Text = "111";
                    cell2.Append(cellValue2);
                    row2.Append(cell2);
     
                    // append rows to SheetData elment
                    sheetData.Append(row1);
                    sheetData.Append(row2);
     
                    // save worksheet
                    worksheet.Save();
                }
            }
     
            private static void CreateSpreadSheet(string sheetName)
            {
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(
       System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"),
       SpreadsheetDocumentType.Workbook))
                {
                    // create the workbook
                    spreadSheet.AddWorkbookPart();
                    spreadSheet.WorkbookPart.Workbook = new Workbook();     // create the worksheet
                    spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
     
                    // create sheet data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(new SheetData());
     
                    // create row
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
     
                    // create cell with data
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(
                          new Cell() { CellValue = new CellValue("100") });
     
                    // save worksheet
                    spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save();
     
                    // create the worksheet to workbook relation
                    spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
                    spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
                    {
                        Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
                        SheetId = 1,
                        Name = sheetName
                    });
     
                    spreadSheet.WorkbookPart.Workbook.Save();
                }
            }
        }
    }
    

    You can refer to the function FillData, it contains the code snippet about inserting formula. Hope this can help you can just feel free to follow up after you have tried.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, December 15, 2011 10:05 AM
  • Hi Bruce Song,

    thanks for ur ur answer. please clarify how to create formula cell in sheet1 based on content in sheet2.

     

    thanks,

    Elangovan P

    Thursday, December 15, 2011 1:37 PM
  • Elangovan P, if we want to create the formula based on other sheets, you can set the cell forumula like this:

    cellFormula1.Text = "SUM(A1,Sheet2!B1)";

    Hope this can give you the hint.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, December 21, 2011 4:14 AM
  • Can you tell me what's wrong with this method?

    Exec does not seem to like the results.

            Protected Overridable Function CreateFormulaCell(ByVal formula As String, ByVal cellReference As String) As Cell
                Dim cell As New Cell() '{ CellReference = "E4" };
                Dim cellformula As New CellFormula(formula)
                'cellformula.Text = formula ' "SUM(A1,C5)"
                cellformula.CalculateCell = True

                Dim cellValue As New CellValue
                cellValue.Text = "0"
                cell.Append(cellformula)
                cell.DataType = CellValues.Number
                cell.Append(cellValue)
                cell.StyleIndex = 2
                cell.CellReference = cellReference
                Return cell
            End Function

    Thursday, July 19, 2012 5:20 PM
  • I'm aware of how old this question is and that it's been closed, but your code only outputs a single value in the created worksheet.
    Monday, October 3, 2016 7:39 AM