Auto Populate Smart Marker Data to Other Worksheets if Data is too Large

Possible Usage Scenarios

Sometimes, you want to auto-populate smart marker data to other worksheets if it is too large. Suppose, your data source has 1500000 records. These are too many records for a single worksheet, then you can move the rest of the records to the next worksheet. 

Auto-Populate Smart Marker Data to Other Worksheets if Data is too Large

The following sample code has a data source that has 21 records. We want to show only 15 records in one worksheet, then the rest of the records will automatically move to the second worksheet. Please note, the second worksheet should also have the same smart marker tag and you must call WorkbookDesigner.Process(sheetIndex, isPreserved) method for both sheets. Please see the output Excel file generated by the code for a reference.

Sample Code

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET

//Create employees data table
DataTable dt = new DataTable("Employees");
dt.Columns.Add("EmployeeID", typeof(int));

//Add rows inside the data table
dt.Rows.Add(1230);
dt.Rows.Add(1231);
dt.Rows.Add(1232);
dt.Rows.Add(1233);
dt.Rows.Add(1234);
dt.Rows.Add(1235);
dt.Rows.Add(1236);
dt.Rows.Add(1237);
dt.Rows.Add(1238);
dt.Rows.Add(1239);
dt.Rows.Add(1240);
dt.Rows.Add(1241);
dt.Rows.Add(1242);
dt.Rows.Add(1243);
dt.Rows.Add(1244);
dt.Rows.Add(1245);
dt.Rows.Add(1246);
dt.Rows.Add(1247);
dt.Rows.Add(1248);
dt.Rows.Add(1249);
dt.Rows.Add(1250);

//Create data reader from data table
DataTableReader dtReader = dt.CreateDataReader();

//Create empty workbook
Workbook wb = new Workbook();

//Access first worksheet and add smart marker in cell A1
Worksheet ws = wb.Worksheets[0];
ws.Cells["A1"].PutValue("&=Employees.EmployeeID");

//Add second worksheet and add smart marker in cell A1
wb.Worksheets.Add();
ws = wb.Worksheets[1];
ws.Cells["A1"].PutValue("&=Employees.EmployeeID");

//Create workbook designer
WorkbookDesigner wd = new WorkbookDesigner(wb);

//Set data source with data reader
wd.SetDataSource("Employees", dtReader, 15);

//Process smart marker tags in first and second worksheet
wd.Process(0, false);
wd.Process(1, false);

//Save the workbook
wb.Save("outputAutoPopulateSmartMarkerDataToOtherWorksheets.xlsx");