Memory Workbook

It is possible to create memory workbook rather than creating a screen control, especially when loading from an Excel file, modifying the worksheet content, then saving as new Excel file, it will be very useful. Memory workbook doesn’t have screen control instance and interface modules such as view-port controller, mouse and keyboard controller, it is more faster and lightweight than the screen control.

342

Figure: upload an Excel or CSV from browser and process the data into database

343

Figure: download an Excel or CSV that is generated in ASP.Form page

To create a memory workbook:

var workbook = ReoGridControl.CreateMemoryWorkbook();

To output an Excel into download stream:

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

To load an Excel file into memory workbook:

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

Available API

The API to load and save Excel, all method of worksheet to access data and styles are same as the screen component.

  • Worksheet Management API
    • CreateWorksheet / AddWorksheet
    • InsertWorksheet / RemoveWorksheet
    • CopyWorksheet / MoveWorksheet
    • GetWorksheetByName / GetWorksheetIndex
  • Worksheets collection
  • Load and Save template Excel file or RGF file
  • All workbook events
  • Internal operations exception notification

Unavailable API

Some API that memory workbook doesn’t support:

  • CurrentWorksheet property – Since memory workbook doesn’t have sheet tab to switch between multiple worksheet, there is no concept about ‘current activated worksheet’. Instead use the Worksheets collection property of workbook object to access worksheets.
  • Scroll methods and events – Memory workbook doesn’t have scroll bar components.
  • Appearance Style – Memory workbook doesn’t have appearance styles.
  • Text, Graphics and other UI-related Methods – Memory workbook doesn’t have graphics object like text font, method uses resource that requires graphics information will not available, e.g. FitWidthToCells and FitHeightToCells method.
  • Action-related methods – The methods DoAction, Undo, Redo and RepeatLastAction are not available for memory workbook. All operations to memory workbook are performed instantly and cannot be undone.

Use Memory workbook in web application

In a web application, ReoGrid memory workbook can work for handling spreadsheet data that is uploaded from web client, or generate an Excel file to make it downloadable.

344

Example: Create an Excel file and make it downloadable

Put the following code inside asp.net form class:

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

  // get worksheet 1 (by default, a workbook contains a default empty worksheet)
  var sheet1 = grid.Worksheets[0];

  // set data into cell A1 
  sheet1["A1"] = "Hello World";

  // set data into cell A2, B2, C2, D2 and E2
  sheet1["A2"] = new object[] {
    10, // B2
    20, // C2
    "=B2+C2", // D2
    "=SUM(B2:D2)", // E2
  };

  // clear current response
  Response.Clear();

  // set download file name
  Response.AddHeader("Content-Disposition", "attachment; filename=SampleData.xlsx");
  // change response stream to binary
  Response.ContentType = "application/octet-stream";

  // generate Excel file and put it into output stream
  grid.Save(Response.OutputStream, unvell.ReoGrid.IO.FileFormat.Excel2007);

  // flush all data in output stream
  Response.Output.Flush();
  // end current response
  Response.End();
}

Next: Worksheet