We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

LightCellsDataProvider problem

 import com.aspose.cells.*;

 public class DemoWrite {

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

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

        // traverse the columns for adding hyperlinks and merging
        for (int i = 0; i < rowCount; i++) {
            // The first 10 columns
            for (int j = 0; j < 32; j++) {
                cells.get(i, j).setValue("dfadsfadsf");
            }
        }
    }


    // Create an object with respect to LightCells data provider
    LightCellsDataProviderDemo dataProvider = new LightCellsDataProviderDemo(wb, 1, rowCount, 32);

    // Specify the XLSX file's Save options
    OoxmlSaveOptions opt = new OoxmlSaveOptions();
    // Set the data provider for the file
    opt.setLightCellsDataProvider(dataProvider);

    // Save the big file
    wb.save("DemoTest.xlsx", opt);
    wb.dispose();
}}class LightCellsDataProviderDemo implements LightCellsDataProvider {
private final int sheetCount;
private final int maxRowIndex;
private final int maxColIndex;
private int rowIndex;
private int colIndex;
private final Style style1;
private final Style style2;

public LightCellsDataProviderDemo(Workbook wb, int sheetCount, int rowCount, int colCount) {
    // set the variables/objects
    this.sheetCount = sheetCount;
    this.maxRowIndex = rowCount - 1;
    this.maxColIndex = colCount - 1;

    // add new style object with specific formattings
    style1 = wb.createStyle();
    Font font = style1.getFont();
    font.setName("MS Sans Serif");
    font.setSize(10);
    font.setBold(true);
    font.setItalic(true);
    font.setUnderline(FontUnderlineType.SINGLE);
    font.setColor(Color.fromArgb(0xffff0000));
    style1.setHorizontalAlignment(TextAlignmentType.CENTER);

    // create another style
    style2 = wb.createStyle();
    style2.setCustom("#,##0.00");
    font = style2.getFont();
    font.setName("Copperplate Gothic Bold");
    font.setSize(8);
    style2.setPattern(BackgroundType.SOLID);
    style2.setForegroundColor(Color.fromArgb(0xff0000ff));
    style2.setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, Color.getBlack());
    style2.setVerticalAlignment(TextAlignmentType.CENTER);
}


@Override
public boolean startSheet(int sheetIndex) {
    if (sheetIndex < sheetCount) {
        this.rowIndex = -1;
        this.colIndex = -1;

        return true;
    }

    return false;
}

@Override
public int nextRow() {
    if (rowIndex < maxRowIndex) {
        rowIndex++;
        colIndex = -1; // reset column index
        return rowIndex;
    }
    return 0;
}

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

@Override
public int nextCell() {
    if (colIndex < maxColIndex) {
        colIndex++;
        return colIndex;
    }
    return 0;
}

@Override
public void startCell(Cell cell) {
    if (rowIndex % 50 == 0 && (colIndex == 0 || colIndex == 3)) {
        // do not change the content of hyperlink.
        return;
    }
    if (colIndex < 10) {
        cell.putValue("test_" + rowIndex + "_" + colIndex);
        cell.setStyle(style1);
    } else {
        if (colIndex == 19) {
            cell.setFormula("=Rand() + test!L1");
        } else {
            cell.putValue(rowIndex * colIndex);
        }
        cell.setStyle(style2);
    }
}

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

why is it giving an error when using it. Thanks in advance for the reply

@Abdumajid,

Please see the api references for the details and requirements when implementing LightCellsDataProvider: https://reference.aspose.com/cells/java/com.aspose.cells/lightcellsdataprovider/.

Firstly, when finishing one Row or one Worksheet, nextCell() and nextRow() should return -1 instead of 0. 0 is a valid column/row index so we cannot take it as the flag to finish current row/worksheet.

Secondly, in your code it seems you have instantiated all cells of the large range: 100000x32 cells in memory. So LightCells is needless and you implementation gives no help for your memory cost. To get better memory performance, you should set values for most cells in the startCell(Cell) method only.

Thank you. johnson.shi

@Abdumajid,

You are welcome.