This document explains how to efficiently load large datasets in ReoGrid using lazy loading. Through concrete code examples, it introduces how to implement lazy loading.
Step 1: Initialize the Worksheet
First, initialize the worksheet and set up the user interface components.
public partial class LazyLoadPerformanceDemo : UserControl
{
private Worksheet worksheet;
public LazyLoadPerformanceDemo()
{
InitializeComponent();
}
public static int RECORD_COUNT = 1000000;
public static int COLUMN_COUNT = 12;
List<FlightLog> logs;
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
// Generate data
logs = FlightLogGenerator.GenerateFlightLogs(RECORD_COUNT);
}
}
Step 2: Generate Data
Create a method to generate a large dataset. In this example, flight log data is generated.
public class FlightLogGenerator
{
public static List<FlightLog> GenerateFlightLogs(int numRows = 10000)
{
Random rand = new Random();
DateTime startTime = DateTime.Now;
List<FlightLog> logs = new List<FlightLog>();
string[] weatherOptions = { "Sunny", "Cloudy", "Rain", "Snow" };
for (int i = 0; i < numRows; i++)
{
logs.Add(new FlightLog
{
Timestamp = startTime.AddMilliseconds(200 * i),
Speed = Math.Round(rand.NextDouble() * 180, 2),
Distance = Math.Round(rand.NextDouble() * 1000, 2),
Throttle = rand.Next(0, 101),
EngineBrake = rand.Next(0, 101),
Fuel = Math.Round(rand.NextDouble() * 100, 2),
DoorStatus = rand.Next(0, 2) == 1,
GPSLatitude = Math.Round(35.0 + rand.NextDouble() * 10, 6),
GPSLongitude = Math.Round(135.0 + rand.NextDouble() * 10, 6),
Weather = weatherOptions[rand.Next(weatherOptions.Length)]
});
}
return logs;
}
}
Step 3: Create a Data Source
Implement a data source class that provides data to the worksheet.
// Data source
public class FlightLogDataSource : IDataSource<FlightlogDataRecord>
{
public List<FlightLog> Logs { get; private set; }
private FlightlogDataRecord[] initedRecords;
public FlightLogDataSource(List<FlightLog> logs)
{
this.Worksheet = worksheet;
this.Logs = logs;
this.initedRecords = new FlightlogDataRecord[logs.Count];
}
public int RecordCount => Logs.Count;
public int ColumnCount => LazyLoadPerformanceDemo.COLUMN_COUNT;
public bool SuspendDataChangeEvent { get; set; }
public event EventHandler<DataSourceChangedEventArgs> OnInputDataChanged;
// Get data for the specified row
public FlightlogDataRecord GetRecord(int row)
{
FlightlogDataRecord record = initedRecords[row];
if (record == null)
{
record = new FlightlogDataRecord(this, row, Logs[row]);
}
// Return row data
return record;
}
}
// Data record
public class FlightlogDataRecord : IDataRecord
{
private FlightLogDataSource source;
private int row;
public FlightLog LogData { get; private set; }
public FlightlogDataRecord(FlightLogDataSource source, int row, FlightLog logData)
{
this.source = source;
this.row = row;
this.LogData = logData;
}
public object GetData(int columnIndex) {
switch (columnIndex)
{
case 0: return $"No. {row + 1}";
case 1: return LogData.Timestamp;
case 2: return LogData.Speed;
case 3: return LogData.Distance;
case 4: return LogData.Throttle;
case 5: return LogData.EngineBrake;
case 6: return LogData.Fuel;
case 7: return LogData.DoorStatus;
case 8: return LogData.GPSLatitude;
case 9: return LogData.GPSLongitude;
case 10: return LogData.Weather;
case 11: return $"=ROUND({new CellPosition(row, 4).ToAbsoluteAddress()} / {new CellPosition(row, 5).ToAbsoluteAddress()}, 1)";
}
return null;
}
}
Step 4: Load Data with Lazy Loading
Use the data source to load data into the worksheet in lazy loading mode to improve performance.
private void button1_Click(object sender, EventArgs e)
{
// Change the number of worksheet rows
worksheet.SetRows(logs.Count);
// Set the data source in lazy loading mode
worksheet.AddDataSource(
new RangePosition(0, 0, logs.Count, COLUMN_COUNT),
new FlightLogDataSource(logs),
// Enable lazy loading
DataSourceLoadMode.LazyLoading);
}
By following these steps, you can efficiently load large datasets into a ReoGrid worksheet using a data source and lazy loading mode.
Applying Borders and Cell Formatting
You can set borders and cell formatting when the GetRecord method is called. The following example uses the worksheet instance to set borders, etc.
public class FlightLogDataSource : IDataSource<FlightlogDataRecord>
{
public Worksheet Worksheet { get; private set; }
private FlightlogDataRecord[] initedRecords;
...
public FlightLogDataSource(Worksheet worksheet, List<FlightLog> logs)
{
this.Worksheet = worksheet;
this.Logs = logs;
this.initedRecords = new FlightlogDataRecord[logs.Count];
}
...
// Modify the GetRecord part as follows
public FlightlogDataRecord GetRecord(int row)
{
FlightlogDataRecord record = initedRecords[row];
if (record == null)
{
record = new FlightlogDataRecord(this, row, Logs[row]);
initedRecords[row] = record;
// Set row borders when the row is accessed for the first time
Worksheet.SetRangeBorders(row, 0, 1, ColumnCount, BorderPositions.Outside, RangeBorderStyle.BlackSolid);
Worksheet.SetRangeBorders(row, 0, 1, ColumnCount, BorderPositions.InsideVertical, RangeBorderStyle.GrayDotted);
}
// Return row data
return record;
}
...
}
Principles and Notes on Lazy Loading
Lazy loading is a technique for efficiently handling large datasets. When a cell is accessed for the first time (for rendering, formula reference, code query, etc.), data is loaded from the dataset. For example, if there are 1 million rows, only about 30 rows that are initially rendered are loaded. As you scroll, more data is loaded as needed.
Supports Formulas and Cell References
Lazy loading also supports formulas. Even if a cell is not rendered, if it is referenced by a formula, the corresponding cell will be loaded automatically when accessed.
Notes on Lazy Loading
Lazy loading is performed only once. If you delete and reload data in a worksheet that has already been loaded, the loaded state is not reset, so lazy loading will not work. Therefore, if you need to reload data, you should recreate the worksheet or take similar measures.