XLSX: Programmatically edit an existing XLSX document using low-level editor

In This Topic

The XlsxDocumentEditor class is a low-level editor for existing XLSX document, i.e. class allows to edit an XLSX document in terms of Open XML document.
The XlsxDocumentEditor class is designed for fast and easy development of report/invoice generator based on XLSX document template.

If you want to generate XLSX report/invoice, you need to do the following steps:

Open Microsoft Excel:
- Create new XLSX document or open existing XLSX document -XLSX document will be used as a template for report/invoice.
- Change content of XLSX document. Document must contain all necessary markup except dynamic data.Places for dynamic data must be marked with unique text constants.
In the code:
- Open template document using XlsxDocumentEditor class andadd dynamic data (replace text, insert table rows, replace images, etc) to the document.
- Save changed document to an XLSX file using OpenXmlDocumentEditor.Save method orexport changed document using OpenXmlDocumentEditor.Export method to any format that is supported by VintaSoft Imaging SDK (PDF, PDF/A, TIFF, ...).

Report Generator Demo project contains several examples,which demonstrate how to generate reports, which are based on XLSX template.

XlsxDocumentEditor class

XlsxDocumentEditor class allows to:

open an existing XLSX document from a file or stream
get access to the sheets of XLSX document - XlsxDocumentEditor.Sheets
get access to the images of XLSX document - OpenXmlDocumentEditor.Images
get access to the charts of XLSX document - OpenXmlDocumentEditor.Charts
get access to the properties (title, creator, etc.) of XLSX document - OpenXmlDocumentEditor.DocumentProperties
save XLSX document to a file or stream - OpenXmlDocumentEditor.Save
export XLSX document to any format that is supported by VintaSoft Imaging SDK (PDF, PDF/A, TIFF, ...) - OpenXmlDocumentEditor.Export

OpenXmlDocumentElement class

OpenXmlDocumentElement represents a base class for all elements in a DOCX or XLSX document and allows to:

get or set text of element - OpenXmlDocumentElement.Text
find text in element - OpenXmlDocumentElement.FindText
replace text in element - OpenXmlDocumentElement.ReplaceText
set text properties in element - OpenXmlDocumentElement.SetTextProperties
set fill color of an element - OpenXmlDocumentElement.SetFillColor
remove this element from document - OpenXmlDocumentElement.Remove
insert copy of this element after self - OpenXmlDocumentElement.InsertCopyAfterSelf
insert copy of this element before self - OpenXmlDocumentElement.InsertCopyBeforeSelf
copy this element - OpenXmlDocumentElement.Clone
access parent element of this element - OpenXmlDocumentElement.Parent
access child elements of this element - OpenXmlDocumentElement.ChildElements

Here is C#/VB.NET code that shows how to find and replace text in XLSX document (example uses template document FindAndReplaceText_template.xlsx):

