Apply column level formatting using LightCells API in Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi Team,

I have a request in my project that I need to apply custom formatting for the columns.
But I would come to know the formatString only after looping through all the cells. That is only after completing the entire flow in startCell.
So is there a way I can apply the column formatting after looping through all the cells ?

Please advise.

PFB the demo program in which I am trying to apply the column formatting.

1. Demo.java

package com.test.aspose.table;

import com.aspose.cells.Cells;
import com.aspose.cells.OoxmlSaveOptions;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class Demo {

  public static void main(final String[] args) throws Exception {
    // Instantiate a new Workbook
    final Workbook wb = new Workbook();
    // set the sheet count
    final int sheetCount = 1;
    // set the number of rows for the big matrix
    final int rowCount = 5;
    // specify the worksheet
   
    for (int k = 0; k < sheetCount; k++) {
      Worksheet sheet = null;
      if (k == 0) {
        sheet = wb.getWorksheets().get(k);
        sheet.setName("test");
      } else {
        final int sheetIndex = wb.getWorksheets().add();
        sheet = wb.getWorksheets().get(sheetIndex);
        sheet.setName("test" + sheetIndex);
      }
      final Cells cells = sheet.getCells();

      // set the columns width
      for (int j = 0; j < 15; j++) {
        cells.setColumnWidth(j, 15);
      }
    }

    // Create an object with respect to LightCells data provider
    final LightCellsDataProviderDemo dataProvider =
        new LightCellsDataProviderDemo(wb, 1, rowCount, 3);
    // Specify the XLSX file's Save options
    final OoxmlSaveOptions opt = new OoxmlSaveOptions();
    // Set the data provider for the file
    opt.setLightCellsDataProvider(dataProvider);

    // Save the big file
    wb.save("DemoTest.xlsx", opt);
  }
}


2. LightCellsDataProviderDemo.java

package com.test.aspose.table;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.LightCellsDataProvider;
import com.aspose.cells.Row;
import com.aspose.cells.Style;
import com.aspose.cells.StyleFlag;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;


public class LightCellsDataProviderDemo implements LightCellsDataProvider {

  private final int sheetCount;
  private final int maxRowIndex;
  private final int maxColIndex;
  private int rowIndex;
  private int colIndex;
  private final Worksheet worksheet;
  private final Style style2;

  @SuppressWarnings("deprecation")
  public LightCellsDataProviderDemo(final Workbook wb, final int sheetCount, final int rowCount,
      final int colCount) {
    // set the variables/objects
    this.sheetCount = sheetCount;
    this.maxRowIndex = rowCount - 1;
    this.maxColIndex = colCount - 1;
    this.worksheet = wb.getWorksheets().get(0);
    this.style2 = wb.createStyle();
  }

  @Override
  public boolean isGatherString() {
    return false;
  }

  @Override
  public int nextCell() {
    if (this.colIndex < this.maxColIndex) {
      this.colIndex++;
      return this.colIndex;
    }
    return -1;
  }

  @Override
  public int nextRow() {
    if (this.rowIndex < this.maxRowIndex) {
      this.rowIndex++;
      this.colIndex = -1; // reset column index
      if (this.rowIndex % 1000 == 0) {
        System.out.println("Row " + this.rowIndex);
      }
      return this.rowIndex;
    }
    // Trying to apply the column formatting after traversing through all the rows, but not working!
    final StyleFlag flag2 = new StyleFlag();
    flag2.setFontBold(false);
    flag2.setNumberFormat(true);
    this.style2.setCustom("0.00");
    this.style2.getFont().setBold(true);
    final Cells cells = this.worksheet.getCells();
    cells.applyColumnStyle(1, this.style2, flag2);
    return -1;
  }

  @Override
  public void startCell(final Cell cell) {
    cell.putValue(12.3456);
    // Trying to apply the column formatting after traversing through all the cells, but not
    // working!
    if (this.rowIndex == this.maxRowIndex && this.colIndex == this.maxColIndex) {
      final StyleFlag flag2 = new StyleFlag();
      flag2.setFontBold(true);
      flag2.setNumberFormat(true);
      this.style2.setCustom("0.00");
      this.style2.getFont().setBold(true);
      final Cells cells = this.worksheet.getCells();
      cells.applyColumnStyle(1, this.style2, flag2);
    }
  }

  @Override
  public void startRow(final Row row) {
    row.setHeight(25);
  }

  @Override
  public boolean startSheet(final int sheetIndex) {
    if (sheetIndex < this.sheetCount) {
      // reset row/column index
      this.rowIndex = -1;
      this.colIndex = -1;
      return true;
    }
    return false;
  }
}
2

@Thilakbabu,

Thanks for the sample code segment and details.

Your code is not right. Please instantiate the Style object and specify/set the relevant attributes in the constructor of LightCellsDataProviderDemo class that implements LightCellsDataProvider interface, see the updated code segment a bit:
e.g
Sample code:

........
class LightCellsDataProviderDemo implements LightCellsDataProvider {

private final int sheetCount;
private final int maxRowIndex;
private final int maxColIndex;
private int rowIndex;
private int colIndex;
private final Worksheet worksheet;
private final Style style2;

@SuppressWarnings("deprecation")
public LightCellsDataProviderDemo(final Workbook wb, final int sheetCount, final int rowCount,
final int colCount) {
// set the variables/objects
this.sheetCount = sheetCount;
this.maxRowIndex = rowCount - 1;
this.maxColIndex = colCount - 1;
this.worksheet = wb.getWorksheets().get(0);
this.style2 = wb.createStyle();
this.style2.setCustom("0.00");
this.style2.getFont().setBold(true);

}

@Override
public boolean isGatherString() {
return false;
}


.....

@Override
public void startCell(final Cell cell) {
cell.putValue(12.3456);
cell.setStyle(style2);
// Trying to apply the column formatting after traversing through all the cells, but not
// working!
if (this.rowIndex == this.maxRowIndex && this.colIndex == this.maxColIndex) {
cell.setStyle(style2);

}
}
.......

You need to modify your code segment accordingly. For reference, please see the document with examples on how to use LightCells APIs:

3

Hi Amjad,
I do not have the value of the format which needs to be set at the beginning of the code.
Thats why I am trying to set the custom format at the end of looping through the cells.

So , Do you mean to say we would not be able to set the format after looping through the cells?

Is there any other way to achieve it ?

Thanks

4

@Thilakbabu,

In LightCells APIs you have to specify/set the formatting attributes at class level, so either you need to instantiate and set the Style formattings in the constructor of the class (that implements the LightCellsDataProvider interface or use normal mode to create and set the style formattings (do not use LightCells APIs mode).