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

Free Support Forum - aspose.com

SHIFT command not working

Aspose.Cells Product Family
#1

Hi,

I'm trying to use the SHIFT command in my ASPOSE tag but it doesn't seem to have any effect.

I have two sections: positions and regions. each section expands shows the proper data but when the "region" section expands it creates blank blue lines to match the number of green lines in the "positions" section. I added the SHIFT keyword to the ASPOSE tag so extra blue line wouldn't be added but it didn't work
I'm trying to use the SHIFT command in my ASPOSE tag but it doesn't seem to have any effect.

I have two sections: positions and regions. each section expands shows the proper data but when the "region" section expands it creates blank blue lines to match the number of green lines in the "positions" section. (approximately 15 positions and 5 regions). I added the SHIFT keyword to the ASPOSE tag so extra blue line wouldn't be added but it didn't work

Positions Regions
&=Positions.Ticker &=Positions.Description &=Positions.MarketValue &=Regions.RegionCode(shift) &=Regions.RegionName(shift) &=Regions.MarketValue(shift)
Total: &=PositionSummary.MarketValue Total: &=RegionSummary.MarketValue

Any ideas to why its not working? Is the SHIFT syntax correct?

#2

Hi there,


Thank you for contacting Aspose support.

We have evaluated your presented scenario, and we think that your syntax for SHIFT marker is not correct. Please try using the SHIFT marker as follow to shift down or right, creating extra rows or columns to fit data.

&=Regions.RegionCode(shift cells down)
or
&=Regions.RegionCode(shift cells right)

In case the problem persists, please provide us your designer as well as dummy data to evaluate the problem cause on our end.
#3

Hi,

thank you for the quick reply. I tried both options and neither of them worked.

see attached sample file with a tab for tags and a tab for data

#4

Hi,

Thanks for providing us your designer file, expected output and using Aspose.Cells.

Please make your smart markers like the following

&=Positions.Ticker(noadd,shift)
&=Positions.Description(noadd,shift)
&=Positions.MarketValue(noadd,shift)

&=Regions.RegionCode(noadd,shift)
&=Regions.RegionName(noadd,shift)
&=Regions.MarketValue(noadd,shift)

I have attached the modified designer file (-SampleSHIFT.xlsx) for your reference. Please check the sheet (smart tags). I have also attached the output (out.xlsx). As you can see in the screenshot that now smart tags sheet has your expected output.

Here is the code, I used to generate the output file.

C#


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


WorkbookDesigner designer = new WorkbookDesigner();

designer.Workbook = new Workbook(filePath);


Worksheet worksheet = designer.Workbook.Worksheets[“Sheet3”];


DataTable dtPositions = worksheet.Cells.ExportDataTable(0, 0, 10, 3, true);

dtPositions.TableName = “Positions”;


DataTable dtRegions = worksheet.Cells.ExportDataTable(0, 4, 4, 3, true);

dtRegions.TableName = “Regions”;


DataSet ds = new DataSet();

ds.Tables.Add(dtPositions);

ds.Tables.Add(dtRegions);


designer.SetDataSource(ds);

designer.Process(true);


designer.Workbook.Save(“out.xlsx”);



#5

Hi,

I implemented your steps and it worked. thank you very much.

Unfortunately my requirements have changed and I had to add conditional formatting on the left section so now using the (noadd,shift) in the smart tag causes the data in the left section to shift. Basically for the left section (positions) I had to add strategy and subtotals where each strategy subtotal will have a grey colored cell and the non subtotal data will have green cells.

Attached is a sample file with smart tags and conditional formatting for the positions section.

If you need more information just let me know

thanks

#6

Hi,

Thanks for your posting and using Aspose.Cells.

Conditional formatting will not work in smart markers. The closest thing I could achieve is the output shown in the output sheet. Hopefully, it will be helpful for you.

I have attached the designer file and the output file for your reference. Please see the following sample code used to achieve the output file.

C#


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


WorkbookDesigner designer = new WorkbookDesigner();

designer.Workbook = new Workbook(filePath);


Worksheet worksheet = designer.Workbook.Worksheets[“Sheet3”];


DataTable dtPositions = worksheet.Cells.ExportDataTable(1, 0, 10, 4, true);

dtPositions.TableName = “Positions”;


DataTable dtRegions = worksheet.Cells.ExportDataTable(1, 6, 4, 3, true);

dtRegions.TableName = “Regions”;


DataSet ds = new DataSet();

ds.Tables.Add(dtPositions);

ds.Tables.Add(dtRegions);


designer.SetDataSource(ds);

designer.Process();


designer.Workbook.Save(".out.xlsx");



#7

Hi,

At the end the formatting for the positions sections was more important than the region section so I left the report to include additional lines in the region section..

thank you again

#8

Hi,

Thanks for your posting and using Aspose.Cells.

It is good to know that you were able to sort out this issue. Let us know if you encounter any other issue, we will be glad to look into it and help you further.