Problem with Smart Markers

Aspose.Cells Product Family
1

Hi,

I am busy trying out Aspose.Cells, and can't figure out a way to solve a problem.

I have two sheets, the first has the summary information (this works fine), the second contains a template, that needs to be filled out and repeated for each row in the first dataset.

I figured I could just set a worksheet variable to contain the template, and add that worksheet and reprocess for each row in the datatable, however there is no way to import a worksheet, and if I copy the sheet, it will either happen before a merge or after, and I will end up with the same data on each sheet.

2

I am not clear about your problem.

Could you please post a sample project to elaborate your problem? And which version of Aspose.Cells are you using?

3

I've attached the project as well as the spreadsheet

In the template, Fig 1 repeats vertically with all the data, then each row of the data needs to create a sheet or repeat Fig 2

the project is at http://www.santrad.co.za/DailyReport.zip

4

Please try to change your code to:

Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
Dim ds As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "select '' as Risks, '' as Issues, CASE WHEN Round((CASE when dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3') = convert(float,0) Then Convert(float, 0) when dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3') is null then Convert(float, 0) when Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0) = convert(float,0) then ((Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost1'),0)) / 100000),0) - Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0)) * 100) ELSE ((Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost1'),0)) / 100000),0) - Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0)) / (Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0)) * 100) End),0) < -10 THEN '? ELSE '' END as Cost, CASE WHEN ISNULL(dbo.GetActualProgress(Proj_ID), 0) - ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Number1'), 0) < -10 THEN '? ELSE '' END as Schedule, CASE WHEN dbo.getenterprisecodevalue(Proj_ID, '8') = 'Yes' THEN '? ELSE '' END as Scope, CASE WHEN ((dbo.getenterprisecodevalue(Proj_ID, '12') = 'Yes') or (dbo.getenterprisecodevalue(Proj_ID, '9') = 'Yes')) THEN '? ELSE '' END as Contracts, dbo.getenterprisecodevalue(Proj_ID, '2') as Region, dbo.getenterprisecodevalue(Proj_ID, '13') as Bexco, dbo.getenterprisecodevalue(Proj_ID, '5') as Customer, dbo.getenterprisecodevalue(Proj_ID, '14') as PRCC, Proj_name as Name, dbo.getenterprisecodevalue(Proj_ID, '4') as IntExt, dbo.getenterprisedatevalue(Proj_ID, 'Task Enterprise Project Date4') as ChangeControlDate, (ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost4'), 0) / 100) + (ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost5'), 0) / 100) as CostBudget, ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost7'), 0) / 100 as forecastcost, (ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'), 0) / 100) - (ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost1'), 0) / 100) as CostVariance, Round((CASE when dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3') = convert(float,0) Then Convert(float, 0) when dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3') is null then Convert(float, 0) when Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0) = convert(float,0) then ((Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost1'),0)) / 100000),0) - Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0)) * 100) ELSE ((Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost1'),0)) / 100000),0) - Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0)) / (Round((convert(float,isnull(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'),0)) / 100000),0)) * 100) End),0) as CostVarianceP, ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Number1'), 0) as PlannedP, ISNULL(dbo.GetActualProgress(Proj_ID), 0) as ActualP, ISNULL(dbo.GetActualProgress(Proj_ID), 0) - ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Number1'), 0) as POCVariance, dbo.getenterprisedatevalue(Proj_ID, 'Task Enterprise Project Date7') as ForecastDate, dbo.getenterpisetextvalue(Proj_ID, 'Task Enterprise Project Text14') as ProjectAlertReason, dbo.getenterpisetextvalue(Proj_ID, 'Task Enterprise Project Text15') as Description, dbo.getenterprisecodevalue(Proj_ID, '11') as Status, dbo.getenterprisecodevalue(Proj_ID, '10') as Programme, dbo.getenterprisecodevalue(Proj_ID, '20') as Project, dbo.getenterprisecodevalue(Proj_ID, '8') as ScopeCharged, dbo.getenterprisedatevalue(Proj_ID, 'Task Enterprise Project Date5') as ConfirmationDate, dbo.getenterpisetextvalue(Proj_ID, 'Task Enterprise Project Text13') as SupplierAssigned, dbo.getenterpisetextvalue(Proj_ID, 'Task Enterprise Project Text13') as ClientAssigned, dbo.getenterprisecodevalue(Proj_ID, '9') as SupplierContract, dbo.getenterprisecodevalue(Proj_ID, '12') as ClientContract, dbo.getenterprisecodevalue(Proj_ID, '6') as ContractType, dbo.getenterpisetextvalue(Proj_ID, 'Task Enterprise Project Text11') as ReasonClient, dbo.getenterpisetextvalue(Proj_ID, 'Task Enterprise Project Text1') as ReasonSupplier, ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost1'), 0) / 100 as ActualCost, ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'), 0) / 100 as PlannedCost from Projectserver4586..MSP_Projects where dbo.getenterprisecodevalue(Proj_ID, '24') = 'Yes'")
ds.Tables(0).TableName = "Projects"
Dim designer As WorkbookDesigner = New WorkbookDesigner()

Dim path As String = MapPath(".")
path = path.Substring(0, path.LastIndexOf("\"))
Dim designerFile As String = "c:\DailyReportTemplate.xls"
designer.Open(designerFile)

designer.SetDataSource(ds)
designer.Process(0, TRUE)
Dim w As Worksheet
Dim dr As DataRow
Dim dx As New DataSet
dx.Tables.Add("Item")
w = designer.Workbook.Worksheets("Fig 2")


For Each dr In ds.Tables(0).Rows
designer.Workbook.Worksheets.AddCopy("Fig 2")
Next
Dim i As integer = 1
For Each dr In ds.Tables(0).Rows
dx.Tables("Item").Rows.Clear()
dx.Tables("Item").Rows.Add(dr)
designer.ClearDataSource()
designer.SetDataSource(dx)
designer.Process(i, TRUE)
i = i + 1
Next

designer.Save("SmartMarker2.xls", SaveType.OpenInBrowser, FileFormatType.Default, Me.Response)

End Sub