C# Workbook.CalculateFormula方法代码示例

        public static void Run()
        {
            // ExStart:UpdateReferenceInWorksheets
            // Create workbook
            Workbook wb = new Workbook();

            // Add second sheet with name Sheet2
            wb.Worksheets.Add("Sheet2");

            // Access first sheet and add some integer value in cell C1
            // Also add some value in any cell to increase the number of blank rows and columns
            Worksheet sht1 = wb.Worksheets[0];
            sht1.Cells["C1"].PutValue(4);
            sht1.Cells["K30"].PutValue(4);

            // Access second sheet and add formula in cell E3 which refers to cell C1 in first sheet
            Worksheet sht2 = wb.Worksheets[1];
            sht2.Cells["E3"].Formula = "'Sheet1'!C1";

            // Calculate formulas of workbook
            wb.CalculateFormula();

            // Print the formula and value of cell E3 in second sheet before deleting blank columns and rows in Sheet1.
            Console.WriteLine("Cell E3 before deleting blank columns and rows in Sheet1.");
            Console.WriteLine("--------------------------------------------------------");
            Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
            Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);

            // If you comment DeleteOptions.UpdateReference property below, then the formula in cell E3 in second sheet will not be updated
            DeleteOptions opts = new DeleteOptions();
            opts.UpdateReference = true;

            // Delete all blank rows and columns with delete options
            sht1.Cells.DeleteBlankColumns(opts);
            sht1.Cells.DeleteBlankRows(opts);

            // Calculate formulas of workbook
            wb.CalculateFormula();

            // Print the formula and value of cell E3 in second sheet after deleting blank columns and rows in Sheet1.
            Console.WriteLine("");
            Console.WriteLine("");
            Console.WriteLine("Cell E3 after deleting blank columns and rows in Sheet1.");
            Console.WriteLine("--------------------------------------------------------");
            Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
            Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);
            // ExEnd:UpdateReferenceInWorksheets
        }

        public static void Run()
        {
            // ExStart:UsingICustomFunctionFeature
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Open the workbook
            Workbook workbook = new Workbook();

            // Obtaining the reference of the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            // Adding sample values to cells
            worksheet.Cells["B1"].PutValue(5);
            worksheet.Cells["C1"].PutValue(100);
            worksheet.Cells["C2"].PutValue(150);
            worksheet.Cells["C3"].PutValue(60);
            worksheet.Cells["C4"].PutValue(32);
            worksheet.Cells["C5"].PutValue(62);

            // Adding custom formula to Cell A1
            workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1,C1:C5)";

            // Calcualting Formulas
            workbook.CalculateFormula(false, new CustomFunction());

            // Assign resultant value to Cell A1
            workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);

            // Save the file
            workbook.Save(dataDir + "UsingICustomFunction_out.xls");
            // ExEnd:UsingICustomFunctionFeature
        }

        public static void Run()
        {
            // ExStart:CalculateIFNAFunction
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create new workbook
            Workbook workbook = new Workbook();

            // Access first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            // Add data for VLOOKUP
            worksheet.Cells["A1"].PutValue("Apple");
            worksheet.Cells["A2"].PutValue("Orange");
            worksheet.Cells["A3"].PutValue("Banana");

            // Access cell A5 and A6
            Cell cellA5 = worksheet.Cells["A5"];
            Cell cellA6 = worksheet.Cells["A6"];

            // Assign IFNA formula to A5 and A6
            cellA5.Formula = "=IFNA(VLOOKUP(\"Pear\",$A$1:$A$3,1,0),\"Not found\")";
            cellA6.Formula = "=IFNA(VLOOKUP(\"Orange\",$A$1:$A$3,1,0),\"Not found\")";

            // Caclulate the formula of workbook
            workbook.CalculateFormula();

            // Print the values of A5 and A6
            Console.WriteLine(cellA5.StringValue);
            Console.WriteLine(cellA6.StringValue);
            // ExEnd:CalculateIFNAFunction
        }

        static void Main(string[] args)
        {
            string MyDir = @"Files\";
            //Instantiating a Workbook object
            Workbook workbook = new Workbook();

            //Adding a new worksheet to the Excel object
            int sheetIndex = workbook.Worksheets.Add();

            //Obtaining the reference of the newly added worksheet by passing its sheet index
            Worksheet worksheet = workbook.Worksheets[sheetIndex];

            //Adding a value to "A1" cell
            worksheet.Cells["A1"].PutValue(1);

            //Adding a value to "A2" cell
            worksheet.Cells["A2"].PutValue(2);

            //Adding a value to "A3" cell
            worksheet.Cells["A3"].PutValue(3);

            //Adding a SUM formula to "A4" cell
            worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";

            //Calculating the results of formulas
            workbook.CalculateFormula();

            //Get the calculated value of the cell
            string value = worksheet.Cells["A4"].Value.ToString();

            //Saving the Excel file
            workbook.Save(MyDir + "Adding Formula.xls");
        }

        public static void Run()
        {
            // ExStart:UsingGlobalizationSettings
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Loads an existing spreadsheet containing some data
            Workbook book = new Workbook(dataDir + "sample.xlsx");

            // Assigns the GlobalizationSettings property of the WorkbookSettings class to the class created in first step
            book.Settings.GlobalizationSettings = new CustomSettings();

            // Accesses the 1st worksheet from the collection which contains data resides in the cell range A2:B9
            Worksheet sheet = book.Worksheets[0];

            // Adds Subtotal of type Average to the worksheet
            sheet.Cells.Subtotal(CellArea.CreateCellArea("A2", "B9"), 0, ConsolidationFunction.Average, new int[] { 1 });

            // Calculates Formulas
            book.CalculateFormula();

            // Auto fits all columns
            sheet.AutoFitColumns();

            // Saves the workbook on disc
            book.Save(dataDir + "output_out.xlsx");
            // ExEnd:UsingGlobalizationSettings
        }

        public static void Run()
        {
            // ExStart:SettingSimpleFormulaForNamedRanges
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create an instance of Workbook
            Workbook book = new Workbook();

            // Get the WorksheetCollection
            WorksheetCollection worksheets = book.Worksheets;

            // Add a new Named Range with name "NewNamedRange"
            int index = worksheets.Names.Add("NewNamedRange");

            // Access the newly created Named Range
            Name name = worksheets.Names[index];

            // Set RefersTo property of the Named Range to a formula. Formula references another cell in the same worksheet
            name.RefersTo = "=Sheet1!$A$3";

            // Set the formula in the cell A1 to the newly created Named Range
            worksheets[0].Cells["A1"].Formula = "NewNamedRange";

            // Insert the value in cell A3 which is being referenced in the Named Range
            worksheets[0].Cells["A3"].PutValue("This is the value of A3");

            // Calculate formulas
            book.CalculateFormula();

            // Save the result in XLSX format
            book.Save(dataDir + "output_out.xlsx");
            // ExEnd:SettingSimpleFormulaForNamedRanges
        }

        static void Main(string[] args)
        {
            //Instantiating a Workbook object
            Workbook book = new Workbook();

            //Obtaining the reference of the newly added worksheet
            int sheetIndex = book.Worksheets.Add();
            Worksheet worksheet = book.Worksheets[sheetIndex];
            Cells cells = worksheet.Cells;
            Cell cell = null;

            //Adding a value to "A1" cell
            cell = cells["A1"];
            cell.Value = 1;

            //Adding a value to "A2" cell
            cell = cells["A2"];
            cell.Value = 2;

            //Adding a value to "A3" cell
            cell = cells["A3"];
            cell.Value = 3;

            //Adding a SUM formula to "A4" cell
            cell = cells["A4"];
            cell.Formula = "=SUM(A1:A3)";

            //Calculating the results of formulas
            book.CalculateFormula();

            //Saving the Excel file
            book.Save("AsposeFormulaEngine.xls");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            new Aspose.Cells.License().SetLicense(new MemoryStream(Resource1.Aspose_Cells));
            Workbook workbook = new Workbook(textBox1.Text);

            string conn = ConfigurationManager.ConnectionStrings["GrReporting"].ConnectionString;
            SqlConnection sqlConn = new SqlConnection(conn);
            sqlConn.Open();
            SqlCommand sqlComm = sqlConn.CreateCommand();

            try
            {
                Handler handler = new Handler();
                fillExcle(workbook.Worksheets["Misc Replications"], handler.getMisc(), sqlComm);
                fillExcle(workbook.Worksheets["MRI Replications"], handler.getMri(), sqlComm);
                fillExcle(workbook.Worksheets["Data Export"], handler.getExport(textBox2.Text, textBox3.Text), sqlComm);
                workbook.CalculateFormula();
            }
            finally
            {
                sqlConn.Close();
            }

            workbook.Save(textBox1.Text);
        }

        public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create workbook
            Workbook wb = new Workbook();
            Cells cells = wb.Worksheets[0].Cells;

            // Set formula
            Cell cell = cells[0, 0];
            cell.SetArrayFormula("=MYFUNC()", 2, 2);

            Style style = cell.GetStyle();
            style.Number = 14;
            cell.SetStyle(style);

            // Set calculation options for formula
            CalculationOptions copt = new CalculationOptions();
            copt.CustomFunction = new CustomFunctionStaticValue();
            wb.CalculateFormula(copt);

            // Save to xlsx by setting the calc mode to manual
            wb.Settings.CalcMode = CalcModeType.Manual;
            wb.Save(dataDir + "output_out.xlsx");

            // Save to pdf
            wb.Save(dataDir + "output_out.pdf");
            // ExEnd:1
        }

        public static void Run()
        {
            // ExStart:1
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

          

            // Load the template workbook
            Workbook workbook = new Workbook(dataDir + "book1.xls");

            // Print the time before formula calculation
            Console.WriteLine(DateTime.Now);

            // Set the CreateCalcChain as false
            workbook.Settings.CreateCalcChain = false;

            // Calculate the workbook formulas
            workbook.CalculateFormula();

            // Print the time after formula calculation
            Console.WriteLine(DateTime.Now);
            // ExEnd:1

        }

 private void Calculate()
 {
     if (postedFile != null && postedFile.ContentLength > 0)
     {
         var wbkMain = new Workbook(postedFile.InputStream);
         wbkMain.CalculateFormula();
     }
 }

        public static void SaveAs(Workbook workbook, string outputPath)
        {
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(outputPath);

            if (!System.IO.Directory.Exists(fileInfo.DirectoryName))
                System.IO.Directory.CreateDirectory(fileInfo.DirectoryName);

            workbook.CalculateFormula(true);

            workbook.Save(outputPath);
        }

        public static void Run()
        {
            // Create an instance of Workbook
            Workbook workbook = new Workbook();

            // Access first Worksheet from the collection
            Worksheet sheet = workbook.Worksheets[0];

            // Access Cell A1 and put a formula to sum values of B1 to B2
            Cell a1 = sheet.Cells["A1"];
            a1.Formula = "=Sum(B1:B2)";

            // Assign values to cells B1 & B2
            sheet.Cells["B1"].PutValue(10);
            sheet.Cells["B2"].PutValue(10);

            // Calculate all formulas in the Workbook 
            workbook.CalculateFormula();

            // The result of A1 should be 20 as per default calculation engine
            Console.WriteLine("The value of A1 with default calculation engine: " + a1.StringValue);

            // Create an instance of CustomEngine
            CustomEngine engine = new CustomEngine();

            // Create an instance of CalculationOptions
            CalculationOptions opts = new CalculationOptions();

            // Assign the CalculationOptions.CustomEngine property to the instance of CustomEngine
            opts.CustomEngine = engine;

            // Recalculate all formulas in Workbook using the custom calculation engine
            workbook.CalculateFormula(opts);

            // The result of A1 will be 50 as per custom calculation engine
            Console.WriteLine("The value of A1 with custom calculation engine: " + a1.StringValue);

            Console.WriteLine("Press any key to continue...");
            Console.ReadKey();
        }

    public void CreateStaticReport()
    {
        //Open the workbook
        Workbook workbook = new Workbook();

        //Obtaining the reference of the first worksheet
        Worksheet worksheet = workbook.Worksheets[0];

        //Adding a sample value to "A1" cell
        worksheet.Cells["B1"].PutValue(5);

        //Adding a sample value to "A2" cell
        worksheet.Cells["C1"].PutValue(100);

        //Adding a sample value to "A3" cell
        worksheet.Cells["C2"].PutValue(150);

        //Adding a sample value to "B1" cell
        worksheet.Cells["C3"].PutValue(60);

        //Adding a sample value to "B2" cell
        worksheet.Cells["C4"].PutValue(32);

        //Adding a sample value to "B2" cell
        worksheet.Cells["C5"].PutValue(62);

        //Adding custom formula to Cell A1
        workbook.Worksheets[0].Cells["A1"].Formula = "=MyFunc(B1,C1:C5)";

        //Calcualting Formulas
        workbook.CalculateFormula(false, new CustomFunction());

        //Assign resultant value to Cell A1
        workbook.Worksheets[0].Cells["A1"].PutValue(workbook.Worksheets[0].Cells["A1"].Value);

        if (ddlFileVersion.SelectedItem.Value == "XLS")
        {
            ////Save file and send to client browser using selected format
            workbook.Save(HttpContext.Current.Response, "UsingICustomFunction.xls", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Excel97To2003));
        }
        else
        {
            workbook.Save(HttpContext.Current.Response, "UsingICustomFunction.xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions(SaveFormat.Xlsx));
        }

        //end response to avoid unneeded html
        HttpContext.Current.Response.End();
    }

        public static void Run()
        {
            // ExStart:SearchDataUsingOriginalValues
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create workbook object
            Workbook workbook = new Workbook();

            // Access first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            // Add 10 in cell A1 and A2
            worksheet.Cells["A1"].PutValue(10);
            worksheet.Cells["A2"].PutValue(10);

            // Add Sum formula in cell D4 but customize it as ---
            Cell cell = worksheet.Cells["D4"];

            Style style = cell.GetStyle();
            style.Custom = "---";
            cell.SetStyle(style);

            // The result of formula will be 20 but 20 will not be visible because the cell is formated as ---
            cell.Formula = "=Sum(A1:A2)";

            // Calculate the workbook
            workbook.CalculateFormula();

            // Create find options, we will search 20 using original values otherwise 20 will never be found because it is formatted as ---
            FindOptions options = new FindOptions();
            options.LookInType = LookInType.OriginalValues;
            options.LookAtType = LookAtType.EntireContent;

            Cell foundCell = null;
            object obj = 20;

            // Find 20 which is Sum(A1:A2) and formatted as ---
            foundCell = worksheet.Cells.Find(obj, foundCell, options);

            // Print the found cell
            Console.WriteLine(foundCell);

            // Save the workbook
            workbook.Save(dataDir + "output_out.xlsx");
            // ExEnd:SearchDataUsingOriginalValues
        }