public static void XlsxFindAndReplaceTextExample()
{
    string templateFilename = "FindAndReplaceText_template.xlsx";
    string outFilename = "FindAndReplaceText.xlsx";
    string outPdfFilename = "FindAndReplaceText.pdf";

    // create XlsxDocumentEditor that allows to edit file "FindAndReplaceText_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet sheet = editor.Sheets[0];

        // replace first occurrence of text "[field1]" by text "value1"
        sheet["[field1]"] = "value1";

        // replace all occurrences of text "[field2]" by text "value2"
        sheet.ReplaceText("[field2]", "value2");

        // find text content that corresponds to the text "[field3]"
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextContent field3Content = sheet.FindText("[field3]");
        // change text in found text content
        field3Content.Text = "value3";

        // replace text "[multiline_field]" by multiline text
        sheet["[multiline_field]"] = "\nline1\nline2\nline3";

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

VB.NET

Public Shared Sub XlsxFindAndReplaceTextExample()
    Dim templateFilename As String = "FindAndReplaceText_template.xlsx"
    Dim outFilename As String = "FindAndReplaceText.xlsx"
    Dim outPdfFilename As String = "FindAndReplaceText.pdf"

    ' create XlsxDocumentEditor that allows to edit file "FindAndReplaceText_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet = editor.Sheets(0)

        ' replace first occurrence of text "[field1]" by text "value1"
        sheet("[field1]") = "value1"

        ' replace all occurrences of text "[field2]" by text "value2"
        sheet.ReplaceText("[field2]", "value2")

        ' find text content that corresponds to the text "[field3]"
        Dim field3Content As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextContent = sheet.FindText("[field3]")
        ' change text in found text content
        field3Content.Text = "value3"

        ' replace text "[multiline_field]" by multiline text
        sheet("[multiline_field]") = vbLf & "line1" & vbLf & "line2" & vbLf & "line3"

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

OpenXmlTextContent class

OpenXmlTextContent represents the text content of DOCX or XLSX document and allows to:

replace text - OpenXmlTextContent.Text
set text properties - OpenXmlTextContent.SetTextProperties

Here is C#/VB.NET code that shows how to change text properties of text content (example uses template document ChangeTextProperties_template.xlsx):

public static void XlsxChangeTextPropertiesExample()
{
    string templateFilename = "ChangeTextProperties_template.xlsx";
    string outFilename = "ChangeTextProperties.xlsx";
    string outPdfFilename = "ChangeTextProperties.pdf";

    // create XlsxDocumentEditor that allows to edit file "ChangeTextProperties_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet sheet = editor.Sheets[0];

        // set text color
        sheet.FindText("COLOR").Substring(0, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Red));
        sheet.FindText("COLOR").Substring(2, 1).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Green));
        sheet.FindText("COLOR").Substring(3, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Blue));

        // set text "bold text" as bold text
        sheet.FindText("bold text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.BoldText);

        // set text "italic text" as italic text
        sheet.FindText("italic text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.ItalicText);

        // set text "underline text" as underline text
        sheet.FindText("underline text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.UnderlineText);

        // set text "strike text" as striked out text
        sheet.FindText("strike text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.StrikeText);

        // change font size
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties setTextSize = 
            new Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties();
        setTextSize.FontSize = 16;
        sheet.FindText("text with size 16pt").Substring(0, 4).SetTextProperties(setTextSize);

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

/// <summary>
/// Creates the color text properties.
/// </summary>
/// <param name="color">The color.</param>
private static Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties CreateColorTextProperties(System.Drawing.Color color)
{
    Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties result =
        new Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties();
    result.Color = color;
    return result;
}

VB.NET

Public Shared Sub XlsxChangeTextPropertiesExample()
    Dim templateFilename As String = "ChangeTextProperties_template.xlsx"
    Dim outFilename As String = "ChangeTextProperties.xlsx"
    Dim outPdfFilename As String = "ChangeTextProperties.pdf"

    ' create XlsxDocumentEditor that allows to edit file "ChangeTextProperties_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet = editor.Sheets(0)

        ' set text color
        sheet.FindText("COLOR").Substring(0, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Red))
        sheet.FindText("COLOR").Substring(2, 1).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Green))
        sheet.FindText("COLOR").Substring(3, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Blue))

        ' set text "bold text" as bold text
        sheet.FindText("bold text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.BoldText)

        ' set text "italic text" as italic text
        sheet.FindText("italic text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.ItalicText)

        ' set text "underline text" as underline text
        sheet.FindText("underline text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.UnderlineText)

        ' set text "strike text" as striked out text
        sheet.FindText("strike text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.StrikeText)

        ' change font size
        Dim setTextSize As New Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties()
        setTextSize.FontSize = 16
        sheet.FindText("text with size 16pt").Substring(0, 4).SetTextProperties(setTextSize)

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

''' <summary>
''' Creates the color text properties.
''' </summary>
''' <param name="color">The color.</param>
Private Shared Function CreateColorTextProperties(color As System.Drawing.Color) As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties
    Dim result As New Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties()
    result.Color = color
    Return result
End Function

XlsxDocumentSheet class

XlsxDocumentSheet represents the sheet of XLSX document and allows to:

access rows of sheet - OpenXmlDocumentElement.ChildElements
find sheet cell by name (A1 format) - XlsxDocumentSheet.FindCellByName
find sheet cell by column and row number - XlsxDocumentSheet.FindCell
find row that contains specified text - OpenXmlDocumentTable.FindRow
find cell that contains specified text - OpenXmlDocumentTable.FindCell
set borders like in sample cell - OpenXmlDocumentTable.SetOutsideBorder
get or set sheet name - XlsxDocumentSheet.Name

XlsxDocumentSheetRow class

XlsxDocumentSheetRow represents the row of XLSX sheet and allows to:

access cells of row - OpenXmlDocumentElement.ChildElements
get row number - XlsxDocumentSheetRow.RowNumber
set row height - OpenXmlDocumentTableRow.Height
find cell that contains text - XlsxDocumentSheetRow.FindCell
set row borders like in sample cell - OpenXmlDocumentTableRow.SetBorder
set fill color of row - OpenXmlDocumentElement.SetFillColor
insert copy of row after self - OpenXmlDocumentElement.InsertCopyAfterSelf
insert copy of row before self - OpenXmlDocumentElement.InsertCopyBeforeSelf
copy row - XlsxDocumentSheetRow.Clone
remove row from sheet - OpenXmlDocumentElement.Remove

Here is C#/VB.NET code that shows how to fill data table (example uses template document CopyTableRow_template.xlsx):

public static void XlsxCopyTableRowExample()
{
    string templateFilename = "CopyTableRow_template.xlsx";
    string outFilename = "CopyTableRow.xlsx";
    string outPdfFilename = "CopyTableRow.pdf";

    // create XlsxDocumentEditor that allows to edit file "CopyTableRow_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet sheet = editor.Sheets[0];

        // find  row that contains text "[cell1]"
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow templateRow = sheet.FindRow("[cell1]");

        // create an array that contains colors
        System.Drawing.Color[] colors = new System.Drawing.Color[] {
            System.Drawing.Color.Red,
            System.Drawing.Color.Green,
            System.Drawing.Color.Blue,
            System.Drawing.Color.Orange,
            System.Drawing.Color.Yellow
        };
        // for each color
        for (int i = 0; i < colors.Length; i++)
        {
            // insert copy of template row before template row
            Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow rowCopy = 
                (Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow)templateRow.InsertCopyBeforeSelf();

            // set row data
            rowCopy[0].Text = string.Format("Copy {0} ({1})", i, colors[i]);
            rowCopy["[cell1]"] = string.Format("cell data {0}", i);

            // set cell colors
            rowCopy[1].SetFillColor(colors[i]);
            rowCopy[2].SetFillColor(colors[i]);

            // if color has odd index in colors array
            if (i % 2 == 1)
            {
                // set row height to 10mm
                rowCopy.Height = Vintasoft.Imaging.Utils.UnitOfMeasureConverter.ConvertToPoints(10, Vintasoft.Imaging.UnitOfMeasure.Millimeters);
            }
        }

        // remove template row
        templateRow.Remove();

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

VB.NET

Public Shared Sub XlsxCopyTableRowExample()
    Dim templateFilename As String = "CopyTableRow_template.xlsx"
    Dim outFilename As String = "CopyTableRow.xlsx"
    Dim outPdfFilename As String = "CopyTableRow.pdf"

    ' create XlsxDocumentEditor that allows to edit file "CopyTableRow_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet = editor.Sheets(0)

        ' find  row that contains text "[cell1]"
        Dim templateRow As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow = sheet.FindRow("[cell1]")

        ' create an array that contains colors
        Dim colors As System.Drawing.Color() = New System.Drawing.Color() {System.Drawing.Color.Red, System.Drawing.Color.Green, System.Drawing.Color.Blue, System.Drawing.Color.Orange, System.Drawing.Color.Yellow}
        ' for each color
        For i As Integer = 0 To colors.Length - 1
            ' insert copy of template row before template row
            Dim rowCopy As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow = DirectCast(templateRow.InsertCopyBeforeSelf(), Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow)

            ' set row data
            rowCopy(0).Text = String.Format("Copy {0} ({1})", i, colors(i))
            rowCopy("[cell1]") = String.Format("cell data {0}", i)

            ' set cell colors
            rowCopy(1).SetFillColor(colors(i))
            rowCopy(2).SetFillColor(colors(i))

            ' if color has odd index in colors array
            If i Mod 2 = 1 Then
                ' set row height to 10mm
                rowCopy.Height = Vintasoft.Imaging.Utils.UnitOfMeasureConverter.ConvertToPoints(10, Vintasoft.Imaging.UnitOfMeasure.Millimeters)
            End If
        Next

        ' remove template row
        templateRow.Remove()

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

Here is C#/VB.NET code that shows how to set table borders like in sample table cell (example uses template document SetTableBorders_template.xlsx):

public static void XlsxSetTableBordersExample()
{
    string templateFilename = "SetTableBorders_template.xlsx";
    string outFilename = "SetTableBorders.xlsx";
    string outPdfFilename = "SetTableBorders.pdf";

    // create XlsxDocumentEditor that allows to edit file "SetTableBorders_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet sheet = editor.Sheets[0];

        // get cell border templates
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell boldBorderTemplate = 
            sheet.FindCell("[bold]");
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell colorBorderTemplate =
            sheet.FindCell("[color]");

        // set borders from template cells
        sheet.FindCell("[bold_cell]").SetBorder(boldBorderTemplate);
        sheet.FindCell("[color_cell]").SetBorder(colorBorderTemplate);

        // set outside border inside table
        sheet.SetOutsideBorder(sheet.FindCell("[color_first]"), sheet.FindCell("[color_last]"), colorBorderTemplate);
        sheet.SetOutsideBorder(sheet.FindCell("[bold_first]"), sheet.FindCell("[bold_last]"), boldBorderTemplate);

        // remove border template table
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement removingRow = boldBorderTemplate.Row.Prev;
        for (int i = 0; i < 5; i++)
        {
            Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement row = removingRow;
            removingRow = removingRow.Next;
            row.Remove();
        }

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

VB.NET

Public Shared Sub XlsxSetTableBordersExample()
    Dim templateFilename As String = "SetTableBorders_template.xlsx"
    Dim outFilename As String = "SetTableBorders.xlsx"
    Dim outPdfFilename As String = "SetTableBorders.pdf"

    ' create XlsxDocumentEditor that allows to edit file "SetTableBorders_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet = editor.Sheets(0)

        ' get cell border templates
        Dim boldBorderTemplate As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell = sheet.FindCell("[bold]")
        Dim colorBorderTemplate As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell = sheet.FindCell("[color]")

        ' set borders from template cells
        sheet.FindCell("[bold_cell]").SetBorder(boldBorderTemplate)
        sheet.FindCell("[color_cell]").SetBorder(colorBorderTemplate)

        ' set outside border inside table
        sheet.SetOutsideBorder(sheet.FindCell("[color_first]"), sheet.FindCell("[color_last]"), colorBorderTemplate)
        sheet.SetOutsideBorder(sheet.FindCell("[bold_first]"), sheet.FindCell("[bold_last]"), boldBorderTemplate)

        ' remove border template table
        Dim removingRow As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement = boldBorderTemplate.Row.Prev
        For i As Integer = 0 To 4
            Dim row As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement = removingRow
            removingRow = removingRow.[Next]
            row.Remove()
        Next

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

XlsxDocumentSheetCell class

XlsxDocumentSheetCell represents the cell of XLSX sheet and allows to:

get cell name in A1 format - XlsxDocumentSheetCell.Name
get column number of cell - XlsxDocumentSheetCell.ColumnNumber
get row number of cell - XlsxDocumentSheetCell.RowNumber
get or set the text that is stored in cell - OpenXmlDocumentElement.Text
get or set the number that is stored in cell - XlsxDocumentSheetCell.Number
get or set the formula, which is used to calculate value of cell - XlsxDocumentSheetCell.Formula
get or set to style index of cell - XlsxDocumentSheetCell.StyleIndex
set cell borders like in sample cell - XlsxDocumentSheetCell.SetBorder
set fill color of cell - XlsxDocumentSheetCell.SetFillColor
set text properties of cell - XlsxDocumentSheetCell.SetTextProperties

Here is C#/VB.NET code that shows how to find cell by reference and set cell formula (example uses template document XlsxCell_template.xlsx):

public static void XlsxCellExample()
{
    string templateFilename = "XlsxCell_template.xlsx";
    string outFilename = "XlsxCell.xlsx";
    string outPdfFilename = "XlsxCell.pdf";

    // create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet sheet = editor.Sheets[0];

        // find cell by reference (A1 format) and set cell fill color
        sheet.FindCellByName("B3").SetFillColor(System.Drawing.Color.Red);
        sheet.FindCellByName("D3").SetFillColor(System.Drawing.Color.Yellow);
        sheet.FindCellByName("C4").SetFillColor(System.Drawing.Color.Green);
        sheet.FindCellByName("D5").SetFillColor(System.Drawing.Color.Blue);
        sheet.FindCellByName("B5").SetFillColor(System.Drawing.Color.Pink);

        // set cell formula
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheetCell cellD16 = sheet.FindCellByName("D8");
        cellD16.Formula = "SUM(B8,C8)";
        cellD16.Number = sheet.FindCellByName("B8").Number + sheet.FindCellByName("C8").Number;

        // find cell that contains text "[cell_name]"
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheetCell cell = sheet.FindCell("[cell_name]");
        // set cell text to cell name in A1 format
        cell.Text = cell.Name;

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

VB.NET

Public Shared Sub XlsxCellExample()
    Dim templateFilename As String = "XlsxCell_template.xlsx"
    Dim outFilename As String = "XlsxCell.xlsx"
    Dim outPdfFilename As String = "XlsxCell.pdf"

    ' create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet = editor.Sheets(0)

        ' find cell by reference (A1 format) and set cell fill color
        sheet.FindCellByName("B3").SetFillColor(System.Drawing.Color.Red)
        sheet.FindCellByName("D3").SetFillColor(System.Drawing.Color.Yellow)
        sheet.FindCellByName("C4").SetFillColor(System.Drawing.Color.Green)
        sheet.FindCellByName("D5").SetFillColor(System.Drawing.Color.Blue)
        sheet.FindCellByName("B5").SetFillColor(System.Drawing.Color.Pink)

        ' set cell formula
        Dim cellD16 As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheetCell = sheet.FindCellByName("D8")
        cellD16.Formula = "SUM(B8,C8)"
        cellD16.Number = sheet.FindCellByName("B8").Number + sheet.FindCellByName("C8").Number

        ' find cell that contains text "[cell_name]"
        Dim cell As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheetCell = sheet.FindCell("[cell_name]")
        ' set cell text to cell name in A1 format
        cell.Text = cell.Name

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

OpenXmlDocumentImage class

OpenXmlDocumentImage represents an image of DOCX or XLSX document:

get image as VintasoftImage - OpenXmlDocumentImage.GetImage
set image from VintasoftImage - OpenXmlDocumentImage.SetImage

Here is C#/VB.NET code that shows how to set barcode image in XLSX document (example uses template document SetBarcodeImage_template.xlsx):

public static void XlsxSetBarcodeImageExample()
{
    string templateFilename = "SetBarcodeImage_template.xlsx";
    string outFilename = "SetBarcodeImage.xlsx";
    string outPdfFilename = "SetBarcodeImage.pdf";

    // create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet sheet = editor.Sheets[0];

        // find cell with text "Invoice number:"
        Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheetCell cell = sheet.FindCell("Invoice number:");

        // get barcode value as text of next row after "Invoice number:" cell
        string barcodeValue = sheet.FindCell(cell.ColumnNumber, cell.RowNumber + 1).Text;

        // create and setup barcode generator
        Vintasoft.Barcode.BarcodeWriter barcodeGenerator = new Vintasoft.Barcode.BarcodeWriter();
        barcodeGenerator.Settings.Barcode = Vintasoft.Barcode.BarcodeType.QR;
        barcodeGenerator.Settings.SetWidth(200);
        barcodeGenerator.Settings.Value = barcodeValue;

        // generate barcode image
        using (Vintasoft.Imaging.VintasoftImage barcodeImage =
            new Vintasoft.Imaging.VintasoftImage(barcodeGenerator.GetBarcodeAsVintasoftBitmap(), true))
        {
            // crop barcode to the rectangular image
            barcodeImage.Crop(new System.Drawing.Rectangle(0, 0, barcodeImage.Width, barcodeImage.Width));

            // set barcode image to the DOCX image at index 0
            editor.Images[0].SetImage(barcodeImage);
        }

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

VB.NET

Public Shared Sub XlsxSetBarcodeImageExample()
    Dim templateFilename As String = "SetBarcodeImage_template.xlsx"
    Dim outFilename As String = "SetBarcodeImage.xlsx"
    Dim outPdfFilename As String = "SetBarcodeImage.pdf"

    ' create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheet = editor.Sheets(0)

        ' find cell with text "Invoice number:"
        Dim cell As Vintasoft.Imaging.Office.OpenXml.Editor.Xlsx.XlsxDocumentSheetCell = sheet.FindCell("Invoice number:")

        ' get barcode value as text of next row after "Invoice number:" cell
        Dim barcodeValue As String = sheet.FindCell(cell.ColumnNumber, cell.RowNumber + 1).Text

        ' create and setup barcode generator
        Dim barcodeGenerator As New Vintasoft.Barcode.BarcodeWriter()
        barcodeGenerator.Settings.Barcode = Vintasoft.Barcode.BarcodeType.QR
        barcodeGenerator.Settings.SetWidth(200)
        barcodeGenerator.Settings.Value = barcodeValue

        ' generate barcode image
        Using barcodeImage As New Vintasoft.Imaging.VintasoftImage(barcodeGenerator.GetBarcodeAsVintasoftBitmap(), True)
            ' crop barcode to the rectangular image
            barcodeImage.Crop(New System.Drawing.Rectangle(0, 0, barcodeImage.Width, barcodeImage.Width))

            ' set barcode image to the DOCX image at index 0
            editor.Images(0).SetImage(barcodeImage)
        End Using

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

OpenXmlDocumentChart class

OpenXmlDocumentChart represents a chart of DOCX or XLSX document:

get the chart title - OpenXmlDocumentChart.Title
get the chart properties - OpenXmlDocumentChart.Properties
get the chart data - OpenXmlDocumentChart.ChartData

Send Feedback