Use the Excel Export API to Create a Worksheet Cell and Set Its Value

• Sep 19, 2023
• 4 minutes to read

The example below demonstrates how to add cells to a worksheet. To do this, call the IXlRow.CreateCell method of the row where you wish to create a cell. If required, pass the following parameter: the zero-based index of the column where the new cell should be located. As a result, the cell will be created at the intersection of the specified row and column.

Each cell in a worksheet can contain a single piece of data - the cell value specified by the XlVariantValue object. To set a cell value, assign the required value to the IXlCell.Value property. A cell value can be of one of the following types: numeric, text, Boolean or error. Data values of these types can have various display formats. For example, a numeric value can be displayed as a decimal number, a percentage or currency value, a date or time value, etc. To specify the cell number format, use the IXlCell.Formatting property.

Note

When you finish working with the IXlCell object, call the Dispose method to release all the resources used by the object. Otherwise, generated content is not written to the output file. You can also modify the IXlCell object within the using statement (Using block in Visual Basic).

View Example

GeneralActions.cs

// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet())
{
    // Create the column A and set its width.
    using (IXlColumn column = sheet.CreateColumn())
    {
        column.WidthInPixels = 150;
    }

    // Create the first row.
    using (IXlRow row = sheet.CreateRow())
    {
        // Create the cell A1 and set its value.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = "Numeric value:";
        }

        // Create the cell B1 and assign the numeric value to it.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = 123.45;
        }
    }

    // Create the second row.
    using (IXlRow row = sheet.CreateRow())
    {
        // Create the cell A2 and set its value.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = "Text value:";
        }

        // Create the cell B2 and assign the text value to it.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = "abc";
        }
    }

    // Create the third row.
    using (IXlRow row = sheet.CreateRow())
    {
        // Create the cell A3 and set its value.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = "Boolean value:";
        }

        // Create the cell B3 and assign the boolean value to it.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = true;
        }
    }

    // Create the fourth row.
    using (IXlRow row = sheet.CreateRow())
    {

        // Create the cell A4 and set its value.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = "Error value:";
        }

        // Create the cell B4 and assign an error value to it.
        using (IXlCell cell = row.CreateCell())
        {
            cell.Value = XlVariantValue.ErrorName;
        }
    }
}

GeneralActions.vb

' Create a worksheet.
Using sheet As IXlSheet = document.CreateSheet()
    ' Create the column A and set its width. 
    Using column As IXlColumn = sheet.CreateColumn()
        column.WidthInPixels = 150
    End Using

    ' Create the first row.
    Using row As IXlRow = sheet.CreateRow()

        ' Create the cell A1 and set its value.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = "Numeric value:"
        End Using

        ' Create the cell B1 and assign the numeric value to it.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = 123.45
        End Using
    End Using

    ' Create the second row.
    Using row As IXlRow = sheet.CreateRow()

        ' Create the cell A2 and set its value.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = "Text value:"
        End Using

        ' Create the cell B2 and assign the text value to it.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = "abc"
        End Using
    End Using

    ' Create the third row.
    Using row As IXlRow = sheet.CreateRow()

        ' Create the cell A3 and set its value.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = "Boolean value:"
        End Using

        ' Create the cell B3 and assign the boolean value to it.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = True
        End Using
    End Using

    ' Create the fourth row.
    Using row As IXlRow = sheet.CreateRow()

        ' Create the cell A4 and set its value.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = "Error value:"
        End Using

        ' Create the cell B4 and assign an error value to it.
        Using cell As IXlCell = row.CreateCell()
            cell.Value = XlVariantValue.ErrorName
        End Using
    End Using
End Using

The image below shows the result (the workbook is opened in Microsoft® Excel®).