Modify Documents in Code

• Sep 27, 2023
• 2 minutes to read

This topic describes how to modify a document in the DevExpress.Web.ASPxSpreadsheet.

Follow the steps below to modify a document on the server side:

1. Call the client PerformCallback method to initiate a callback to the server side to access the document model.

2. Use the Spreadsheet Document API in the Callback event handler to modify the document on the server side.

First, use the Document property to obtain an IWorkbook. This interface encapsulates the spreadsheet document API.

C#

IWorkbook workbook = ASPxSpreadsheet1.Document;

Use the following APIs to access a worksheet:

• The Worksheets property - Provides access to a worksheet by its index:

  C#

  Worksheet worksheet = workbook.Worksheets[0];
  

• The ActiveWorksheet property - Provides access to an active worksheet:

  C#

  Worksheet worksheet = workbook.Worksheets.ActiveWorksheet; 
  

Note

You should reference the DevExpress.Office.24.1.Core assembly.

The Rows and Columns properties allow you to access rows or columns in a worksheet (Rows and Columns).

You can access cell ranges to modify a document’s appearance, formulas, and cell formats. Use the Worksheet object to access the cell range(s):

C#

Range rangeA1B5 = worksheet["A1:B5"];

Example

function OnCommandExecuted(s, e) {
    var command = e.item.name;
    Spreadsheet.PerformCallback(command)
}

<dx:ASPxRibbon runat="server" ID="ASPxRibbon1" ShowFileTab="false" ShowTabs="false" OneLineMode="true">
    <ClientSideEvents CommandExecuted="OnCommandExecuted" />
    <Tabs>
        <dx:RibbonTab>
            <Groups>
                <dx:RibbonGroup  >
                    <Items>
                        <dx:RibbonButtonItem Text="Apply formatting" Name="applyFormatting" />
                        <dx:RibbonButtonItem Text="Insert link" Name="insertLink" />
                        <dx:RibbonButtonItem Text="Draw Borders" Name="drawBorders" />
                        <dx:RibbonButtonItem Text="Show total" Name="showTotal" />
                    </Items>
                </dx:RibbonGroup>
            </Groups>
        </dx:RibbonTab>
    </Tabs>
</dx:ASPxRibbon>
<dx:ASPxSpreadsheet runat="server" ID="ASPxSpreadsheet1" ClientInstanceName="Spreadsheet" 
    OnCallback="ASPxSpreadsheet1_Callback" />

protected void ASPxSpreadsheet1_Callback(object sender, DevExpress.Web.CallbackEventArgsBase e) {
    ASPxSpreadsheet spreadSheet = sender as ASPxSpreadsheet;
    IWorkbook workbook = spreadSheet.Document;
    Worksheet worksheet = workbook.Worksheets[0];
    switch (e.Parameter) {
        case "applyFormatting":
            CellRange priceRange = worksheet.Range["C2:C15"];
            Formatting rangeFormatting = priceRange.BeginUpdateFormatting();
            rangeFormatting.Font.Color = Color.SandyBrown;
            rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
            rangeFormatting.Fill.BackgroundColor = Color.PaleGoldenrod;
            rangeFormatting.NumberFormat = "$0.0#";
            rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
            rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            priceRange.EndUpdateFormatting(rangeFormatting);
            break;
        case "insertLink":
            worksheet.Columns["G"].WidthInPixels = 180;
            CellRange cell1 = worksheet.Cells["G4"];
            cell1.Fill.BackgroundColor = Color.WhiteSmoke;
            worksheet.Hyperlinks.Add(cell1, "https://docs.devexpress.com/OfficeFileAPI/14912/spreadsheet-document-api", true, "Spreadsheet Document API");
            break;
        case "drawBorders":
            CellRange tableRange = worksheet.Range["A2:E16"];
            tableRange.Borders.SetAllBorders(Color.RosyBrown, BorderLineStyle.Hair);
            break;
        case "showTotal":
            CellRange cell2 = worksheet.Cells["E16"];
            cell2.Formula = "=SUBTOTAL(9,E2:E15)";
            CellRange cell3 = worksheet.Cells["A16"];
            cell3.Formula = "SUBTOTAL(103,A2:A15)";
            CellRange cell4 = worksheet.Cells["D16"];
            cell4.Value = "Total amount";
            break;
    }
}