Memory Workbook

Creating a memory workbook is a viable option, particularly beneficial when the task involves loading from an Excel file, editing the worksheet contents, and then saving it as a new Excel file. Unlike the traditional approach that involves creating a screen control, a memory workbook operates without a screen control instance and lacks interface modules such as a viewport controller, and mouse and keyboard controllers. This makes it significantly faster and more lightweight compared to screen control-based implementations.

Figure: Uploading an Excel or CSV file from a browser and processing the data into a database.

Figure: Downloading an Excel or CSV file generated on an ASP.NET Form page.

To initialize a memory workbook, the following syntax is used:

var workbook = ReoGridControl.CreateMemoryWorkbook();

To save the workbook as an Excel file to a download stream, use:

workbook.Save(Response.OutputStream, unvell.ReoGrid.IO.FileFormat.Excel2007);

To load an Excel file into a memory workbook, the following code snippet can be employed:

workbook.Load(@"C:\MyTemplate.xlsx", FileFormat.Excel2007);

Available APIs

The APIs for loading and saving Excel files, along with all methods for accessing data and styles in a worksheet, remain consistent with those used in the screen component.

• Worksheet Management API
  • Methods for creating and adding worksheets: CreateWorksheet, AddWorksheet
  • Methods for inserting and removing worksheets: InsertWorksheet, RemoveWorksheet
  • Methods for copying and moving worksheets: CopyWorksheet, MoveWorksheet
  • Methods for retrieving worksheets: GetWorksheetByName, GetWorksheetIndex
• Worksheets Collection
  • Access to the collection of worksheets within the workbook
• Load and Save Operations
  • Capability to load and save template Excel files or RGF files
• Workbook Events
  • Access to all workbook-related events
• Internal Operation Exception Notifications
  • Notifications for exceptions that occur during internal operations

Unavailable APIs

Certain APIs are not supported by the memory workbook due to its nature:

• CurrentWorksheet Property - As the memory workbook lacks a sheet tab for switching between multiple worksheets, the concept of a 'current activated worksheet' does not apply. Worksheets should be accessed via the Worksheets collection property of the workbook object instead.
• Scroll Methods and Events - The memory workbook does not include scrollbar components.
• Appearance Style
  • No appearance styles are available for the memory workbook.
• UI-related Methods
  • Methods related to text, graphics, and other UI elements are unavailable, as the memory workbook does not possess a graphics object. This includes methods like FitWidthToCells and FitHeightToCells, which require graphical information.
• Action-related Methods
  • Methods for performing actions such as DoAction, Undo, Redo, and RepeatLastAction are not available. All operations on a memory workbook are executed instantly and cannot be reversed.

Utilizing Memory Workbook in Web Applications

In web applications, the ReoGrid memory workbook is ideally suited for processing spreadsheet data uploaded from a web client or for generating Excel files for download.

Example: Creating and Downloading an Excel File

In an ASP.NET application, the following code snippet can be implemented within a page class to generate an Excel file and prompt the user to download it:

protected void Page_Load(object sender, EventArgs e)
{
  // Create an instance of a memory workbook
  var grid = ReoGridControl.CreateMemoryWorkbook();

  // Access the first worksheet by default (a new workbook includes one empty worksheet)
  var sheet1 = grid.Worksheets[0];

  // Populate cell A1 with text
  sheet1["A1"] = "Hello World";

  // Populate cells A2 to E2 with data and formulas
  sheet1["A2"] = new object[] {
    10, // A2
    20, // B2
    "=A2+B2", // C2
    "=SUM(A2:C2)", // D2
  };
  
  // Prepare the response for file download
  Response.Clear();
  Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
  Response.AddHeader("Content-Disposition", "attachment; filename=SampleData.xlsx");

  // Save the workbook to the response stream in Excel 2007 format
  grid.Save(Response.OutputStream, unvell.ReoGrid.IO.FileFormat.Excel2007);

  // Flush the response stream and end the response
  Response.Flush();
  Response.End();
}