I want to apply specific data format and style to a single column from an Excel Flat Table. Is such feature exist ?
This is the way I thought it would be possible. If you know any alternative, please let me know :
public void setDataFormatFlatTableField(String sheetName, String flatTableName, String fieldName, String dataFormat, int R, int G, int B) {
try {
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(sheetName);
ListObject flatTable = sheet.getListObjects().get(flatTableName);
ListColumn fieldColumn = flatTable.getListColumns().get(fieldName);
//fieldColumn.setDataFormat(dataFormat);//??
//Style style = fieldColumn.getStyle();//??
//style.setPattern(BackgroundType.SOLID);//??
//style.setBackgroundColor(Color.fromArgb(R, G, B));//??
} catch(Exception e) {
String parameters = " sheetName : "+sheetName+"\n"+
" flatTableName : "+flatTableName+"\n"+
" fieldName : "+fieldName+"\n";
Logger.error("excelinterpreter.ExcelInterpreter.setDataFormatFlatTableField", e.getMessage()+parameters, e);
}
}
@GVA32156 please check our documentation to create and format tables.
Additionally, if you need further assistance to get the expected result attach an Excel file (zip it first) with an example of how the result should look.
Find the file attached, tab “PolicyData” a FLAT table containing the data format and style I would like to apply programmatically within Aspose.Cells components : sampledatainsuranceFlatDataField.zip (213.5 KB)
I went to this documentation already and haven’t found anything relevant for my case. If you think about anything, please let me know
@GVA32156 thank you for the additional information I will look into it and return to you soon.
@GVA32156 you can use the following code to apply the desired format, You just need to set the right colors.
Additionally, please notice that all the additional format in the table is not part of the table itself, that format is applied to the whole column or to specific cells.
// Create a workbook.
Workbook workbook = new Workbook();
// Obtaining the reference of the default(first) worksheet
Worksheet sheet = workbook.Worksheets[0];
// Obtaining Worksheet's cells collection
Aspose.Cells.Cells cells = sheet.Cells;
#region Fill table
// Setting the value to the cells
Aspose.Cells.Cell cell = cells["A1"];
cell.PutValue("Employee");
cell = cells["B1"];
cell.PutValue("Quarter");
cell = cells["C1"];
cell.PutValue("Product");
cell = cells["D1"];
cell.PutValue("Continent");
cell = cells["E1"];
cell.PutValue("Country");
cell = cells["F1"];
cell.PutValue("Sale");
cell = cells["A2"];
cell.PutValue("David");
cell = cells["A3"];
cell.PutValue("David");
cell = cells["A4"];
cell.PutValue("David");
cell = cells["A5"];
cell.PutValue("David");
cell = cells["A6"];
cell.PutValue("James");
cell = cells["A7"];
cell.PutValue("James");
cell = cells["A8"];
cell.PutValue("James");
cell = cells["A9"];
cell.PutValue("James");
cell = cells["A10"];
cell.PutValue("James");
cell = cells["A11"];
cell.PutValue("Miya");
cell = cells["A12"];
cell.PutValue("Miya");
cell = cells["A13"];
cell.PutValue("Miya");
cell = cells["A14"];
cell.PutValue("Miya");
cell = cells["A15"];
cell.PutValue("Miya");
cell = cells["B2"];
cell.PutValue(1);
cell = cells["B3"];
cell.PutValue(2);
cell = cells["B4"];
cell.PutValue(3);
cell = cells["B5"];
cell.PutValue(4);
cell = cells["B6"];
cell.PutValue(1);
cell = cells["B7"];
cell.PutValue(2);
cell = cells["B8"];
cell.PutValue(3);
cell = cells["B9"];
cell.PutValue(4);
cell = cells["B10"];
cell.PutValue(4);
cell = cells["B11"];
cell.PutValue(1);
cell = cells["B12"];
cell.PutValue(1);
cell = cells["B13"];
cell.PutValue(2);
cell = cells["B14"];
cell.PutValue(2);
cell = cells["B15"];
cell.PutValue(2);
cell = cells["C2"];
cell.PutValue("Maxilaku");
cell = cells["C3"];
cell.PutValue("Maxilaku");
cell = cells["C4"];
cell.PutValue("Chai");
cell = cells["C5"];
cell.PutValue("Maxilaku");
cell = cells["C6"];
cell.PutValue("Chang");
cell = cells["C7"];
cell.PutValue("Chang");
cell = cells["C8"];
cell.PutValue("Chang");
cell = cells["C9"];
cell.PutValue("Chang");
cell = cells["C10"];
cell.PutValue("Chang");
cell = cells["C11"];
cell.PutValue("Geitost");
cell = cells["C12"];
cell.PutValue("Chai");
cell = cells["C13"];
cell.PutValue("Geitost");
cell = cells["C14"];
cell.PutValue("Geitost");
cell = cells["C15"];
cell.PutValue("Geitost");
cell = cells["D2"];
cell.PutValue("Asia");
cell = cells["D3"];
cell.PutValue("Asia");
cell = cells["D4"];
cell.PutValue("Asia");
cell = cells["D5"];
cell.PutValue("Asia");
cell = cells["D6"];
cell.PutValue("Europe");
cell = cells["D7"];
cell.PutValue("Europe");
cell = cells["D8"];
cell.PutValue("Europe");
cell = cells["D9"];
cell.PutValue("Europe");
cell = cells["D10"];
cell.PutValue("Europe");
cell = cells["D11"];
cell.PutValue("America");
cell = cells["D12"];
cell.PutValue("America");
cell = cells["D13"];
cell.PutValue("America");
cell = cells["D14"];
cell.PutValue("America");
cell = cells["D15"];
cell.PutValue("America");
cell = cells["E2"];
cell.PutValue("China");
cell = cells["E3"];
cell.PutValue("India");
cell = cells["E4"];
cell.PutValue("Korea");
cell = cells["E5"];
cell.PutValue("India");
cell = cells["E6"];
cell.PutValue("France");
cell = cells["E7"];
cell.PutValue("France");
cell = cells["E8"];
cell.PutValue("Germany");
cell = cells["E9"];
cell.PutValue("Italy");
cell = cells["E10"];
cell.PutValue("France");
cell = cells["E11"];
cell.PutValue("U.S.");
cell = cells["E12"];
cell.PutValue("U.S.");
cell = cells["E13"];
cell.PutValue("Brazil");
cell = cells["E14"];
cell.PutValue("U.S.");
cell = cells["E15"];
cell.PutValue("U.S.");
cell = cells["F2"];
cell.PutValue(2000);
cell = cells["F3"];
cell.PutValue(500);
cell = cells["F4"];
cell.PutValue(1200);
cell = cells["F5"];
cell.PutValue(1500);
cell = cells["F6"];
cell.PutValue(500);
cell = cells["F7"];
cell.PutValue(1500);
cell = cells["F8"];
cell.PutValue(800);
cell = cells["F9"];
cell.PutValue(900);
cell = cells["F10"];
cell.PutValue(500);
cell = cells["F11"];
cell.PutValue(1600);
cell = cells["F12"];
cell.PutValue(600);
cell = cells["F13"];
cell.PutValue(2000);
cell = cells["F14"];
cell.PutValue(500);
cell = cells["F15"];
cell.PutValue(900);
#endregion
// Adding a new List Object to the worksheet
Aspose.Cells.Tables.ListObject listObject = sheet.ListObjects[sheet.ListObjects.Add("A1", "F15", true)];
// Adding Default Style to the table
listObject.TableStyleType = Aspose.Cells.Tables.TableStyleType.TableStyleLight9;
workbook.Worksheets[0].AutoFitColumns(0, 5);
var factory = new CellsFactory();
var borderColStyle = factory.CreateStyle();
borderColStyle.SetBorder(BorderType.RightBorder, CellBorderType.Medium, Color.Black);
borderColStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Medium, Color.Black);
cells.Columns[4].SetStyle(borderColStyle);
var redTextStyle = factory.CreateStyle();
redTextStyle.Font.Color = Color.Red;
cells.Columns[0].SetStyle(redTextStyle);
var cellStyle = factory.CreateStyle();
cellStyle.ForegroundThemeColor = new ThemeColor(ThemeColorType.Background1, 1);
cellStyle.ForegroundColor = Color.ForestGreen;
cellStyle.Pattern = BackgroundType.Solid;
for (var i = listObject.StartRow + 1; i <= listObject.EndRow; i++)
{
var tempCell = cells.GetCell(i, 2);
tempCell.SetStyle(cellStyle);
}
// Saving the Excel file
workbook.Save("C:\\Temp\\cells\\output.xlsx");
@GVA32156,
Please note, to support your requirements (Set formatting/style to list column of the table) specifically, we logged a new ticket with an id “CELLSJAVA-45393” in our internal issue tracking system. We have supported it now. The fix/enhancement will be included in our upcoming release (Aspose.Cells v23.6) that we plan to release in the first half of June 2023. You will be notified when the next version is released.
Thank you, I could figure something based on your example :
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(sheetName);
ListObject flatTable = sheet.getListObjects().get(flatTableName);
ListColumn fieldColumn = flatTable.getListColumns().get(fieldName);
Range fieldRange = fieldColumn.getRange();
Cells cells = sheet.getCells();
final int column = fieldRange.getFirstColumn();
for (int row = fieldRange.getFirstRow(); row < fieldRange.getRowCount(); row++) {
Cell cell = cells.get(row, column);
Style style = cell.getStyle();
//Data Format (numeric values in my case)
if(cell.isNumericValue()) {
style.setCustom("# ### ### ##0.000");
}
//Style : Font and Background color
Font font = style.getFont();
font.setBold(bold);
font.setItalic(italic);
font.setColor(Color.fromArgb(255, 0, 0));
style.setPattern(BackgroundType.SOLID);
style.setForegroundColor(Color.fromArgb(0, 255, 0));
style.update();
cell.setStyle(style); //Happens to be mandatory to make it work ??
}
Simply looping from the index start to the index end of the column field. It is interesting by the way that I had to call the setter for the style property to apply my modifications (comment in the code)
1 Like
@GVA32156,
Thanks for the sample code segment.
It is nice to know that you are have workaround your task for your requirements. Currently, you may loop though your desired range of cells in the List object column(s) and apply the style/formatting accordingly until the next version (Aspose.Cells v23.6) comes out in the next month in which we will include support setting style/formatting to list column(s) of the table directly.
Yes, you need to follow Cell.getStyle() and Cell.setStyle() approach which is more efficient.
The issues you have found earlier (filed as CELLSJAVA-45393) have been fixed in Aspose.Cells for Java 23.6.