Set Default Values for Cell Range

When creating Excel documents or reports such as finance documents, sales reports, invoices, student status reports, forms, and others, it is often required to have cells with pre-filled text or fixed data rather than empty or blank cells that can be used in further processing and calculations.

The default value can help in such a scenario and provides a value or formula to be displayed and used in calculations like a normal cell value. DsExcel provides setDefaultValue method in IRange interface that allows you to set the default value of a cell to avoid empty cell scenarios.

The default value has certain characteristics that are listed below:

• You can set the value and formula through the default value. The string starting with "=" will be considered a formula.
• If the cell is empty, its default value or formula will participate in recalculation.
• The logic behind the default value formula adjustment is the same as the cell formula.
• When exporting Excel, DsExcel exports the default value as a cell value when the cell is empty. If the default value is a formula, DsExcel discards it and only keeps the calculation result.
• You can clear the default value by setting null to the default value.
• clear and unMerge methods do not affect default values. The default values are filled after executing these methods.

Refer to the following example code to set the default value of cells:

// Initialize workbook.
var workbook = new Workbook();

// Open defaultValue.xlsx.
workbook.open("defaultValue.xlsx");
var worksheet = workbook.getActiveSheet();
            
// Set default value for standard reduction.
worksheet.getRange("C4:C8").setDefaultValue("=B4 - B4*0.12");

// Set normal value for specific percent reduction.
worksheet.getRange("C6").setFormula("=B6 - B6*0.08");
worksheet.getRange("C8").setFormula("=B8 - B8*0.05");

// Calculate total on default value and specific values.
worksheet.getRange("C9").setFormula("=SUM(C4:C8)");

// Save to an Excel file.
workbook.save("DefaultValueOutput.xlsx");

Limitations

• You cannot set the default value for the entire row or column.
• DsExcel does not support the dynamic array when defaultValue sets a formula, so the cell default value will follow the implicit intersection policy when it is a reference and will get the top-left cell value when it is a dynamic array.
• You cannot copy the default value.