Highlighting Highest value in Column

Aspose.Cells Product Family
1

Hi,

How to highlight the cell with highest value in a Column using Aspose code.

Please provide a code for this.

Can we do this without using Conditional Formatting. If yes, please provide a sample example.

Thanks,

2

Hi,


Well, it is easy to accomplish the task via using Max formula/ function to extract the highest value from a column or a range of cells, see the sample code below. I have used a simple way to do it. I used a simplest template file (attached) and first extract the highest value from a given range in the sheet, then find out the cell that contains the value. And, lastly, I set the background color of that cell to highlight it. I also saved the Excel file.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\bk_highestval.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
//Calculate the highest value in the first column A

//Get the last record/row index.
int maxrow = worksheet.Cells.MaxDataRow +1;
//Write the formula based on your desired range.
var formula1 = @"=MAX(A1:A" + maxrow.ToString() + “)” ;

var result1 = worksheet.CalculateFormula(formula1);
//Show the highest value
MessageBox.Show(result1.ToString());

//Find out the highest value in the column.
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
CellArea area = CellArea.CreateCellArea(0,0,maxrow,0);
opts.SetRange(area);
//Search the cell that contains that value
Cell cell = worksheet.Cells.Find(result1, null, opts);

//Set the background color of that cell to highlight it.
Style style = cell.GetStyle();
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
cell.SetStyle(style);

//Save the excel file.
workbook.Save(“e:\test2\outbk_highestval1.xlsx”);

Hope, this helps you a bit.

Thank you.
3

Please share same code for Java

4

@FizzFrags,

Here you go:
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\bk_highestval.xlsx");
		Worksheet worksheet = workbook.getWorksheets().get(0);
		//Calculate the highest value in the first column A

		//Get the last record/row index.
		int maxrow = worksheet.getCells().getMaxDataRow() +1;
		//Write the formula based on your desired range.
		String formula1 = "=MAX(A1:A" + maxrow + ")" ;

		Object result1 = worksheet.calculateFormula(formula1);
		//Show the highest value
		System.out.println(result1.toString());

		//Find out the highest value in the column.
		FindOptions opts = new FindOptions();
		opts.setLookInType(LookInType.VALUES);
		opts.setLookAtType(LookAtType.ENTIRE_CONTENT);
		CellArea area = CellArea.createCellArea(0,0,maxrow,0);
		opts.setRange(area);
		//Search the cell that contains that value
		Cell cell = worksheet.getCells().find(result1, null, opts);

		//Set the background color of that cell to highlight it.
		Style style = cell.getStyle();
		style.setForegroundColor(Color.getYellow());
		style.setPattern(BackgroundType.SOLID);
		cell.setStyle(style);

		//Save the excel file.
		workbook.save("f:\\files\\outbk_highestval1.xlsx");

Hope, this helps a bit.