// Example for COUNTIF function from
// https://exceljet.net/functions/countif-function
//
// Extended for case-sensitive comparison
program countif_casesensitive_test;
uses
fpspreadsheet, fpstypes, xlsBiff8, xlsxooxml, fpsopendocument;
const
w: array[0..5] of Integer = (8, 8, 12, 2, 22, 8);
var
workbook: TsWorkbook;
worksheet: TsWorksheet;
r, c: Cardinal;
begin
workbook := TsWorkbook.Create;
try
workbook.Options := workbook.Options + [boCalcBeforeSaving];
worksheet := workbook.AddWorksheet('Test');
worksheet.WriteText(0, 0, 'COUNTIF(range, criteria)');
worksheet.WriteText(2, 0, 'Name');
worksheet.WriteText(2, 1, 'State');
worksheet.WriteText(2, 2, 'Sales');
// Text to be compared with in case-sensitive COUNTIF, in cell K1
worksheet.WriteText(2, 10, 'jim');
worksheet.WriteText(3, 0, 'Jim');
worksheet.WriteText(3, 1, 'MN');
worksheet.WriteCurrency(3, 2, 100.0, nfCurrency, 2, '$');
worksheet.WriteFormula(3, 10, 'EXACT(A4,$K$1)');
worksheet.WriteText(4, 0, 'Sarah');
worksheet.WriteText(4, 1, 'CA');
worksheet.WriteCurrency(4, 2, 125.0, nfCurrency, 2, '$');
worksheet.WriteFormula(4, 10, 'EXACT(A5,$K$1)');
worksheet.WriteText(5, 0, 'Jane');
worksheet.WriteText(5, 1, 'GA');
worksheet.WriteCurrency(5, 2, 200.0, nfCurrency, 2, '$');
worksheet.WriteFormula(5, 10, 'EXACT(A6,$K$1)');
worksheet.WriteText(6, 0, 'Steve');
worksheet.WriteText(6, 1, 'CA');
worksheet.WriteCurrency(6, 2, 50.0, nfCurrency, 2, '$');
worksheet.WriteFormula(6, 10, 'EXACT(A7,$K$1)');
worksheet.WriteText(7, 0, 'Jim');
worksheet.WriteText(7, 1, 'WY');
worksheet.WriteCurrency(7, 2, 75.0, nfCurrency, 2, '$');
worksheet.WriteFormula(7, 10, 'EXACT(A8,$K$1)');
worksheet.WriteText(8, 0, 'Joan');
worksheet.WriteText(8, 1, 'WA');
worksheet.WriteCurrency(8, 2, 150.0, nfCurrency, 2, '$');
worksheet.WriteFormula(8, 10, 'EXACT(A9,$K$1)');
worksheet.WriteText(9, 0, 'Jane');
worksheet.WriteText(9, 1, 'GA');
worksheet.WriteCurrency(9, 2, 200.0, nfCurrency, 2, '$');
worksheet.WriteFormula(9, 10, 'EXACT(A10,$K$1)');
worksheet.WriteText(10, 0, 'Jim');
worksheet.WriteText(10, 1, 'WY');
worksheet.WriteCurrency(10, 2, 50.0, nfCurrency, 2, '$');
worksheet.WriteFormula(10, 10, 'EXACT(A11,$K$1)');
worksheet.WriteText(2, 4, 'Example');
worksheet.WriteText(2, 5, 'Result');
worksheet.WriteText(3, 4, 'Sales over $100');
worksheet.WriteFormula(3, 5, 'COUNTIF(C4:C11,">100")');
worksheet.WriteText(4, 4, 'Sales by ' + worksheet.ReadAsText(2,10));
// Compare the result column in which we have the results of the EXACT() function
worksheet.WriteFormula(4, 5, 'COUNTIF(K4:K11, TRUE)');
worksheet.WriteText(5, 4, 'Sales in California');
worksheet.WriteFormula(5, 5, 'COUNTIF(B4:B11, "CA")');
// Hide the auxiliary column
worksheet.HideCol(10);
workbook.WriteToFile('countif_test.xls', sfExcel8, true);
workbook.WriteToFile('countif_test.xlsx', sfOOXML, true);
workbook.WriteToFile('countif_test.ods', sfOpenDocument, true);
for r := 0 to 10 do
begin
for c := 0 to 5 do
Write(worksheet.ReadAsText(r, c):w[c]);
WriteLn;
end;
WriteLn;
finally
workbook.Free;
end;
Write('Press ENTER to close...');
ReadLn;
end.