Spreadsheet - Define formatting on whole column

[Daniel]

Hello guys,

is it possible to define a conditional formatting on a whole column? E.g. if you want to set the background of a cell to yellow if the value is greater than 0 (see screenshot, column 'Fehlend').

In my case it's even more difficult because my whole datasource is coming from the server.

@(Html.Kendo().Spreadsheet()
	.Name("bestandsliste")
	.Columns(3)
	.HtmlAttributes(new { style = "width:100%" })
	.Sheets(x =>
	{
		x.Add()
			.Name("Bestandsliste")
			.Columns(y =>
			{
				y.Add().Width(400);
				y.Add().Width(120);
				y.Add().Width(120);
				y.Add().Width(120);
			})
			.DataSource<Fahnenbestand>(y => y
				.Custom()
				.Sort(z => z.Add(a => a.Fahnenkategorie))
				.Transport(z => z.Read("spreadsheet_onRead"))
			)
			.FrozenRows(1)
			.Rows(y =>
			{
				y.Add().Height(30).Cells(z =>
				{
					z.Add().FontSize(20).TextAlign(SpreadsheetTextAlign.Center);
					z.Add().FontSize(20).TextAlign(SpreadsheetTextAlign.Center);
					z.Add().FontSize(20).TextAlign(SpreadsheetTextAlign.Center);
					z.Add().FontSize(20).TextAlign(SpreadsheetTextAlign.Center);
				});
			});
	})
	.Sheetsbar(false)
	.Toolbar(false)
)

function spreadsheet_onRead(options) {
	$.ajax({
		url: `@ViewData[ViewDataVariables.UrlWebApi]/FlagCategory/GetOverviewAllFlagsAsSpreadsheet`,
		headers: { 'Authorization': '@token' },
		success: function (result) {
			var sheet = kendoSpreadSheetBestandsliste.activeSheet();
			sheet.resize(result.Total + 5, 3);

			options.success(result.Data);
		},
		error: function (result) {
			options.error(result);
		}
	});
}

I already check the css of the cells. Indeed I can simply set the background color of the cells of a specific column by a condition via jquery (like "if value greater 0, set background yellow"), but the cells carry very little informations. It's not possible to prove if the cell I'm currently looking is belonging to correct column. It would be easy if there would be an attribute like aria-labeledby="COLUMNHEADERCELL" but that's not the case.

<div class="k-spreadsheet-cell" style="[..]"><div class="k-vertical-align-bottom">1</div></div>

Maybe there is a solution to get all cells of one column via the api of my spreadsheet object? Looking forward to hearing from you.

Best regards,

Daniel

[Daniel]

Found a workaround I'm happy with.

So instead to try defining formatting for a column, I'm now dynamically creating all rows by myself and can programmatically prove if a certain condition is true. If so, I'm programmatically set the background color. 

For that solution I'm not using the datasource binding anymore but the direct binding to a list of sheets.

public async Task<IActionResult> Index()
{
	List<Fahnenbestand> bestaende = await response.Content.ReadFromJsonAsync<List<Fahnenbestand>>();
			
	SpreadsheetSheet sheet = new SpreadsheetSheet()
	{
		Columns = new List<SpreadsheetSheetColumn>() 
		{ 
			new SpreadsheetSheetColumn() { Width = 350 },
			new SpreadsheetSheetColumn() { Width = 120 },
			new SpreadsheetSheetColumn() { Width = 120 },
			new SpreadsheetSheetColumn() { Width = 120 }
		},
		Rows = new List<SpreadsheetSheetRow>()
		{
			new SpreadsheetSheetRow() // Header
			{
				Cells = new List<SpreadsheetSheetRowCell>()
				{
					new SpreadsheetSheetRowCell() 
					{ 
						Background = "#e4e7eb", 
						FontSize = 30, 
						TextAlign = SpreadsheetTextAlign.Center, 
						VerticalAlign = SpreadsheetVerticalAlign.Center, 
						Value = "Bestandsliste" 
					}
				},
				Height = 50
			},
			new SpreadsheetSheetRow() // Column titles
			{
				Cells = new List<SpreadsheetSheetRowCell>()
				{
					new SpreadsheetSheetRowCell() { FontSize = 20, TextAlign = SpreadsheetTextAlign.Center, Value = "Fahnenkategorie" },
					new SpreadsheetSheetRowCell() { FontSize = 20, TextAlign = SpreadsheetTextAlign.Center, Value = "Istbestand" },
					new SpreadsheetSheetRowCell() { FontSize = 20, TextAlign = SpreadsheetTextAlign.Center, Value = "Sollbestand" },
					new SpreadsheetSheetRowCell() { FontSize = 20, TextAlign = SpreadsheetTextAlign.Center, Value = "Fehlend" }
				},
				Height = 30
			}
		}
	};

	foreach (Fahnenbestand bestand in bestaende)
	{
		SpreadsheetSheetRow row = new SpreadsheetSheetRow();

		row.Cells.Add(new SpreadsheetSheetRowCell() { Value = bestand.Category });
		row.Cells.Add(new SpreadsheetSheetRowCell() { Value = bestand.Istbestand });
		row.Cells.Add(new SpreadsheetSheetRowCell() { Value = bestand.Sollbestand });

		SpreadsheetSheetRowCell cellMissing = new SpreadsheetSheetRowCell() { Value = bestand.Sollbestand - bestand.Istbestand };
		int.TryParse(cellMissing.Value.ToString(), out int cellValue);
		if (cellValue > 0)
		{
			cellMissing.Background = "#ffc107";
		}
		row.Cells.Add(cellMissing);

		sheet.Rows.Add(row);
	}

	ViewData["Bestand"] = new List<SpreadsheetSheet>() { sheet };
	return View();
}

@(Html.Kendo().Spreadsheet()
	.Name("bestandsliste")
	.BindTo((IEnumerable<SpreadsheetSheet>)ViewData["Bestand"])
	.Columns(3)
	.Excel(x => x.FileName($"Fahnenlager Bestandsliste {DateTime.Now:yyyyMMddTHHmmss}.xlsx"))
	.HtmlAttributes(new { style = "width:100%" })
	.Pdf(x =>
	{
		x.FileName($"Fahnenlager Bestandsliste {DateTime.Now:yyyyMMddTHHmmss}.pdf");
		x.FitWidth(true);
		x.HCenter(true);
		x.Landscape(false);
		x.PaperSize("A4");
		x.Margin(y => y.Left(20).Top(20).Right(20).Bottom(20));
	})
	.Rows(((IEnumerable<SpreadsheetSheet>)ViewData[ViewDataVariables.Bestandsliste]).Count())
	.Sheetsbar(false)
	.Toolbar(false)
)

[Aleksandar]

Hi Daniel,

Data Source binding switches the sheet to a special data-bound mode where cell styles, formulas, and formats are not persisted in the data source. So the second approach would be the one I would have suggested.