Disable Excel Error Checking through Coldfusion

Question

I enforced the number having leading zero to the text . It is working well but it is showing green icon on the corner on clicking it displays the error . My question is that how i can stop or disable this button not to show on the excel sheet programmatically.

In excel sheet you can easily disable error checking but i want this through code

Any Help will be appreciated

Thanks!

if you can control the excel object model through `coldfusion` you can use the [vba syntax](http://stackoverflow.com/questions/4754108/turn-off-excel-background-error-checking-on-opening-the-workbook) to replicate the right syntax in `coldfusion` — Scott Holtzman, Apr 07 '16 at 17:56
Agreed. From what I have read, error checking is a global setting in Excel. AFAIK, modifying the behavior is not currently supported in POI (underlying java library). Without an external tool, the closest you can get is to treat the cell type as numeric and apply a format to generate zero padding `SpreadSheetFormatCell(sheet, {dataFormat="00000"}, row, column)`. — Leigh, Apr 07 '16 at 18:17
I stand corrected. The feature was added in POI 3.14. See answer below. — Leigh, Apr 07 '16 at 20:37

score 2 · Answer 1 · edited May 23 '17 at 12:24

Short answer:

The ability to suppress Excel's error checking on a cell level is supported in POI 3.14+. See below details. The other option is to leave the cells as numeric, but apply a mask with leading zeroes, ie: SpreadSheetFormatCell(sheet, {dataFormat="00000"}, row, column).

Longer answer:

After a bit of reading, it turns out the ability to suppress this error was added in POI 3.14. Unfortunately, CF11 is bundled with an older version, 3.9. However, you can access the newer functionality by loading POI 3.14 (or later) using this.javaSettings in your Application.cfc, along with a bit of java code.

Start by creating the spreadsheet as usual:

// format a few cells as text, then add values with leading zero
cfSheet = SpreadSheetNew("Test", true);
SpreadSheetFormatCell(cfSheet, {dataFormat="@"}, 1, 1);
SpreadSheetSetCellValue(cfSheet, "01234", 1, 1);
SpreadSheetFormatCell(cfSheet, {dataFormat="@"}, 1, 2);
SpreadSheetSetCellValue(cfSheet, "05678", 1, 2);

In order to manipulate the spreadsheet with the newer classes, it must be read into a new Workbook object using the POI WorkbookFactory. The factory supports loading from both a physical file OR a stream of bytes. (For brevity, I will use bytes).

// load workbook from byte array
bytes = SpreadsheetReadBinary( cfSheet );
stream = createObject("java", "java.io.ByteArrayInputStream").init(bytes);
factory = createObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory");
wb = factory.create(stream);
// grab reference to our worksheet
jSheet = wb.getSheet("Test");

Next create a range object representing the cells where you want to suppress the "number as text" error. Then add the range to the list of ignored errors in the sheet.

// Create range of cells containing numbers as text
CellRange = createObject("java", "org.apache.poi.ss.util.CellRangeAddress");
rangeToModify = CellRange.valueOf("A1:A2");
// Flag the range to ignore "number as text" error
IgnoreTypes = createObject("java", "org.apache.poi.ss.usermodel.IgnoredErrorType");
jSheet.addIgnoredErrors(rangeToModify, [ IgnoreTypes.NUMBER_STORED_AS_TEXT] );

Finally, save the updated workbook to a file:

// Save to disk
fos = createObject("java", "java.io.FileOutputStream").init("c:/path/uniqueName.xlsx");
wb.write(fos);
fos.close();

Disable Excel Error Checking through Coldfusion

1 Answers1

Linked