Excel Data formatting using LightCellDataProvider

Aspose.Cells Product Family
1

Hi Amjad,

I have created XLS (Office-2003) file using LightCellDataProvider in Aspose.Cells.

Now I want to format data in the excel. Is there any method in the LightCelldataProvider interface like endSheet(int sheetIndex). Currently we have only startSheet(int sheetIndex) method which performs some operations after starting the sheet.

Do we have any way to format the data in Excel file after putting in the worksheet ?

Thanks

Shrikant

2

Hi,

Thanks for using Aspose.Cells.

With LightCells API, cell values and formattings will be saved to the ultimate file data directly when you process one cell.

So, after the process of one cell, the value and formatting of this cell cannot be modified again.

Therefore please set the cell formatting before you start to process one sheet (such as format the whole column of sheet), or you can set formatting of the cell while process it.

For formatting a cell while using LightCells API, please see the sample in the document provided by our prior reply to you.

http://www.aspose.com/docs/display/cellsjava/Using+LightCells+API

3 
Hi,

Well, in LightCellsDataProvider Interface, there are 6 methods that you have to override while implementing the interface, i.e…,



LightCellsDataProvider Methods Summary:


abstract boolean	isGatherString()	

Checks whether the current string value of cell needs to be gathered into a global pool.

abstract int	nextCell()	

Gets next cell to be saved.

abstract int	nextRow()	

Gets the next row to be saved.

abstract void	startCell(Cell cell)	

Starts to save data of one cell.

abstract void	startRow(Row row)	

Starts to save data of one row.

abstract boolean	startSheet(int sheetIndex)	

Starts to save a worksheet.


There is no endSheet method(int sheetIndex). You can define style objects with your desired formattings in the constructor of the class that implements the interface, e.g

//…

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 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
    int index = wb.getStyles().add();
    style1 = wb.getStyles().get(index);
    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
    index = wb.getStyles().add();
    style2 = wb.getStyles().get(index);
    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);
}



Now you may specify styles for cells in rows and columns in the e.g startCell(Cell cell) method, e.g


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);

}

}


For complete reference, please see the example in the article for your reference:

http://www.aspose.com/docs/display/cellsjava/Using+LightCells+API


Thank you.

4

Right, I saw this example earlier only. I have to format the below VBA code using Aspose.cells based on the data put into worksheet. This is TEXT column and have some formatting rules. Now I have to apply formatting rules based on the VBA code mentioned below.

Select Case ThisWorkbook.Sheets("Metadata").Cells(iceJ, 3)
Case "TEXT"
.NumberFormat = "@"
.HorizontalAlignment = xlLeft
.EntireColumn.AutoFit
If .ColumnWidth > 70 Then
.ColumnWidth = 70
.VerticalAlignment = xlTop
.WrapText = True
End If
If .ColumnWidth < 15 Then
.ColumnWidth = 15
End If

Now If i have to apply formatting logic based on ColumnWidth , i can't do any formatting before putting data into worksheet. So only way is to do formatting Cell by Cell instead of applying formatting logic for entire column which is kind of messy coding.

Please suggest.

5

Hi,


Please note:

1) Firstly, in LightCells mode, user has to fill cell value one by one, format the cells one cell by one cell, or one row by one row. Or, even if all the cells in one column can use the same style, then user can set the style to the whole column outside of the loop and process of the LightCells.

2) Secondly, to measure the column width, you may use the Cell.GetWidthOfValue() method for every cell and gather the maximum width as the column width after processing all cells.

Also, we think you need to know the basic knowledge of the LightCells that all cells data are not kept in the workbook/cells model (and this is how and it is just the way for LightCells to reduce the memory cost), so we cannot do such kind of operation (such as, auto-fit operations which actually demand to know the content of all cells in one column) with LightCells mode.

Thanks for your understanding!

6

Hi Amjad, Shakeel


Thanks for the reply. I got all your points. But

1) In LightCells mode, user has to fill and format value one by cell. What if anyone wants to format excel based on the data
putting in the sheet.

In my case, I am reading data from a text file row by row and then putting that data into the Excel file using LightCells mode. Then based on data size, I have to format worksheet.

2) Cell.GetWidthOfValue() is not at all reliable. I tried on one of the data row. For all data values, it is giving differant result.

3) I agree LightCells that all cells data are not kept in the workbook/cells model. but this won't be quite helpful, if we won't autofit column, though it takes more memory This is very basic functionality in excel.

4) CellsHelper.setFontDir() is also not working. I set font DIR like this :- CellsHelper.setFontDir("C:\\windows\\fonts");

Thanks

Shrikant

7

Hi,

Thanks for your questions.

I am afraid, the normal code will not work in Light Cells mode. It is good for only reading/writing large data. You cannot use it to manipulate the data.

So, you should use some mixed approach, for example, write your data to some temp file in Light Cells mode, then reload your workbook in Normal mode and manipulate your data.

8

If I save large file using LightCell mode and then open this file to manipulate date, how to load large file in memory without getting OutOfmemory exception ?

9

Hi,

Thanks for your question and using Aspose.Cells.

Normally, for larger Ms-Excel files, larger memory is needed.

Could you please compile and execute your application in 64-bit environment?

I think, it will help you to get rid of memory problems.