Hi, I am dynamically adding sheets to an excel workbook, as well as rows to the primary sheet using smartmarkers.
I want to link those rows added to the sheets added. Below is the code I am using.
Dim ds As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "select Proj_ID, CASE WHEN dbo.HasRisks(Proj_ID) > 0 THEN 'ü' ELSE '' END as Risks, CASE WHEN dbo.HasIssues(Proj_ID) > 0 THEN 'ü' ELSE '' END 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, ROUND((ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost4'), 0) / 100000) + (ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost5'), 0) / 100000), 0) as CostBudget, ROUND(ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost7'), 0) / 100000, 0) as forecastcost, ROUND((ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'), 0) / 100000) - (ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost1'), 0) / 100000), 0) 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) / 100000 as ActualCost, ISNULL(dbo.getenterprisenumbervalue(Proj_ID, 'Task Enterprise Project Cost3'), 0) / 100000 as PlannedCost from Projectserver4586..MSP_Projects where Proj_ID = 1239 or PRoj_id = 993 --dbo.getenterprisecodevalue(Proj_ID, '24') = 'Yes'-- and not dbo.getenterprisecodevalue(Proj_ID, '11') LIKE '%Archive%'")
Dim dx As New DataSet
dx.ReadXmlSchema("c:\xml.xml")
ds.Tables(0).TableName = "Projects"
dx.Tables(0).TableName = "Item"
Dim xds As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "dailyrisklist 993")
xds.Tables(0).TableName = "Risks"
dx.Tables.Add(xds.Tables(0).Copy)
dx.Tables(1).TableName = "Risks"
Dim xds3 As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "dailyissuelist 993")
xds3.Tables(0).TableName = "Issues"
dx.Tables.Add(xds3.Tables(0).Copy)
dx.Tables(2).TableName = "Issues"
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)
Dim w As Worksheet
Dim dr As DataRow
'dx.Tables.Add("Item")
Dim i As Integer = 1
Dim ii As Integer
Dim iii As Integer = 1
For Each dr In ds.Tables(0).Rows
dr("Customer") = Mid(dr("Customer"), 1, dr("Customer").ToString.IndexOf("_"))
dr("Name") = Mid(dr("Name"), 1, dr("Name").ToString.LastIndexOf("."))
If iii <> ds.Tables(0).Rows.Count Then
ii = designer.Workbook.Worksheets.AddCopy("Fig 2")
End If
iii += 1
Next
iii = 1
For Each dr In ds.Tables(0).Rows
designer.Workbook.Worksheets(iii).Name = dr("Name")
iii += 1
Next
designer.Process(0, True)
Dim adr As DataRow
For Each dr In ds.Tables(0).Rows
dx.Tables("Item").Rows.Clear()
adr = dr
dx.Tables("Item").ImportRow(adr)
dx.Tables("Risks").Rows.Clear()
dx.Tables("Issues").Rows.Clear()
Dim dr2 As DataRow
Dim xds2 As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "dailyrisklist " & dr("Proj_ID"))
xds2.Tables(0).TableName = "Risks"
For Each dr2 In xds2.Tables("Risks").Rows
dr2("Description") = Regex.Replace(dr2("Description").ToString.Replace("
", Chr(13)).Replace("
", Chr(13)), "<(.|\n)*?>", String.Empty)
dr2("Mitigation") = Regex.Replace(dr2("Mitigation").ToString.Replace("
", Chr(13)).Replace("
", Chr(13)), "<(.|\n)*?>", String.Empty)
adr = dr2
dx.Tables("Risks").ImportRow(adr)
Next
xds2 = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "dailyissuelist " & dr("Proj_ID"))
xds2.Tables(0).TableName = "Issues"
For Each dr2 In xds2.Tables("Issues").Rows
dr2("Description") = Regex.Replace(dr2("Description").ToString.Replace("
", Chr(13)).Replace("
", Chr(13)), "<(.|\n)*?>", String.Empty)
dr2("Mitigation") = Regex.Replace(dr2("Mitigation").ToString.Replace("
", Chr(13)).Replace("
", Chr(13)), "<(.|\n)*?>", String.Empty)
adr = dr2
dx.Tables("Issues").ImportRow(adr)
Next
designer.SetDataSource(dx)
designer.Process(i, False)
designer.Workbook.Worksheets(i).AutoFitRows()
'designer.SetDataSource(xds)
'designer.Process(i, True)
'dx.Tables.Remove("Risks")
i += 1
Next
'designer.Workbook.Worksheets(0)
designer.Workbook.Worksheets(0).AutoFitRows()
designer.Save("SmartMarker2.xls", SaveType.OpenInBrowser, FileFormatType.Default, Me.Response)