logo
down
shadow

In SpreadsheetGear, how do I check for formula or calculation errors?


In SpreadsheetGear, how do I check for formula or calculation errors?

By : amoschou
Date : November 18 2020, 01:01 AM
I think the issue was by ths following , By "error" I presume you mean an Excel formula error value, such as #NUM!, #VALUE, #NAME?, etc? Unfortunately, there is no property you can check to determine whether a workbook contains one or more of these after a recalculation. About the only way to do this would be to manually iterate through any relevant cells in the workbook yourself and check for a ValueType of Error. For a large and complex workbook, I understand this might not be feasible.
Regardless, for such an approach my best suggestion would be, if possible, to limit this error checking to a smaller range or set of ranges that you are interested in. Secondly, you might consider using the "high performance" API under the SpreadsheetGear.Advanced.Cells namespace to squeak out a little better performance for this routine, versus using the traditional IRange interface, which would likely perform a bit slower. Below is an example that demonstrates this sort of approach. Feel free to modify as needed for your requirements:
code :
using SpreadsheetGear.Advanced.Cells;
...
private bool RangeContainsErrors(IRange range)
{
    // We obtain a "high performance" IValues object by casting a worksheet
    // to IValues.
    IValues values = (IValues)range.Worksheet;

    // Setup some indexes to help clarify and shorten the for loops.
    int startRow = range.Row;
    int startCol = range.Column;
    int endRow = startRow + range.RowCount;
    int endCol = startCol + range.ColumnCount;

    // Loop through rows of desired range.
    for (int row = startRow; row < endRow; row++)
    {
        // Now loop through columns of desired range.
        for (int col = startCol; col < endCol; col++)
        {
            // Get an IValue object for this cell.
            IValue val = values[row, col];

            // Check to ensure this cell even as a value and if so, check
            // for an Error value type.
            if(val != null && 
               val.Type == SpreadsheetGear.Advanced.Cells.ValueType.Error)
            {
                return true;
            }
        }
    }

    // If we made it this far there weren't any errors.
    return false;
}


Share : facebook icon twitter icon
Reference another sheet in Formula string SpreadSheetGear

Reference another sheet in Formula string SpreadSheetGear


By : Ricky Leone
Date : March 29 2020, 07:55 AM
will be helpful for those in need Find the answer, actually, the formula is OK, I was generating the cell ranges with some manual, over the top code, and all that I needed was to put
code :
MySheet.Cells[add ranges here].Range.ToString();
Excel formula is displayed as text upon exporting using SpreadsheetGear

Excel formula is displayed as text upon exporting using SpreadsheetGear


By : Lily
Date : March 29 2020, 07:55 AM
seems to work fine It would help if you provided the SpreadsheetGear-related routine to see if there are any issues in that code.
Since you mention using DataTables I am guessing you are using the IRange.CopyFromDataTable(...) method. Given that formulas are being treated as text, I will also guess that you are passing in the SetDataFlags.AllText option.
Spreadsheetgear replace formula with it's result

Spreadsheetgear replace formula with it's result


By : Nick
Date : March 29 2020, 07:55 AM
I hope this helps you . This could be accomplished a couple different ways:
Set IRange.Value to itself for the desired range. IRange.Formula contains a cell's formula and IRange.Value the calculated value. Setting IRange.Value to itself will effectively remove the formula from the cell and leave only the value. This will work on a single cell and also on a multi-cell range (since IRange.Value can return a object[,] array and can also be set from an object[,] array). Use IRange.Copy(...) method with the PasteType.Values option on the desired range, which is the equivalent of doing a Paste Special > Values in Microsoft Excel.
code :
using SpreadsheetGear;
...

// Create workbook and some local variables.
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.ActiveWorksheet;
IRange cells = worksheet.Cells;

// EXAMPLE 1 - IRange.Value with single cell
// Set A1 to a formula and output some info about A1's contents.
IRange a1 = cells["A1"];
a1.Formula = "=ROUND(RAND(),2)";
Console.WriteLine($"{a1.Address}: HasFormula={a1.HasFormula}, Formula='{a1.Formula}', Value={a1.Value}");
// OUTPUT: $A$1: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.69
// Set IRange.Value to itself
a1.Value = a1.Value;
Console.WriteLine($"{a1.Address}: HasFormula={a1.HasFormula}, Formula='{a1.Formula}', Value={a1.Value}");
// OUTPUT: $A$1: HasFormula=False, Formula='0.69', Value=0.69


// EXAMPLE 2 - IRange.Value with multiple cells
// Works on multiple cells since IRange.Value will return an object[,]
// array for a range representing multiple cells.
IRange b1b3 = cells["B1:B3"];
b1b3.Formula = "=ROUND(RAND(),2)";
foreach (IRange cell in b1b3)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $B$1: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.81
// $B$2: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.55
// $B$3: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.24

// Set IRange.Value to itself (2D object[,] used)
b1b3.Value = b1b3.Value;
foreach (IRange cell in b1b3)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $B$1: HasFormula=False, Formula='0.81', Value=0.81
// $B$2: HasFormula=False, Formula='0.55', Value=0.55
// $B$3: HasFormula=False, Formula='0.24', Value=0.24


// EXAMPLE 3 - IRange.Copy() with PasteType.Values option
IRange c1c2 = cells["C1:C2"];
c1c2.Formula = "=ROUND(RAND(),2)";
foreach (IRange cell in c1c2)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $C$1: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.04
// $C$2: HasFormula=True, Formula='=ROUND(RAND(),2)', Value=0.14

