Is it possible to include a conditional statment in the smart tag such as the following Excel function?
=IF(F13>=0,G13*1,G13*-1)
Please also explain how to calculate column totals
Also is it possible to have sub-totals on criteria "breaks" ie sorting on a column "A" and when the field changes to include a sub total for that section?
Thanks for your help
Jonathan Marsden
Hi Jonathan,
Thanks for considering Aspose.
Well Aspose.Cells smart markers does not support conditional statements in the formulas. I think you 'd better fill the data into the worksheet processing the markers first and then use Aspose.Cells APIs to add conditioanl formattings to the worksheets cells.
For sub-totalling, Aspose.Cells does not support sub-totaling as we will support it in out future versions soon although you may sort the data into the columns. I think you may try to insert your subtotal rows manually to calculate the sets of data using Aspose.Cells APIs.
Thank you.
Thanks for getting back to me
In the following code I create a datatable and thne use smart tags to place the fields in the spreadsheet. By default all the values are positive. If the value of field name Rejecteddebit, is >'0' I want to convert the values of fin_mnthtotalbrok, fin_monthly0, fin_pliab, fin_fun1, fin_ppa toa negative(-).
Could you explain how I can do this, and include them in the spreadsheet, using your previous suggestion.
Thank you so much for your help
Jonathan
Code
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Instantiate an instance of license and set the license file through its path
Dim license As Aspose.Cells.License = New Aspose.Cells.License()
license.SetLicense("Aspose.Cells.lic")
Dim myCommand As OleDbCommand
Dim dataReader As System.Data.OleDb.OleDbDataReader = Nothing
'Instantiating an WorkbookDesigner object
Dim designer As WorkbookDesigner = New WorkbookDesigner()
'Open a designer spreadsheet containing smart markers
designer.Open(Server.MapPath("~/reports/monthlyrecon.xls")) ' please make sure the file is placed on the correct path
'Set the data source for the designer spreadsheet
designer.SetDataSource(DataSet)
'Process the smart markers
designer.Process()
designer.Workbook.Save("Report.xls", FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, Response)
end sub
Have you had a chance to look at my request?? Thanks for getting back to me.
Thanks for getting back to me
In the following code I create a datatable and thne use smart tags to place the fields in the spreadsheet. By default all the values are positive. If the value of field name Rejecteddebit, is >'0' I want to convert the values of fin_mnthtotalbrok, fin_monthly0, fin_pliab, fin_fun1, fin_ppa toa negative(-).
Could you explain how I can do this, and include them in the spreadsheet, using your previous suggestion.
Thank you so much for your help
Jonathan
Code
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Instantiate an instance of license and set the license file through its path
Dim license As Aspose.Cells.License = New Aspose.Cells.License()
license.SetLicense("Aspose.Cells.lic")
Dim myCommand As OleDbCommand
Dim dataReader As System.Data.OleDb.OleDbDataReader = Nothing
'Instantiating an WorkbookDesigner object
Dim designer As WorkbookDesigner = New WorkbookDesigner()
'Open a designer spreadsheet containing smart markers
designer.Open(Server.MapPath("~/reports/monthlyrecon.xls")) ' please make sure the file is placed on the correct path
'Set the data source for the designer spreadsheet
designer.SetDataSource(DataSet)
'Process the smart markers
designer.Process()
designer.Workbook.Save("Report.xls", FileFormatType.Default, Aspose.Cells.SaveType.OpenInExcel, Response)
end sub
Hi,
After you process the markers, the data is filled into the worksheet. Then you may try to use conditional formattings Aspose.Cells APIs to do your task.
For reference, please check:
Thank you.
Hi,
Conditional formatting post processing markers will work. But is there anyway we can handle it with smart markers. I see previous query is 12 years old for now, Is there any enhancements done to include conditional formatting in the smart markers?
My requirement goes like this:
&=News.Title &=News.Event &=News.Date &=News.NewsText
I have 4 columns in template with smart tags defined. If we dont have data for one column lets say &=News.Event in this case. I need column to be removed completely so that my final result would have only three columns.
Let me know if its possible with smart tags? If not let me know the possible ways to achieve this.
Conditional formatting is separate entity in Excel spreadsheet, so Smart Markers do not support conditional statements in its formulas. Also, Aspose.Cells will not give you any data return or throw any exception if your marker’s data source is null or marker is wrong itself.
Well, you may easily cope with your scenario. You may evaluate if there is data/values in the source column, you may use SQL query or statements for confirmation. You may remove the column using Worksheet.Cells.DeleteColumn() method accordingly. Alternatively, after processing markers and when the data is filled into the sheet cells, you may check it manually (via Aspose.Cells API) if your specified column has data in its cells. If there is no data, you may remove that column via the APIs.