Format ListColumn values with custom number format

[hitesh.dholaria]

Hi,

Please refer to the attached workbook (Output_3.xlsx) which is generated by running the below code segment.

listObject.getListColumns().get(1).setTotalsCalculation(TotalsCalculation.AVERAGE);

listObject.getListColumns().get(2).setTotalsCalculation(TotalsCalculation.SUM);

I want to round up cell values in second column (i.e., UnitPrice) to two (or any arbitrary) decimal places. How do I do that?

[amjad.sahi]

Hi,

Thanks for the template file and some details.

Well, you need to apply display numbers formattings for your desired column cells in the worksheet for your requirements. See the document on how to set display numbers formattings (including your desired formatting) for your reference:

http://www.aspose.com/docs/display/cellsjava/Setting+Display+Formats+of+Numbers+and+Dates

Moreover, you may apply numbers formatting to the whole column or row, see the document for your reference (you may add relevant lines to apply your desired numbers formatting for the Style and StyleFlag attributes), see the document for your reference:

http://www.aspose.com/docs/display/cellsjava/Formatting+Rows+and+Columns

Thank you.

[babar.raza]

Hi again,

Please check the following piece of code that uses the styling approach as mentioned by Amajd in above post.

Java

Workbook workbook = new Workbook(dir + “Output_3.xlsx”);
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
ListObject table = sheet.getListObjects().get(0);
Row row = cells.getRows().get(table.getEndRow());
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
Style style = row.get(1).getStyle();
style.setNumber(2);
row.get(1).setStyle(style, flag);
workbook.save(dir + “output.xlsx”);

[hitesh.dholaria]

Will the above code set precision to all the values in that column or just the last last row’s cell? I want to set the same precision to whole column.

[babar.raza]

Hi Hitesh,

Previously shared snippet applies the number formatting to only one cell, that is B11. If you wish to apply the number formatting on all cells of the second column in the ListObject, please try the following piece of code.

Java

Workbook workbook = new Workbook(dir + “Output_3.xlsx”);
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
ListObject table = sheet.getListObjects().get(0);
ListColumn column = table.getListColumns().get(1);
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
Style style = workbook.createStyle();
style.setNumber(2);
column.getRange().applyStyle(style, flag);
workbook.save(dir + “output.xlsx”);