- Excel Version
- 365
One way to display text in the values area of a pivot table is to use conditional formatting with a custom number format. Unfortunately, Excel scripts currently cannot apply this kind of feature to pivot tables, only to ordinary cells.
On the other hand, good old VBA can do it. So, this is a hybrid project using these two different languages. Here are the main points:
On the other hand, good old VBA can do it. So, this is a hybrid project using these two different languages. Here are the main points:
- It is done with a single click because the VBA code is event driven and will automatically run when the TypeScript code activates the sheet.
- It starts from scratch, generating a normalized table that will be pivot table source data.
- The two numerical limits are written to the worksheet making them available to the VBA code.
- The pivot table is created, and a copy of its contents is placed at another location. This regular range will receive conditional formatting to show text instead of numbers, if a condition is met.
- The VBA code applies conditional formatting to the real pivot table. We can have the whole values area with text, or only part of it.
JavaScript:
interface tbl{tn:string}
interface ptbl extends tbl{lr:ExcelScript.Range}
interface cod { bg: string, sm: string }
function main(workbook: ExcelScript.Workbook) {
let sheet2 = workbook.getWorksheet("sheet2")
const headers=["region","employee","amount","reviewed"]
const reg=["central","east","west","north"]
const emp=["Elaine","Tom","Paula","Greg"]
const rev=["yes","no"] as const
sheet2.getRange("f3:i3").setValues([headers])
let r=sheet2.getRange("f4")
r.setValue(reg[Math.round( Math.random()*3)])
for (let i = 0; i < 25; i++) {
r.getOffsetRange(i, 1).setValue(emp[Math.round(Math.random() * 3)])
r.getOffsetRange(i, 0).setValue(reg[Math.round(Math.random() * 3)])
r.getOffsetRange(i,2).setValue(Math.random()*100)
r.getOffsetRange(i, 3).setValue(rev[Math.round(Math.random() )])
}
const p = workbook.getWorksheet("Pivot");
const f = p.addPivotTable("Pivot2", /* The name of the PivotTable. */
sheet2.getRange("g5").getSurroundingRegion(), p.getRange("e5") /* The location to put the new PivotTable. */);
f.addRowHierarchy(f.getHierarchy("region"));
f.addRowHierarchy(f.getHierarchy("employee"));
f.addDataHierarchy(f.getHierarchy("amount"));
f.addFilterHierarchy(f.getHierarchy("reviewed"));
let cl = f.getLayout().getColumnLabelRange();
let s=f.getLayout().getRange()
let tl=p.getRange("z5")
tl.getAbsoluteResizedRange(30, 5).clear(ExcelScript.ClearApplyTo.all)
tl.copyFrom(s)
tl.getSurroundingRegion().getFormat().autofitColumns()
const pi={tn:f.getId(),lr:f.getLayout().getFilterAxisRange()} as ptbl
if(infot(pi)){console.log(pi.lr.getAddress())}
let dr = f.getLayout().getBodyAndTotalRange();
let arr:number[]=new Array()
reg.push("Grand Total" as const)
let hc=f.getRowHierarchies()
hc.forEach((v,i)=>{
console.log(i+") "+ v.getName())
})
let rt=tl.getSurroundingRegion()
let nc=rt.getColumn(1)
nc.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
tl.setRowHidden(true)
for (let i=0;i<reg.length;i++){
let res=rt.find(reg[i], { completeMatch: true });
res.setRowHidden(true)
arr.push(res.getRowIndex()+1)
}
const fc = nc.getSpecialCells(ExcelScript.SpecialCellType.visible);
let lim={bg:"150",sm:"80"} as cod
let ar=fc.getAreas()
let bign=glim(lim,"bg")
let sml=glim(lim,"sm")
p.getRange("a1").setValue(bign)
p.getRange("a2").setValue(sml)
for(let i=0;i<fc.getAreaCount();i++){
CF(ar[i], "lightgreen", ">" + bign, '[> ' + bign + ']"big";General')
CF(ar[i], "orange", "<"+sml, '[< ' + sml + ']"small";General')
}
sheet2.activate()
sleepy(2)
p.activate()
console.log("I am back.")
let ir = p.getRange("e1:f1")
ir.merge();
ir.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
ir.getFormat().getFont().setName("Century")
ir.getFormat().getFont().setSize(16)
}
function infot(v: tbl): v is ptbl {
return "lr" in v
}
function glim(L:cod, k:keyof typeof L ){
return L[k]
}
function sleepy(sec: number): void {
const wu: number = new Date().getTime() + sec * 1000;
while (new Date().getTime() < wu) {
// do nothing
}
}
function CF(r:ExcelScript.Range,clr:string,fp:string,nf:string){
let pos2 = r.addConditionalFormat(ExcelScript.ConditionalFormatType.custom).getCustom();
pos2.getFormat().getFill().setColor(clr);
pos2.getFormat().getFont().setBold(true);
pos2.getRule().setFormula(`=${r.getCell(0, 0).getAddress()}` + fp);
pos2.getFormat().setNumberFormat(nf)
}
VBA Code:
Private Sub Worksheet_Activate()
Dim pv As PivotTable, r, i%, f, big$, small$
big = CStr(Me.[a1])
small = CStr(Me.[a2])
Me.[e3:g100].ClearFormats
Set pv = Me.PivotTables(1)
Set r = pv.DataBodyRange.SpecialCells(xlCellTypeVisible)
pv.DataBodyRange.HorizontalAlignment = xlCenter
For i = 1 To r.Areas.Count
f = Replace(Split(r.Areas(i).Address, ":")(0), "$", "")
r.Areas(i).FormatConditions.Add xlExpression, , "=" & f & ">" & big
r.Areas(i).FormatConditions(1).NumberFormat = "[>" & big & "]""big"";General"
r.Areas(i).FormatConditions(1).Interior.Color = RGB(5, 250, 5)
r.Areas(i).FormatConditions.Add xlExpression, , "=" & f & "<" & small
r.Areas(i).FormatConditions(2).Interior.Color = RGB(225, 160, 40)
r.Areas(i).FormatConditions(2).NumberFormat = "[<" & small & "]""small"";General"
Next
Me.Rows("1:30").EntireRow.Hidden = False
Application.Wait Now + #12:00:01 AM#
End Sub
