PRoblem with smartmarkers and copied rows

Aspose.Cells Product Family
1

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

2

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.

3

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

4

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.

5

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

6

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.