Build 3.0.0.88 of xlsgen makes it possible to create what-if tables from scratch in XLS and XLSX files.


What-if tables in xlsgen

What-if tables is a feature in Excel for testing values in a formula.

In the screenshot above, we have a regular formula, computing the Total = Number of items * Price of item, symbolized by the formula in cell C5=C2*C3. For testing out the total for different prices (cell C3), we can enter a number of prices in cells B6:B10, a row below the Total formula, then select a cell range around both the formula and the prices, i.e. B5:C10, go in Excel / Data / Table (or Excel / Data ribbon / What-if analyiss / Data table in Excel 2007 or above) in order to create a What-if table. Creating the what-if table results in values automatically calculated for each price, which is what can be seen in cells C6:C10.

What we have described can be achieved programmatically in xlsgen.

(Java code)

worksheet.putLabel(2,2, "Number of items");
worksheet.putNumber(2,3, 250);
worksheet.putLabel(3,2, "Price");
worksheet.putNumber(3,3, 5);

XlsWhatIfTable table = worksheet.NewWhatIfTable(5,2, 10,3); // B5:C10

table.putFormula("=C2*C3"); // Total

XlsWhatIfTableValues values = table.AddInputValues();

values.SetInputCell(3, 3); // C3 is the value we want to test
values.AddValueFloat(5);
values.AddValueFloat(7);
values.AddValueFloat(9);
values.AddValueFloat(11);
values.AddValueFloat(13);