Free Support Forum - aspose.com

PRoblem with smartmarkers and copied rows

Hi,

I am copying rows with smart markers into a worksheet, and then processing them, however it doesn't insert the data, even when there is definitely data in the dataset with the right tablename

a snippet of my code is below, and I have attached the template:

Dim x As Integer = designer.Workbook.Worksheets.AddCopy("Competency")

Dim r As Range = designer.Workbook.Worksheets(x).Cells.CreateRange("A5", "V6")

Dim ds3 As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "GetOutlineCodeValues 14")

Dim sdr As DataRow

Dim row1 As Integer = 5

Dim row2 As Integer = 6

For Each sdr In ds3.Tables(0).Rows

Dim r2 As Range = designer.Workbook.Worksheets(1).Cells.CreateRange("A" & row1, "V6" & row2)

r2.Copy(r)

Dim ds2 As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "pGetCompetencyDailyReport '" & sdr("FieldValue") & "'")

ds2.Tables(0).TableName = "Projects"

designer.SetDataSource(ds2)

designer.Process(1, False)

row1 = row1 + IIf(ds2.Tables(0).Rows.Count = 0, 1, ds2.Tables(0).Rows.Count) + 1

row2 = row2 + IIf(ds2.Tables(0).Rows.Count = 0, 1, ds2.Tables(0).Rows.Count) + 1

Next

What happens if you change your code to:

Dim x As Integer = designer.Workbook.Worksheets.AddCopy("Competency")

Dim r As Range = designer.Workbook.Worksheets(x).Cells.CreateRange("A5", "V6")

Dim ds3 As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "GetOutlineCodeValues 14")

Dim sdr As DataRow

Dim row1 As Integer = 5

Dim row2 As Integer = 6

sdr = ds3.Tables(0).Rows(0)

Dim r2 As Range = designer.Workbook.Worksheets(1).Cells.CreateRange("A" & row1, "V6" & row2)

r2.Copy(r)

Dim ds2 As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationSettings.AppSettings("DB"), CommandType.Text, "pGetCompetencyDailyReport '" & sdr("FieldValue") & "'")

ds2.Tables(0).TableName = "Projects"

designer.SetDataSource(ds2)

designer.Process(1, False)

When Process is caused, all smart markers in this worksheet will be clear. So second call of r2.Copy(r) won't copy data.

That works fine,

but the problem with calling process more than once shouldn't happen, because I am pasting the smartmarkers into the sheet again after the call to process

When you call process method, all smart markers is processed, right? After being process, sure they should be removed.

So you have to use other methods to handle this issue. I think you can put those smart markers in another worksheet and copy them from another worksheet after processing.

That’s exactly what I am doing, in the first 2 lines inside the for loop, but it still clears out the markers

Please add following line of code after calling process method:

designer.Process(1, False)

designer.ClearDataSource()

Smart marker doesn't process on DataTables with duplicate name. If there are 2 DataTables with same name are set as WorkbookDesigner datasource, only the first DataTable will be processed.