Some questions concerning DesktopGrid

Aspose.Cells Product Family
1

Dear all,

During the last days I have evaluated Aspose.Grid. First of all, Aspose.Grid could be a powerful control. But there are also a lot of questions.

  1. Performance: I create the grid structure at runtime, depending on the content of some database tables. I’m using different styles and fonts as well as formulas. The generation of the initial grid takes in the most cases over 90 seconds (please find an example attached). In my opinion this is far too much.
  2. Formulas: Please take a look at the following lines of code. First of all I’m writing 4 numbers into 4 cells. Then I add a Formula that should add the values of the second row. To set the values (both values as well as the formula) of this row I used the member “temprow” that is set to 1. The result of this operation is 5, and not 2! The formula refers every time to the row with the index temprow – 1.

Worksheet wks = mGridDesktop.Worksheets[0];

Style rowcountStyle = new Style(mGridDesktop);

rowcountStyle.HAlignment = HorizontalAlignmentType.Right;

rowcountStyle.NumberFormat = 1;

wks.Rows[1].SetStyle(rowcountStyle);

wks.Rows[2].SetStyle(rowcountStyle);

wks.Rows[3].SetStyle(rowcountStyle);

int temprow = 1;

wks.Cells[0,1].SetCellValue(2);

wks.Cells[0,2].SetCellValue(3);

wks.Cells[temprow,1].SetCellValue(1);

wks.Cells[temprow,2].SetCellValue(1);

wks.Cells[temprow,3].SetCellValue("=SUM(B" + temprow.ToString() + ":C" + temprow.ToString()+ ")" );

mGridDesktop.RunAllFormulas();

  1. It is planed to offer the same functions for the DesktopGrid as they are offered for the WebGrid. It is frustrating to find out that functions are implemented for WebGrid and not for the DesktopGrid
  2. How can I change the title of a HeaderColumn? It is possible to hide Row and Column Headers?

Take Care

Erik

2

Dear Erik,

Thanks for considering Aspose.Grid.

1. For performance issue, we don't find the attachement. Could you please post it again?

2. For formula issue, we will check it ASAP.

3. Which function you find that is available in WebGrid but not in DesktopGrid? Generally we will provide same functions for Web and Desktop control. But because of the dev steps, there may be some difference. Please let me know if you find something is different between WebGrid and DesktopGrid. We will make them ASAP.

4. For title and row/column header issue, our developers will reply on next Monday. Now it's 9:15PM in China. Thanks for your patience.

3

Hi Erik,

I used your code to calculate the formula "=SUM(B1:C1)", the result is 5.

It is correct.

To change the titile of a HeaderColumn, you can use following code:

wks.Columns[3].Header = "MyTitle";

To hide row or column headers is not supported now. We will support the feature soon.

4

Hi,

please find attached the forgotten .xls file. Are you going to fix the formula problem?

Regards

Erik

5

Hi Erik,

I used gridDesktop.LoadFromExcel() function to load example.xls in attachment, and input some values in cells, the formulas ran ok.

About you question of "The formula refers every time to the row with the index temprow – 1". There are two ways to access cell, one is by cell name another is by cell index. Cell name is like "B1", "C2" based index 1. And the cell index is based 0.

Cells["B1"] and Cells[0, 1] refer to same cell.

6

Hi,

It is even not sufficient to create the grid using the gridDesktop.LoadFromExcel() method. The content of the grid depends on the content of two database tables and some additional, hard coded rules. Therefore the grid must be generated at runtime. I believe that my problem depends on the use of different styles and fonts.

In my opinion your statement “Cells[“B1”] refers to Cells[0,1]” seems to be a little bit inconsistent and irritating I am able to write the content of cells of the second row using the index 1, but I have to use index 2 to create a correct formula. Especially in the situation, when you have to create your grids at runtime this task could become challenging. Maybe you can define Cells[1,1] as the top left cell of the grid.

Regards

Erik

7

Hi,

Worksheet class provides CellIndexToName and CellNameToIndex static methods to convert from cell name to cell index each other.

You can predefine some styles and fonts, so when you call Cell.SetStyle(), the GridDesktop control only adds reference count of the style object not style object. You can find the code in "Aspose.Grid.Desktop.Demos.Reports" Northwind.

Style s1 = new Style(gridDesktop);
s1.Color = Color.Cyan;

Style s2 = new Style(gridDesktop);
s2.SetBorderLine(BorderType.Top,BorderLineType.Thin);
s2.SetBorderLine(BorderType.Left,BorderLineType.Thin);
s2.SetBorderLine(BorderType.Right,BorderLineType.Thin);
s2.SetBorderLine(BorderType.Bottom,BorderLineType.Thin);

Style s3 = (Style)s2.Clone();
s3.HAlignment = HorizontalAlignmentType.Centred;

Style s4 = (Style)s2.Clone();
s4.HAlignment = HorizontalAlignmentType.Right;

Style s5 = (Style)s4.Clone();
s5.NumberFormat = 7;

for( int col = scol; col < colcount; col++ )
{
for( int row = srow; row < rowcount; row++ )
{
if (dt.Columns[col - scol].DataType.Equals(System.Type.GetType("System.Decimal")))
{
decimal d = decimal.Parse(dt.Rows[row - srow][col - scol].ToString());
gridDesktop.GetActiveWorksheet().Cells[row, col].Value = d;
}
else
{
gridDesktop.GetActiveWorksheet().Cells[row, col].Value = dt.Rows[row - srow][col - scol].ToString();
gridDesktop.GetActiveWorksheet().Cells[row, col].SetStyle(s2);
}

if( col == 2 )
{
gridDesktop.GetActiveWorksheet().Cells[row, col].SetStyle(s4);
}

if( col == 3 )
{
gridDesktop.GetActiveWorksheet().Cells[row, col].SetStyle(s3);
}

if( col == 4 )
{
gridDesktop.GetActiveWorksheet().Cells[row, col].SetStyle(s5);
}
}
}

8

Hi,

Thanks for your support. I’m already using predefined styles & fonts. But I have found the real performance killer. Due to the fact that I’m using a lot of formulas I must use setCellValue() instead of the Value property also for such cells, that are independent from any formula. Now it takes only one second to create the report.

Regards

Erik