c1c2.Copy(c1c2, PasteType.Values, PasteOperation.None, false, false);
foreach (IRange cell in c1c2)
    Console.WriteLine($"{cell.Address}: HasFormula={cell.HasFormula}, Formula='{cell.Formula}', Value={cell.Value}");
// OUTPUT:
// $C$1: HasFormula=False, Formula='0.04', Value=0.04
// $C$2: HasFormula=False, Formula='0.14', Value=0.14
C# SpreadsheetGear : Fetch calculated value of a formula

C# SpreadsheetGear : Fetch calculated value of a formula


By : Cotfas Vlad Nicolae
Date : March 29 2020, 07:55 AM
it helps some times You are running into a known limitation. SpreadsheetGear does not yet support "Excel Tables" (Excel’s Ribbon > Home > Styles > Format as Table...). These Table objects are dropped upon reading the file. This has the consequence of also dropping any formulas that use "structured references" (i.e., in your case the [@[Customer CID]] reference in your formula. Such references will be converted to #REF! errors.
We do have a feature request item to add support for Excel Tables (I work for SpreadsheetGear). You are welcome to contact us at support@spreadsheetgear.com so that I can add you to our request list for this feature, although I cannot provide any sort of timeline for when this might become available.
SpreadsheetGear 2012 - 2017 Upgrade - SpreadsheetGear.data no longer valid

SpreadsheetGear 2012 - 2017 Upgrade - SpreadsheetGear.data no longer valid


By : Mahmood Malik
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , The problem is that you are comparing two entirely different products. There are two primary products offered by SpreadsheetGear (if you don't count the Silverlight product, which as we all know Silverlight is basically a dead-end technology):
SpreadsheetGear for .NET - this product targets the full .NET Framework and is distributed as installers on the SpreadsheetGear website's Licensed User Downloads page. SpreadsheetGear for .NET Standard - this product is distributed on NuGet and currently targets .NET Standard 1.3. .NET Standard 1.3 did not support DataTables or DataSets and therefore SpreadsheetGear for .NET Standard could not, either, so is why you are not seeing these API's in this product. There are some other differences between these two products, all of which are pointed out on SpreadsheetGear's Comparison Page.
Related Posts Related Posts :
  • Decimal output parameter rounded to integer in EF5.0
  • Error when deploying ear in weblogic 11g
  • autocomplete with extjs :ComboBox with REMOTE query store
  • How to make full background color in skeleton, responsive 960 grid?
  • How to send a variable of type struct in MPI_Send()?
  • I accidentally deleted a picture in my album on FB and it was uploaded years ago and I dont have a copy how can i get it
  • Does setting techOrder for video.js really work?
  • What are XMPP extensions (XEPs), and how do I use them?
  • Interactive alternative to dot?
  • Doctrine 2: how do you use a subquery column (in the SELECT clause)
  • Merging graphs using boost graph
  • Creating Order Item Grid in Magento
  • Can't remap Ctrl+W to the F2 key in autohotkey
  • Property 'throw' does not exist on type 'typeof Observable'
  • Unable to deploy SAPUI5 application with 404 error
  • Xcode 10 build fail rm permission denied in /bin/sh/
  • Handling quorum writies fail on Cassandra
  • How to finalize log file just after time is over when using logback SizeAndTimeBasedFNATP?
  • Write custom widget with GTK3
  • How to remove UINavigationBar inner shadow in iOS 7?
  • Wordpress dynamic widget by location?
  • XDocument replace all node value with lower case
  • Invalid tagdir attribute while web-fragment is used
  • Set default font/text size in RTF Control
  • what video formats supported by chromecast device (mp4,flv,m3u8,flv,avi)?
  • sbcl - how to muffle "undefined variable" warning?
  • Get an eventbrite event ID
  • Obtain date without timestamp in DB2
  • Cron job to SFTP files in a directory
  • Draw two head arrows in fabric.js
  • YOLO darknet vs darkflow
  • Parse custom rss tags using Rome API
  • Creating text editor like EDIT on Command Prompt using FreePascal
  • Subplot hides xlabel
  • NServiceBus long running process that timesout
  • Qt4 - How to add a row in a QTableWidget when sorting is enable?
  • Cancel command in Grunt that hasn't been launched
  • Canvas globalCompositeOperation is not working correctly
  • HTML.Kendo().Dropdownlist set default item
  • vagrant up failed, /dev/vboxnetctl: no such file or directory
  • How to use jssor carousel to build client carousal
  • Theory what is meant by validating security concerns for these user groups
  • How to build correct SPARQL Query
  • Connecting to MySQL (on Google Cloud SQL) via JDBC and IPv6?
  • Fetching transaction in several iterations
  • is it possible to use dropbox datastore api as shared datastore?
  • phpExcel 1.8 Named Ranges in Formula
  • Cisco VPN IP address
  • No address associated with hostname using vagrant
  • 2 way data-binding between a polymer component and a model?
  • How can I rename an already published app in the Windows Dev Center?
  • Using Chef 12 , Chef Client unable to connect to chef Server
  • GCM notification not working (GoogleCloudMessaging)
  • Measured GFLOPS is greater then theoretical GFLOPS
  • Progress ABL - strip and add to temp table
  • Errors occuring when make-ing Aircrack-ng on raspberry pi
  • Detecting Handedness from Device Use
  • Query a manual list of data items
  • Similar to pivot - Windows 8.1 Desktop app
  • CKEDITOR And Styles configuration with only toolbar
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org