Free Support Forum - aspose.com

Hyperlink using smartmarker

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)

Smart marker doesn’t work with hyperlinks. So you have to dynamically create those links with your code.