 | Microsoft Excel has some special settings/options, one of them is error checking options/rules. You might have noticed the error checks while you create formulas in Microsoft Excel, you sometimes get a small green triangle displayed in the the top left corner of a cell.
The little green triangle tells you that Excel is checking for common/general errors and provides you with the options of correcting them. |
Types of Errors
Errors that cannot return a result (such as dividing a number by zero) require attention and display an error value in the cell. By clicking on the green triangle you will get an exclamation mark, clicking on this, a pop up list of available options are displayed. You can resolve an error by using the options that are displayed, or you can ignore the error by clicking the Ignore Error. If you ignore an error in a particular cell, the error in that cell does not appear in further error checks. Aspose.Cells for Java does provide the feature of Error Checking Options/Rules. It has ErrorCheckOptions class to manage different types of errors checks, e.g., Numbers stored as text, Formula calculation errors and Validation errors etc. You may use ErrorCheckType constants to set your desired errors' checking.
Errors that cannot return a result (such as dividing a number by zero) require attention and display an error value in the cell. By clicking on the green triangle you will get an exclamation mark, clicking on this, a pop up list of available options are displayed. You can resolve an error by using the options that are displayed, or you can ignore the error by clicking the Ignore Error. If you ignore an error in a particular cell, the error in that cell does not appear in further error checks. Aspose.Cells for Java does provide the feature of Error Checking Options/Rules. It has ErrorCheckOptions class to manage different types of errors checks, e.g., Numbers stored as text, Formula calculation errors and Validation errors etc. You may use ErrorCheckType constants to set your desired errors' checking.
Numbers Stored as Text
Occasionally, numbers might be formatted and stored in cells as text, which later can cause problems with calculations or produce confusing sort orders. For example, you might have typed a number in a cell that was formatted as text, or the data may have been imported or copied as text from an external data source. An important thing to remember and check is that numbers that are formatted as text are left-aligned instead of right-aligned in the cell. If a formula that should perform a math ematic operation on cells doesn't return a value, check the alignment in the cells that the formula refers to – some or all of those cells might be numbers formatted as text.You can use the Error Checking Options to quickly convert numbers stored as text to numbers too.In MS Excel 2003, on the Tools menu, click Options , and then click the Error Checking tab.Now, you will see the related option, e.g Number stored as text box as checked (by default) . You may disable it for your need. See the below picture on how the green triangle is displayed for the data in MS Excel.
Please see the following Sample Code on how to disable the “Numbers Stored as Text” error checking option for a worksheet in the template XLS file using the Aspose.Cells APIs.
Occasionally, numbers might be formatted and stored in cells as text, which later can cause problems with calculations or produce confusing sort orders. For example, you might have typed a number in a cell that was formatted as text, or the data may have been imported or copied as text from an external data source. An important thing to remember and check is that numbers that are formatted as text are left-aligned instead of right-aligned in the cell. If a formula that should perform a math ematic operation on cells doesn't return a value, check the alignment in the cells that the formula refers to – some or all of those cells might be numbers formatted as text.You can use the Error Checking Options to quickly convert numbers stored as text to numbers too.In MS Excel 2003, on the Tools menu, click Options , and then click the Error Checking tab.Now, you will see the related option, e.g Number stored as text box as checked (by default) . You may disable it for your need. See the below picture on how the green triangle is displayed for the data in MS Excel.
Please see the following Sample Code on how to disable the “Numbers Stored as Text” error checking option for a worksheet in the template XLS file using the Aspose.Cells APIs.
Example:
[Java]
//Create a workbook and opening a template spreadsheet
Workbook workbook = new Workbook("d:\\files\\Book1.xls");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Instantiate the error checking options
ErrorCheckOptionCollection opts = sheet.getErrorCheckOptions();
int index = opts.add();
ErrorCheckOption opt = opts.get(index);
//Disable the numbers stored as text option
opt.setErrorCheck(ErrorCheckType.TEXT_NUMBER, false);
//Set the range
opt.addRange(CellArea.creatCellArea(0, 0, 65535, 255));
//Save the Excel file
workbook.save("d:\\files\\out_test.xls");
//Create a workbook and opening a template spreadsheet Workbook workbook = new Workbook("d:\\files\\Book1.xls"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Instantiate the error checking options ErrorCheckOptionCollection opts = sheet.getErrorCheckOptions(); int index = opts.add(); ErrorCheckOption opt = opts.get(index); //Disable the numbers stored as text option opt.setErrorCheck(ErrorCheckType.TEXT_NUMBER, false); //Set the range opt.addRange(CellArea.creatCellArea(0, 0, 65535, 255)); //Save the Excel file workbook.save("d:\\files\\out_test.xls");