Aspose.cells Horizonal Feature with formulas

Aspose.Cells Product Family
1

Hi Team,


we are using latest licensed version of aspose.cells and i am using horizontal feature of it.
But when i am placing formula in Grosvenor_Transpose.xlsx Column B its not getting expanded horizontaly in output gros_tra_-_0.xlsx. its just comig at the end.
Can you please review the attachment and provide answer for this.
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please spare us sometime. We will look into your issue and help you asap.

Please also provide us your expected output xls/xlsx file which you can create manually using Ms-Excel 2010 and attach here.

It will help us investigate this issue in detail.

3

Hi Team,


I have attached the Expected output excel sheet. In this sheet alternate columns B,D,F… are the expected formula columns which i am expecting.
This is very crucial for my demo on friday.
Please do the needful asap.
4

Hi,

Thanks for your posting your expected output xlsx file.

It will surely help us look into your issue and we will be able to provide you a sample code or advice asap.

Please spare us some time, we will get back to you asap.

In case of some issues or missing features, you might have to wait more.

Thanks for your cooperation.

5

Hi Team,


Any update on this issue?

Thanks,
Rajendra
6

Hi,

Thanks for your posting and using Apsose.Cells for .NET.

I have looked into your issue thoroughly, but it seems to me there is some bug in dynamic formulas when offsets are used, which needs to be fixed.

For example, this works fine, the formula repeats itself with number of rows.

&=&=A{r}-B{r}~(skip:1)

However, when this is used with some offset, formula does not repeat. e.g it will not repeat

&=&=A{-1}-B{-1}~(skip:1) (only one row is inserted)

I also tried noadd parameter, but it was no avail.

7
rajsingh85007:
Hi Team,

Any update on this issue?

Thanks,
Rajendra
Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have found, it is a bug, Dynamic Formula with Offset does not repeat itself.

We have logged this issue in our database for a fix. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41327.
8

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have looked into your issue in detail and found that we could not directly support your need because we could not found how many formulas should be repeated when we process smartmarkers column by column if it’s vertical.

If you want to get expected file, please add a SmartMarker in the column B and hide the row 3.

Please check the attached file for your reference.

9

Hi Team,


When i use your sent sheet its giving me blank output.
I am using aspose.cells 7.3.1.1 version.

Regards,
Rajendra
10

Hi,

Thanks for your feedback and using Aspose.Cells for .NET.

First of all, we wish you a great 2013 year and all Aspose users too. :slight_smile:

I have attached the following screenshot that shows the output which I got from your input file.

The output is not perfect because although formula repeats itself but the formula’s columns are not changing.

Please see the following test code. I have also attached the template and output files for a reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Grosvenor_Transpose.xlsx”;


DataTable dt = new DataTable(“Total”);


DataColumn dcscode = new DataColumn(“scode”, typeof(string));

DataColumn dcsmtd1 = new DataColumn(“smtd”, typeof(int));

DataColumn dcsmtd2 = new DataColumn(“smtd2”, typeof(int));


dt.Columns.Add(dcscode);

dt.Columns.Add(dcsmtd1);

dt.Columns.Add(dcsmtd2);


string[] scodes = { “nlp00001”, “nlp00001”, “nlp00001”, “nlp00002”, “nlp00002”, “nlp00002” };

int[] smtd1 = { -86500, -86500, -86500, -86500, -62000, -347000, -352000 };

int[] smtd2 = { -8650, -8650, -8650, -8650, -6200, -34700, -35200 };


int idx=0;


foreach (string scode in scodes)

{

DataRow dr = dt.NewRow();

dr[dcscode.ColumnName] = scodes[idx];

dr[dcsmtd1.ColumnName] = smtd1[idx];

dr[dcsmtd2.ColumnName] = smtd2[idx];


dt.Rows.Add(dr);

idx++;

}


Workbook template = new Workbook(filePath);

Worksheet worksheet = template.Worksheets[0];

///worksheet.Cells[“A1”].PutValue("&=Total.smtd1(skip:1)");

///worksheet.Cells[“B1”].PutValue("&=Total.smtd2(skip:1,noadd)");

///worksheet.Cells[“C1”].PutValue("&=&=A{r}-B{r}~(skip:1,noadd)");

///worksheet.Cells[“D2”].PutValue("&=&=A{-1}-B{-1}~(skip:1)");


WorkbookDesigner designer = new WorkbookDesigner();

designer.Workbook = template;


designer.SetDataSource(dt);

designer.Process();


designer.Workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

System.Diagnostics.Process.Start(filePath + “.out.xlsx”);

Screenshot:
11

Hi,


Please change your smart markers with attached sample file accordingly.

Hopefully, it would work for your needs.

Thank you.